Breadcrumbs

How To Perform a Data Lookup

Look up a record in SQL from a value the user enters, and fill in other fields automatically. In this example, the user enters a Customer ID and the form fills in the customer's name and contact.

Step 1: Create the route

Add a Resquel route that takes the ID from the address and returns one row. Restart the GlobalForms service afterwards:

JSON
{
  "method": "get",
  "endpoint": "/CustomerInfo/:id",
  "query": "SELECT TOP 1 CustomerName, CustomerContact FROM CustomerInfo WHERE CustomerID = {{ params.id }};"
}

  • TOP 1 makes sure only one record is returned. For MySQL or PostgreSQL, use LIMIT 1 at the end instead.

  • Do not add quotes around {{ params.id }}, even if the ID is text.

  • Return only the columns the form needs.

Test it by opening https://forms.example.com/CustomerInfo/12345 with a real ID.

Step 2: Add the input field

Add a Text Field for the user's input, with the API key customerID.

Step 3: Add the lookup

Add a Select component to fetch the record, with the API key lookup, and set these on its Data tab:

Setting

Value

Data Source Type

URL

Data Source URL

https://forms.example.com/CustomerInfo/{{ data.customerID }}

Data Path

rows

Value Property

(leave blank)

Disable limiting response

Checked

Filter Query

skip=0

Item Template

<span>{{ item }}</span>

Refresh On

customerID

With these settings the Select picks the returned record automatically. To keep it out of sight, turn on Hidden. To keep the looked-up data out of the submission, turn off Persistent.

Step 4: Fill in the other fields

Add a Text Field for each value to fill in. On its Data tab, under Calculated Value, enter:

JavaScript
value = data.lookup ? data.lookup.CustomerName : '';

Use the column name you want, such as CustomerContact, for each field. Save the form and test it: enter an ID and check that the other fields fill in.