Updating a field in a table with a field from another record in a different table

[Revolutions_AI]

Hello,

So I basically have a table for candidates and a table for job adverts. I have a make.com automation set up that when a candidate applies to one of my job adverts it automatically feeds them into my candidates airtable and will also create a new record in my job advert table if one doesn't already exist (first person who applies for the job). 

I am running this for 3 different offices each with its own make.com scenario to distinguish which office they are applying for. It will automatically feed the candidate into the candidate table and adds the correct office field that it is linked to.

Where I am having the issue is I also want it to update the job advert office field automatically. It seems that it cannot be done within a make.com module. I was wondering how I can set up an automation so that when a job record is created it will find the candidates record that triggered the creation of that job advert and grab the office that candidate is linked to and automatically feed that into the job advert office field. 

Open to options if there is an easier way to do this but this logic is all I can think of as to how this would work but I am also struggling with setting up an automation that will grab this candidate and the value in the field and then feed that value back to update the job advert.

Probably should add that the office field is a linked one as is also a seperate table containing the 3 different offices in it.

If anyone can help or has any suggestions it will be much appreciated! 

[Dimitris_Goudis]

Hey @Revolutions_AI, 

You can either add a module on Make to find the record on Airtable so that the create module will add the found record ID in the link field to match them or build automation on Airtable with the find action and update the action to follow. I think the first option is more efficient. 

The find action is needed to find the right record id. Please let me know if you need deeper explanation 🙂 

Thanks

Yours sincerely,
Dimitris Goudis

[Cobey-Wan]

Hello there. I see several issues you are having.

The first issue is you are using a 3rd party (Make) to do something that Airtable can do all on its own!

Make is actually making your process more complex than it needs to be (not to mention more expensive with their operation/subscription fees etc.) so I highly suggest avoiding that direction.

Here is my solution for you:

You will want to use Airtables Automation features. The process is similar to what you are used to with Make (triggers, actions etc.) but it is in-house and much better in my opinion.

1. Trigger: Set up an Airtable automation in your Job Adverts table with the trigger "A record is created."
   
   
2. Action: Use the "Update record" action. In the "Record to update" section, choose "Current record."
   
   
3. Find the Candidate Record: Within the update record action, you can utilize a formula to find the linked candidate record that triggered the creation of the job advert. The formula you want to use is: “RECORD_ID({Candidate})”
• Replace {Candidate} with the actual field name in your Job Adverts table that stores the linked candidate record. This formula will return the ID of the candidate record.
4. Grab the Office Field Value: Now, with the candidate record ID, use another formula to extract the office value from the linked record. The formula you want to use is:  “LOOKUP(Office, {Candidate}, RECORD_BY_ID({Candidate}, RECORD_ID({Candidate})))”
   • Replace Office with the actual field name in your Candidate table that stores the linked office record. This formula uses LOOKUP to find the "Office" field within the linked candidate record identified by its ID retrieved earlier.
5. Update Job Advert Office: Finally, set the "Office" field (or the actual field name in your Job Adverts table) to the value returned by the previous formula.

I hope this solves your problems. Please let me know if you have any questions.

🤘Airtable Rocks 🤘

[Revolutions_AI]

This is exactly what I am trying to do to be honest but I struggle with the 3rd step of finding the candidate record within the update record action, I am unsure on where you can add in this find “RECORD_ID({Candidate})” formula?

[Revolutions_AI]

I wanted to avoid the make module route as this step will only be used on the first time a job ad is created meaning that if I get 500 applicants it will use an extra 500 operations, having it automatically update in an airtable automation when the record is created seems like the best route but just struggling with grabbing the candidate and the office they are assigned to and then using that value to update the Job advert office field.

[TheTimeSavingCo]

Hmm, you should be able to do this all within a Make scenario like so:

This will either create a new Job Advert if it's the first person who's applying for that job, or link the new Applicant record to the existing Job Advert record.  What issues did you face setting this up?

[Revolutions_AI]

Yeah this will lead to lots of uneccesary operations being used after the job ad has been created unfortunately. 

I actually solved this by creating a new scenario with a webhook that will trigger from airtable when a record is created that gets the office from the first candidate and adds that to the job ad record. I am sure there is probably an easier way to do this but it works for the moment so cant complain.