Syncing tables across bases--help needed

[wootim]

I have an Employee Base for each employee where they can update their timesheets.
In their personal base they can also see their current assigned jobs through a sync to the ‘All Jobs’ table.

However, employees need to update job statuses when their work is done (I have automations set up which take care of the assignee based on job status).

My issue is that I can’t figure out a way to update the status of the source ID through the Employee Base. Source records cannot be stored in employee bases as job records need to be delegated by administration.

Can anyone crack this puzzle with an automation (or by any other means)? I should mention that Interfaces are not an option due to the size of our timesheets which would overload a single base.

[TheTimeSavingCo]

Hmm, how many Employee Bases are there? If it’s 10 or fewer, we could probably do the easy (but messy) thing of just syncing all 10 Employee Bases into the Main Base that contains the Source Records

Alternatively, if the Employee Bases are all in a Pro workspace, we could make an automation with a script that would update the Source Record in the Main Base (Either via another automation in the Main Base, or directly via code. I don’t recommend the “directly via code” route as you’d have to use your API key, which would be a major security risk)

Lastly, if they’re not in a Pro workspace, we could use a button with a scripting app that the Employee can click that would update the Source Record in the Main Base

Happy to help with any of the above, let me know if you have any questions!

[wootim]

Alternatively, if the Employee Bases are all in a Pro workspace, we could make an automation with a script that would update the Source Record in the Main Base (Either via another automation in the Main Base

This is exactly what I’m looking for. All Employee Bases would be in a Pro base (if I can get this to work). There would be > 10 Employee Bases too.

Could you show me an example of how this would work? And would the script automation run in the background or would it force the sidebar on the right to open and run a scripting app?

[TheTimeSavingCo]

wootim:

Could you show me an example of how this would work?

Yeap sure, let me put something together.

wootim:

And would the script automation run in the background or would it force the sidebar on the right to open and run a scripting app?

It’d run in the background

wootim:

This is exactly what I’m looking for. All Employee Bases would be in a Pro base (if I can get this to work). There would be > 10 Employee Bases too.

Oof, that’s an expensive bill

[TheTimeSavingCo]

Hey wootim,

So we update the New Status field in the Employee Base and an automation will send a POST to a webhook URL

This webhook is set up via an automation in the Jobs Base, and once that webhook is received it’ll update the record with the new status

You should be able to view all the automations and the script used in the links below

Let me know if you have any questions!

Links:
Employee Base
Jobs Base

[Diana_Lamriben]

Hi! This is exactly what I’m trying to implement across my bases. Currently I keep returning this error :

TypeError: Invalid arguments passed to table.updateRecordAsync(recordOrRecordId, fields): • recordOrRecordId → recordOrRecordId should be a Record, not undefined or recordOrRecordId should be a string, not undefined

Does this have anything to do with the fact that I can’t figure out how to fix the broken “jobrecordid” input??

[TheTimeSavingCo]

Hi Diana, apologies for the inconvenience! I’ve fixed it up and it should be working fine now

The jobRecordId input was supposed to reference a synced field that contained the record ID of the Jobs record

[Diana_Lamriben]

Thank you Adam! That was so helpful!

One thing I am wondering: Do these fields all have to be toggled to be visible in order for it to work? I’m still in the process of rearranging some fields and deciding where the source should be maintained, and I just “broke” the automation. I haven’t started doing a deep dive to diagnose yet, but I was wondering if the visibility of my fields might be the culprit?

If not, I’m going to review and ensure none of the field names were changed (I thought I was careful not to change them, but it’s possible!)

[TheTimeSavingCo]

Hmm, I don’t think the visibilty of the fields should affect anything!

I’d be happy to take a look at it for you too if you’d like