Estimates with multiple products/ Multiple Products with Multiple qtys

[Greg_Noonan]

Hey all,

I’m trying to create an estimating tool that pulls pricing details from one base and sums them together.

I have 10 “item” fields and 10 “quantity” fields. The item field is linked.

The goal is to select the item, enter a qty and then get one big total for all the prices as well as the individual totals for each item * qty.

Problem 1: I now have 10 linked fields on the services base. I can just hide them, but I don’t understand this at all.

Problem 2: Do I have to create 10 lookup fields to pull the pricing details into the estimate base and then have that be the data that’s used for the formula? Is there an easier way?

Thanks!

[AlliAlosa]

Hi there @Greg_Noonan!

I think you’re probably missing a junction table in your base structure. Take a look at my example base for invoices and line items:

Airtable

Invoices & Line Items (Example Base) - Airtable

Explore the "Invoices & Line Items (Example Base)" base on Airtable.

The base is structured to allow for multiple quantities of multiple items on an invoice (or in your case, an estimate).

I hope this helps to point you in the right direction! Happy to answer any questions you might have.

[Greg_Noonan]

This makes sense!

Just to make sure I follow:

“Items” is your product catalog, and sets the pricing.

“Line Items” is where you’re constructing the actual invoice

“Invoice” is basically just used to summarize the “Line Items” info into a customer facing document and to facilitate the final business math?

Can you summarize your workflow with these 3 sheets if you don’t mind - I think I’ve got it, or at least can make it work, but I want to double check.

Thanks either way!

[AlliAlosa · Accepted solution]

Hi Greg!

Yes, looks like you’ve got it down!

[Items] could be considered a product catalogue - each item is unique and has an associated price

[Line items] is where the invoice is constructed; where each record pertains to one {Item} and one {Invoice}. This is where varying quantities of varying items can be recorded.

[Invoice] is where you can sum up the total and assign the document to a customer. It’s also where the Page Designer block should be pointed if you want to use it to create an invoice or estimate.

When I’m constructing an invoice, I generally do everything from the [Line Items] table.

Hope this is helpful! :slightly_smiling_face: