Product Catalog & Orders using pricing schedules

[W_Vann_Hall]

A recurring question on the forum is how to change item pricing without corrupting existing data. With the Product Catalog & Orders base in Airtable templates, for instance, changing an item price changes the price shown in current and fulfilled orders – not necessarily what one wants should a refund be required.

I made a quick-and-dirty mod of the Product Catalog and Orders base to make use of price schedules. This allows items to be repriced going forward while leaving existing data unchanged. To provide this functionality, I added the [Price Schedule] table; added two fields and modified a third in the [Furniture] table; and added two fields and modified a third in [Order Line Items]. It should be easy to find the little I changed: Any field with a description is one I added or modified, and the description explains why.

[Avid_Huck]

But maybe that’s not what you’re talking about?

[W_Vann_Hall]

This is what I thought their current free plan offered.

From their FAQ:

Multi-Step Zap

A single step Zap has one Trigger and one Action. If the Zap has more than one Action or includes Filters or Searches it is considered a Multi-Step Zap and is not available on the Zapier Free plan.

Is that a relatively new account you’re using?

[Avid_Huck]

no, i have had this free account for 4 + yrs

[W_Vann_Hall]

Ah, maybe you’ve been grandfathered in at a different level of ‘free’ account.

I knew you’d been working with Zapier for a while, but I thought perhaps the particular account pictured was still in its first two weeks. From online comments from early adopters, it appears there may be any number of legacy free accounts still in play; someone mentioned being on a plan with a five-hour (!) polling interval.

I’ve also discovered it can be difficult to see Zapier’s standard offerings if one is on a legacy or otherwise atypical plan. To access a screen containing the plan definition I posted, I had to log out of my usual account and log in using a secondary account I’m not even sure why I have. (To document how to share Zaps within a workspace, perhaps?) Initially I tried to access the ‘plans’ page from an ‘incognito’ session, but you can’t see actual pricing info unless you’re logged in…

[Avid_Huck]

5 hrs!?! That’s not good

[W_Vann_Hall]

Yeah, that was someone who began using Zapier back when it was analog, and the 5-hour comment was made a while back. No doubt he or she has been brought up to parity with everyone else by now.

In my experience, most SaaS (and cloud hosting sites, for that matter) grandfather in accounts with service levels better than comparable current offerings, while older accounts with service levels inferior to those available today are typically improved to match. Which is awfully nice of them, I have to admit.

[PTNTH]

@W_Vann_Hall This is almost exactly what we need (thank you!).

What if Order Line Items > Furniture Items is set to “Allow linking to multiple records” - so the Prices lookup gives two or more results of the string? i.e. 01:5429|02:5836.18|, 01:2337.5|02:2512.81|

The Price formula only returns the result of the first instance i.e. $5,429.00

What if we want to return, and sum, both results with the 01 Schedule from the string? i.e $7,766.50

[W_Vann_Hall]

That’s the reason for Order > Order Line Items being set to allow multiple records, and for Order line items to allow an amount multiplier. You need to have a 1:1 correspondence between line items and furniture items to be able to map them correctly. (I suppose you could support multiple furniture items per line item by rolling up the individual prices with a SUM() aggregation function, but you couldn’t do so and still be able to support pricing schedules.)

Somewhere there’s a handy way to describe why it’s ok for Order > order items to be a one-to-many relationship but line items > furniture items must be one-to-one, but I can’t find it at the moment…

[PTNTH]

@W_Vann_Hall ah, I see now. Thank you for the explanation :thumbs_up:

[W_Vann_Hall]

It wasn’t a very good explanation… :winking_face:

Because Airtable currently lacks processes to support array manipulation, it’s very difficult to write formulas that can access all the instances in an array. (That’s why the schedule prices are passed in that odd 01:###.##|02:####.##| format: I have to be able to tag each instance with an explicit label before I can access it.) For the present, this limits what can be done sanely with lookup, rollup, or linked-record fields.

That’s a slightly better explanation :slightly_smiling_face: