Finding a record and updating a field based on form submission from other table

[Jose_Rodrigues]

HI All,

I am new to Airtable and it’s automations. I have been trying to get this one automation right but always trip an error when updating. I have tried several different triggers but just cant seem to get it to work. Any help would be appreciated.

My scenario:

1. My techs go to their appointments and after each one they submit a form. (Table 1)
2. At the end of the day I pull a report of work completed and import into another table. (Table 2)

When I import the information into table 2, I need the “Automation” to find the same “account number” and “scheduled date” in Table 1 (form submission) and update a filed in Table 2 as either “Submitted” or as “Missing”.

I need both fields to match in order to differentiate between several visits to the same account.

Any guidance would be appreciated.

[Adam_C]

Hey Jose, would love to help but need more details I’m afraid.

Could you provide screenshots of the following please?

1. Table 1 and Table 2 with the relevant fields used by the automation
2. The automation set up

Once I understand how your base is set up and how your automation works, I could try to provide a suggestion

[Jose_Rodrigues]

Adam_C,

I appreciate the help. I have attached a few screen shots. I have tried a few different automation combinations to see if I could get it to populate correctly.

In this first screen shot, this is the results from a form the techs submit. I want to use the account number as the referencing field between both tables. I think I am going to add a “Scheduled Date” field to the form and tie that into the primary field so it would be “Scheduled Date - Account # - Job Type”

In the second table this is the information I pull out of Oracle and clean up in a template before loading it into Air table. The last column is the Field in which I am trying to automate.

![Screen Shot 2022-05-24 at 6.10.49 AM|700x393](upload://vg

E299aN7eku9iZUl1MGcipjNSq.png)

I have tried 2 separate automations to see which would be the best for my workflow. I have changed it so many times that I feel like I have to start over with them.

![Screen Shot 2022-05-24 at 6.11.26 AM|700x393](upload://az

cTJty1gOa5e1fbzL1YRIOupnU.png)

Ultimately what I am trying to accomplish is simple in theory. My techs submit the forms through out the day. At the end of the day, or next morning, I add the completed jobs to the table and the automation checks to see if a form was submitted for that account on that day. If it was, it updates “QC Status” as either “Submitted” or “Missing”

I hope that makes sense.

[ScottWorld]

I’m sure @Adam_C will come up with some excellent workarounds on how to do this in Airtable.

In the meantime:

For me personally, I would just outsource this to Make.com, which is a no-code/low-code automation tool.

This is how easy this automation would be to setup in Make — just 3 simple steps:

[Alexey_Gusev]

Hi,

It’s quite easy and that is standard way, no ‘workarounds’. You have difficulties because you are new to Airtable.
Usual trouble with 'Find Records" happens because it returns several records when you need to update single record or put a single value. But that’s not your scenario.
If i understood well, you don’t need to use returned result. You just need “found/not found”.

Automation should run triggered by your action (i suppose when you fill some fields in ‘Job2’
It should find records based on Condition. Like this:

During test, you will see the result.
The rest is simple

[ScottWorld]

That won’t work, because you can’t update multiple found records (for the condition where record length is greater than 0) — unless you’re trying to update a single record from a previous step, which WOULD work. Your screenshot doesn’t show which one you’re trying to do.

It’s too bad that Airtable has made the “find records” action almost entirely unusable, since this has now become the #1 most frequently asked question in this forum, which we receive almost every single day now.

I would encourage everyone to email support@airtable.com to ask them to fix/improve the “Find Records” action.

[Adam_C]

Interesting! @Alexey_Gusev’s solution should work for your needs I think. One refinement I would suggest is that, if the Jobs 2 table has a date field that you get from Oracle, I would match that date to the creation dates of the submitted forms instead of using “Yesterday”, thus allowing you to import the Oracle data on the same day as the form submissions.

Personally, I wouldn’t even use an automation for this as they’re way too precious of a resource for me right now heh.

Instead, I’d just paste formatted values from the Oracle data table into a field that’s linked to the Form Submissions table. The idea is for the Form Submissions table to have a formula field as the primary field, where the format is:
[Creation Date of Record] - [Account Number]

And the Oracle table will have a similar field, except with the date from Oracle:
[Date from Oracle] - [Account Number]

You’d then paste those values into a field that’s linked to the Form Submissions table.

If the Form Submissions table has a record that matches exactly, e.g. 24 May 2022 - 1, then the value gets pasted in. If the Form Submissions table does not, then the paste will be unsuccessful, leaving you with an empty field

We then have a “Status” field that outputs “Submitted” or “Missing” based on whether the link field is empty.

Here’s a gif of it working:

I’ve also get it set up here in case you’d like to see it. To view the setup (formulas, automations etc), duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

Happy to answer any questions you have