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.

Initialize a SQL database:
    Server Administrator steps:

• Create a SQL database
• Add a data connection to this SQL database - set connection string info
  • 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.)
    • In this example set to "Sample Data Connection"

Initialize and Add data to the SQL database:
    User steps:

• As a Mi-Apps 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

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

• In the AfterSetData event handler, edit and use the following example function to set the value of form fields based upon the selected value of your Drop Down and the matching record from the database.

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],"");
    }
}