I have a table from the Strategic Planning base I am synching to a table in the Marketing base.
I created an automation to create a record in the Requests table of the Marketing basewhen a record in the synched table contains certain data. This is working brilliantly.
However, if someone changes a field in the Strategic Planning base (i.e. due date) base, and the date is updated on the synched table in the Marketing base, I want the data to update on the Requests table too. I cannot seem to configure the update record automation correctly.
It sounds as though you might be missing the "Find Records" step in your automation.....
When you create a new record, you don't need to find anything, you just schwack a record onto the table and you're done. But in your case, you need to go and find that record that you created before and schwack the new due date on top of the old due date.
Your trigger is "When a record is updated," and you have 2 actions:
Find the record you need to update
Using the RecordID of the record you found in Step 1, update the due date
I need a little insight on finding the right record.
Do you have any suggestions on building a query to locate the record that should be updated? I can't do something as simple as find a record with 1/1/2001 in the due date field because that information would vary depending on what was schwaked from the synched table. (LOVE that word BTW - totally incorporating it into my vocabulary!)
I would need to say if the record on the synched table changes, then find the record created by this synched record and then update it to match its current state. Does that make any sense?!?!?
I can tell the way I usually do it, although others might have different methods.
Whenever I'm syncing data from one base to another, I make sure to sync over the RecordID of the record in the original base. I don't call it RecordID, though - I call it Record Trace because it helps me trace a synced record back to its source in the original table in the original base. In your case, if you don't already have the RecordID in the table in your Strategic Planning Base, you might have to add it. It's easy - you add a formula field called Record Trace and the formula is
Then you sync that over to the Marketing base and adjust your automation to include that in the record that you create when a record is added to the synced base. Important note: this is not the actual RecordID for the record that is created in the synced table as a result of the sync, nor is it the RecordID for the record that is created in the Requests table as a result of the automation.
On to the automation. The trigger is "When a record is updated" and you add whichever fields you want to watch in the synced table. Your first action is to find the record in the Requests table that correlates to the record that was changed in the synced table, which is easy because the Record Trace is the same for both of them. So you find records based on a condition, use the little gear icon on the right of the conditions to make it dynamic, and choose Record Trace [the one from the synced table that just got changed] is Record Trace [the one in the Requests table that was created by your existing automation]. You should only be getting one record. If you're getting more than one, something is amiss.
Once you've found that record, your next action is to schwack (I love that you are adding that to your vocabulary!) the new date from the synced table to the Requests table, and you do that by updated the **actual** record ID of the record you just found in the Requests table.
you don't want thisyou want this
And then you have some chocolate, or knitting time, or a nice gin & tonic, or a cuddle with your pet. You deserve it!