Product Catalog & Orders using pricing schedules


#1

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.


Multiple Quantities in the same Field
Freezing Data From Linked Records
Multi-item selection lookup formula? or: Formula that can handle multiple data points in one cell
Linked field snapshot
#2

This is great - thanks so much for sharing:) Im thinking about starting to use this template… but is there any way to create a new client order all one place? (as far as i can tell, i have to create it in the client order table - then jump to the order line items table - be sure to select the correct order from the client order table - then enter the line items and this isnt ideal for the person at the front desk processing client orders) Thanks!


#3

(I want to apologize for the delay in responding. I had written about 2/3 of a reply, offline, and misremembered as having posted it already.)


I want to caveat this reply by saying my contribution to this base was the addition of the pricing schedules feature, which allows the user to change item prices without invalidating historical data. I may have spent an hour on it — if I include time spent getting coffee, and if I had at least a couple of cups while working on it.

That said, based on a relatively superficial test consisting of my creating a new order for a pair of end tables and a surprisingly expensive burnt orange sofa, I don’t see anything I would have implemented differently (well, except for something I did implement and would probably change — but more on that later). To my way of thinking, this is single-screen data entry; to flatten it out into truly a single screen would eliminate most of the benefits gained by building the system on top of an RDBMS.

New orders should be processed from the [Client Orders] table. When a call comes in, the CS rep first creates a new client order record by clicking the plus sign below the last number in the left-hand column. (This ensures CREATED_TIME() and the actual time of order are identical.) Next, he or she clicks the plus sign in the {Client} field. This drills through to the [Clients] table and allows either an existing client be selected from the list of known clients or, for a first-time order, new client data to be captured and entered. Once either of those steps has been completed, the user is returned to the [Client Orders] table and view.

Now, what just happened here may vary based on interpretation. To you, this may seem like the rep ‘jumped’ from the [Client Order] table to the [Clients] table and back again; to me, the rep remained in the [Client Order] table the entire time but accessed, when necessary, a related table in order to access data needed fully to populate [Client Order]. Note the rep was never required to navigate from one table to another; instead, when needed he or she passed ‘through’ the current table to an underlying one. Once the necessary data was retrieved from the related table, the rep was returned to the point of departure. This same ‘drill-through’ model pertains to the other linked records you mention — line items, for instance.

Note as well there is no need for the rep to "select the correct order from the client order table.’ Instead, because of the reciprocal nature of linked records, the line items added to the order are automatically related with the appropriate customer order, which in turn is linked to the appropriate customer. (To minimize confusion, though, I would reconfigure the {Belongs to Order} field in the [Order Line Items] table to turn off ‘Allow linking to multiple records.’ While the link from orders to line items should be one-to-many, the reciprocal link should be one-to-one; otherwise, upon adding a line item to an order, the rep will be prompted for a new “belongs to order” link.)

The one thing I don’t like about this base — and that you might wish to modify for your own use — is the handling of the current pricing schedule. As it stands now, {Schedule} is a single-line text field within the [Order Line Items] table in which a two-character code is entered indicating under which schedule the item is to priced. In theory, the currently active schedule can be specified by setting a default value for {Schedule}. Unfortunately, at present Airtable does not honor default values for fields in linked records; that is, when the rep drills through from [Client Order] to [Order Line Items], instead of its configured default value, {Order Line Items::Schedule} displays a blank.

Ultimately, I chose to keep {Schedule} as a line item field, even though doing so means anyone entering orders from the [Client Order] table will need manually to specify the pricing schedule, for a couple of reasons: First, I assume Airtable will correct this oversight in an upcoming release, but second, and more importantly, doing so allows an order to contain items priced according to multiple schedules — for instance, to support a ‘rain check’ on an order placed under an earlier schedule, or to reflect a special offer or group discount. However, should such concerns not apply in your case, you may wish to make the schedule a variable at the [Client Order] level, with one pricing schedule for all items in an order. If so, you’ll want to define {Schedule} as a single-line text field in the [Client Order] table; you’ll also want to change the definition of {Order Line Item::Schedule} to a lookup field using the {Belongs to Order} link to access {Schedule} in the linked record.

Assuming you don’t make such a change, you may want to take a look at the latest version of this base — specifically, the [Order Line Items] table. There I have changed the formulas for {Price} and {Total Cost}, and I’ve added a new field, {Alert}, to make data entry a little more foolproof. (You can find the syntax of the new formulas within the field descriptions; that way, you won’t need to copy the base simply to access field configurations.)

If you have further questions about this base, please ask. :wink:


#4

Thank you so much!!!


#5

Hi - Thank you so much for your answer. I now understand the base better, and we are going to start using this… So I’m quite excited about it.

I really appreciate your instruction on the steps to follow in creating a new client order, however there is still one step in this process that I dont fully understand…

When you say " upon adding a line item to an order, the rep will be prompted for a new “belongs to order” link.)" This is the part Im having trouble with:) Im not seeing this…

Could you describe the steps that come after adding the information in the Client Order table - When Im ready to add the line items to the order… What are the steps for this?

THANK YOU!!
-Dana


#6

Ah, that’s probably because I made the change I recommended you make. (I wasn’t sure how much you had customized the base, so I didn’t want to assume you could pick up a fresh copy and start over.) Originally, there had been a one-to-many link from each [Order Line Items] record back to [Client Orders] via the field {Belongs to Order}; I changed it to a one-to-one link, as it should be. When the rep creates a line item via drill-through from the order, the order<->line item link is defined. When the reciprocal link from the item back to the order was defined as one to many, in the expanded line item record there was a prompt encouraging the user to define links to additional orders; that prompt no longer appears.

Let’s see: I’ve created a new order; I’ve either selected or created a client record; I’ve entered the client order-specific values (for instance, order number; fulfill-by date). At this point I’m ready to specify the actual items included in the order.

  1. I click the plus sign in the {Order Line Items} field. This prompts me to choose or create a new line order record. Unless I’m somehow having to recreate an existing order (perhaps it was accidentally deleted), I am always going to want to select + Add new record at the bottom of the popped-up record list. So I do (select +Add new record, that is).
  2. This causes a new [Order Line Items] record to be created and expanded (popped up) on the screen. The name will be partially completed and should reflect the customer whose order I’m processing; if you scroll down slightly you’ll see the reciprocal link back to the containing order. (This is where the erroneous prompt used to appear.)
  3. Each line item should represent one or more instances of a furniture item. Let’s start with that: Click the grey box labeled + Link to a record from Furniture.
  4. This causes a list of all furniture items in your catalog to pop up. Scroll through it until you locate the first piece of furniture to add to the order — say, the Kelton Table Lamp. Click on the lamp’s record.
  5. The [Furniture] list should disappear, and the Kelton Table Lamb should appear under ‘Furniture Item.’ However, you’ll notice no unit or total price is shown. This is because the base does not yet know under which pricing schedule this order is placed. Accordingly,
  6. In the {Schedule} field, enter '02' (without the quotes). Once you do, you’ll notice the [unit] {Price} updates automatically.
  7. Finally, enter the quantity desired. (Depending on what you are selling, you may find it’s worth your time to set a default quantity — for instance, if customers always order only one of any item.) Once both the quantity and unit price are known, the total cost of the line item is calculated and filled. (Had the furniture item and quantity been entered without specifying a price schedule, a warning message would have appeared in the {Alert} field.)

At this point the rep can close the expanded record window. Upon doing so, he or she is returned to the [Client Order] grid view. If there are additional items to add to the order, steps 1 through 7 are repeated until all items have been ordered. The order entry process seemingly ends at this point, and the order [presumably] passes into the order fulfillment stage.

Is that what you were looking for (although probably with fewer words)? :wink:


#7

Hi - Sorry it took me so long to really examine this and - Oh - yes - thank you SO MUCH! This is exactly what I was looking for! I had no idea you could use the little PLUS sign to add the products to order line items via a popup window - this is GREAT! we’ll be using it regularly - THANK YOU SO MUCH!! It’s exactly what I was hoping for - so you really can enter everything in one screen. Thanks again!! - Dana


#8

Hi - this has been so helpful - really thank you so much.

I realized I do have one more hurdle to overcome…

We’ll be using this base to keep track of our inventory and sell products & create receipts at the front desk for walk-in customers and those with appointments (we’re two acupuncturists selling Chinese Herbs)

I’ve customized the base to keep track of inventory, create our Purchase Orders for each supplier, and we’ll be able to add inventory from each PO when it arrives pretty much with one click. We’ll also be able to see at the end of the month how much was sold by each of the two acupuncturists for our bookkeeping. So it’s pretty amazing

But I also have a system (zoho subscriptions) that processes my recurring monthly orders, and each month I will want to transfer these into our base as client orders. Is there a way to import those into this base?

I’ve actually already been importing them from a google sheet into another airtable base… but that base does not have the order line items on a separate table the way this base does…

Each order would specify a seller (something I’ve added to id which acupuncturist this sale is associated with) customer name, date, and the order line items (usually between 2 and 4 items) with the quantities of each item.

Is there a way to import this information from a google sheet (or csv or something similar) into this base as client orders with associated order line items? Or is there another way to import them? I would want the products taken out of inventory… And for the totals to show at the end of the month as sales associated with one of the two “sellers”

Thank you!! my mind is just going on circles on this one:)

  • Dana
    PS - i never mind too many words… helps me get all the details and see it in action through your storytelling!!

#9

Jumping in here. I have successfully done similar things by creating a Zapier.com integration. This depends on how you get those orders from Zoho -> google sheet -> airtable (and why the intermediary google sheet?)

Zapier should be able to directly connect Zoho to Airtable and then you may just need another field (checkbox?) that is “recurring monthly order” that specifies that this will then be made again and again.

Zapier has a lot of its own triggers that allow it create actions so I could see you using Zapier to add new orders to Airtable (potentially a different base) and then each month look at that base and send ALL the orders in it to the Airtable order base. Since the free tier is limited to 3 steps and 100 zaps/month you may have to be creative about how exactly you move this data.

Most importantly, Zapier is only ONE WAY. So modifications to data are not synced back to the source. Also, most of the triggers are “New Data in Data Source” or something to that effect. There is an airtable trigger called “New Record in a View” so you may be able to play with that (Recurring orders populate out 12 months or more automatically in a “recurring orders table”, and then you change the “make me some orders view” to the next month (MANUALLY :frowning: ) and it creates orders (via Zapier) based on those clients).

This last part seems clunky and prone to error and I bet there are more streamlined ways to do all of what I just suggested.

tl;dr ZAPIER or Automate.io or any number of other API-er services


#10

Aren’t free accounts limited to two-step Zaps? I was under the impression that anything that took input from System A and output it to System B required a paid account. (Triggering on a new record and updating the same record would be two-step.)

@Dana_LaVoie I don’t have any idea how I missed your message originally. Are you still looking for assistance?


#11

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


#12

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?


#13

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


#14

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…


#15

5 hrs!?! That’s not good


#16

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.