Once customers have started interacting with the nFORM application and have filled out and submitted some forms, you will want to be able to take a look at those form submissions and do something useful with that data. You may want to generate a report or maybe you would like to update information in another system or database. While you could query nFORM directly, it's important to remember that nFORM faces your customers and it's important to ensure that it remains responsive. Running big queries or generating reports is often taxing on nFORM and it's backing database server, it's best avoided. nFORM provides nTEGRATE with updates as changes occur in near real time, we can run reports and query against nTEGRATE's database instead.

This document assumes that you are comfortable with SQL, that you have a query tool installed (for instance, Microsoft SQL Server Management Studio) and that you can connect to your nTEGRATE database.

Terminology

Most of us think of a form as set of questions and submissions as a set of answers. nFORM and nTEGRATE manage forms and submissions in much the same way, however, we need to add some additional terms to keep things orderly. We need to think about the different versions of the form (the form version), it's common for questions to be added, updated or removed over the lifetime of a form. We also need to think about different version of each submission (the submission version); nFORM provides the ability for a customer to come back to a form they have submitted and correct erroneous information. When it comes to the various answers for each question in a form, we need to be able to tell the difference between an open text field and something more complicated and structured, like an address or a date.

Each submission has a version so that we can track the changes in the submission over time, although we are typically only interested in the most recently submitted version. A submission is related to a particular form, this is referred to as a form item. Each form, or form item, has it's own version to track changes in the form over time; again, we are typically interested in the version of the form that is most recent (unless we are looking at older submissions). Every submission (and every form item) has one or more sections and each section has a set of questions, which we call controls.

Each control has a label, this is what we usually think of as the question or entry on a form. The label might be something like "current address" or "most recent inspection date". Each control then has one or more associated control values, this is the information provided on the submission (the answer to the question). For a simple text field there will only be one "value" for the control: whatever the customer typed into the form. More complicated controls, like the address control, will contain several values (street, locality, postal code, etc.)

Lastly all of the above may have a tag, something that makes it easier to find and categorize the data. Tags are typically short and have a concise name like "current_contact" or "case_history". They are most often used as shortcuts to find the specific information you are looking for. You may have a system that manages the address and contact information for your customers, to make it easier to get that data out of nTEGRATE you could query for all of the sections that have the tag "current_contact" from all of the recent form submissions and use that to update your customer contact system.

Topics Covered on This Page

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.

Query Submission Headers

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.

Query for Submission Data

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.

SubmissionNumber	SectionName	SectionTag	ControlTag	ComponentType	ControlType	ControlLabel	ControlName	ControlValue
HP6-RW23-WGC11	Inspection	INSPECTION	INSPECTOR	input	controlValue	Person who inspected facility	INSPECTOR_controlValue	Jonathan Winters

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

SubmissionNumber	SectionName	SectionTag	ControlTag	ComponentType	ControlType	ControlLabel	ControlName	ControlValue
HP6-RW23-WGC11	Inspection	INSPECTION	FAC_ADDRESS	address	street1	Facility Address	FAC_ADDRESS_street1	32 West Street
HP6-RW23-WGC11	Inspection	INSPECTION	FAC_ADDRESS	address	locality	Facility Address	FAC_ADDRESS_locality	Portland
HP6-RW23-WGC11	Inspection	INSPECTION	FAC_ADDRESS	address	areaCode	Facility Address	FAC_ADDRESS_areaCode	OR
HP6-RW23-WGC11	Inspection	INSPECTION	FAC_ADDRESS	address	postalCode	Facility Address	FAC_ADDRESS_postalCode	29883

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.

Control Type	Description
controlValue	The text supplied by the customer

Multiple Selection List

A drop down list (or set of check-boxes) where the customer may select one or more items.

Component Type: selectMulti

Control Type	Description	Notes
name	The name of the selected option	Comma delimited list of selected valued
select	The unique code of the selected item	Comma delimited list of item codes, typically business keys
selectKey	A unique identifier used by nFORM	Comma delimited list of nFORM unique identifiers

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

SubmissionNumber	SectionName	SectionTag	ControlTag	ComponentType	ControlType	ControlLabel	ControlName	ControlValue
HP6-RW23-WGC11	Inspection	INSPECTION	EQUIP	selectMult	name	Equipment Inspected	EQUIP_name	TNK,PIP,ALRM
HP6-RW23-WGC11	Inspection	INSPECTION	EQUIP	selectMult	select	Equipment Inspected	EQUIP_select	Tank,Piping,Alarm
HP6-RW23-WGC11	Inspection	INSPECTION	EQUIP	selectMult	selectKey	Equipment Inspected	EQUIP_selectKey	11885854...,12923829...,...

Control Type	Description
controlValue	The text supplied by the customer

Single Selection List

A drop-down list (or a set of radio buttons) where the customer may select one and only one item.

Component Type: select

Control Type	Description	Notes
name	The name of the selected option	This is the value the customer saw and selected
select	The unique code of the selected item	This is the code associated with the item, most often this is a business key
selectKey	A unique identifier used by nFORM

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

SubmissionNumber	SectionName	SectionTag	ControlTag	ComponentType	ControlType	ControlLabel	ControlName	ControlValue
HP6-RW23-WGC11	Inspection	INSPECTION	EQUIP	select	name	Equipment Inspected	EQUIP_name	TNK
HP6-RW23-WGC11	Inspection	INSPECTION	EQUIP	select	select	Equipment Inspected	EQUIP_select	Tank
HP6-RW23-WGC11	Inspection	INSPECTION	EQUIP	select	selectKey	Equipment Inspected	EQUIP_selectKey	11885854-3e47-44bf-ac36-da77c5de3dcc

Social Security Number

This control collects a person's Social Security Number and it has the ability to mask out the everything but the last four characters of the supplied value (in the nFORM application, not the data stored).

Component Type: ssn

Control Type	Description
controlValue	The Social Security Number supplied by the customer

Formatted Controls

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

Control Type	Description
controlValue	The date supplied by the customer in the format MM/DD/YYYY

Control Type	Description
controlValue	The email address supplied by the customer

Control Type	Description
controlValue	The number supplied by the customer

Control Type	Description
controlValue	The phone number supplied by the customer

Control Type	Description
controlValue	The time supplied by the customer

Control Type	Description
controlValue	The URL supplied by the customer

Advanced Controls

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

Address

The address control collects and stores information about an postal address.

Component Type: address

Control Type	Description
street	The first line of the street address
locality	The town
areaCode	The state or province abbreviation
postalCode	The ZIP or ZIP+4 code

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

SubmissionNumber	SectionName	SectionTag	ControlTag	ComponentType	ControlType	ControlLabel	ControlName	ControlValue
HP6-RW23-WGC11	Inspection	INSPECTION	FAC_ADDRESS	address	street1	Facility Address	FAC_ADDRESS_street1	32 West Street
HP6-RW23-WGC11	Inspection	INSPECTION	FAC_ADDRESS	address	locality	Facility Address	FAC_ADDRESS_locality	Portland
HP6-RW23-WGC11	Inspection	INSPECTION	FAC_ADDRESS	address	areaCode	Facility Address	FAC_ADDRESS_areaCode	OR
HP6-RW23-WGC11	Inspection	INSPECTION	FAC_ADDRESS	address	postalCode	Facility Address	FAC_ADDRESS_postalCode	29883

Attachment

The attachment control allows the customer to attache one or more documents to a submission.

Component Type: attachment

Control Type	Description
fileId	Unique identifier, pointer into the "section_control_file" table
fileName	The name of the attached file
attachmentComment	Comment for the file
confidential	Flag indicating if this file is deemed "confidential" by the customer
confidentialReason	Text explaining why the customer believes the attachments are confidential

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

SubmissionNumber	SectionName	SectionTag	ControlTag	ComponentType	ControlType	ControlLabel	ControlName	ControlValue
HP6-RW23-WGC11	Inspection	INSPECTION	REPORT	attachment	fileId	Report	REPORT_fileID	1807797f-7f4c-40a2-9337-ff8dc7e8810b
HP6-RW23-WGC11	Inspection	INSPECTION	REPORT	attachment	fileName	Report	REPORT_fileName	Inspection Report 12321.docx
HP6-RW23-WGC11	Inspection	INSPECTION	REPORT	attachment	attachmentComment	Report	REPORT_attachmentComment	In progress, will be finalized soon

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

Query for Section File

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

Query for File Content

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

Query for File Info

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.

Contact

This control collects all of the information about a contact: their name, title, phone, email, address, etc.

Component Type: contact

Control Type	Description
titleContact	The business title of the contact
firstNameContact	First name of the contact
middleNameContact	Middle name of the contact
lastNameContact	Last name of the contact
phoneTypeContact	The type of phone (business, mobile, etc.)
phoneContact	The phone number
extensionContact	The extension for the phone number
contactStreet	First line of the street address
contactStreet2	Second line of the street address
localityContact	City for the address
areaCodeContact	State or province abbreviation for the address
postalCodeContact	ZIP or ZIP+4 of the address
countryCodeContact	The country for the address

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.

SubmissionNumber	SectionName	SectionTag	ControlTag	ComponentType	ControlType	ControlLabel	ControlDisplay SortOrder	ControlName	ControlValue
HP6-RW23-WGC11	Inspection	INSPECTION	OWNER	contact	titleContact	Owner	0	OWNER_titleContact	CEO
HP6-RW23-WGC11	Inspection	INSPECTION	OWNER	contact	firstNameContact	Owner	0	OWNER_firstNameContact	Howard
HP6-RW23-WGC11	Inspection	INSPECTION	OWNER	contact	lastNameContact	Owner	0	OWNER_lastNameContact	Hughes
HP6-RW23-WGC11	Inspection	INSPECTION	OWNER	contact	phoneTypeContact	Owner	0	OWNER_phoneTypeContact	Business
HP6-RW23-WGC11	Inspection	INSPECTION	OWNER	contact	phoneContact	Owner	0	OWNER_phoneContact	413-333-2323
HP6-RW23-WGC11	Inspection	INSPECTION	OWNER	contact	extensionContact	Owner	0	OWNER_extensionContact	321
HP6-RW23-WGC11	Inspection	INSPECTION	OWNER	contact	phoneTypeContact	Owner	1	OWNER_phoneTypeContact	Mobile
HP6-RW23-WGC11	Inspection	INSPECTION	OWNER	contact	phoneContact	Owner	1	OWNER_phoneContact	413-333-2323

Calculated

This control contains a formula that is calculated when the submission is filed. These formulas may use values of other controls in the form and may, optionally, be hidden from the customer.

Component Type: calculated

Control Type	Description
controlValue	The result of the calculation

Control Type	Description
controlValue	The value of the field

Location

A field that collects a latitude and longitude of a location.

Component Type: location

Control Type	Description
location	The latitude and the longitude (in that order) separated by a comma

Control Type	Description
titleValue	The business title of the person
fullNameValue	The full name of the person

Table

The customer may paste in a chunk of data that they copy out of an Excel (or Excel compatible) spreadsheet.

Component Type: table

Control Type	Description
table	Tab delimited text containing the table data

Table, Advanced

The customer may add and remove rows with a fixed number of columns and types for those columns.

ComponentType: grid

Control Type	Description
Tag of the column	The control type will contain the column in the grid that corresponds to the value in the "ControlValue" column

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.

SubmissionNumber	SectionName	SectionTag	AdvancedTableRow SortOrder	ControlTag	ComponentType	ControlType	ControlLabel	ControlName	ControlValue
HP6-RW23-WGC11	Income	PREV_4_QS	0	INC	grid	QUARTER	Prev. 4 Quarters	INC_QUARTER	1
HP6-RW23-WGC11	Income	PREV_4_QS	0	INC	grid	INCOME	Prev. 4 Quarters	INC_INC_QUARTER	1300.00
HP6-RW23-WGC11	Income	PREV_4_QS	0	INC	grid	COUNTRY	Prev. 4 Quarters	INC_COUNTRY	US
HP6-RW23-WGC11	Income	PREV_4_QS	1	INC	grid	QUARTER	Prev. 4 Quarters	INC_QUARTER	2
HP6-RW23-WGC11	Income	PREV_4_QS	1	INC	grid	INCOME	Prev. 4 Quarters	INC_INC_QUARTER	999.00
HP6-RW23-WGC11	Income	PREV_4_QS	1	INC	grid	COUNTRY	Prev. 4 Quarters	INC_COUNTREY	US

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.