Sort and filter a lookup entry table

I have an app to add clients to the calendar view. Thanks to all the help on this site, all is going well. I’m stuck on a fairly simple step though. When I click the drop down next to client ID:

image

it brings up a list of all clients in the Clients table. How do I firstly, sort this list in alphabetical order and secondly show only the current clients? I have managed to grey the past clients but cannot seem to remove them from the drop down.

image

There is an enum column in the client table for current or past. The client table links to the client dives table where one client can have many dives and it is this client dives table that I still need to show all details of past clients (in a different view) but in this form, only bring up present clients on the list.

@Riki_Armstrong
I’m not aware of your sheet structure, but below expression will help. I assume that CLIENT ID is the Key Column for your ref table:

ORDERBY(SELECT(TableName[CLIENT ID],[STATUS]="Current"),[NAME],FALSE)

This expression will filter away the rows where [STATUS]=“Past” and sort the dropdown content as per [NAME] column and in ascending order. For descending order you can replace FALSE with TRUE at the end.

1 Like

This looks great, thank you. Where do I put this expression as there are not many options on a form UX.

Would it be in the auto generated action?

@Riki_Armstrong
in the Valid_if property of your ref column.

1 Like