Skip to main content

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 oCars] table for all records where the oEmployer] lookup contains the Trigger Record’s name.


  3. Create records step: Create a record in the 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.

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”

Reply


Terms & Conditions