Extracting records from one table to another based on dates

Michael1 · ‎Mar 11, 2022

Hello everyone,

So I’m a bit confused on how to do this one.

I have two tables named “Dashboard” and “Payments”.

Table “Dashboard” has two important fields - “PU Date” and “Driver(s)”.

Table “Payments” has 4 important fields - “Driver” (manually choose), “From” (manually choose), “To” (manually choose) and “Loads” (needs to be automated).

So, I want to import all records from “Dashboard” table to another field (Loads) in “Payments” table “as link to another table”. But I want these newly imported records to be between certain dates “From” and “To” and to match the Driver I choose.

How can I do that?

Hope you understand. Thanks

Kamille_Parks · ‎Mar 13, 2022

You may need two automations. Try one of the following setups and if it doesn’t suffice use both:

Triggers when record in Payments has the fields {Driver}, {From}, and {To} are not empty
Find Records step that looks at the dashboard table where the conditions are “{PU Date} is on or after {From} field” and “{PU Date} is on or before {To} field” and “{Driver} is {Driver}” and “Link to Payments is empty”
Update record step to fill in the trigger record’s Link field with a concatenation of the list of record IDs from step 2 and whatever the current value for that field is (so records already linked don’t get overwritten)

Second Option

Triggers when record in Dashboard has {PU Date} not empty
Find Records step in Payments table where “{PU Date} is on or after {From}” and “{PU Date} is on or before {To}” and “{Driver} is {Driver}”.
Update record step to fill in the trigger record’s Link field with the list of record IDs from step 2

Michael1 · ‎Mar 13, 2022

There’s no option to choose {From} and {To} fields in conditions. I can only choose “today, tomorrow, yesterday, onw week ago” etc.