I’m syncing a table from 1 base to another which contains 30 or so employees and their daily salary on a project. At the end of the day each day, I would like to add all the employees and their salaries to another table, the data table, in the base. As I add employees (or offboard them) I would like to only those people in that day’s list.
Do I need to have 30 different automations, one for each employee? And how do I deal with employees being onboarded and offboarded?
Perhaps a script or something can elegantly solve this? I do not know how to build scripts personally.
Any advice on this relatively straightforward but hard for me issue would be greatly appreciated!
Do the values in the synced table change throughout the day? Do the records get deleted from the synced table at the end of the day? If someone is in the table for multiple days, do you want a new record for each day?
This could be done with a scripting automation, or without scripting.
Here is one way to do it without scripting. Note that if you ever grow to more than 100 employees, this technique won’t work.
Have a control table with a single control record that is linked to the synced table.
At a set time each day, run an automation that finds records in the synced table and updates the linked record field of the control record with a list of the record ids of the found records. This will also set the back link field in the synced table.
Have a second automation for the synced table that triggers when the link to the control record has a value. This automation has two actions: a create record action to create the record in the [DATA] table, and an update action to remove the link to the [Control] table that was set in the previous automation.
I’m not sure how your onboarding or off boarding works, so this system does not take that into account.
Personally I like scripts, and a single daily script could be used so you would have only one automation run per day (versus 31) and no control table. A script could also probably handle the onboarding/off boarding better than the non-scripting method.
If you are interested in a script or would like a more detailed explanation of this non-scripting method, feel free to book a meeting with me.