Add a dropdown in Zendesk with data from PostgreSQL

FactBranch can show customer data from your PostgreSQL database. But what if you want to assign a ticket to one of the customer's recent orders or to a specific location? This is why we've come up with a simple no-code solution to add a dropdown field in our Zendesk app.

The dropdown gets its entries directly from your PostgreSQL query. So this is an up-to-date list with data from your database. When the agent hits "Apply" the selection is saved to a Zendesk field and FactBranch reloads. It only takes a few simple steps to add a dropdown to the FactBranch app in Zendesk.

Adapting the PostgreSQL query

Because we want to display multiple entries in a single item (the dropdown) we need to generate a JSON array in our PostgreSQL query. Fortunately this is easy to do. Use a combination of the array_to_json, array_agg and row_to_json functions:

select array_to_json(array_agg(row_to_json(t)))
  from (
    select * from transaction
    where email=%(email)s
  ) t

The preview will show the raw JSON code. For example something like this:

Adding the dropdown in the Designer

Once your query returns a JSON array, continue to the FactBranch Designer and change the type of the item that contains the JSON to “HTML”.

The dropdown is implemented as a filter that takes three parameters: The name of the JSON field to display in the dropdown, the field that's used as a value when an item is selected and the Zendesk field where we want to save the selected value to. The filter is called form_select.

Here is the code we would use in our HTML-template for the example above:

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

That's all!

Zendesk ticket fields

You might have noticed that the Zendesk field is longer than the short form you use in the query editor. That's to give you maximal flexibility in the type of field to use. Depending on the location in Zendesk you can use any of the ticket properties, chat or visitor properties or company, deal, lead or person properties. If you're unsure which field to use or how to address a specific field, simply reach out to support@factbranch.com and we can help you.

Most people use custom fields as the target field. Here are the three fields most FactBranch customers use:

• ticket.customField:custom_field_123456789 (replace 123456789 with the ID of your custom field)
• ticket.subject
• ticket.tags

Limitations in the preview

In the preview panel you can try out the dropdown, but the "Apply" button won't do anything. In the real app inside Zendesk all dropdown entries are sorted alphabetically and the apply button saves the selected value to the Zendesk field you've specified.

Here's how the preview looks:

Combining it with HTML

Because this is just a filter you use in your HTML-templates, it’s easy to combine the dropdown with other HTML. To add a heading above the dropdown, simply define an H3-tag in your HTML like this:

<h3>Assign an order</h3>

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.