Automation that Update linked record if it exists, if it doesn't exist, Create New Record

[aleyna]

Hello there,

I have a base where we are taking job hours and rolling up all the hours into monthly payroll. I attached screenshots of the sample base.

Basically, I am trying to create an animation where if {Payroll Link} is empty, then copy and paste  {Payroll Link Calculation} into the linked field. If that record doesn't already exist in the Payroll table already, then create a new record, and just like before, update the {Payroll Link} link with {Payroll Link Calculation}.

I've tried multiple versions of this automation and keep getting stuck. Please let me know if I need to provide more information on the base's structure. Thank you in advance for any help!

[bruceconsulting]

Hi Aleyna,

You most likely need an automation that triggers when a new record is added to the Entries table that finds records in the Payroll table that are equal to the {payroll link calculation} field.  Then create 2 automations: One that fires when the number is zero and one that fires when the number is 1. 

It's a bit involved, so here's a video from Airtable that will help:
https://airtable.wistia.com/medias/wzmn2p0dif

If you want to talk about this further or if you ever need additional help, please schedule some time with me.  Here's my Calendly link: https://calendly.com/d/2fq-7sw-dw9/30-minute-help-meeting

Jody

[TheTimeSavingCo · Accepted solution]

Hm, in "Payroll", could you convert your "Name" field from a formula field into a single line text field?

After that, you'll just need an automation that triggers like you said, where if {Payroll Link} is empty, then copy and paste {Payroll Link Calculation} into the linked field.  This will automatically create a new record in "Payroll" if needed, and if a record with that value (e.g. Bob - January) already exists, it'll just link it

Having the primary field in the "Payroll" table be a formula field actually prevents new records from being created from values being pasted into the linked field, so that may have been the source of some of your issues

[aleyna]

Thank you so much! Tiny detail that just solved the problem stupidly easy.