This article describes how data is integrated between nFORM and nCORE for the purposes of both (1) prepopulation of new submissions and (2) population of nCORE data elements from new nFORM submissions.

Data Integration from nFORM into nCORE

Data provided in an nFORM submission is extracted and used to update data in nCORE. Data integration rules and behavior is largely driven by the Tag value assigned to nFORM Sections and Controls. This section describes how to use tags to control data integration behavior when a new submission is received.

Submission data is used to update Site, Contact, Submission, and Feature details. Specifics for each are described in the following sections.

Site Data Integration into nCORE (including Person and Organization)

Technical Note: nFORM control tag mapping from nFORM to nCORE is defined in nformExport.VW_SITE_EXPORT. Integration logic is defined in dbo.SP_NFORM_STG_TO_SUBM_SITE.

Site Control Tag to nCORE Site Database Field Mapping

The table below indicates form control Tag are used to

Control Tag	nCORE Target Field	Description	Updates Existing Site?
SITE_NUM	SITE.SITE_NUM		No
SITE_NAME	SITE.SITE_NAME	By default, the Site Name in nCORE will not update if a different site name is provided in an nFORM submission, however this behavior is configurable using the Allow form to update Site Name checkbox on the Form Details screen.	Configurable
SITE_TYPE	SITE_TO_REF_SITE_TYPE. REF_SITE_TYPE_CODE	This value is mapped to the Code or Description column from REF_SITE_TYPE. The row will only be inserted if the same Site Type does not already exist on the Site record. Only one Site Type is supported per submission.	Yes
SITE_ADDR	SITE.(Multiple Address Fields)	Populated from nFORM Address control. Data is parsed from this control into the individual Address fields on the Site Details page: SITE.ADDR_1 SITE.ADDR_2 SITE.CITY SITE.ZIP_CODE SITE.ADDR_CMNTS (nFORM Location Description field) SITE.REF_STATE_CODE - Tag name is misleading. The nFORM value is mapped to the Description column in REF_STATE SITE.REF_CNTY_CODE - Tag name is misleading. The nFORM value is mapped to the Description column in REF_CNTY. GIS-derived county takes precedent over user-supplied value SITE.REF_CNTRY_CODE - Tag name is misleading. The nFORM value is mapped to the Description column in REF_CNTRY Address data can be configured to create a geocoded “Site” Feature/coordinate in lieu of a location (map) control on the form. This requires the configuration of a the “Geocode Site Address” server task. See geocoding topic for more information.	Yes
SITE_ADDR_CMNTS	SITE.ADDR_CMNTS	This is a fallback if nFORM Address control's "Location Description" is not visible and populated.	Yes
Contact control tag SITE	SITE.PREFIX	Used for Person records	Yes
Contact control tag SITE	SITE.PERSON_FIRST_NAME	Used for Person records	Yes
Contact control tag SITE	SITE.PERSON_LAST_NAME	Used for Person records	Yes
Contact control tag SITE	Organization Name	NOT USED (yet…)	Yes
Contact control tag SITE	SITE.EMAIL	Used for Person records	Yes
Contact control tag SITE	PHONE table	Used for Person records
SITE_COORD	n/a	NOT USED. See FEATR mapping	n/a
SITE_LAT	n/a	NOT USED. See FEATR mapping	n/a
SITE_LONG	n/a	NOT USED. See FEATR mapping	n/a
SITE	FEATR_LOC table	When tagged on a location (map) nFORM control, the centroid lat/long point will be prepopulated on the submission	No
SITE_NAICS	SITE_TO_REF_NAICS table	This is the numeric NAICS code. Only one value is supported and the value must be an exact match to the CODE or CODE-DESCR (e.g. "236118" or "236118-Residential Remodelers") in REF_NAICS.	Yes
SITE_SIC	SITE_TO_REF_SIC table	This is the numeric SIC code. Only one value is supported and the value must be an exact match to the CODE or CODE-DESCR (e.g. "0131" or "0131-cotton") in REF_SIC.	Yes
[REF_SITE_RLNSHP_TYPE_CODE]	SITE_RLTD_SITE table	When a field is selected from a lookup populated by a Site Alternative Name, the selected site will be created as a Related Site to the submission’s site. See the Relating Sites In a Submission article for more information.	No
TAX_PARCEL_NUM	SITE.TAX_PARCEL_NUM	Tax Parcel Number	Yes
SITE_LUG_NAME	SITE.REF_LUG_CODE	This value is mapped to the Description column from REF_LUG	Yes
SITE_WATERBODY_NAME	SITE.REF_WATERBODY_CODE	This value is mapped to the Description column from REF_WATERBODY	Yes
REF_OWNRSH_TYPE_DESCR	SITE.REF_OWNRSH_TYPE_CODE	This value is mapped to the Description column from REF_OWNRSH_TYPE	Yes
SITE_ALT_NAME+[SITE_ALT_NAME_CODE]	SITE_ALT_NAME table	Adds a Site Alternative Name record to the site for the ALT_NAME_CODE specified in the tag name suffix following the + symbol. If one or more existing Site Alternative Name(s) are found on that site with that same Alt Name Type, whose value differs from the information on the submission, then the previous alternative names will be set to Inactive. Note only one site alternative name can be added or updated per submission.	Yes
SSN	SITE.SSN_HASH	Social Security Number. Only applies to Person records. See Windsor Internal documentation for specific details on configuring SSN integration.	Yes

Submission Data Integration into nCORE

Technical Note: nFORM control tag mapping is defined in nformExport.VW_SUBM_EXPORT. Integration logic is defined in dbo.SP_nFORM_STG_TO_SUBM.

Only one SUBM record is ever associated with a given nFORM submission.

nFORM Control Tag to nCORE Submission Database Field Mapping

The following table provides a mapping from individual fields on the nFORM contact control to nCORE Submission fields.

Control Tag	nCORE Target Field	Description
CMPLNT_DESCR	SUBM.CMPLNT_DESCR	Complaint Description
IS_CONF_REQSTD	SUBM.IS_CONF_REQSTD	nFORM submission value must be YES or NO. Applies to Complaints only. Used to indicate that the complainant has requested to remain confidential. If YES, a warning label will appear on the submission detail screen’s sidebar indicating “Remain Confidential Requested”.
CMPLNT_ISSUE_DATE	SUBM.CMPLNT_DATE	Joined with CMPLNT_ISSUE_TIME, if present
CMPLNT_ISSUE_TIME	SUBM.CMPLNT_DATE	Only used if CMPLNT_ISSUE_DATE tag is also present
PROJ_NAME	SUBM.PROJ_NAME	Project Name
ACTN_TYPE	SUBM.REF_APP_REQST_ACTN_TYPE_CODE	This value is mapped to the Description column from REF_APP_REQST_ACTN_TYPE. If not supplied, import will fall back to Form's Action/Deadline setting for mapped nFORM Reason Type.
SUBM_CMNTS	SUBM.SUBM_CMNTS	Submission Comments
SUBM_DCSN	SUBM_VERSN.REF_DSCN_CODE	Specific to schedule submissions. If a matching value is found in the REF_DCSN lookup table, the submission version's Decision will automatically be set to the value supplied. Allowed values are: `APPROVED` or `NOT_APPROVED` (if schedule type requires approval) `ACKNOWLEDGED` or `REQ_RESUBM` (if schedule type does not require approval)
SUBM_DCSN_DATE	SUBM_VERSN.DCSN_DATE	Specific to schedule submissions. Sets the Decision Date of the submission version to the value provided. Only populated if a valid SUBM_DSCN is found. If a SUBM_DCSN is specified but a SUBM_DSCN_DATE is not specified, the decision will default to the submission version received date/time.
RESUBM_DUE_DATE	SUBM_VERSN.RESUBM_DUE_DATE	Specific to schedule submissions. Sets the Resubmission Due Date of the submission version to the value provided. Only applicable to decision codes NOT_APPROVED and REQ_RESUBM.

Feature Data Integration into nCORE

Technical Note: nFORM control tag mapping is defined in nformExport.VW_FEATR_EXPORT. Integration logic is defined in dbo.SP_nFORM_STG_TO_SUBM_FEATR.

Feature information is gathered from nFORM sections whose control tags are prefixed with the text SITE or FEATR, or contains a Location control. For example, if a control in a section is tagged `FEATR_CARWASH`, the import process will attempt to insert or update a feature. There are no requirements around section tag names for data import; it is based entirely on the section's control data.

The integration logic can only create or update one feature record per section.

Sections can be repeating to import multiple features.

Inserting versus Updating Features

nCORE will only create new features if a Feature Type is specified in the submission data. The Feature Type can be specified using any of the following methods:

Include a Location control whose label matches an existing Feature Type Description, or
Include a Location control whose tag matches an existing Feature Type Code, or
Include a pre-populated, hidden text control whose default value matches a Feature Type Code or Description, or
Include a single select control whose dropdown values match an existing Feature Type Description.

If an existing Feature is found with a matching FEATR_ID_TXT, the feature will be updated with all fields. If a feature is not found with a matching FEATR_ID_TXT, a new feature will be created upon import.

nFORM Control Tag to nCORE Feature Database Field Mapping

The following table provides a mapping from individual fields on the nFORM contact control to nCORE Feature fields.

Control Tag	nCORE Target Field	Description
FEATR_ID_TXT	FEATR.FEATR_ID_TXT	Feature Identifier business key (e.g. "001")
FEATR_TYPE	FEATR.REF_FEATR_TYPE_CODE	nFORM value can match on either CODE or DESCR in REF_FEATR_TYPE.
FEATR_DESCR	FEATR.DESCR	Feature Description
FEATR_LOC	FEATR_LOC.(lat/long value)	Derived as a point (single lat/long coordinate) from the nFORM location control. Tag value does not matter. Integration logic will fetch the coordinate from any location control.

Contact Data Integration into nCORE

Submission contacts in nCORE are created from nFORM Contact controls.

Configurating a Contact’s Affiliation Type

Every contact imported from an nFORM submission into nCORE is assigned one or more Affiliation Types. The Affiliation Type assigned to the contact upon import is determined as follows:

The nFORM contact control’s Label matches an Affiliation Type Description (e.g. “Applicant”), or
The nFORM contact control's Tag matches an Affiliation Type Code (e.g. “APPLCNT”)

If no matching Affiliation Type Code or Description is found for the contact, nCORE will assign the generic Affiliation Type “Contact” to the submission contact.

Allowing a User to Assign One or More Affiliation Types to a Contact

Often a form needs to allow the user to select one or more roles to assign a contact. To achieve this:

Add a Contact control to the section. The Tag value is not important.
Add a single or multi-select nFORM control. Set the Tag to ‘AFFIL’ and set the values to one or more Affiliation Type descriptions.

When this configuration is used, the section may only contain one contact control. Otherwise, the system would not know which contact to assign the selected Affiliation Types to. The section can be set to repeating if multiple contacts with different Affiliation Types are needed.

Configuring Multiple Contact Controls with the Same Affiliation Type

Form design rules require that all tags in a form are unique, but this could cause a challenge if two or more contact controls need to be tagged with the same Affiliation Type Code. To overcome this limitation, use the + sign to add text at the end of the tag to create a unique tag value. For example, a contact control with tag CMPLNT+TEST1 will yield a contact with affiliation type CMPLNT.

Configuring a Contact Control to Update Site (Person) Details

A common scenario for license forms will require that a Contact control be used to update the Site (Person) Details. When the Applicant is also the Site, the contact control may be used to create both. To achieve this, the Contact control Label can be set to "Applicant" and Tag can be set to "SITE".

Note: The Site Name comes from contact Company Name / Organization field. The first name/last name fields are only used for updating Person sites.

Creating Submission Contacts and Site Relationships from a Site Lookup

See the Relating Sites in a Submission article for more information on configuring form controls to reference a list of other sites, organizations, or persons, and then create submission contacts or site relationships based on the selection in a submission.

Contact Control Field to nCORE Contact Database Field Mapping

The following table provides a mapping from individual fields on the nFORM contact control to nCORE Contact fields.

Control Tag	nCORE Target Field	Description
Type	AFFIL.REF_AFFIL_TYPE_CODE	After contacts are added to CONTCT, affiliations are inserted into the AFFIL table linking each contact record with 1..many affiliation types matching the REF_AFFIL_TYPE_CODE.
Title	CONTCT.TITLE
prefixNames	CONTCT.PREFIX
formula	CONTCT.CONTCT_NAME	Complex formula using either the company name or a concatenation of first and last names.
firstName	CONTCT.FIRST_NAME
lastName	CONTCT.LAST_NAME
companyName	CONTCT.ORG_NAME
email	CONTCT.EMAIL
phone	PHONE.PHONE	Saves the tag mapped to "phone" as the REF_PHONE_TYPE_CODE = "OFFICE" phone. Appears to be for legacy form use only. Uses FN_FORMAT_PHONE to format the phone number.
phone_0..3	PHONE.PHONE	Saves phone_0, phone_1... phone_3 to distinct phone records of different phoneType. Uses FN_FORMAT_PHONE to format the phone number.
phoneType_0..3	PHONE.REF_PHONE_TYPE_CODE	Saves the phoneType of phone_0, phone_1... phone_3 to distinct phone records of different phoneTypes. WHEN phoneType = "BUSINESS" THEN "OFFICE" WHEN phoneType = "MAIN" THEN "OTHER" ELSE phoneType END
extension_0..3	PHONE.PHONE_EXT	Saves the extension of phone_0, phone_1... phone_3 to distinct phone records of different phoneTypes.
extension	PHONE.PHONE_EXT	Saves the tag mapped to "extension" as the REF_PHONE_TYPE_CODE = "OFFICE" phone. Appears to be for legacy form use only.
fax	PHONE.PHONE	Saves with REF_PHONE_TYPE_CODE = "FAX".
street	CONTCT.ADDR_1
street2	CONTCT.ADDR_2
locality	CONTCT.CITY
areaCode	CONTCT.REF_STATE_CODE
countryCode	CONTCT.REF_CNTRY_CODE
postalCode	CONTCT.ZIP_CODE
county	unmapped
description	CONTCT.ADDR_CMNTS	Labeled as “Location Description” on screen

Document Integration from nFORM into nCORE

Submission PDF

nFORM produces a PDF document containing the information provided in the submission form. The naming convention of the PDF is configurable in the Form Details screen.

Submission Attachments

Document attachments are always imported from an nFORM submission and attached to the Submission record in nCORE. If a description has been provided for the document within the nFORM attachment control, the description will be carried forward into nCORE.

Integration from nCORE into nFORM (prefill)

Prepopulation from nCORE into nFORM is accomplished through nFormImport.SOURCE_[SUFFIX] views where the view name suffix indicates the section tag to populate.

Prerequisite

To prepopulate forms, Pre-fill → Import from External Source (SQL) must be checked:

For each Section that will require prefill, the Enable Form Pre-Fill (via Context ID) field must be checked and the Section Tag should be set to view name suffix of the source name eg. (SITE, PRMT, or FEATR, )

Available sources are:

nCORE - nFORM Integration Tags#Sites
nCORE - nFORM Integration Tags#Submissions
nCORE - nFORM Integration Tags#Permits
nCORE - nFORM Integration Tags#Features
Other sources can exist depends on clients needs.

Site Prefill into nFORM

Populated from view: nFormImport.SOURCE_SITE

Available standard data for pre-population in a section tagged SITE are:

Tag	Name	Control Type	Notes
SITE_NUM	Site Number	Text Control
SITE_NAME	Site Name	Text Control
SITE	Site Location	Location Control	This will populate a location control with site coordinates
SITE_ADDR	Site Address	Contact Control	This will populate contact control or Address control tagged SITE_ADDR
SITE_CNTY	Site County	Text Control
SITE_ADDR_CMNTS	Site Address Comments	Paragraph Control
TAX_PARCEL_NUM	Tax Parcel Number	Text Control
REF_OWNRSH_TYPE_DESCR	Ownership Type	Text Control
SITE_LUG_NAME	Site Lug Name	Text Control
SITE_WATERBODY_NAME	Site Waterbody Name	Text Control
SITE_SIC	Site SIC Codes	Text Control	Any control that supports binding to dynamic datasource. Set up the control to use the SIC_CODES datasource. The binding assumes dbo.REF_SIC.CODE = nform.LOOKUP_VALUE.NAME
SITE_NAICS	Site NAICS Codes	Text Control	Any control that supports binding to dynamic datasource. Set up the control to use the NAICS_CODES datasource. The binding assumes dbo.REF_NAICS.CODE = nform.LOOKUP_VALUE.NAME
SITE	Contact	Contact Control	For modifying PERSON records, the Contact control can be used.

Submission prefill into nFORM

Populated from view: nFormImport.SOURCE_SUBM

This view is primarily used for:

Populating data about the submission's parent Schedule
Populating the Alternate Identity in the Submission Header
Schedule Due Date (that should typically be set to Read Only when populated)
Schedule Name (see Notes below on what it contains)

Available standard data for pre-population in a section tagged SUBM are:

Tag	Name	Control Type	Notes
SUBM_REF_NUM	Submission Number	Text Control
SCHD_DUE_DATE	Schedule Due Date	Date Control
SCHD_NAME	Schedule Name	Text Control	If there is a Custom name then that takes the priority over Schedule type name.
SCHD_EXTRNL_DISP_TXT	Schedule External Display Text	Text Control	Instructions provided by the agency to the external user for a schedule. Should only be used in a read-only control and conditionally displayed only if a value is present.
SCHD_CNTXT_FUNC_AREA	Schedule Context Functional Area	Text Control	Returns `PRMT`, `PROJ`, `CMPL_ACTN` or `SUBM`. Ideal for use in a hidden field to control conditional logic based on a schedule’s source functional area. For example, auto-approve schedules on permits but not compliance actions.

Populating the Alternate Identity with the nCORE-generated submission number

The nCORE source view nFormImport.SOURCE_SUBM can be used to populate the nCORE-generated submission number into the nFORM Alternate Identity, making this available on the Submission Wizard Header and Submission Summary screen:

Create a new section (or use an existing section), and tag it SUBM, and enable it for prefill
Create a new short-text control tagged SUBM_REF_NUM and make it "Read Only"
At the form version level in the "Details" Section, check box "Activate Display of Alternate Submission Identifier"
Check newly-displayed box "Override Display Label", and enter label of your choice

Permit prefill into nFORM

Populated from view: nFormImport.SOURCE_PRMT

Sections must have a Tag set to ‘PRMT’ in order to prefill permit data into the section.

Since permit information is never updated in nCORE upon submission receipt, any controls tagged for Permit data prefill controls should set to read-only.

A maximum of one permit can be specified on a form using these tags. The PRMT section should never be marked repeatable. Information about other permits can be collected on the form but the controls for these permits should be given tag values that are not in the reserved list below.

A section tagged PRMT should only be included on Permit Change forms or Compliance Schedule forms. Since Permit Change forms (e.g., modifications, renewals) and Compliance Schedules (e.g., annual reports, notifications) are typically used in the context of an existing, in-effect permit, including a PRMT section on these forms provides a convenient way of automatically displaying information about that permit on the form. For example, the permit number and effective date can be displayed at the beginning of the form so that the applicant can confirm that they are filling out the form for the correct permit.

Permit Change forms and Compliance Schedules can also contain site information, but this should be done by using a section tagged SITE containing the appropriate SITE tagged controls.

Available standard data for pre-population in a section tagged PRMT are:

Tag	Name	Control Type	Notes
PRMT_NUM	Permit Number	Text Control
PRMT_VERSN	Permit Number	Text Control
PRMT_NUM_ALT	Alternative Permit Number	Text Control
REF_PRMT_CATG_DESCR	Permit Category	Text Control
REF_PRMT_TYPE_DESCR	Permit Type	Text Control
REF_PRMT_STAT_DESCR	Permit Status	Text Control
ISSUE_DATE	Permit Issue Date	Date Control
EFCTV_DATE	Permit Effective Date	Date Control
EXPR_DATE	Permit Expiration Date	Date Control
PERMT	Permittee Information	Contact Control	This will populate a contact control with Permittee details
PERMT_NAME	Permittee Name	Text Control	Populates the "Contact Name" from the permit's permittee. Note that this will not create a contact when submission is imported back into nCORE.
SITE_NAME	Site Name	Text Control
SITE_NUM	Site Number	Text Control
SITE	Site Coordinates	Location Control	This will populate a location control tagged SITE
SITE_ADDR	Site Address	Contact Control	This will populate contact control or Address control tagged SITE_ADDR
SITE_CNTY	Site County	Text Control
SITE_ADDR_CMNTS	Site Address Comments	Paragraph Control
TAX_PARCEL_NUM	Tax Parcel Number	Text Control
PROJ_NAME	Project Name	Text Control

Feature prefill into nFORM

Populated from view: nFormImport.SOURCE_FEATR

Feature data can be filtered for prefill/autofill into nFORM Submissions by using a special Tag syntax on the Section where the feature type code to filter for is suffixed onto the tag after a dollar sign. For example Tag FEATR$STACK would filter only for features of feature type code STACK.

Available standard data for pre-population in a section tagged FEATR are:

Tag	Name	Control Type	Notes
DATASET_NAME	Dataset Name	Text Control
FEATR	Feature Location Coordinates	Location Control	Location coordinate used with a location control
FEATR_DESCR	Feature Description	Text Control, Paragraph Control
FEATR_ID_TXT	Feature ID	Text Control	Can be auto-populated using a feature identifier numbering scheme specified on the Feature Type lookup screen based on the `FEATR_TYPE` tagged control value. The `FEATR_TYPE` value can match a Feature Type’s Code or Description and the value must be present at the time the section is created, either using a control default value or via pre-population (inheritance or pre-fill).
FEATR_TYPE	Feature Type	Text Control

Contact prefill into nFORM

Populated from view: nFormImport.SOURCE_CONTCT

Section Prefill

Contact data can be filtered for prefill/autofill into nFORM Submissions by using a special Tag syntax on the Section where the contact type code to filter for is suffixed onto the tag after a dollar sign. For example Tag CONTCT$BILLING would filter only for contacts with affiliation type BILLING.

Single Contact Prefill (v2023.01+)

If a contact control on any section that allows prefill is tagged XXXXX${AFFIL CODE} (e.g. APPLCNT$BILLING) then it will be prefilled with the most recently entered contact of type BILLING for that regulated entity (e.g. Site). The prefix (XXXXX) of the tag can also match the same or a different affiliation type and will be imported into nCore if so.

In summary: {Export AFFIL CODE}${ Prefill AFFIL CODE}. So for example OWNR$OWNR would prefill with the Owner contact, and would export to nCore as a new version of that Owner. If the {Export AFFIL CODE} does not match a valid AFFIL code, then it will not be pulled back into nCore, it will just remain in nForm, but still can be reported on in nVisage.

On this page

1 Data Integration from nFORM into nCORE
2 Integration from nCORE into nFORM (prefill)

Related Content

Windsor Support

nCORE - nFORM Data Integration Tags