Introduction
Database Integrations allow you to pass data from a submitted form into your own database, where the data can be retrieved either by another form, or by your own internal applications.
You need to select a ‘System’ from the drop down list and enter a query in the appropriate format to your database eg SQL, My SQL etc.
Some times you might want to query a database using alternative fields.
In Forms if you are querying a table by 1 or more fields you will need to use the hash tag in the token to denote some of the query fields maybe blank.
Examples
If you want to query an LLPG by Street, Post Town or Town your query will need to look like this:
SELECT house_name_number as House, street_name as Street, locality_name as Locality, town_name as Town, post_town as PostTown, postcode as Postcode, county_name as County
FROM fs_llpg
WHERE
street_name like ‘%’+’{SearchStreet}‘+’%’ AND post_town like ‘%’+’{SearchPostTown}‘+’%’ AND town_name like ‘%’+’{SearchTown}‘+’%’
ORDER BY post_town, town_name, street_name, house_name_number
If you intend on using the results page you can also change the values displayed on screen for the table. For example if your look up is returning these columns;
house,street,town,postcode
This is how they would show in the results page as the headers. You can use the Results page columns box in the integration manager to transform these to more customer friendly values. This is done with JSON. For aboe the above for example;
{
"house":"House",
"street":"Street",
"town":"Town",
"postcode":"Postcode"
}
Attaching a lookup to a select field
You can populate a select field with a value from a database by attaching a lookup to it.
It is also possible to populate other fields with other values from that database by using default values. In another field you need to have a default value of the name of the select field followed by a colon and the data name of the field that you wish to populate. So for example, if your query reads:
SELECT first_name as display,
second_name as name,
date_of_birth as dateOfBirth,
and the data name of the select field with the lookup attached to it is 'selectField', then the default value would be {selectField:dateOfBirth} if you wish the field to populate with the date of birth.
For security reason we have to pass all values as parameters, essentially to prevent SQL injection. This would prevent a column name from being able to be a token.
Accessing Active Directory data
AD can be linked to an existing SQL server as a linked server. This allows SQL server to act as a query proxy with which to query AD via LDAP. The Firmstep Platform cannot make queries directly,
Some general tips on optimising lookup performance
- Check the integration log to see how long the lookup is taking. This will show you the time that the platform is waiting for the results. If most of your delay is here, consider optimising the query, adding indexes to the database, and upgrading the hardware of the database server.
- Update the lookup query to only return essential columns - this will reduce the sql>xml processing, LIM encryption, data transfer time, etc.
- Turn on "integrations 2" in the forms settings - if the integration does NOT work using V2 please notify support
Points to note;
1. Forms parameterising all tokens.
In Forms you would have to do this;
'%'+{field_name}+'%'
2. Number fields are treated as integers.
In Forms you would need to explicitly convert the int to a varchar first to do any kind of compare.
3. Integrations in the platform use SQL bindings to protect against SQL injection. This means that 15:00:00', '15:30:00 will actually be '15:00:00\', \'15:30:00'. This means you cannot just pass a string, you need to pass individual tokens.
Further useful reading:
