Airtable Free: Create Invoice between last invoice...

[Matthew_Fox]

Hello Airtable Rockstars!

Could you please help:

My Challenge: I want help with creating an invoice. I’ve built out the system, however I am having trouble getting certain information to automatically populate.

Details:
I have a weekly event happening for 6 weeks for a total of 6 events. I’d like to print an invoice by customer or table number.

I built out a system with the following:

• Customer table
• Company Info for the customer
• Order table for individual customer orders - I also think this works as my join table
• Menu table listing out all available dish/beverage options
• Events table to define which week each dish/beverage is available
• Invoice table to link to the above

Certain things on my invoice table work:

• Connects and displays information from the customer and company tables
• Totals up the orders correctly

It doesn’t pull in the orders though. I have it linked to the order table, but I have to manually select each line item. Any thoughts on a way to have it automatically pull in the order information?

TIA!

[JonathanBowen]

Hi @Matthew_Fox - I’m not quite getting the structure of your base here. Are you able to share a link to a copy of this with sample data, so that we can see the table links you’ve got?

JB

[Matthew_Fox]

Hey @JonathanBowen

Thanks for the reply. https://airtable.com/shr11krIGzD6wOlZh

Take a look and let me know if you have any questions or need me to add more data?

[Justin_Barrett]

You can pull in the order information by adding a lookup field to your [Events] table. It would reference the {Orders} field, and bring in the contents of the {Food + Beverage Ordered} field.

Is that what you’re looking for?

[JonathanBowen]

Hi @Matthew_Fox - have a look at this simplified version of your base:

Airtable

Catering - Airtable

Explore the "Catering" base on Airtable.

Maybe some reworking of the tables and some workflow changes will give you what you want.

I’ve got an “Orders/Invoices” table and an “Order Items” table. Obviously, an order item belongs to an order and an order can have one or more order items.

From a workflow point of view, you would go into the order items table, select (or create new) an order (which is a combo of event and customer), select a dish and add a quantity. Each order item is one dish (but a quantity of 1 or more) so when a customer orders two different dishes, this is two order items (two records in the table).

You’ll see that the order item record has the unit price and total price.

Your “Orders/Invoices” table should end up something like this:

Now you can create a page designed block on the “Orders/Invoices” table which will give you the order and order item information:

Don’t know if you have seen this in page designer, but when you add the “order item id” field to the page, you can access its detail records in the order item table:

In the “Orders/Invoices” table you can add rollup fields to give the order total:

Does this help?

JB

[Matthew_Fox]

It’s close and this might solve a different piece that I hadn’t gotten to yet. :slightly_smiling_face: Thank you for the suggestion.

Still stuck with a manual process to associate orders with invoice a missing link back to the invoice table though. :expressionless:

[Matthew_Fox]

Thanks Jonathan - This setup is interesting and does work. Though the place it falls apart for me is around adding a new customer.

If I add a new customer to the setup it also looks like I have to also add them to the Event table as well… correct? In short, it doesn’t look like this setup allows for new customers to be dynamically added in. I debated on something like this that used Zapier to auto populate fields.

I need to keep experimenting with this.

[JonathanBowen · Accepted solution]

Hi @Matthew_Fox - you don’t have to add customers on the event table. This is the workflow I would follow:

1. Create your event on the events table
2. Create the event/customer combo on the orders/invoices table. If the customer doesn’t yet exist you can create a new customer in the modal:

(You’ll notice at this point that the event-customer combo has been added to the event table automatically)

3. Now in your “order item” table select the order (event-customer combo) and the order item (notice how this also creates an entry to the order item on the orders/invoices table).

JB

[Matthew_Fox]

Thanks @JonathanBowen and @Justin_Barrett!

I think I have what I need now. JB the walk through above was helpful.