In this article we'll look at something that many larger organizations will have to tackle: integrating data from another database into a form.

If the dropdown list is just a simple query, that gets populated when the form is first displayed, and doesn't rely on values of any other fields in the form to populate, then you can just create a Generation Script in a central dropdown as described here. You would then use the data:RunODBC function to retrieve the data and return a string list to put into the dropdown.

Note: You can only use RunODBC with the downloaded version (Professional or Express) Edition, as it will need to be installed onsite to access your local database.

An example might be this Generation Script:

var #data = data:RunODBC("Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;", "select * from myTable")
var #list = NewList()
loop through #data as #record
    add #record/Name to #list
end loop
return #list

This script runs an external database query, then extracts the Name field from the result, and adds it to a simple string list and then returns that. If you create a central dropdown list, and put that script into the Generation Script, then you can add a text field to your form with that dropdown list selected and it will automatically populate it with that data every time the form is displayed.

That's the simple scenario. A more complex scenario is if you want to use the value of other fields in your form to change how the data is queried. To do this we'll need to create a different kind of dropdown that is populated when the user shows the dropdown, rather than when the form is first displayed.

Being able to do this is a little more advanced, but also a lot more useful. So that's what we'll be focusing on for the rest of this article.

The first step is to create a simple hidden form (a "type") that will have just one field: Title. We'll need this because link dropdowns are always lists of records that are defined in the system.

So add an Application Tab to your application, and just call it LinkType, and add a field to it called Title (a text field). Edit the form settings and make sure that Hide = Yes under Display Settings.

Next we'll need a plugin script. These are scripts that you can re-use in different places. In this case our plugin script will be the script that returns the list of data to display dynamically, similar to the Generation Script. Please read through this article to understand how to create a plugin script.

The script won't be that different to a Generation Script either, except it will have to create records and set the type, rather than just return a list of strings.

var #data = data:RunODBC("Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;", "select * from myTable")
var #list = NewList()
loop through #data as #record
   var #item = new record for "LinkType"
   set #item/Title = #record/Name
   add #item to #list
end loop
return #list

This creates a list of records of type "LinkType", sets the Title of each record according to the Name field in the list, and then returns that list.

Next you'll add a field into your form, like you'd normally add a link field into a form. Select the LinkType as the "Link" setting in the field. Then simply go to the field definition of the field you added, then go to Advanced (add that record) and in the Link DbfScript field and enter in:

RunPlugin("Functions:YourFunction", &path, #input)

...replacing YourFunction with the name of the function you added.

You can now publish and test out your form.

The next step is to see how you can dynamically integrate other fields in your form into the external database query. Unlike the Generation Script, your plugin script will be able to access #input which will be the current form the user sees. That means you can access any of the fields in the form by putting #input/FieldName in your script.

Let's look at an example. Let's say you want to

var #data = data:RunODBC("Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;
Pwd=myPassword;", "select * from myTable where Category='" + #input/Category + "'")
var #list = NewList()
loop through #data as #record
   var #item = new record for "LinkType"
   set #item/Title = #record/Name
   add #item to #list
end loop
return #list

Notice the change in the first line - now instead of just using a simple select, we're including a bit of SQL that has a "where" clause, filtering the result, and incorporate the #input/Category field from the form into that filter. Basically whatever value is in Category will be used to filter the Category in the table we're querying.

You can use any field in the form through #input. If you want to use another link field, get the text out of the link field by using GetDisplay(#input/LinkField).