Processing Submission Data

Database Structure

If you have looked at the nFORM database, the nTEGRATE database will seem familiar. To the degree that it's possible and makes sense, the nTEGRATE database mirrors the nFORM database. All of the tables, views and stored procedures are located in the "nForm" schema and many of these have names similar to those used by nFORM. We can interrogate our submission data by working with these tables directly but we have created a set of views that will make this easier.

Submission Views

Every nTEGRATE installations comes with a set of eight views that provide easy access to your submission data, their names are pretty reasonable. The "VW_Submission_Header" view contains summary information about each submission (who submitted it, what form was submitted, when was it submitted, etc.) The "VW_Controls_Data_Cleansed" table contains all of the data for each submission (answers to the specific questions, tags for those questions, and so on). Information about notes related to the submission are located in the "VW_Submission_Notes_View", likewise meta-data about the controls are in the "VW_Submission_Meta_Controls_Data" view. Lastly all of the data about the various processing steps of each submission and the history of the various statuses that each submission has transitioned through are in the "VW_Processing_Steps" and the "VW_Submission_Status_History" views.

Submission Header

The view "VW_Submission_Header" contains one row for every submission that has been processed by nFORM. Take a moment to query the view in your database and take a quick look at the data.

select top 5 * from nForm.VW_SUBMISSION_HEADER order by LatestSubmissionDate desc;

The query above will return the five most recent submissions in your database. The interesting columns here are...

• Sub_Id: The unique identifier for the submission
• Tag: The tag for the form that was submitted
• Organization_Code: The code of the organization of the form
• OrganizationName: The name of the organization
• SubmissionNumber: The human readable submission number
• FormName: The name of the form
• ApplicantName: The name of the applicant
• ApplicantLogin: The login of the application
• LatestSubmissionDate: The date the submission was most recently updated
• CurrentStatus: The status of the submission

Submission Data

The data for each submission is available through the view "VW_SUBMISSION_CONTROLS_DATA_CLEANSED", for each value of each control of each submission there will be a row in this table with the data. This table ends with "...DATA_CLEANSED" because some sanitization has been performed to eliminate control characters and other bits of data that may cause trouble when querying or producing reports.

In the previous section we queried nTEGRATE for the five most recent submissions, go ahead and run that query again. Take a look at these submissions and copy the "SUB_ID" value for one of them. Run the query below, inserting the "SUB_ID" that you have chosen.

select * from nform.VW_SUBMISSION_CONTROLS_DATA_CLEANSED
where SUB_ID = '71AA2437-A050-4004-9063-1B9829451202'    -- use your sub_id ;-)
order by SectionSortOrder, ControlSortOrder, ControlDisplayOrder, AdvancedTableRowSortOrder;

The result set will contain all of the values for all of the controls for the submission. These values will be in order by section (the first section of the form will be first) then by the order in which each control appears in the section and then, lastly, the order of the field in the control. This result set is quite wide but if you scroll across you will see the submission data at the far right-hand side. The most interesting columns here are...

• SubmissionNumber: The human readable submission number
• SectionName: The name of the section
• SectionTag: The tag of the section
• ControlTag: The tag on the individual control
• ComponentType: The type of the control (address, contact, select, selectMult, etc.)
• ControlType: Confusingly, this is the type of the field in the control, not the control itself (street, locality, etc.)
• ControlLabel: The label for the control ("Previous Address", "Business Phone", etc.)
• ControlName: The name of the field of the control, this is generated by nFORM by combining the tag on the control with the control type
• ControlValue: The value supplied by the customer when they filled out the form

With this information in hand, you should be able to parse out the data that the customer has provided with their submission. First, looking to the "SectionTag" column we can see what section these values are coming from. Next we can move to the "ControlTag" to see which question in the form the customer is answering. Looking at the "ComponentType" column we can see if we should expect one value (for instance an input) or a set of values (in the case of an address). Lastly we can consult the "ControlType" to get the type of field in the control and the "ControlValue" which holds the value supplied by the customer.

This data layout can take some getting used to but with practice you will find that you are able to scan through sets of submission data and pick out items that you want to use for further processing or seem erroneous. Below is an example of an "input" control. For the sake of clarity we have only listed the fields that we outlined in the list above.

Many controls collect several values, below is an example of the data collected by the address control.

You will find that the "VW_SUBMISSION_CONTROLS_DATA_ALL" view contains the same information, however, the data in that view has not been sanitized and could contain troublesome control characters. It is best avoided.

Controls and Their Data

Listed below are details on the various controls and their fields. You may consult this table to find out where your data will appear in the views mentioned above. The values provided in the submission are always returned in the "ControlValue" column.

Simple Controls

These controls typically collect one value.

Here is an example of the values collected with a multiple selection control.

Below is an example of the data collected by a single selection list.

Formatted Controls

These controls typically collect one value but may format it differently, depending on the data (date, time, etc).

Advanced Controls

These controls collect groups of data (an address, contact information, etc) and semi-structured data like a table.

The sample output below demonstrates what you can expect to find for an Address control.

If the customer uploads more than one document, the fileId and fileName fields will contain a comma separated list of values.

Download the Attachments through nFORM or the nTEGRATE API

In all cases the easiest way to get a copy of the attached file is to either download the submission from the nFORM application (find the submission and click on the "Download" button) or to fetch the files programatically through the REST-ful API provided by nTEGRATE. Alternatively you can find the data attached to the file by following the directions below.

Manually Extract Attachments from the Database and File System

Depending on your installation, the content of the document may be stored either in the nTEGRATE database itself or on disk at a location configured by your administrator. The contents of the file uploaded by the customer are arbitrary and, in some cases, quite large; for this reason we encourage storing these attachments on disk. The "ControlValue" in the "fileId" row points to a row in the "section_control_file" table. You may query this data like so...

select * from nform.section_control_file where section_control_file_id = '1807797f-7f4c-40a2-9337-ff8dc7e8810b';

The query will return the matching row from the table, the "file_content_id" column is a pointer to a row in the "file_content" table. You can take that value and use it to query the "file_content" table...

select * from nform.file_content where file_content_id = '915FAF32-494D-4A5A-A597-20D76FC2059A';

If the attachments are stored in the nTEGRATE database itself, you will find the data for the file stored in the "content" column of this table as a binary object. If your installation is storing attachments on disk, the value in the "file_info_id" column is a pointer to a row in the "FileInfos" table. Query that table for more information...

select * from nform.FileInfos where file_info_id = '91167EA1-1BDE-4D3B-A5AF-08D8D9AC9A97';

The value in the "Reference" column represents the name of the file stored on disk. You may now navigate to this location, you will find the file in a directory named after the first four characters in the reference (for example, "fac3"). The file will have the same name as the value in the "Reference" value and no file extension.

When more than one phone number is provided, you can correlate their values with the value in the "ControlDisplayOrder" column. Below is example output for a contact with two phone numbers.

Be sure to include the "AdvancedTableRowSortOrder" column in your query (it should be last). This column contains an integer that indicates which row in the table to which the values belong. Below is example output that contains the values from a grid control that has three columns (quarter, income, and country) and two rows of data.

In this example the rows are in the order they appear in the grid (quarter, income, country) but this will not be the case for every query. We guarantee that each row with the matching value in the "AdvancedTableRowSortOrder" contains values for that row but the order returned in the query is not enforced. It's important to pick out the values you need by grabbing the values for the row based on the "AdvancedTableRowSortOrder" column and then to handle the individual values through the use of the column name in the "ControlType" column.