How To Perform a Data Lookup in GlobalForms 10

Sometimes you will want to use a Data Lookup to automatically populate GlobalForms components with information from a SQL table. This can be accomplished using the Select component's ability to populate the dropdown list with values from a SQL query. We can then read that value and use it to fill out other components on the form.

Step-by-step guide

1. For our example, we will be using the SQL table shown below.
   

   

2. Set up your SQL Data source using the instructions in this guide.

3. In your "default.json" file, set up your route to include a URL parameter. This is the initial value that the user will need to input. You will need to restart the GlobalForms service for any changes to take effect.

   • For our example, the user will input the “Customer ID”. In the screenshot below, the URL includes the Customer ID as “:getID”. GlobalForms will pass “:getID” to the SQL query using the notation {{ params.getID }}.

   • Please note how the query specified "TOP 1" in the SELECT statement. This will be necessary for data lookups to ensure only one result is matched.

   • Please note that your SQL query may vary based on the type of data. For example, if your CustomerID is a string, then you would need to enclose {{ params.getID }} in single quotes.

   • You can confirm your query pulls data by browsing to “http://servername:3001/CustomerInfo/SampleData”, where “servername:3001” is replaced with your GlobalForms server and port, “CustomerInfo” is replaced with the route you have set up, and “SampleData” is replaced with valid data (in this case, a valid CustomerID).

     CODE

     		{
     			"method": "get",
     			"endpoint": "/CustomerInfo/:getID",
     			"query": "SELECT TOP 1 * FROM SmartSearch.dbo.CustomerInfo WHERE [CustomerID] = {{ params.getID }};"
     		}

4. Create your GlobalForm, or open an existing form that you would like to use.

5. Add a new Text component for your user-inputted data. Note the API key for your Text component.

   • For our example, we will use a Text component with the API key “customerID”.

6. Add a new Select component for the data pulled from SQL. Note the API key. Set up the component as follows:

   1. Set the Data Source Type to URL.

   2. Set the Data Source URL to “http://servername:3001/CustomerInfo/{{ data.customerID }}”, where “servername:3001” is replaced with your GlobalForms server and port, “CustomerInfo” is replaced with the route set up in step 2, and “data.customerID” is replaced with the API key created in step 3.

   3. Set the Data Path to “rows”.

   4. Leave the Value Property blank.

   5. Make sure “Disable limiting response” is checked.

   6. Set the Filter Query to “skip=0”.

   7. Set the Item Template to “<span>{{ item }}</span>”.

   8. Set “Refresh On” to the Text component created in step 2.

   

   

   A Filter Query is necessary for the dropdown list to pick the first value automatically. If "Disable limiting response" is not checked off, then GlobalForms will add "?limit=100&skip=0" to the URL for the query. By checking off "Disable limiting response" and instead using the Filter Query to add "skip=0" to the URL, the dropdown list will automatically select the first value returned from the SQL query.

   
   

7. Create a Text component for the data lookup to populate. In the “Data” tab, select “Calculated Value”. Set the JavaScript value to the following:

   • CODE

     value = data.return.CustomerName;

   • Replace “return” with the API key for the Select component created in step 4.

   • Replace “CustomerName” with the name of the SQL column being read.

8. Repeat step 7 for each field you want populated. In this example, we are using the Customer Name and Customer Contact.

9. Save your form. Test by selecting “Enter Data” and refreshing the page. If set up correctly, you should be able to enter the initial Text component, and the rest should automatically populate.
   

   

For a more user-friendly appearance, make the Select component Hidden, so it does not appear on the form. You can also uncheck “Persistent” if you do not want the SQL data saved with your submission.

Here is the example form created in this article.

Data Lookup.json

Related articles