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.