Trying to Perfect a _LookupQuery field

How do I?
1

My database has a BoardPositions table and form. Working fine so far.

Some of the fields in the BoardPositions form and table are IsPresident, IsOverseenByPresident and OverseenByPresident.

Some of the board positions are supposed to be overseen by one of the 2 co-presidents.

If IsOverseenByPresident is true, then the OverseenByPresident form field becomes visible. Its type is _LookupQuery, and the dependent data source is the GetPresidents query:

SELECT
  `BoardPositions`.`BoardPositionsKey` AS `BoardPositionsKey`,
  `BoardPositions`.`PositionName` AS `Position`,
  `Members`.`SortName` AS `SortName`,
  `Members`.`FullName` AS `FullName`
FROM
  `BoardPositions`
  INNER JOIN `BoardMembers` ON (
    `BoardMembers`.`BoardPositionKey` = `BoardPositions`.`BoardPositionsKey`
  )
  INNER JOIN `Members` ON (
    `BoardMembers`.`MemberKey` = `Members`.`MembersKey`
  )
WHERE
  (`BoardPositions`.`IsPresident` = 1)

So when the application is running, and I am viewing a board position that IS overseen by a president, I click the drop-down next to that field, and it properly lists the 2 records returned by the GetPresidents query. But the result returned when I choose one of the 2 items is the Position, which is Co-President 1 or Co-President 2.

What I really want is one of the other fields to be displayed, either SortName or FullName. Is there a way to have a different field displayed in the _LookupQuery field? Currently, the second field, Position is being displayed. The first field, BoardPositionsKey, is not displayed either. Can’t I have one of the other fields displayed instead? Is it simply the first non-primary-key field in the query?

The documentation article on _LookupQuery doesn’t seem to address this.

Thanks…

2

Hi @RMittelman,

The _LookupQuery display type is formed by:

  1. A primary key field to ensure uniqueness.
  2. A value field to display in the lookup.

In your SQL, BoardPositionsKey serves as the primary key, while PositionName is currently the display value field. If you want SortName or FullName to be displayed instead, adjust the query so that the preferred field appears right after the primary key.

If you want to display a combination of two fields, you can use CONCAT.

CONCAT(`TableName`.`Field1`, ' - ', `TableName`.`Field2`) AS `DisplayValue`

Further information can be found at _LookupQuery | Five | Low-Code For Real Developers

Thanks.

3

Thanks so much for the quick answer Vin.

Is the _LookupQuery type indeed the proper way to do this? I didn’t quite understand the other display types enough to know if I should use one of them.

4

Five offers various lookup display types, each serving different purposes:

  • _Lookup - Standard lookup for selecting values from a related table.
  • _LookupBrowse - Lets you browse and select records without leaving the current view.
  • _LookupCustom - Allows custom logic to define lookup values.
  • _LookupList - Uses values from a list on the current form without having to save the form first.
  • _LookupQuery - Extend query, useful for filtering or extending results dynamically.
  • _LookupUnique - Ensures a value can only be selected once, preventing duplicates.

If your lookup values need to be dynamically generated from a query instead of directly linking to a table, _LookupQuery would be the suitable choice.

For further details, you can check out Five’s documentation on Lookups here:
Introduction to Five’s Lookups

5

Thanks for the quick answer. I use _LookupQuery, but I don’t think it needs to be dynamically generated. It’s actually using the same exact table for the lookup as the form uses, but it’s only showing the records in the list of those positions which have IsPresident set to true. So I thought I needed a query lookup. Is there another way to qualify which records show in the drop-down other than a query? Seems _Lookup asks for a reference form, which may return all records on the form. I don’t see a way to qualify which records to return other than a query. Any ideas, or should I just leave it as _LookupQuery? Thanks…

6

Yes, _LookupQuery is the right choice in this case. Thanks!"