Creating a custom lookup / dropdown in a form

How do I?
1

Hi, how can I adjust what’s shown inside a lookup field in Five?

I have a lookup in one of my forms. The lookup acts like a dropdown, which is fine.

However, when a user clicks on the dropdown, the field gets populated with only the first value of the other form.

How can I make the lookup retrieve concatenated values from my other form? For example, instead of only showing “First Name” in the lookup/dropdown, I’d like it to show “First Name, Last Name”.

2

Hi!

We have a display type called _LookupQuery. This display type enables you to display whatever you want in the field using SQL.

In its most minimal form, it needs a primary key field for the record in the lookup and a value field that will be displayed in the lookup. You can concatenate fields from your table using this method and attach it to the field with a display type of _LookupQuery.

So at the moment you would just have a display type of _Lookup on the field and it would reference the appropriate form, but only its first field. Let’s change this, so that the form field references a concatenated field.

What you need to do is come back into Five and go to Data > Queries. Add a query that gives the primary key for the record and then concatenate your fields.

Here’s an example that takes stores and concatenates their addresses:

SELECT
StoreKey,
CONCAT(Address, ', ’ , City, ', ', State) AS Location
FROM
Stores

Now go back to your form and go into your lookup field and make two changes:

  1. Change the Display Type of your lookup field to _LookupQuery, and
  2. Select your query as the Lookup Data Source.
    Now go back to your form with the field you are using. Just to ensure nothing gets cached (not a requirement) just click the Cancel button in the Reference Form field and then select _LookupQuery in the Display Type field and then select your query in the Lookup Query field. (Note, my image will have Lookup Data Source field, yours will be Lookup Query, this is just another improvement to the next version, but yours will work correctly with the old name of the field).

Save and run your application again. Go to the form and click the lookup icon in the field and you will now see multiple fields in the dropdown.