- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 08, 2025 11:37 PM
Hi,
I’m trying to update our CRM’s product listing with upgradeable pricing. The current structure is simple, one record per product, with price as one of the fields. Customer orders are directly linked to products sold.
Now we need to update prices. The challenge becomes that if we just update the existing products’ prices, historical data such as value of sold product would change, and any reports would change totally.
I have seen in similar systems that products may have Valid From and Valid To fields for prices. Or perhaps for the whole product record, I’m not sure.
Testing this concept I’m able to build a logic that tests if a product price is valid TODAY. I guess I could duplicate the whole price list, and use some “Valid today?” formula to determine which product to link to an order. But this would mean soon having a lot of rows in the table, depending on the frequency of updates.
So I tried to place “price validity” in an own linked table, with just name, price, valid from and valid to as fields. Linking to this from the product works nicely, and there I can pick out the price valid today. This would allow me to keep the actual products table neat and only have “validity” entries becoming a potentially long list.
But when I try to combine this with the Orders table it doesn’t work anymore 😊. The order should be linked to a product, and the product should have a linked price, based on which price is valid that specific day.
I tried to use both a look and rollup field and limit the results based on the Valid From and Valid to fields, but I can’t get it work. Additionally, these lookup/rollup conditional formulas don’t allow a variable from table it is placed in to filter, one can only use the different dates Airtable provides.
The idea I had was to use the Order Date as a filter to show only the price that was valid the day when the orders was placed.
I realise this must sound messy, sorry for that 😊. But if anybody would be able to hint in the right direction I’d be very grateful.
Cheers,
Björn
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 09, 2025 06:31 PM
Might be simpler to convert your system to log the prices at the time of sale instead, i.e. have a static Currency field that's populated when the item is sold with the item's price at that point
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 09, 2025 10:34 PM
Thanks @TheTimeSavingCo for the reply. How would I go about that? With linked fields they will always update, only way I can think of is to use an automation to "copy" the current price into a static field. Non?
The final use case is actually a little more complex. We have a long time window during which the client can buy, before ending the project, typically 2 months. This means I have to register the prices of product not yet sold at the time when the customer first registers with us. They should "keep" their price list until finished with their project, regardless of any pricing changes while them being in the funnel.
Cheers,
Björn
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 10, 2025 02:06 AM
only way I can think of is to use an automation to "copy" the current price into a static field. Non?
Yeap, that's right!
