Our employees are paid in multiple ways. First, the amount of Revenue they generate dictates Hourly Pay Rate for the week. We are able to import all revenue transactions from our CRM via .csv Revenue Report files. We have a chart that displays Hourly Pay Rate by Weekly Revenue level in the base as well.
An Airtable form is used to capture timesheet entries from the field on a daily basis. The number of hours entered is applied to the Hourly Pay Rate to generate Straight Time and Overtime pay.
Additionally, we pay bonuses for certain high value items that are sold (commissions are on another rate sheet in the base). Again, we use an Airtable form for field reps to submit bonus items that have been sold.
Finally, in all situations, we are using DateTime functions to identify the Week of the Year for pay periods (Sunday through Saturday). So, all transactions include a Pay Period (Week of the year) in the records.
All of this data is in the base in the following tables:
My question is how to automatically roll data from these three tables into one combining numbers and dollar amounts to calculate the gross pay in each employee’s paycheck. All transactions in all tables have a Technician name included in the records. Should I do this by creating an automation that updates the table upon creation of new records in Revenue, Timesheet Entries and Bonus Entries?