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:

Image Removed

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:

Image Removed

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:

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

...

...

Creating an nFORM Lookup from a Site Alternative Name Type

Anchor
Site_Alt_Name_Lookup Site_Alt_Name_Lookup

nVIRO can be configured to create an nFORM lookup from a Site Alternative Name Type, and can be configured on an Alternative Name Type basis. The lookup can provide submission preparers with a pre-defined list of sites to choose from in a type-ahead search, single select, or multi-select control. To accomplish this:

1. Find the desired Alternative Name Type Row in table REF_SITE_ALT_NAME_TYPE, and set IS_NFORM_LOOKUP = 1.
2. Updating this row will create a new entry in nForm.LOOKUP, where the LOOKUP.NAME = REF_SITE_ALT_NAME_TYPE.CODE.
3. At the same time, nForm.LOOKUP_VALUE will be populated with entries for each SITE_ALT_NAME record for the given type.
   1. LOOKUP_VALUE.NAME is set to the Site Alternative Name (SITE_ALT_NAME.SITE_NAME).
   2. LOOKUP_VALUE.DESCRIPTION is set to "Site Alternative Name - Site Name" (SITE_ALT_NAME.SITE_NAME + ' - ' + SITE.SITE_NAME).
4. When new Site Alternative Names are added in nCORE, corresponding nFORM lookup values are added in real-time.
5. Nightly, when the end date for a given Site Alternative Name passes, the corresponding nFORM lookup value is set to Inactive.
6. Nightly, when the start date for a given Site Alternative Name passes, the corresponding nFORM lookup value is set to Active.

See the following page for information on creating submission contacts from referenced nFORM Alternative Name lookups: nCORE - nFORM Data Integration Tags