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 ๐
Solved! Go to 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!
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!
Ah you're amazing! Thank you so much. Such a simple solution ๐
User | Count |
---|---|
19 | |
10 | |
7 | |
5 | |
5 |