How to create a dropdown with a condition?

[Kevin_Chan]

I need help with the expression. I wanted to create a dropdown to show the events where RSVP = TRUE. Please advice.

Currently, I am using this expression (EVENT[Event Name]) and it is showing me all the events in my EVENT table.
Then I tried to this expression (EVENT[Event Name], AND(EVENT(RSVP Needed?] = “TRUE”) and it didn’t work so I must have done something wrong.

[Belinda]

i think you might need to use the SELECT() expression in your VALID_IF.

SELECT(EVENT[Event Name], ([RSVP Needed?] = “TRUE”))

[Kevin_Chan]

I just tried the suggested expression and inserted under VALID_IF, there was no error, however, the column Event Name did not show up on the app. I did check the “Show” button if it was disabled but it wasn’t.

[Belinda]

To make this work properly, I had to set the “Event Name” column in the EVENT table as a KEY column. Then I made a second table ATTENDEES. For the “Event Name” column in ATTENDEES, I set the VALID_IF to “SELECT(EVENT[Event Name], [RSVP Needed?]=TRUE)”. When I then would go to enter in a person attending, the only events that would show up in the list would be those which required RSVPs.

[Kevin_Chan]

Thank you for your help. The last suggestions worked but I couldn’t make the dereferences work. I have a table call RSVP and I would like to reference the detail of the selected event over to my RSVP. Do you have any suggestion for me?

[Belinda]

I’m not having any luck getting that to work either, but I know it’s possible. There should be a way to use SELECT() or some form of function that will grab the value based on another matching value.

[LeventK]

@Kevin_Chan
if your [RSVP Needed?] column is a Y/N type column than your SELECT expression shall be like this:

SELECT(EVENT[Event Name], [RSVP Needed?] = TRUE)

[Kevin_Chan]

@LeventK
Yes, my [RSVP Needed?] column is Y/N. I am using this SELECT expression as you stated above and it works but I couldn’t reference/deference for the next column based on the selected [Event Name]. Do you know why?

Example:
The app has columns of [Event Name], [Event Category], [Service Code], and [Service Number].

I would like the [Event Category], [Service Code], and [Service Number] auto-fill when [Event Name] is selected.

I used [Event Name].[Event Category] and EVENT[Event Name].[Event Category] but both expressions couldn’t reference.

Please advice.

[LeventK · Accepted solution]

@Kevin_Chan
You can use de-ref expressions provided you have a REF column associated for your table. If you don’t have a reference column, then the option is using an ANY(SELECT(…)) expression:

ANY(SELECT(EVENT[Event Category],AND([RVSP?]=TRUE,[Event Name]=[_THISROW].[Event Name])))

OR you can use a LOOKUP(…) expression as well

LOOKUP([_THISROW].[KeyColumnName],"EVENT","Event Name","Event Category")

[Kevin_Chan]

@LeventK
Thank you very much. The expressions works beautifully. I appreciate all your help.

[LeventK]

You’re welcome

[Kevin_Chan]

@LeventK

I am trying to the same thing for the [School]. Once the school is selected, the [Student ID] column will show all the students attending the chosen school following by deference the student’s [First Name], [Last Name], etc.

This time, I Ref [School] column to STUDENT table because that table has the [School] column. It does show all the schools but with an exclamation mark. Do you know where I did wrong? Or can you please suggest a better way that I should do it?

[LeventK]

@Kevin_Chan
Remove the expression in Valid_if of the ref column. You don’t need that if you explicitly don’t want to filter values.

Yellow triangle states that you ref is wrong. Possibly [School] column is not the key column in Student table.

[Kevin_Chan]

@LeventK

The [School] column is not the key column. The key column is [UID], but I would like the dropdown to show [School] column. Is this possible?

Well, I fixed it by changing the label to [School] instead of [UID]. I think the problem is fixed for now. We will see.