Who is this article for?

Ideagen Smartforms users configuring the system.

Understanding of code is required.

This tutorial will demonstrate how to configure a form to capture data and then later retrieve that data as a record via a Drop Down in the form.

A common use case for this scenario is a method for retrieving previously captured contact information and entering new contact information if the contact doesn't exist yet.

1. Initialize a SQL Database

As a Server Administrator:

• Create a SQL database
• Add a data connection to this SQL database by setting connection string information.

Example: In the example below, the data connection name will be "Sample Data Connection".

Form Design Steps:

• On the Design tab, Palette tab
• Add a Drop Down field which will serve as the "name" of your record, e.g. "ContactName".
• Set the Drop Down to allow arbitrary text entry
• Add fields that describe your record, e.g. ContactAddress", "ContactCity", "ContactState", "ContactZip, and "ContactPhone", etc.

Form Results Steps:

• On Results tab, check "Automatic SQL Export" from "How do you want to receive completed forms?"
• Set the Data connection to the desired data connection. (If a data connection does not exist here, please contact your administrator for configuration steps.)

Example: In the example below, the data connection name will be "Sample Data Connection".

2. Initialize and Add data to the SQL database:

User steps:

• As a Ideagen Smartforms user, fill out the form and submit it
• For the ContactName, set the contact name and use "Select this text" to set a Contact Name
• The Auto SQL export should then generate a new SQL table with fields based upon the form fields in your SQL database

3. Add data lookup capabilities to the form:

Administrative steps:

• Add a SQL datasource that references the SQL database created
• Upon entering SQL connection string info, the new datasource should then query your datasource and list tables as entities
• Keep the entity that corresponds to the name of your form
• Remove any entities that are not required for lookup
• Check "key" for any fields that will uniquely identify that record (e.g. check "ContactName" and "ContactPhone" if you wish to uniquely return contact records based upon a Contact's Name and Phone; if 2 contacts share the same name but different phone #'s - they are different contact records)

    Form Design steps:

• On the Design tab, Data data, add the entity name that corresponds to the name of your form

    Form Script Steps:

• In the AfterOpen event handler, edit and use the following example function to populate your Drop Down with the record names (e.g. Contact Names).

//Load the Contact Name (ContactName)
function LoadContactName() {
  try {
     _form.queryDataResource("Sample_Data","Select DISTINCT ContactName from [Sample Form]",)
     .then(function(rows) {
       var loadContactName = [];
       for(var i=0; i<rows.length; i++){
          loadContactName.push([rows[i].ContactName])
       }
       _form.picklistValueTuples("ContactName",loadContactName); 
     });
  }
  catch (ex){
     _form.showAlert("Could not Query Data Resource");
   }
}

In code the above,

• Sample Form is the name of the form
• Sample_Data is the name of the datasource
• ContactName is the name of the Drop Down field

function PopulateContactDetails() {
  var contactName = _form.getValue("ContactName"); 
  try {
    _form.queryDataResource("Sample_Data","Select ContactAddress,ContactCity,ContactState,ContactZip,ContactPhone from [Sample Form] where ContactName=? order by __SessionID__ DESC LIMIT 1",[contactName])
    .then(function(rows){
      if(rows.length>0){
        var contactDetails = rows[0];
        _form.setValue("ContactAddress",contactDetails["ContactAddress"]);
        _form.setValue("ContactCity",contactDetails["ContactCity"]);
        _form.setValue("ContactState",contactDetails["ContactState"]);
        _form.setValue("ContactZip",contactDetails["ContactZip"]);
        _form.setValue("ContactPhone",contactDetails["ContactPhone"]);
        );
      }
      else{
        clearFields(["ContactAddress","ContactCity","ContactState","ContactZip","ContactPhone"]);
      }
    });
  }
  catch(ex){
    _form.showAlert("Could not Query Data Resource");
  }
}

In code the above,

• ContactName as the name of the Drop Down field
• Sample_Data is the name of the datasource
• The SQL query is retrieving fields from the [Sample Form] table where field names match field names in the form
• The SQL query is defined to return the last matching record

The clearFields function is defined here.

//utility function that sets multiple fields at once to ""
//param: fields --> an array of fieldnames 
function clearFields(fields){
  for(var i=0; i< fields.length; i++){
    _form.setValue(fields[i],"");
  }
}