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:
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:
| 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 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 from a nCORE datasource using a stored procedure. The 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
...
| language | sql |
|---|---|
| title | Populating nForm Lookups and Lookup Values from a Source Table |
...
)
...
.
...