Published Jan 19, 2024

Form Prefill SQL Configuration

By Logan Korn
2 min

This form is a work in progress, information is actively being added.

Step 1: Form initialization

The three statements below are necessary to start your query. Ensure you have your prefill tag and context ID ready along with the tag for the section of interest.

DECLARE @DATASET_ID AS uniqueidentifier = NEWID();
Insert into nform.DATASET_CONTEXT (DATASET_CONTEXT_ID, DATASET_ID, CONTEXT_TYPE, CONTEXT_KEY) values (NEWID(),@DATASET_ID,'PREFILL'/*<- Prefill tag name in step 1*/,'1857');
Insert into nform.DATASET (DATASET_ID,TAG, NAME, CREATED_DATE) values (@DATASET_ID,'RO_PP'/*<-Name of section holding contact control*/,'CONTACT',GETDATE());

Step 2: Add Controls to Script

Below you will find insert statements for some of nFORM’s most common controls. To utilize these statements below you will need to change the tag value to match the tag of your desired control as well as the desired value of that control.

Short Text Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'SHORTPOP', 'input', 'controlValue', 'SHORT TEXT PREFILL', 0);

 

Paragraph Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'PARAPOP', 'paragraph', 'text', 'Paragraph prefill', 0);

 

Select Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'SSPOP', 'select', 'select', 'Workgroup 1', 0);

 

Multi-Select Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'MSPOP', 'selectMult', 'select', 'west', 0);

 

SSN Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'SSNPOP', 'ssn', 'controlValue', '111111111', 0);

 

Number Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'NUMPOP', 'number', 'controlValue', '15', 0);

 

Date Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'DATEPOP', 'date', 'controlValue', '01/01/2001', 0);

 

Time Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'TIMEPOP', 'time', 'controlValue', '01:30', 0);

 

Email Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'EMAILPOP', 'email', 'controlValue', 'prefill@email.com', 0);

 

URL Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'URLPOP', 'input', 'controlValue', 'www.prefiller.com', 0);

 

Phone Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'PHONEPOP', 'phone', 'controlValue', '6023503500', 0);

 

Name Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'NAMEPOP', 'name', 'titleValue', 'Mr', 0);

 

Location Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'LOCATIONPOP', 'location', 'text', 'Prefill Test', 0);

 

Address Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'ADDRESSPOP', 'address', 'streetContact', '105 ne multnomah st', 0);

 

Attachment Control

Insert into nform.DATASET_ITEM ([DATASET_ITEM_ID], [DATASET_ID], [TAG], [CONTROL_TYPE], [FIELD], [VALUE], [TABLE_SORT]) values (NEWID(), @DATASET_ID, 'ATTACHPOP', 'attachment', 'attachmentComment', 'Test Comment stuff', 0);

Step 3: Reset Dataset ID

SET @DATASET_ID = NEWID();

Related content