Dynamic Look Up Data (Populating a Drop Down Menu with Dynamic Data)

Dynamic Lookup Fields

Dynamic Data is an option available for Drop Down Menus which allows you to populate the drop down menu with data from an alternate form. You can create a query to return specific data from the lookup table and you can use values on the current form in the lookup. For example, when a user selects their STATE from the state drop down menu, you could populate a list of available seminars in that state.

This functionality is demonstrated and explained below:

Dynamic Data in Action
In this example, we're going to create a form that lists available events in the state selected by the user.

Creating the lookup Data
For this example, we first created the lookup form/table and populated it with the state, the event name and a flag to indicate if the event is sold out or not. Our lookup Query will not return any of the sold out events. The submission bin we will be looking up data from is shown below:


This is the Data from our Current Events Form.

Building the Form
Now that we have the data that we will 'lookup', lets create the form we'll use it on. For this example, we've built the simple form shown here:


This is our Register for our Event Form with the initial fields.

Next, we'll add the drop down menu that will show available events based on the selection from the States field.

• Click the edit button on your form and choose "Insert After" to insert a new field
• Select the Drop Down Menu field and click Continue
• Set the fieldname to Events, and the caption to "Select Event:" and click the Field Settings Tab
• In the field Settings window, under field Options, click the text link "Switch to Dynamic Lookup Mode".
• After clicking the link, you'll see the button in the following screen shot, click the button to open the Dynamic Data Settings Window
  
  
  Click the "Edit Dynamic Data Settings" button to open the settings window
  
  
• In the new window, you will configure all of the lookup settings. Start by selecting the form to lookup data from. In this example, we're going to use the Current Events form that contains our event data. Notice the checkbox "Return All Records (no Lookup Query)". This option should be used when you want to return all the records and do not need to write a query. In this example, we need a query to return only the Events for the selected state and we also do not want to return any events that are marked as "Sold Out"
  
  
  Dynamic Lookup Settings: Select the lookup form
  
  

• Select the 'sold out' field and set the operator to 'does not equal' and then enter "1". "1" is the data value for events that ARE sold out.
  
  
  
  
• Click the PLUS icon to the right of the first filter clause to add a row/clause
• The next filter clause is a bit different because we want to base it on a field on the current form
• Select "States" in the field drop down and leave the operator as "equals"
• Next, to switch the criteria field to a list of fields on our current form, click the red icon to the right of the row as shown below
  
  
  Clicking the red icon, switches the criteria field to the current form fields
  
  
• Select [CurrentFormField.states]. This will use the value currently selected in the states field on the form to lookup our events.

Field Formatting
The field formatting options are where you select which field(s) will be returned to populate the drop down. You can combine fields together, or return just a single field. In this example, we only want to return the [Event Name] field. You will also notice the option to 'Use Alternate field for the Data value'. This means, we could populate the actual data value of the drop down (the value that gets recorded in the database) with an alternate field. In our case, we're happy to use the same returned value for both the label and data.


Clicking the red icon, switches the criteria field to the current form fields

Additional Lookup Options
The Additional Lookup Options are each explained below:

Max Rows
This is the maximum number of rows to return. In this example, we'll leave it at the default of 100.

Select Distinct
Select Distinct returns only distinct results and no duplicates. This option can only be used when you are returning a single field with no formatting. The DISTINCT operator may cause undesired results if you are using an Alternate Field for the Data Value. In most cases, you should only use SELECT DISTINCT when the returned data is both a) A single field without formatting and b) the Use Alternate field for the Data value is not selected.

Populate on Load
Indicates that data should be fetched as soon as the form loads. This option should be deselected when your lookup query references current fields, unless you have a default value set for those fields. In this example, even though we ARE using current form fields in our lookup clause, we'll leave this turned on. The first option in the states drop down (on our current form) is Alaska, so when this form loads, the Alaskan Events should be shown automatically.

No Results String
This is the string that will be shown in the drop down if no matches from the lookup query are returned.

Cached Results
This is the amount of time the data is 'cached' for. This means that after the first query/lookup, subsequent requests will use cached data. Caching is highly recommended and will speed up the lookup process. You should NOT use caching, or set caching to a low value, if your lookup data is constantly changing.




Query Results Sort Order
The final option is the sort order for the resulting data. Set the fields to sort by and the ascending/descending option.

Okay, we're done with the Settings! Click the Save & Close Button to close the window and then click the Save & Close Tab to finalize the changes.

When you are returned to the form editor, the field will appear empty. This is because the data is not loaded inside the editor and you will need to preview your form to see the data loaded. Click the link above the form to preview it. You should see the loading animation and then the events available in Alaska. Try changing the state to Arizona or California to see the new data being dynamically loaded.



Using 2 Dynamic Lookup Fields
In this example, we list a lot of states, but only a few of them have events. We're going to change the states drop down so that it only shows states where we actually have events. To do this, we'll change it to use Dynamic Data and we'll use the Distinct Operator to return a list of unique states from our Events table.

• Click the edit button and select edit properties on the states field
• Click the Field Settings Tab
• Toggle to Dynamic Data and open the Settings Window
• Set the settings as shown here to lookup from our Current Events Form all the Distinct States which have events.
  
  
  
  
• After Saving these settings, we need to make one small adjustment to the Events field. Navigate to the Dynamic Data Settings for the events field and de-select the "Populate on Load" checkbox. Save the changes. The reason we are doing this is because the states field will be empty (it will be fetching data) when the form loads, so we do not want the events to try to retrieve data on load.

Return to KB Article Index