Line item rollup for specific months and clients

[MZ13]

I have a database with 3 tables - clients, line items, Invoices.

I would like to have a form where a client can add line items and then at the end of each month an invoice for each client would be created (if possible automatically add a record to the invoice table) including a rollup of only the items that were added that month and for that client.

I haven't found a way of doing this so any suggestions would be much appreciated!

[TheTimeSavingCo · Accepted solution]

Hmm for the form where the client can add line items, I'd suggest checking out Fillout.com (https://www.fillout.com/).  They integrate with Airtable and support line item functionality, which Airtable forms currently doesn't

The idea would be you'd have a table set up just to have these form submissions, and it'd have a linked field to Line Items.  You could also have a linked field to the Clients table here but that's probably a security / privacy issue, so I'd recommend just getting your clients to key in their emails and using an automation to match it against the Client record.  That form would thus allow them to identify themselves via email, and create multiple line items via one form submission

In the "Line Items" table, you would:
1. Create a formula field that will output the month year of the created time of that record, i.e. the submission date, and it would look like "Feb 24" or something like that. 
2. Create a formula field to combine that with the client's email address (or some other unique identifier), to come up with "test@example.com - Feb 24"
3. Create a linked field to "Invoices"
4. Create an automation that will trigger every time a "Line Item" record is created, and its action will be to paste the value from the field from step 2 into the linked field to Invoices

This will create one invoice per client per month, with all of the line items they've submitted across the month

[MZ13]

@TheTimeSavingCo Thank you for the answer!

I'm still pretty new to Airtable so I didn't fully understand (I'm sorry if I'm missing something basic), I've tried following through with the steps and haven't been successful.

Would you be able to clarify the following points?

1. Why does there need to be a separate table for submissions?

2. I like the idea of using emails to identify clients, would this kind of automation be able to link to a specific contact? And what kind of automation would I use?

2. I didn't understand step In 4, can you elaborate? How will a single invoice automatically be created that will include all client line items for that month?

Thank you so much!

[TheTimeSavingCo]

re: 1. Why does there need to be a separate table for submissions

Whenever a form's submitted it creates a new record, and so it pretty much just exists to accommodate the workflow really as there's nowhere else to put that data. 

---
re: 2. I like the idea of using emails to identify clients, would this kind of automation be able to link to a specific contact? And what kind of automation would I us

Yeap it would, you'd trigger the automation whenever the form was submitted and it'd have a "Find Record" action to look for a record in "Clients" with entered email.  If it found one it'd link'em together, if not it'd email you and tell you there was an issue

---
re: 2. I didn't understand step In 4, can you elaborate? How will a single invoice automatically be created that will include all client line items for that month

When we paste a value into a linked field Airtable automatically tries to link it to an existing record in the linked table.  If no record exists in the linked table with the same primary field value, it'll create a new record for us, does that make sense?  This is one of those things that's hard to explain, but once you see it happening you'll be like "oh, yeah that make sense" heh

[MZ13]

Thanks so much for the clarifications!

Understanding the how linked records works is real game changer!

I have almost everything working, I'm just stuck on getting the automation to work for linking the submission email to a client in the client table. I've created the automation to find the record but I must have missed something because it's found the matching record but isn't linking them (screenshot showing what I've done).

Thank you so much for the help!

[TheTimeSavingCo]

Interesting!  That looks like you've set it up correctly.  If you could provide a read-only invite link to a duplicated copy of your base with some example data I could take a look at this for you! https://support.airtable.com/docs/adding-a-base-collaborator#adding-a-base-collaborator-in-airtable

[MZ13]

I just went back and realized that I had been using an Airtable form for the submissions, I switched to a form through Fillout and it's working great!

Only problem is (which is why I had tried originally without using a fillout form) that one of the fields in the form is a duration.  A client would fill out 1:17 to indicate an hour and 17 min. etc.  In Fillout this field only lets you enter a time like 12:30, and only what's on the dropdown.

Any suggestion how to get around this without changing the way our clients input the information?  

Thank you!

[MZ13]

I just found a workaround. By changing the Fillout form duration field to 1 min increments it seems to solve the issue.

Thank you very much for all the help!