Aggregating Pricing for Quotes for Multiple Items

And the build list page looks like this:

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.

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}&','&
                [...]
            )
        )
    )

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

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…