Architecture question


#1

Fundamental architecture question:

I want to try creating quotes, work orders, and pricing with Airtable.
A quote would have normal data (like linked customer names, date, and valid-until date…) but it would also have line-item descriptions plus unit costs (all custom, no price-list). Something like:

  1. 32 Apples at $0.50/apple
  2. 6 pounds of Oranges at $0.15/pound
  3. 3 hours of peeling at $25/hour

I don’t think I can make a new table for each quote… with 100 quotes per week, that would get unmanageable. So, I can either have 48 extra field columns per record (in case 12 line-items are needed, each with a description, unit quantity, unit type, price per unit)… or I can use multiple records with the same quote number and somehow tie them back together when sending the quote or making pricing an order.

After the quote, we might get the job. Or we might get the job without a quote. So I might need to attach the quote pricing structure and part descriptions to an order table, or I might need to type them in the order for the first time (if the job wasn’t quoted before we got it). Then finally, I’ll need to invoice the job (I can send the info out to another piece of software, or I can do something in Airtable with Page Designer.

I can get to the details later, but from the start, the architecture/schema seems like an important consideration. Any advice on how to contain these multiple line-item pricing within a single-row order record? Is this a poor use-case for Airtable?

Thanks in advance!
-Mike


#2

That’s impossible, you need an auxiliary table: https://support.airtable.com/hc/en-us/articles/218734758-A-beginner-s-guide-to-many-to-many-relationships

So:

  • Quotes
  • Line Items
  • Works
  • Invoices (optional, depending on what needs do you have)

I do something similar in my Events base.