Tip: How to show a dropdown with data from an SQL database

This is a use case we come across often: Show a dropdown that lists data from a live database. Either loading something specific to that ticket (like the customer's most recent orders) or a general list of items (like a list of locations or products for example). And when the agent selects an entry from the dropdown, save that value to a ticket field.

We've just added that feature to our app FactBranch and here is how it looks:

Step 1 - Setup FactBranch

First you need to create a FactBranch account and connect your data source. This could be a PostgreSQL, MySQL or SQL Server database.

Step 2 - Adapt your Query

Make your query return an array of JSON objects. This is a bit different in every SQL dialect and so we wrote specific documentation on how to do this here:

• PostgreSQL
• MySQL
• Microsoft SQL Server

Step 3 - Add the dropdown

In the FactBranch Designer set the type of the item containing the JSON-response to "HTML" and add the dropdown like this:

{{ value|form_select('name', 'order_id', 'ticket.customField:custom_field_123456789') }}

Here is what's going on: name is the key in the JSON object that should be displayed in the dropdown, order_id is the value that will be assigned to the ticket's custom field 123456789.

If you need help …

… do reach out to support@factbranch.com. This is an advanced feature and we’re happy to help you set it up or hear what else you need.