Filter AND Sort in a REF column

[Marvin-JR]

I've been trying to get this to work using my own knowledge, and ChatGPT, and Google Gemini. Nothing has worked.

The column I'd like filtered and sorted (in a dropdown list) is in the table "Merchandise". The column is "Family" and references the column "Family" in the table "Family". (I know, confusing. I'm a novice.) The table family has three columns: Family ID (key), Client ID, Family. I've successfully been able to filter what appears in the dropdown based on the Client ID column using this: [Client]=[Family].[Client ID]. Separately, this works to sort the list alphabetically: ORDERBY(SELECT(family[Family ID], TRUE), [Family], FALSE). What I am unable to do is use both expressions. I've placed them into the valid if of the "Family" column of the "Merchandise" table.

Is this possible to do? I realize that using the key column would simplify things. That column is a unique identifier (random string) so that if I need to change the entire family name I can do so easily. (These are product families, not people. There maybe he hundreds of records per family.)

Am I going in circles after a solution that does not exist based on my data configuration?

[Suvrutt_Gurjar · Accepted solution]

Probably you wish to use an expression something like below

ORDERBY(SELECT(family[Family ID], [Client ID] =[_THISROW].[Client]), [Family], FALSE)

[Marvin-JR]

A million thanks! This worked perfectly.