Summarising monthly costs for clients; multiple relationships removed

[Jamie_Talisker]

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.

[Kamille_Parks]

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.

[Jamie_Talisker]

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.

[Kamille_Parks]

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”