Sum calculations logic - when the item price changes

[Nils_M_Alexande]

Hello All

A customer has rented som stuff from us, and all is just fine …
I have a record in the system saying … rented item x for y days at a daily price of z
The price is y * x … so far so god

Now … for some reason the base price for rental is increased or decreased ( a sale or a price hike)

I need all now orders to reflect the new price (z-new), but not the old ones …They should still use z.

So …What is the best way to make this happen?
I already have in place a field that updates based on rental returned checkbox, that reads “completed” when this checkbox is checked

Kind regards Nils

[Justin_Barrett]

I suggest using a rate lookup table. I use this kind of setup for my business base. Here’s how it looks:

The {Rate Name} field is a concatenation of the client and description fields. The {From} and {To} date fields allow me to track when a rate is active. If there’s only a date in {From}, then the {Current} formula marks it with a check emoji. This used in a second view to only show current rates, and that view is what is used when tagging line items with a specific rate in another table. If a rate is not current, then the {Rate Name} concatenation also appends the active date range for that rate, so that I can see at a glance when an older rate was used.

I can see something similar working in your situation. Instead of a client, you’d choose a rentable item from another table. The {Description} field could be used to label special sales. When a rate goes up, fill in the end of the old rate in the {To} field, then make a new record and mark the start date of the new rate.

When setting up a rental record, you could still pick the rental item as you do now, and then pick the rate from this rate table in another field, or drop the item field and just use the rate field, as its name contains the item name. Add a lookup field to pull in the rate for that item, and use that when calculating the total price. When the rate changes, you’ll be pointing to a different rate record for the same item, so the old price will stay in place for old rentals, and newer rentals will use the new price.

[Nils_M_Alexande]

Thanks a lot for your reply. Great stuff …

Just to check with you … and the reason for asking is that I don’t know AirTable very well as of now :slightly_smiling_face:

So there is no way to tell a field inside Airtable to stop updating / freeze its content and keep its current value … based on the input from another field … example a returned field for a rental that has been checked

I can see that you suggestion will work, but I can also see that it will trigger a lot of work, as a typical rental facility will have a lot of items … (large and small)

Kind regards Nils

Kind regards

[Justin_Barrett]

Nils_M_Alexandersen:

So there is no way to tell a field inside Airtable to stop updating / freeze its content and keep its current value … based on the input from another field … example a returned field for a rental that has been checked

Correct. Airtable fields are either always dynamic—creating their content based on other fields, formulas, etc—or always manual/static. They can’t change type or freeze based on certain criteria.

Nils_M_Alexandersen:

I can see that you suggestion will work, but I can also see that it will trigger a lot of work, as a typical rental facility will have a lot of items … (large and small)

Maybe, but how many of those items will be changing price all at once, or be affected by a sale? If it’s a lot, then doing the upkeep might be taxing, though there still might be some ways to bulk-process things with the right setup.

As far as the freezing thing goes, that might be doable by integrating some external tools. Instead of working directly within Airtable for setting up rentals, you would work through a separate interface built in something like Jotform, which can pull Airtable data, then feed data back into Airtable via Zapier (using the On2Air: Forms and On2Air: Actions products by @openside). With that, the rate for a given item would be read into the form, then saved into the reservation record as a static value, not as one driven by a lookup or formula. When the rate changes, it only would affect the price of future reservations, as all rate data for past reservations is still static data.