Page Comparison

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

...

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.

...

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.

...

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.

...

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.

...

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.

...

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.

...