- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 09, 2024 10:00 AM
Hi all, first-time poster. I'm working with form submissions right now, and I'd like to check if the free text of a Location form field matches any value in an existing table's list of locations.
For instance, if we received 'Bayview South' in the 'Location' form field, then I would want to check whether 'Bayview South' appears on the below list:
A | B |
LOCATION | SUM |
Bayview North | 1 |
Bayview South | 1 |
Sea Breeze | 1 |
Palms North | 1 |
In Excel, I would normally just use an XLOOKUP and IF formula on the above table with the below syntax:
IF(XLOOKUP("Bayview South",A:A,B:B,"0")>1,"In list","Not in list")
In this case, the XLOOKUP formula would check all of column A for 'Bayview South,' returning a 1 if 'Bayview South' exists and 0 if it doesn't. The IF formula then returns an 'In list' value if 'Bayview South' is anywhere in the list or a 'Not in list' value if 'Bayview South' is not in the list.
The reason I'm looking for this solution is to run exception automations if a value does appear in the list. I tried using an automation to drop the Location value from the form field into a Location lookup field (linked to the Location exception list). However, the issue is that Airtable creates a new value in the linked table if the value doesn't already exist, so there's no way to differentiate between a value that exists in the list already and one that doesn't.
Anyway, apologies for the wall of text, thank you in advance for any help with this!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 09, 2024 07:28 PM
I assume that list of locations is its own table? If so, try using a Find Records action to look in that table, and then use a conditional to either put "In list" or "Not in list" in your triggering record
