Rolling Up Payroll Data

Hi @Bobby_Gay, as you mentioned this is for calculating employee paychecks, it feels like the automation you mentioned would be the way to go about it

I would have a table called “Paychecks” or something that would contain all these paychecks, and in each of the tables that log money to be paid out to the person, I would have a formula field that would combine the person’s name with the Pay Period (Week of the year) value you mentioned, so you’d end up with something like “John Doe - 23 2022” or some such

The automation would paste the above value into the linked field to the Paychecks table where we could do a rollup fields as required, and sum them all, which would give you the total amount you needed to pay that person for that pay period, does that make sense?

Thanks Adam! I’ll try this out and let you know how it goes.

I have been able to put a table together named Gross Pay that captures information from Airtable forms named: Timesheet Entries, Spiff Entries and 5 Star Review Commissions. I have used multiple automations where, upon the submission of these forms (trigger), a record in the Gross Pay table is created.

Gross Pay is like an umbrella table that accepts records from the other 3 component tables (Time, Spiffs and 5 Star Reviews). So, if there are10 fields in each component table, there are 30 in the Gross Pay table. When a time entry form is received, the 10 Time Entry fields in a new Gross Pay record are entered into the new Gross Pay record. When a Spiff Form is submitted, the 10 fields associated with Spiffs receive entries in the new Gross Pay record, etc.

Here is my challenge. Calculating Straight Time and Overtime pay is a matter of multiplying Hours Worked by an Pay Rate per Hour. Pay Rate per Hour is driven here by the amount of Revenue each employee generates for the week. For every $1,000 in revenue - above $5,000 - in a week, the employee is paid an additional $1 per hour.

Example
Suppose the Base Hourly Rate is $20. The employee is paid $20 per hour until $5,000 in revenue is generated. From that point forward, the employee is paid accordingly:

Weekly Revenue | Hourly Rate
$5,001 to $6,000 | $21
$6,001 to $7,000 | $22
$7,001 to $8,000 | $23, etc.

We have all revenue-related transactions in a Revenue table. So, it’s possible, using grouping by Pay Week and Employee to see how much the revenue transactions total for the week per employee.

All hours worked are located in the Timesheet Entries table. It’s not hard to physically see the amount of weekly revenue the employee had in the Revenue table, and I can see the hours worked, per employee, for the week.

I’m trying to figure out how to, based on the amount of weekly revenue, automatically select the correct Pay Rate per Hour to apply. So, if Jane Doe had revenues of $18,020 for the week, and I have all of Jane’s time entries for that week in the Gross Pay table, how do I get the proper rate applied in the Gross Pay table?

Thanks for any suggestions.

Thanks Adam. I’ll give that a shot.

Boom! That worked great. I was making it more complicated than it needed to be.

Thanks again Adam.