We are creating a base to process and archive our weekly payroll. We have created two tables. One is called HOURS and holds the records for the current week’s payroll. The other is HOURSHIST and this holds the records from previous payrolls.
We need a field in HOURS to sum what has been paid previously to that person in HOURHIST because some tax calculations in the HOURS payroll are dependent on how much someone has earned so far this calendar year.
I have run in to two things I can’t figure out.
The first is that my link from HOURHIST TO HOURS based on the EmpID field (primary field in HOURS) only works on the first instance of an EmpID record in HOURS payroll table, it does not work on the second instance of the same EmpID in HOURS. In my test table, you will see employee ID “1” has two entries. The first entry, record #1, shows the linked records in hourshist and accurately totals the earnings in HISTHOUR. But, the second entry for employee ID “1” (record #4) does not link at all. Quite often, an employee will have two records in HOURS in one week and I need the previous earnings to be summed from HISTHOUR to both HOURS records.
The second is that we want HOURHIST to have all the payroll records going back multiple years but the rollup field in HOURS we want to only sum HISTHOUR records where the pay period ending was in the same calendar year as the HOURS record. I have not tried to do this yet because I haven’t solved my first problem but this will be the next thing to figure out.
We have talked about just having one table and using views but there were two concerns. One is the possibility of the previous pay period records getting changed inadvertently. The other is that the previous records will wind up being quite a large amount of records over time and would slow down processing.
I have created a small base with just fields essential for doing what we are trying to do here: https://airtable.com/shrUZO6PqA1px6z9a .
Any and all help would be appreciated! I was so happy to see this forum and with so many skilled contributors helping people. Thank you!
