Versions Compared

Version Old Version 3 New Version Current
Changes made by

Chris Tyler

Will Rensmith

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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 ControlImage 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:

Binding a Lookup Table to an nForm Single Selection ControlImage Removed

Info
titlePerformance 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. 

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 from a nCORE datasource using a stored procedure. The syntax is shown below:

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
Code Block
languagesql
titlePopulating nForm Lookups and Lookup Values from a Source Table
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
     ( 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 @REF_TABLE_NAME_COL_NAME = N'CODE',   --Column in source table to become the NAME column in nform LOOKUP_VALUE
            @USER_ID , -- CREATED_USER - uniqueidentifier           GETDATE() , -- UPDATED_DATE - datetime2           @USER_ID @REF_TABLE_DESCR_COL_NAME = N'DESCR', --Column UPDATED_USERin -source uniqueidentifiertable to become the DESCRIPTION column in nform LOOKUP_VALUE
  1  -- IS_AVAILABLE_FOR_DROPDOWN - bit           ;   INSERT INTO NFORM.LOOKUP_VALUE         ( LOOKUP_VALUE_ID ,           LOOKUP_ID ,
          NAME ,@IS_CONCAT_NAME_AND_DESCR = 1, --Set the DESCRIPTION in nform lookup to 'NAME-DESCR'
           DESCRIPTION ,           CREATED_DATE ,           CREATED_USER ,        @NFORM_LOOKUP_NAME =  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;

ENDN'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).

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