Two "filters" on data from different tables

[cartelk]

Hi all,

I'm new to Appsheet, so bear with me. I'm creating a CRM for my business. I have a Leads table (key: Lead ID) and a Clients table (key: Client ID, also has corresponding Lead ID column). The Leads table also has a "Lead status" column.

I want to only be able to create a new client (in the clients_form view) if the corresponding lead's "Lead status" is "Won" AND they don't already have a corresponding Client ID in the Clients table (i.e. they've already been added as a client). I've been able to do the first part by putting this: "FILTER("Leads", [Lead ID].[Lead status] = "Won")" in the "valid if" field for the Lead ID in the Clients table. How can I add the second part to make sure it only shows non-existing clients in the Lead ID drop down when adding a new client?

Here's what the tables look like:

Leads:

 

Clients:

Thanks 🙂

[WillowMobileSys · Accepted solution]

When you added Lead ID column to the Clients table,  the Leads table woul dhave automatically been given a column named [Related Clients] as a relationship between the two tables.  You simply need to check that column is empty.   So you can modify your expression as follows - with slight improvement...you don't need "[Lead ID].":

FILTER("Leads", AND([Lead status] = "Won", ISBLANK([Related Clients]))

 I hope this helps!

[cartelk]

Ah you're amazing! Thank you so much. Such a simple solution 😄