Help

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

Solved
Jump to Solution
1260 3
cancel
Showing results for 
Search instead for 
Did you mean: 
aleyna
4 - Data Explorer
4 - Data Explorer

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!

1 Solution

Accepted Solutions
TheTimeSavingCo
17 - Neptune
17 - Neptune

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

See Solution in Thread

3 Replies 3
bruceconsulting
7 - App Architect
7 - App Architect

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
17 - Neptune
17 - Neptune

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

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