Add Choice To Form Field

[RMittelman]

I have a table for BoardPositions in my application.
Our organization has 2 co-presidents. Positions are overseen by one co-president or the other (or none).
My table and form have an “OverseenBy” field. I made the field an integer, and on the form I created values of 0:None, 1:Co-President 1, 2:Co-President 2.
This works perfectly so far.

I have decided that it should be more dynamic, incase we add more co-presidents later. So since I have a boolean field in the table called IsPresident, I thought I could populate the drop-down with the actual positions needed. I created a query called “GetPresidents” which contains the following:

Select BoardPositionsKey, PositionName from BoardPositions where IsPresident=true

I changed the table field and form fields to GUID instead of integer, and for Page List Source I put “GetPresidents”. I thought this would give me a drop-down list showing the current Co-President records. This does not work. The drop-down says “No options”. However, when I run the query from the code window, it properly shows the 2 records who have IsPresident=true.

Any idea what I’m doing wrong? Thanks…

Update:

I read the article about using the _LookupQuery display type and modified my query to use parameters. Still didn’t work.
https://help.five.org/2.1/docs/setup/display-types/default-display-types/lookups/lookup-query/

It seems the article is incomplete, because it has a heading at the bottom called Add the Forms, but no further instructions there.

[Jo-Anne]

Hi Ron,

The documentation that you found was Version 2.1, we have Version 2.6 documentation out now, it is continually being updated with more information every day and you will find it more up to date. The lookup display types are currently in progress of being documented.

Welcome to Five | Five | Low-Code For Real Developers

I have provided a couple of links for you that maybe of help for now.

Can you please take a look at these two links and see if they will help your scenario for a _LookupQuery.

Get data from one table to another table in a form

I have a Drug Table that has a selling price field. I have a Sales Table with a quantity and Total field. It is also referencing the drug table via relationships. I am trying to create a function that triggers when i enter the Total field on the Sales form. I want this function to use the selling price of the selected drug to multiply the quantity so as to get the total amount. How do I achieve that? [function] [sales] [drug]

Add field to form page list

Now I fully understand your situation! When you create a _Lookup type, Five will take the first logical field in the referenced form and display this in your lookup. For you it is taking the First Name field. Here are my members, inclusive of first name and last name. [image] In my lookup only the first name is listed when I come to my Membership form and go to select a member. [image] To have the First Name and the Last Name fields both displayed, you will need to change your display ty…

The essence of it is you use a _LookupQuery display type, choose the query and the query at a minimum has to return the primary key and a visible value which is what you will see in the lookup.

[RMittelman]

Thanks for the reply Jo-Anne. I thought I did all of the steps in the first article.

Here is my query, and what it returns:

Here’s the fields from the GetPresidents query:

Here is the definition of the form field:

Here is what comes up when I run the application:

I can’t figure out what I’m doing wrong.

[Jo-Anne]

Hi Ron,

I have setup what you are trying to perform (as close as what I think from your images) and it is working for me. Is it possible for you to email me your FDF so I can get a better look? You don’t have to attach to the forum, I can send you an email privately so you can send via that.