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:
...
| Info | ||
|---|---|---|
| ||
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 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
| Code Block | ||||
|---|---|---|---|---|
| ||||
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
| Code Block | ||||
|---|---|---|---|---|
| ||||
BEGIN
DECLARE @LOOKUP_ID UNIQUEIDENTIFIER;
DECLARE @USER_ID UNIQUEIDENTIFIER;
SELECT @LOOKUP_ID=NEWID()
SELECT @USER_ID = PRINCIPAL_ID from NFORM.PRINCIPAL where LOGIN = 'MyLogin'; -- (should this be some sort of system login?)
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 |