Aggregating Pricing for Quotes for Multiple Items

[John_Braithwait]

So I have a client with a Treehouse business. They have a list of timber elements with pricing associated with them (see first attached screenshot).

They want to build up different pick/cutting lists for each type of build (Towers, swing sets and so on). This will ideally ‘pick’ the price from the Elements spreadsheet multiple by the quantity (as there are often more than one) and then display the total cost price of that particular element.

The only way it seems to work is if each of these is done one by one with a separate column for Quantity and Type and then a calculation. What this means is that this is very wide and cumbersome.

Furthermore:

• Different builds have different quantities of both Elements as well as Element types
• I cannot create (I don’t think) a pick list per element (i.e. quantity of Element and Element) per build
• This is then supposed to go into a master quote which will include not just the timber, but also other things like bolts / screws, accessories, accommodation and so on. like this:

I am sure there must be a better way of getting this ‘Timber Pricing’ sheet sorted out so that it makes the whole thing cleaner. So before I do it all this way, can you advise of a neater way of getting these items in?

[John_Braithwait]

And the build list page looks like this:

[W_Vann_Hall]

I’m swamped and don’t have time to put together a demo base (at least not for free :winking_face: ), so here’s a view from 30,000 feet. Should it prove as incomprehensible as I fear, let me know, and I’ll try to expand as time permits.

---

Here’s how I’d do it:

1. Obtain from your client an item inventory for each model of treehouse supported.

2. Convert those inventories into a really ugly IF() statement that looks something like this:

IF(
    {TreeHouseType} = 'HouseType1',
    {MungedDate}&'-'&{CustID}&'-01-'&{Item01ID}&':'&{Item01Qty}&','&
        {MungedDate}&'-'&{CustID}&'-01-'&{Item02ID}&':'&{Item02Qty}&','&
        {MungedDate}&'-'&{CustID}&'-01-'&{Item03ID}&':'&{Item03Qty}&','&
            [...]
        {MungedDate}&'-'&{CustID}&'-01-'&{ItemLastID}&':'&{ItemLastQty},
    IF(
        {TreeHouseType} = 'HouseType2',
        {MungedDate}&'-'&{CustID}&'-02-'&{Item01ID}&':'&{Item01Qty}&','&
            {MungedDate}&'-'&{CustID}&'-02-'&{Item02ID}&':'&{Item02Qty}&','&
            {MungedDate}&'-'&{CustID}&'-02-'&{Item03ID}&':'&{Item03Qty}&','&
                [...]
            {MungedDate}&'-'&{CustID}&'-02-'&{ItemLastID}&':'&{ItemLastQty},
                [...]
        IF(
            {TreeHouseType} = 'HouseType3',
            {MungedDate}&'-'&{CustID}&'-03-'&{Item01ID}&':'&{Item01Qty}&','&
                [...]
            )
        )
    )

2. (cont.) I typically use Airtable to generate the text of the formula itself, which I then copy and paste into the formula configuration. See this reply for an example.

   Essentially, what you want as output is a field that looks something like

   20180512-01456-01-100/36:05,20180512-01456-01-100/24:02,[…]

   Each treehouse type will result in a different combination of component items and quantities.

3. Place this formula field (called {Items}) in your [Orders] table.

4. Immediately to the right of the formula field, define a new linked records field directed at a new table called [Items]; call this field something like {Link to Items}.

5. Now, as part of the process of creating a new treehouse order, the user will select the {Items} field and either

   1. Press Ctrl-C to copy the value.
   2. Select the {Link to Items} field.
   3. Press Ctrl-V to paste the values

   or

   1. Select {Items}'s fill handle (the small white square in the lower right-hand corner of the field) and drag it one column to the right, thus copying the value of {Items} into {Link to Items}.

      In either case, once the values have been copied into the linked-record field, Airtable will generate a new record in the [Items] table for each component of that ugly IF() statement’s output — e.g., ‘20180512-01456-01-100/36:05’ — with the record’s primary field set to that component.

6. In [Items], create two formula fields to extract the item ID and quantity from the string that makes up the primary field. If each section making up that component is fixed-length, then you can most easily do so by using the MID() function. For instance, assuming the component listed in item 5, the item ID would be ‘100/36’, for a 3′6″ (presumably) 100mm post, and the quantity would be ‘5’.

   Based on those two values, calculate cost, price, shipping cost, and the like.

To generate itemized invoices, packing slips, and pick lists, create a {Line Item} field in [Items] that assembles a single-line entry — at fixed spacing — for each record. Somehow, link all records for a given order to another table (either link all [Items] records to a single record in another table and use a combination of lookup and formula fields to isolate only records associated with the order or — my preference — extract a unique key per order [e.g., {Date} + {CustID}] and copy/paste that value into a linked-record field directed at the [Invoices] table. From there, roll up {Items::Line Item} into the {Invoices::Line Items} field using ARRAYJOIN(values,'\n') as the aggregation function. You can then drop {Invoices::Line Items} into a Page Designer Block invoice and have it display a list of items included in the order.

The only caveat that comes to mind is this approach assumes no customer will order more than a single instance of a given treehouse style on a single day. Should you live in a more treehouse-mad section of the world, you will need further to differentiate among orders, way back in that ugly IF() statement — for instance, by adding an autonumber field to [Orders] and including it in the component string.

---

That’s longer than I expected it to be — but probably no more coherent. Any questions, let me know.

[John_Braithwait]

Thank you very much for the reply, it is greatly appreciated. The trouble is that the treehouse are all, in themselves, unique so whilst they are built on a set of relatively normal standards (lengths of timber) each one is created based on what the client orders.

So it does really need to go from a ‘cutting list’ backwards. So, for example, a twin tower treehouse with v bridge would be:

• One 3.6 x 3m 1.8 High Platform
• One 2.4 x 1.8m Tree House with Shingle Roof
• One 3.6m V Bridge
• One 1.8 x 18m High platform with Roof
  And each of the above has it’s own list of items.

I really appreciate your response however, but it needs to work from:

• Component List, to
• Build Item, to
• Tree House Quote

Many thanks

John

[W_Vann_Hall]

That’s even easier, then — all the necessary pieces are set out in my earlier reply. If I can find the time, I’ll try to rough in a possible approach…