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!
Best answer by JonathanBowen
Matthew_Fox wrote:
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.
Hi @Matthew_Fox - you don’t have to add customers on the event table. This is the workflow I would follow:
Create your event on the events table
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)
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).
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?
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.
Hi @Matthew_Fox - have a look at this simplified version of your base:
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:
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?
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:
Hi @Matthew_Fox - have a look at this simplified version of your base:
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
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.
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.
Hi @Matthew_Fox - you don’t have to add customers on the event table. This is the workflow I would follow:
Create your event on the events table
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)
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).
