Skip to main content

I have 3 tables:

Table 1: Work productivity data from our delivery management system. This has the employee’s email and date of shift.

Table 2: Employee Data from our HR system. This has the employee’s email address and employee ID.

Table 3: Shift data from our clock in/clock out system. This includes the employee ID, hours worked, and date of shift.

 

There is a way to link all three as they share sufficient fields. I would like to match the productivity data for a specific employee on a specific date to the hours work within Table 1. So I have hours worked in there.

For example, I’d like in Table 1:

Bryan - bryan@hotmac.com - employee ID: 113495 - 34 tasks - 4.73 hours worked.

 

I can link each table, but I’m stuck on how to match the shifts on the correct date between Table 1 and Table 3.

 

Any help is really welcomed. Thank you.

Hmm, how does this look:

I’m not too sure about your table setup though.  Does each record in Table 1 represents one employee on one day instead?  I couldn’t quite tell as the example you provided didn’t have the date

If it does you could create an automation that would help you link the data from Table 3 to Table 1?  Could you provide some screenshots of your tables please?

 

Hello ​@kingbee_c.c.s,

 

What do you mean by ’to match the shifts on the correct date between Table 1 and Table 3.’

Do you need to pull the data from Table 3 when new records are created? 

Thank you for you replies. I think screenshots of the tables will give more context.

 

Table 1: Productivity Data per day - This includes an email address. It also includes data on tasks and the date those tasks were done.

Table 2: Employee data - This includes their email and salary identifier.

 

I’ve pulled through their salary identifier into table 1 from table 2 using the email address.

 

Table 3: Timesheet Data - this includes Employee ID which is the same as salary identifier.

 

So what I would like to do is use the date from table 1 and the salary identifier that i’ve done a lookup for to find the correct timesheet with the hours worked from Table 3.

For example, Clive@hotmail.com - Salary Identifier / Employee ID: 111111 - 26/08/2025. What hours did he work on that date?

Thank you very much.

Hm, try creating an automation that triggers whenever a new record gets created in Timesheet Data (Assuming they get created via a Form or API, if they don’t then the trigger would be when the employee ID’s been fully filled out)

The actions would be:

  1. Find record action that looks for a record in ‘Productivity Data per day’ where
    1. ‘Planday Employee’ field value is equal to the ‘employeeId’ value
    2. The date of the ‘Timesheet Data’ record is the same as the date of the ‘Productivity Data per day’ record
      1. To handle this one I recommend creating a formula field in each of those tables with DATETIME_FORMAT, then doing a text to text comparison
  2. Update record action to link the ‘Timesheet Data’ record to the ‘Productivity Data per day’ record

And once they’re linked up you’ll be able to rollup the hours worked on that day

Thank you for all of your help. I have got it sorted for you.

Reply


Terms & Conditions