how can I check if this contact is in another column?

[Sara]

Hi, I'm having a little trouble with a project.

I have a table called "Report" with the report names, submitted by, email address, and attachments and another table (Contact list) with a contact list with just names and emails.

My customers have to fill in a form, and this fill in the main table (report table). 

My question is, how can I check if the contact that submitted the form (submitted by) is already on the contact list? I would like to do like a checkbox, so I can link an automation to this column and send me an email with the contact information of people that have not been on the contact list.

I can't automatically add new people to the contact list, because that table is synchronized to another base.

How can I solve this problem? I thought about the function lookup, but I wasn't able to apply it. 

Thank you very much!

[Stephen_Orr1 · Accepted solution]

Hi @Sara,
I would ask for some unique identifier as a question on the form (such as email address) to compare with an existing contact list when the form is submitted. You can set up an automation with

• the trigger "when form is submitted"
• a Find Records step that searches your contact list for the email address (you might need to convert this submitted email address to all lowercase using a separate formula field)
• a conditional action that updates a checkbox field on the form submission record if the Find Records "list properties -> length" > 0

Then you can create a view based on this checkbox field and/or some notification automation as needed.

Hope that helps!
-Stephen

[Sara]

Hi, Thank you very much for your fast reply! I was able to apply what you suggested to me, but I think that I made a mistake, because it updates the checkbox even if there isn't in the table "contact list".

Those are some screenshots, I don't know if you are able to identify the mistake. By the way, Thank you !

[Sho]

Hi, @Sara 

In the third screenshot, there is a mistake in "Find records".
The "Find records based on" should be set to "Condition" and the same email address should be used as the condition for comparison

[Sara]

Hi Sho, Thank you for your help! How can I select the condition between 2 columns from 2 different tables? 

The dynamic condition shows me only the columns of the same table.

[Sara]

Never Mind, I was able to solve it, Thank you both guys!

[Sho]

This article about "⚙️Dynamic conditions" might be helpful.
https://support.airtable.com/docs/find-records-automation-action