Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Summarising monthly costs for clients; multiple relationships removed

Topic Labels: Base design
282 3
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

3 Replies 3

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”