Hi all. My first question on the Community forum, so please bear with me.
Now that Airtable has added a Find Record action in automations (Yay!), I am wondering if anyone has figured out how to create an action in the event that a record is not found matching the criteria. I have found a need for this automation a few times. To give one example, here is a summary of a scenario:
I have two tables - an “All Contacts” table and a “Submitted from Form” table. When someone submits their name, phone, town, state and email address, that info goes into the “Submitted from Form” table.
When a record enters the “Submitted from Form” table, I want to search the “All Contacts” table to see if any record has a value in the email field that matches.
If there is a matching value, then I want to update the record in “All Contacts” to the current info for the other fields. [So far so good, using the current/new automations]
If there is no match, then I want to create a record in “All Contacts.” [This is where I am stuck]. Unlike Integromat or Zappier, I don’t see an option to say if the results from the search are zero matches, then do this other thing.
I suspect that the option isn’t there yet - after all, the Find Record automation is still in the Alpha phase. But maybe someone has a work-around?
I have thought of a workaround for this issue. In your Submitted from Form table, add several fields: first, a Created Time field. Next, create a Formula field which uses the DateAdd function to calculate Created Time + 5 minutes. Then create another Formula field which uses an IF function to return “Yes” if Now() is greater than the Created Time + 5 field. Let’s call this field “Past5”. Finally, create a Text field (let’s call it “TestFind”).
Now, in your automation, add an additional update-record action at the end of your automation. In this action you want to update the trigger record (the new record that just arrived in the Submitted from Form) by placing some text such as “true” in the new text field called “TestFind”. The word “true” therefore will only get inserted in the TestFind field IF the automation successfully finds a matching record in the Contacts table. If no record is found, the TestFind field will remain empty.
Now create a new view with the following two filters (1) TestFind field is empty AND (2) Past5 field = “Yes”. This means that the view will catch records where the TestFind field has remained empty after at least 5 minutes; which means that the TestFind field still remains empty after the automation has run! And that could only be the case if the record could NOT be matched to an existing field in the Contacts table! So then, all you have to do is create another automaton, triggered by a record entering this new view, which creates a new record over in the Contacts table.