Lookup tables can be used to populate drop down boxes and typeahead autocomplete textbox fields on nForm forms.

Binding Lookups to Form Controls

To bind a lookup table to a Short Text control, select the lookup table from the Data Source drop down menu in the auto-complete settings for the control:

Binding a Lookup Table to an nForm Short Text Control

To bind a lookup table to a Single Selection or Multiple Selection control, choose a Data Source Type = "Dynamic", and then select the lookup table from the Dynamic Data Source drop down box:

Binding a Lookup Table to an nForm Single Selection Control

Performance Tips for Large Lookups

If you have a large lookup table with more than 50 entries, it is recommended that the Short Text control is used. Slow performance can occur when large lookups are bound to a Single Selection or Multiple Selection Control since the entire lookup table is embedded in the form data which must be downloaded to the user's browser.

In contrast, when a Short Text control is used, lookup values are dynamically loaded from the server as the user types, reducing the amount of data sent to the user's browser.

Creating Lookup Tables

Lookup tables must be loaded into the nForm database by inserting directly into the nform.LOOKUP and nform.LOOKUP_VALUE tables.

The nform.LOOKUP table provides a name for the lookup table as displayed in the Data Source drop downs shown above

The nform.LOOKUP_VALUES table will contain a row for each of your lookup values. Note that lookup values can be inactivated by setting the IS_ACTIVE field to 0.

The refreshing of lookup values can be done as part of a periodic manual or automated process.

Using a Stored Procedure to Automatically Create and Update nFORM Lookups

Starting with nCORE release 2018.06 a new stored procedure has been created to quickly and easily create or update a set of nFORM lookups. Syntax is shown below

nFORM Lookup Population Stored Procedure

EXEC nsuite_dev.dbo.SP_ADD_NFORM_LOOKUP_FROM_REF_TABLE @REF_TABLE_NAME = N'REF_SIC', --Source lookup table name. schema-qualify if needed
                                            @REF_TABLE_NAME_COL_NAME = N'CODE',   --Column in source table to become the NAME column in nform LOOKUP_VALUE
                                            @REF_TABLE_DESCR_COL_NAME = N'DESCR', --Column in source table to become the DESCRIPTION column in nform LOOKUP_VALUE
                                            @IS_CONCAT_NAME_AND_DESCR = 1, --Set the DESCRIPTION in nform lookup to 'NAME-DESCR'
                                            @NFORM_LOOKUP_NAME = N'SIC_CODES' --The Name of the nFORM Lookup list to create or update. It will be created if it does not exist

Nightly refreshes of one or more nFORM lookup tables can be accomplished by adding the procedure call above to a nightly batch procedure, such as SP_BATCH_UPDATE_CSTM (client-specific).

Using a Script to Manually Create nFORM Lookups

The script below can be used for a one-time loading of a new lookup

Populating nForm Lookups and Lookup Values from a Source Table

BEGIN

DECLARE @LOOKUP_ID UNIQUEIDENTIFIER;
DECLARE @USER_ID UNIQUEIDENTIFIER; 

SELECT @LOOKUP_ID=NEWID()
SELECT @USER_ID = PRINCIPAL_ID from NFORM.PRINCIPAL where LOGIN = 'DATAINIT'; -- This is the nFORM login used for automated operations


INSERT INTO NFORM.LOOKUP
        ( LOOKUP_ID ,
          NAME ,
          CREATED_DATE ,
          CREATED_USER ,
          UPDATED_DATE ,
          UPDATED_USER ,
          IS_AVAILABLE_FOR_DROPDOWN
        )
SELECT @LOOKUP_ID , -- LOOKUP_ID - uniqueidentifier
          N'MyName' , -- NAME - nvarchar(255)
          GETDATE() , -- CREATED_DATE - datetime2
          @USER_ID , -- CREATED_USER - uniqueidentifier
          GETDATE() , -- UPDATED_DATE - datetime2
          @USER_ID , -- UPDATED_USER - uniqueidentifier
          1  -- IS_AVAILABLE_FOR_DROPDOWN - bit
          ;
 
INSERT INTO NFORM.LOOKUP_VALUE
        ( LOOKUP_VALUE_ID ,
          LOOKUP_ID ,
          NAME ,
          DESCRIPTION ,
          CREATED_DATE ,
          CREATED_USER ,
          UPDATED_DATE ,
          UPDATED_USER 
         -- , IS_ACTIVE
        )
SELECT NEWID() , -- LOOKUP_VALUE_ID - uniqueidentifier
          @LOOKUP_ID , -- LOOKUP_ID - uniqueidentifier
          MySourceTable.CODE , -- NAME - nvarchar(600)
          MySourceTable.DESCR , -- DESCRIPTION - nvarchar(600)
          GETDATE() , -- CREATED_DATE - datetime2
          @USER_ID , -- CREATED_USER - uniqueidentifier
          GETDATE() , -- UPDATED_DATE - datetime2
          @USER_ID ,  -- UPDATED_USER - uniqueidentifier
         1  -- IS_ACTIVE - bit
FROM MySourceTable;

END

Creating nFORM Lookup Tables

Binding Lookups to Form Controls

Creating Lookup Tables

Using a Stored Procedure to Automatically Create and Update nFORM Lookups

Using a Script to Manually Create nFORM Lookups