Guide for "WRK Table" Approach For Prepopulation

Owned by Ben Worley
Jun 05, 2024
7 min read

Using the four PREPOP_DATASET_* tables directly can be somewhat difficult to work with. Windsor has introduced a process to shield database developers from these tables to provide a more concise way to load both prepopulation data and lookup values. We call this the Work (WRK) Table approach. This provides abstraction from the PREPOP_DATASET_* tables and an audit/transaction log that is handy when troubleshooting.

To establish the WRK table approach, a new integration schema is created on the nTEGRATE database where the WRK tables and procedures will reside. The WRK table approach script will generate the schema, the WRK tables and the WRK procedures needed.

Loading Prepopulation Data

There are 3 steps to loading prepopulation data for use in nFORM forms.

  1. Clear the prepop WRK tables (by calling the integration.WRK_PREPOP_RESET stored procedure)

  2. Insert into the prepop WRK tables (WRK_PREPOP_DATA and optionally WRK_PREPOP_USER). This is performed by loading data from a program database.

  3. Merge the data from WRK tables into the PREPOP_DATASET_* tables (by calling the integration.WRK_PREPOP_PROCESSOR stored procedure).

For steps 1 and 3, the work is done, simply call the correct stored procedures. The real work occurs in step 2, which is loading the prepopulation data into WRK_PREPOP_DATA from a program database.

WRK_PREPOP_DATA

Table Definition:

 create table integration.WRK_PREPOP_DATA (
   ID uniqueidentifier
      constraint PK_WRK_PREPOP_DATA primary key
      constraint DF_WRK_PREPOP_DATA_ID default newid(),
   CONTEXT_TYPE nvarchar(255) not null,
   CONTEXT_KEY nvarchar(255) not null,
   SECTION_TAG nvarchar(255) not null,
   SECTION_SORT int not null
      constraint DF_WRK_PREPOP_DATA_SECTION_SORT default 0,
   CONTROL_TAG nvarchar(255) not null,
   CONTROL_TYPE nvarchar(255) not null,
   ROW_ORDER int not null
      constraint DF_WRK_PREPOP_DATA_ROW_ORDER default 0,
   FIELD_NAME nvarchar(255) not null,
   FIELD_VALUE nvarchar(max) not null,
   constraint UK_WRK_PREPOP_DATA unique (CONTEXT_TYPE, CONTEXT_KEY, SECTION_TAG, SECTION_SORT, CONTROL_TAG, ROW_ORDER, FIELD_NAME)
);

Definition of each column and what to load into each:

  • CONTEXT_TYPE - Specified at the form level in nFORM and is referred to as the Prepop Context Tag. Example: PERMIT_NUM

  • CONTEXT_KEY - What the user will enter to start the form. This is the “key” for prepopulating a form with the data that you’ve loaded into the WRK tables (e.g., permit number). Example: TNR009872

  • SECTION_TAG - The tag of the section the field is in. Example: FACILITY_INFO

  • SECTION_SORT - Only used for repeating sections. Specifies the numeric order of the repeating section, starting from 0. For example, if the sections are about a person, perhaps you'd want to have the sections ordered by the person's name. For fields not in repeating sections the SECTION_SORT is automatically defaulted to 0.

  • CONTROL_TAG - Tag for the control (field/question) to prepopulate. Not for advanced table control and data grid control types, this is the the tag of the overall table. (e.g., RESPONSIBLE_OFFICIAL)

  • CONTROL_TYPE - The type of control as defined by nTEGRATE. There are several types:

    • input - Simple text controls (short text, paragraph, number, date, SSN, etc.)

    • select - Single or multi-select drop-downs

    • address - Address control

    • contact - Contact control

    • location - Location control

    • grid - Advanced Table control

    • supergrid - Data Grid control

  • ROW_ORDER - Only used for advanced table (grid) and data grid (supergrid) controls. Used for specifying the table row the data is in, starting from 0. For fields not in a table the ROW_ORDER is automatically defaulted to 0.

  • FIELD_NAME - Further defines the control type, especially for more complex controls such as address, contact and table controls. Value options broken down by the control type (CONTROL_TYPE). Can have several values, for:

    • input = controlValue

    • select =

      • controlValue for the value

      • otherReason if the selected item is "Other", value for the "Please Describe" field.

    • address =

      • street - address line 1

      • street2 - address line 2

      • locality - town/city

      • areaCode - state (yes, confusing name)

      • postalCode - zip code

      • county - county

      • country - country

    • contact =

      • companyNameContact - When company/organization name is needed

      • firsNameContact - When individual name is needed

      • lastNameContact - When individual name is needed

      • titleContact - When the individual title is needed

      • emailContact - When the individual email is needed

      • streetContact - When the individual street (address line 1) is needed

      • street2Contact - When the individual street (address line 2) is needed

      • localityContact - When the individual town/city is needed

      • areaCodeContact - When the individual state (yes, confusing name) is needed

      • postalCodeContact - When the individual zip code is needed

      • countyContact - When the individual county is needed

      • countryContact - When the individual country is needed

      • phoneTypeContact_0 - A contact can have 1 or more phone type. 0 is the first phone type. If more than one phone type, then _1, _2, etc.

      • phoneContact_0 - A contact can have 1 or more phone number. 0 is the first phone number. If more than one phone number, then _1, _2, etc.

      • extensionContact_0 - A contact can have 1 or more phone extension. 0 is the first phone extension. If more than one extension, then _1, _2, etc.

    • location = mapCoord values loaded into FIELD_VALUE will be latitude and longitude, separated by comma

    • grid = This is the control tag for the column in the table. Example: TANK_NUM

    • supergrid = This is the control tag for the column in the table. Example: TANK_NUM

  • FIELD_VALUE - The most important piece, the actual value you want to prepopulate onto the form. Done as a string. For example, if the field is a numeric value (e.g., quantity), the value needs to be cast to a varchar. As noted, if CONTROL_TYPE is location then the latitude and longitude are comma delimited into this field.

WRK_PREPOP_USER

The WRK_PREPOP_USER prepop table is optional and is only necessary if the forms will have security. This is used for locking down available contexts, by-user, for those that will be completing the form in nFORM. If there is a security need to allow users to only see and use specific contexts, then it would require high-quality data in the WRK_PREPOP_USER table that ties users to contexts; nFORM will make use of that relationship before the user begins form entry so that they can only query for their user-specific context(s).

Table Definition:

CREATE TABLE [integration].[WRK_PREPOP_USER](
[CONTEXT_TYPE] nvarchar NOT NULL,
[CONTEXT_KEY] nvarchar NOT NULL,
[USER_EMAIL] nvarchar NOT NULL,
[USER_LOGIN] nvarchar NOT NULL,
[EDITOR_IND] [bit] NOT NULL,
[SUBMITTER_IND] [bit] NOT NULL
)

Definition of each column and what to load into each:

  • CONTEXT_TYPE - Specified at the form level in nFORM and is referred to as the Prepop Context Tag. Example: PERMIT_NUM

  • CONTEXT_KEY - What the user will enter to start the form. This is the “key” for prepopulating a form with the data that you’ve loaded into the WRK tables (e.g., permit number). Example: TNR009872

  • USER_EMAIL - This is the email of the user to whom contexts will be assigned in the database. It’s important to have high-quality data for these users in the database in cases where WRK_PREPOP_USER is used for security.

  • USER_LOGIN - This is the user’s login (username) to nFORM. This will be the email that they used to register for the system.

  • EDITOR_IND - This is a flag in the database (bit, not null) to flag the user as having submitting rights (only works when inject security roles is turned on - not common)

  • SUBMITTER_IND - This is a flag in the database (bit, not null) to flag the user as having submission editing rights (only works when inject security roles is turned on - not common)

Note: EDITOR_IND and SUBMITTER_IND are only used in tandem with the form-specific prefill setting, Allow injection of security roles. This allows individual users' permissions (editing, signing/submitting) on a given submission to be defined externally, using a pre-fill data source.

Example Process/Script

Below is a simple script that can be run to set up some prepop data into the PREPOP_DATASET_* tables. Note, step 1 as mentioned is to reset (purge) the data out of the WRK tables, so the WRK_PREPOP_RESET stored procedure is called. Then step 2 is to load the actual data from the database. Step 3 is to merge the data into the various PREPOP_DATSET_* tables by calling the WRK_PREPOP_PROCESSOR stored procedure.

exec integration.WRK_PREPOP_RESET;
insert into integration.WRK_PREPOP_DATA(CONTEXT_TYPE, CONTEXT_KEY, SECTION_TAG, CONTROL_TAG, CONTROL_TYPE, FIELD_NAME, FIELD_VALUE)
values ('NOTIF_NUM', 'AG123456', 'NOTIF_AND_DESIGN', 'NOTIFNUM', 'input', 'controlValue', 'AG123456'),
       ('NOTIF_NUM', 'AG123456', 'FACILITY_OWNER', 'BUILDINGOWNER', 'contact', 'companyNameContact', 'Acme Inc'),
  ('NOTIF_NUM', 'AG123456', 'FACILITY_OWNER', 'BUILDINGOWNER', 'contact', 'streetContact', '100 Main St'),
  ('NOTIF_NUM', 'AG123456', 'FACILITY_OWNER', 'BUILDINGOWNER', 'contact', 'localityContact', 'Manchester'),
  ('NOTIF_NUM', 'AG123456', 'FACILITY_OWNER', 'BUILDINGOWNER', 'contact', 'areaCodeContact', 'NH'),
  ('NOTIF_NUM', 'AG123456', 'FACILITY_OWNER', 'BUILDINGOWNER', 'contact', 'postalCodeContact', '03101'),
  ('NOTIF_NUM', 'AG123456', 'FACILITY', 'BUILDINGNAME', 'input', 'controlValue', '1ST FEDERAL BANK');

exec integration.WRK_PREPOP_PROCESSOR;

In terms of generating the data from Oracle, you can unpivot the data or you can do a cross apply. Here's a simple cross apply example:

select x.*
from TBL_CONTACTS c
cross apply (
select 'CONTACT' as SECTION_TAG, 'CONTACT_FIRST_NAME' as CONTROL_TAG, 'input' as CONTROL_TYPE, 'controlValue' as FIELD_NAME, c.FIRST_NAME as CONTROL_VALUE from dual union all
select 'CONTACT' as SECTION_TAG, 'CONTACT_LAST_NAME' as CONTROL_TAG, 'input' as CONTROL_TYPE, 'controlValue' as FIELD_NAME, c.LAST_NAME as CONTROL_VALUE from dual

) x;

Loading Lookup Data

When lists of data are needed in single-select and multiple-select controls in nFORM, the data can be loaded from a program database. This ensures when the value is selected by the applicant/end user, we can be assured that this value is valid and will match what is in the program database during submission processing/ETL.

There are 3 steps to loading prepopulation data for use in nFORM forms.

  1. Clear the lookup WRK tables (by calling the integration.WRK_LOOKUP_RESET stored procedure)

  2. Insert into the lookup WRK tables (WRK_LOOKUP_DATA). This is performed by loading data from a program database.

  3. Merge the data from WRK tables into the LOOKUP_DATASET_* tables (by calling the integration.WRK_LOOKUP_PROCESSOR stored procedure).

For steps 1 and 3, the work is done, simply call the correct stored procedures. The real work occurs in step 2, which is loading the prepopulation data into WRK_LOOKUP_DATA from a program database.

WRK_LOOKUP Table Definition:

CREATE TABLE [integration].[WRK_LOOKUP](
[LOOKUP_NAME] nvarchar NOT NULL,
[VALUE_CODE] nvarchar NOT NULL,
[VALUE_DESC] nvarchar NOT NULL)

Definition of each column and what to load into each:

  • LOOKUP_NAME - The name of the lookup, as it will appear in nFORM. Example: Waterbodies

  • VALUE_CODE - This is the code or ID that is sourced from the program database lookup/domain lists - if one exists. If one does not exist, this can be loaded with t he same value as VALUE_DESC. Example: WB100

  • VALUE_DESC - This is the value that the user will see in the actual dropdown in nFORM. Example: McKenzie River