Dependent column with ref type

[Donny_rep]

I’ve got a specific problem and couldn’t find the answer in related topics…

My data looks like this:

• Agencies (with columns like: agency ID, agency name etc.)
• Clients (with columns like: client ID, client name, agency name etc.)
• Orders (with columns like: order ID, Client name, Agency name, order details etc.)

Any client belongs to an agency. When someone creates an order I managed to build a form where order ID is not shown and is created automatically. Agency name is a dropdown of reference type and refers to ‘Agency name’ with the agency ID as key and agency name as label. Client name is also a dropdown of reference type and refers to ‘Clients’ with the client ID as key and Client name as label. However, to avoid wrong input, the available Client names in the dropdown should depend on the input of the Agency name.

I tried several formulas in the Valid If area of the Client names column in the Orders table but nothing worked so far…

Hope you guys can help me out here.

[Suvrutt_Gurjar]

Hi @Donny_rep,

Welcome to the AppSheet community.

Could you please add if the Clients table references Agencies table?

[Donny_rep]

Thanks Suvvrutt_Gurjar!

Yes, the Clients table does have a reference to Agency because every client belongs to an agency. Thus, the Clients table consist of a column called ‘agency name’ (this was not mentioned in the description so just added this), referring to the agency name column in the Agency table.

[Suvrutt_Gurjar · Accepted solution]

Thank you,

In that case, does an expression something like below help in the valid_if of Client Name column in Orders table

SELECT(Clients[client [ID], [agency ID]=[_THISROW].[agency ID])

[Donny_rep]

Thanks Suvrutt_Gurjar!

Saw this expression somewhere else earlier, but it didn’t work in my situation. Applying it to my situation and seeing your expression helped me to understand the expression. I only needed to change the last columnname because ‘agency ID’ is called different in the Order table.

Thanks again.

[Suvrutt_Gurjar]

Great , thank you for the update.

Just in case you have not yet taken a look at the following article, you may wish to take a look at the usage of [_THISROW]

help.appsheet.com

SELECT()

Gather column values from row in table or slice