Summarising monthly costs for clients; multiple relationships removed

Looking for some help.

We work with employers to provide company cars as an employee benefit.

  • For each employer there are multiple employees
  • For each employee there can be multiple cars
  • For each car we charge a series of monthly payments

I’m trying to setup AirTable so that when I select an employer and month, I can output an invoice with the relevant monthly payments.

I’ve done the first steps:

  1. Set up an employers table that links multiple employees per employer

  2. Set up an employees table that links multiple cars per employee

  3. Set up a monthly payments table that links cars to a series of payments by month

I’m struggling with the final step where I want to set the employer and month and output the total payments, so that I can create an invoice. Any advice would be appreciated.

You could set up a system using Automations to find all the [Cars] records currently linked to an [Employee] at a particular [Employer]:

Table: [Employers]

  • Make a Checkbox field to trigger the Automation. Once the structure is in place you could convert this field to a Formula to trigger the automation automatically at the end of a month/start of a new month.

Table: [Cars]

  • Add a Lookup field pointing to the [Employee]s linked [Employer]
  • Add a Rollup field that gets the sum of all [Monthly Payments] linked to the [Car]

Table: [Invoices]

  • Add a link to the [Employers] table
  • Add a link to the [Cars] table
  • Add a Rollup field that gets the sum of all linked [Car] payment amounts

Automation

  1. Trigger: Set the Automation to run when an [Employer] record is “checked”
  2. Find records step: Look in the [Cars] table for all records where the [Employer] lookup contains the Trigger Record’s name.
  3. Create records step: Create a record in the [Invoice] table. Fill the {Employer} field with the Trigger Record’s name. Fill the {Cars} record with the List of ‘Airtable record ID’ from the Find Records step.

Thanks for this.
Ive managed to follow the steps and can see how this can provide a total for fees in each invoice.
However it does not account for the fact that within the Monthly Payments table I have a series of payments stretching out into the future.
How could I include the payment desired for a specific month, or for all previous months?
Thanks for your help.

Either post screenshots of how your Tables are set up, or add a link to your base.

The answer will likely be some variation of: “Add a Rollup field that only includes payment records that match a certain condition”

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.