Populating nFORM Lookups from nCORE

Owned by Will Rensmith (Unlicensed)
Last updated: Jan 17, 2024 by Will Rensmith

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:

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

Creating an nFORM Lookup from a Site Alternative Name Type

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