Link a record to another record automatically

[Michael_Townsen]

I have a Timesheets base with a “Timesheets” table that is filled when my workers submit timesheets each month.

I have a separate “Rates” table (in a different base) that is synced to my Timesheets base and table.

In this Rates table, I have a list of rates connected to each of my referral partners, including a start and stop date. If they do not have a stop date then that rate is considered active, if it does have a stop date than that is not an active rate.

My problem is that I am currently manually syncing the Rates table Referral partner to the Timesheet entry, and it’s too much work and error prone.

The whole point of doing Rates in a different table was to not break that past timesheet entry’s as you can see here: Financial reporting dynamic changing past data - #3 by Justin_Barrett

I am looking for a script that can automatically detect what record selection to make in the Timesheets table from the Rates table, connecting my referral partner to the timesheet. The identifier can be email (or something else) as the referral partner is already tagged to that Timesheet, but the Rate is not automatically tagged…

[Justin_Barrett]

This can be done without a script, but you’ll need to add another field to your [Rates] table first. Make it a link field to the table containing your referral partners, and link each rate to a partner. With that done, you can set up an automation that runs like this:

• Trigger: “When record matches conditions”, with the condition being that the field linking a given timesheet record to a referral partner is not empty.
• Action 1: “Find records”. Find the rate record where the link to its associated partner matches the timesheet’s partner link (matching by the text in the primary field)
• Action 2: “Update record”. Update the triggering timesheet record to link to the found rate record.

[Michael_Townsen]

Appreciate the input.

There is still a problem.

I set up a trigger when the submitted timesheet has a connected Recruiter (same thing as referral partner), however Airtable will not let me find a record based on the look up fields of that timesheet row, i.e. the recruiter’s email, which makes it impossible for me to find the record since the timesheet entered data would not have anything about the Recruiter, it would be connected to the candidate who submitted the timesheet.

I’m still stuck on how to automate the finding and linking a row in a table to another table through the Linked field.

[Alexey_Gusev]

Hi,

Airtable counts lookup field as “array of values” even if that array has only single element. To mandatory convert it to string, you should add formula field,
CONCATENATE(field) or add it to empty string, ‘’&(field)
Then you may use formula result in condition.

[Justin_Barrett · Accepted solution]

@Michael_Townsend Another way to get a string without adding a formula field is to change the lookup to a rollup, and use this aggregation formula: values & "". That force-converts the array (represented by values) to a string. As long as you’re only pulling in data from a single linked record, this should do the trick.