Loading a Dynamic (Lookup) Data into nTEGRATE

When you are designing a form in nFORM, many of the form controls will work with a "typed in" data source. Typically these are lists that contain a code of some kind and then a description. When a customer is filling out the form, they will be presented with a list of options and will select the description they feel is most accurate, behind the scenes the code will be stored in the submission. These are often referred to as lookup list or table, an example lookup table is presented below, this one provides a list of permit-able water recreation features

Many lists are short or static, typing them in by hand is reasonable. Other lists are longer or change more often and may be better served by being backed by a "dynamic datasource". With a dynamic data source you don't type in the values, instead these values are loaded into nFORM from it's backing database. The values in nFORM's backing database are loaded by nTEGRATE either programmatically or on a set schedule, after that they are available for use forms. As these tables are changed an updated in nTEGRATE these changes will be propagated out to nFORM and will always be up-to-date.

The term "dynamic data source" is vague and may be unfamiliar to you. For the rest of this document we will refer to this kind of data as a "lookup table". Below is the table of contents for this document, if you are familiar with how lookup tables work you can jump right to the section you need.

Naming your Lookup Table

There are some nFORM installations that are dedicate to the use of one department and others for an entire organization; we even have some nFORM installations that service the entire state! When we are naming things in nFORM it's important to keep in mind that the instance you are working with could be promoted at any time. The nFORM instance dedicated to the Underground Storage Tank program might be used by the entire state-wide Environmental organization in the future. Likewise, it might someday be used by the entire state.

Our advice is to think about what level your lookup table. Ask yourself the following questions...

• Could this lookup table be used in other forms?
• What about using this table in other departments?
• Does this lookup make sense state-wide?

In general, name your table from the organization level down. If you had a table of permit-able recreation features for residential yards (pools, hot tubs, small water parks, etc.) for the water recreation department of the state housing authority, you might name that lookup table "Housing Authority, Water Recreation: Permit-table Feature". If the nFORM instance was ever promoted to cover more than one organization or even the whole state the name of your lookup table will still make sense.

How Lookup Tables are Stored in nTEGRATE's Database

The nTEGRATE product stores lookup table data in two separate tables; the "lookup_stage" table contains data about the list as a whole (like it's name) and the "lookup_value_stage" table contains all of the individual values for each lookup table. These two tables are linked by the value in the "lookup_stage_id" column of the "lookup_stage" table; each row in the "lookup_value_stage" table that has the same "lookup_stage_id" value is part of the same lookup table.

The process is straightforward...

• Create or update the row in the "lookup_stage" table
• Create or update the rows in the "lookup_value_stage" table
• Set the "send" value on the "lookup_stage" table for your lookup table's row to "1"

When we set the "send" column for a "lookup_stage" row to "1", we're letting nTEGRATE know that we're done updating that lookup table and it should send that data over to nFORM. Typically this happens on a set schedule and could be as often as every fifteen minutes or as long as every evening.

We can load this data in by leveraging nTEGRATE's RESTful API or we can manually insert and update the data in nTEGRATE's database. We'll cover the manual process first.

Loading a Lookup Table into nTEGRATE Manually with SQL

While it is uncommon to manually load a lookup table into nTEGRATE (after all, you could just type the same data into nFORM directly) it is helpful to start with the simplest possible scenario and then to crank up the complexity once the fundamentals have been established. Let's stick with our permit-able residential recreation feature example, first we'll create our new lookup table by inserting a row into the "lookup_stage" table of the nTEGRATE database.

INSERT INTO nform.lookup_stage 
       (lookup_stage_id, name, data, is_available_for_dropdown,
        created_date, created_user)
VALUES (NEWID(), 'HCD, Water Rec.: Permitable Feature', 1,
        CURRENT_TIMESTAMP, '44183257-DE20-45DF-BDB6-6B3A2C79E98B');

We create a new unique identifier for our lookup stage with the "newid()" function and then pass in the name. This list isn't super long and it's appropriate to display it as a drop-down menu, so we set the "is_available_to_dropdown" flag to "1". If you had a really long list you could set this to "0"; nFORM would then let people use the type-ahead function to find the lookup value they need. Next we set the created date to the current time and we use the literal unique identifier of a "principal" in the nFORM system for the "created_user" column.

The "created_user" column cannot be null and it needs to be a valid principal identifier. You can take a look at the "Principal" table in the nTEGRATE database to find a value that suits your purpose, typically there's one named "nTEGRATE" that you may use.

Now that our new table has been created, we need to get it's unique identifier so we can use that when we create the items in our table.

SELECT lookup_stage_id
FROM nform.lookup_stage
WHERE name = 'HCD, Water Rec.: Permitable Feature';

This will return the unique identifier for our table, note it so that you can use it when we insert the items for this table. We'll do that next!

INSERT INTO nfom.lookup_value_stage
       (lookup_value_stage_id, lookup_stage_id, name,
        description, created_date, created_user)
VALUES (NEWID(), 'F5C296B1-3AB9-48BC-9006-16801070F4D3',
        '001', 'Pool, Above Ground', CURRENT_TIMESTAMP,
        '44183257-DE20-45DF-BDB6-6B3A2C79E98B');
INSERT INTO nfom.lookup_value_stage
       (lookup_value_stage_id, lookup_stage_id, name,
        description, created_date, created_user)
VALUES (NEWID(), 'F5C296B1-3AB9-48BC-9006-16801070F4D3',
        '002', 'Pool, In Ground', CURRENT_TIMESTAMP,
        '44183257-DE20-45DF-BDB6-6B3A2C79E98B');
INSERT INTO nfom.lookup_value_stage
       (lookup_value_stage_id, lookup_stage_id, name,
        description, created_date, created_user)
VALUES (NEWID(), 'F5C296B1-3AB9-48BC-9006-16801070F4D3',
        '002', 'Pool, Kiddie', CURRENT_TIMESTAMP,
        '44183257-DE20-45DF-BDB6-6B3A2C79E98B');
INSERT INTO nfom.lookup_value_stage
       (lookup_value_stage_id, lookup_stage_id, name,
        description, created_date, created_user)
VALUES (NEWID(), 'F5C296B1-3AB9-48BC-9006-16801070F4D3',
        '003', 'Slip and Slide', CURRENT_TIMESTAMP,
        '44183257-DE20-45DF-BDB6-6B3A2C79E98B');

We then insert our child values with the four "insert" statements above. For each one we use the "newid()" function to create a new unique identifier for our item and then we provide the unique identifier of our lookup table. Next we insert the code (or "Name", as nTEGRATE calls it) for our item and it's human-friendly description. Lastly we set the "created_date" to the current data and time and provide the unique identifier of a "principle" for the "created_user".

At this point we have our new lookup table and all of it's associated values established in the database, we can now set the "send" flag to indicate that nTEGRATE can send it over with its next batch of updates.

UPDATE nform.lookup_stage
SET send = 1
WHERE lookup_stage_id = 'F5C296B1-3AB9-48BC-9006-16801070F4D3';

After we execute the "update" statement above, the "send" flag will now be set to "1". When nTEGRATE next runs it's update process (it runs on a regular schedule) it will submit that data to nFORM and the lookup table will be either created or updated in the nFORM system. Once that is complete you will be free to choose "HCD, Water Rec.: Permitable Feature" as a "Dynamic Data Source" for your input field control in nFORM.

Importing Data from Another Database Table

Sometimes the lookup table you want to import into nTEGRATE is already present in another database and table on the database server or it's on a database server that is linked to the nTEGRATE database server. In this case you can often import that data directly into nTEGRATE from the source database and table. First we will need to check and see if the lookup table is already present. If it isn't, then we want to create it.

BEGIN  
  IF NOT EXISTS (
    SELECT lookup_stage_id    
    FROM nform.lookup_stage    
    WHERE name = 'HCD, Water Rec.: Permitable Feature')  
  BEGIN    
    INSERT INTO nform.lookup_stage
           (lookup_stage_id, name, data, is_available_for_dropdown,
            created_date, created_user)
    VALUES (newid(), 'HCD, Water Rec.: Permitable Feature', 1,
            current_timestamp, '44173257-DE20-45DF-BD86-6B3A2C79E98B')
  END
END;

Now that we're sure our lookup table is present, the first step is to delete any existing lookup data. If you have a really, really long lookup list you might want to take a different tack (for instance, you could MERGE in just the changed values. For now we're going to keep it simple.

WITH lookup_table AS (
  SELECT lookup_stage_id, name
  FROM nform.lookup_stage 
  WHERE name = 'HCD, Water Rec.: Permitable Feature')
DELETE FROM nform.lookup_value_stage
where lookup_stage_id in (select lookup_stage_id from lookup_table);

The query above find the unique identifier of our lookup table and then deletes any exiting child rows. Next we can insert our new rows...

WITH lookup_table AS (
  SELECT lookup_stage_id, name
  FROM nform.lookup_stage 
  WHERE name = 'HCD, Water Rec.: Permitable Feature'),
items as (
  SELECT lookup_table.lookup_stage_id           AS lookup_stage_id,
         source_table.code                      AS name,
         source_table.description               AS description,
         current_timestamp                      AS created_date,
         '44183257-DE20-45DF-BDB6-6B3A2C79E98B' AS created_user
  FROM ma_hcd.dbo.RecFeatureCodes AS source_table, lookup_table),
INSERT INTO nform.lookup_value_stage
	(lookup_value_stage_id, lookup_stage_id, name, description, 
	created_date, created_user)
SELECT * from items);

We select the values from the "RecFeatureCodes" table from the "ma_hcd" database into the format that we need and then insert that data into the nTEGRATE "lookup_value_stage" table. There are other ways to organize your query but we believe this way makes the query easier to understand.

In this example we are doing this work manually but you could easily make this a scheduled job, or put it in a stored procedure that is called by a scheduled job. In this way, your lookup table data will always be up-to-date and match the data from the source database.

Sending the Lookup Data to nFORM

Your lookup table data won't be sent over to nFORM until the "send" flag is set on rows in the "lookup_stage" database. Typically you'd set the send flag after you have populated all of the values for the lookup table.

UPDATE lookup_stage SET send = 1
WHERE name = 'HCD, Water Rec.: Permitable Feature'

Once the flag is set, nTEGRATE will flow the table on it's next run. Typically nTEGRATE will look for new lookup data to flow every five minutes, you shouldn't have to wait long! When it does flow the tables it will do it one lookup table at a time. You can monitor the progress of the data flowing over to nFORM with the following query.

SELECT COUNT(*) AS 'Lookup items left to send'
FROM nform.LOOKUP_STAGE WHERE send = 1;

Write Your Lookup Data in a Stored Procedure

While you can put queries together to create and update your lookup table data, we recommend that you create a stored procedure to do this work. During development you might run the procedure several times as mistakes are noticed or the data changes. Once you're out of development it's very likely that the customer will want to run the procedure on a set schedule to ensure that the lookup tables in nFORM match the data in their source system.

Starting with a stored procedure in the first place will make both tasks much easier.