Skip to main content

Hello - I’m quite new to Airtable and trying to prototype out an invoicing application, but I’m running into an snag and could use some guidance.


My base:



  • Clients Table (name, id, project lead, etc)

  • Items table - this is a granular list of the items for an entire project - name, descirption, costs, % complete, status, last updated datetime, and a link to the clients table

  • Invoices - In this table my goal is to be able to create a monthly invoice for a client based on the items table with the following conditions.

    • Where the CLIENTassociated with the ITEM = CLIENT associated with the ITEM AND ITEM’S Last Updated date is BETWEEN {invoice start date} & {invoice end date}




In SQL I would join on the client ID for both tables, but it’s not yet clear to me whether this kind of join can be done in Airtable. I’ve looked at other invoicing examples and it looks like many have the ITEMS associated at the invoice, but in my use case that may be too laborious with the number of line items and error prone, so automating it off the last updated date would be ideal.


Thanks for the help. Excited for what’s possible with AirTable

For anything you would have used join in SQL you use a linked record field in airtable.

Consider the following setup, which involves linking Items to Invoices and relies on Automations to do the linking for you:


Have your Items table look like this:



I used a regular date field for {Last Modified} for testing purposes, but you’d likely use a Last Modified Time-type field. {Last Invoice Month} converts the {Last Modified} date and Client name into a unique string that matches the structure of the primary field for the Invoices table. The {Composed Invoices String} field takes whatever currently linked invoices there are and adds the {Last Invoice Month} value to the list if its not already there; the formula I used for that is:


IF(

   Invoices, 

   IF(NOT(FIND({Last Invoice Month}, Invoices)), {Last Invoice Month} & ",") & Invoices, 

   {Last Invoice Month}

)

I set up the Invoices table like so:


Then I set up an Automation to trigger when an Item record’s {Last Modified Month} field changed. The Automation includes a simple Update Record step that copies the value of {Composed Invoices String} into the {Invoices} link field.


The end result is that every time you update an Item, if it hasn’t already been associated with an Invoice record for that month it gets linked to an Invoice record for this month. So long as the Invoices’ table’s primary field isn’t a formula, a new Invoice record will be created if it doesn’t already exist.

Consider the following setup, which involves linking Items to Invoices and relies on Automations to do the linking for you:


Have your Items table look like this:



I used a regular date field for {Last Modified} for testing purposes, but you’d likely use a Last Modified Time-type field. {Last Invoice Month} converts the {Last Modified} date and Client name into a unique string that matches the structure of the primary field for the Invoices table. The {Composed Invoices String} field takes whatever currently linked invoices there are and adds the {Last Invoice Month} value to the list if its not already there; the formula I used for that is:


IF(

   Invoices, 

   IF(NOT(FIND({Last Invoice Month}, Invoices)), {Last Invoice Month} & ",") & Invoices, 

   {Last Invoice Month}

)

I set up the Invoices table like so:


Then I set up an Automation to trigger when an Item record’s {Last Modified Month} field changed. The Automation includes a simple Update Record step that copies the value of {Composed Invoices String} into the {Invoices} link field.


The end result is that every time you update an Item, if it hasn’t already been associated with an Invoice record for that month it gets linked to an Invoice record for this month. So long as the Invoices’ table’s primary field isn’t a formula, a new Invoice record will be created if it doesn’t already exist.


Thanks so much @Kamille_Parks! It may take me a little time to churn on this and get my head around it, but going to start working through.


Enjoying learning and appreciate the guidance and help!

Reply


Terms & Conditions