Updating Calculation field pricing structures without writing over old data

[mpau]

Hi all,

I've got 3 related tables:

Orders

Products

Orderline

Orderline is a child table of the other two.

I'm trying to trouble shoot a calculation field on the order page, a sum calculation. I have to periodically change pricing for shipping rates and in this case want to be able to update the shipping rate field on the Products page without overwriting all the order records that calculated shipping totals using the old rates. Is there a way to do this?

On the orders table I've got a field called "Shipping Total"Sum ( Orderline::ShippingTotal )

The Orderline field "ShippingTotal" is also a calculation: Quantity * ShippingUnitPrice

and ShippingUnitPrice is yet another calculation:

Case (Type of Salefk = "Wholesale" ; Products::Shipping ; Type of Salefk = "Direct" ; Products::Shipping ; Type of Salefk = "To-The-Trade" ; Products::Shipping ; Type of Salefk = "Drop-Ship" ; "0")

Essentially the ShippingUnitPrice = Products::Shipping

Everything works fine here, except for when I need to change/update my shipping rates. Plugging in new shipping rates writes over all my old data. Is there a way to work around this?

[comment]

Use a lookup - same as you do with product prices.

[mpau]

Lookup doesn't work as it only populates a field upon record creation.

The Order record is created before the multiple Orderline related records.

The sum calculation I'm doing is done after or, even better, while the orderline records are being created.

Orderline records are created in a portal on the order page.

[bcooney]

You need to store ShippingUnitPrice in OrderLineItems rather than having it an unstored calc to Products.

[comment]

Lookup doesn't work as it only populates a field upon record creation.

No, a lookup is populated every time the matchfield is populated (and there will be a relookup when the matchfield is modified).

Ideally, there should be a daughter table of ShippingRates with a separate record for each ProductID and TypeOfSale combination, and your orderline would lookup the rate from there. But if you just turn your ShippingUnitPrice into a Number field auto-entering the existing formula, I believe that should work too.

[mpau]

Thank you both for the comments, unfortunately I'm getting confused. Maybe I haven't explained something right, or more likely I'm not understanding something correctly.

I'll try the lookup, but since I need to do a sum calculation of the extended shipping total on all of the related records, what am I having the field lookup?

I have the same question in regards to changing pricing for my products, so maybe the easiest way of asking my question is:

Is there a way to structure calculations so that they won't overwrite old records when you update data in source fields, ie as in my case where I need to periodically change pricing for products or for shipping?

[comment]

It's best to take pricing first, because it's simpler (only one price per product). I have assumed you already had that part set up correctly. In a standard invoicing solution, the Products table has:

• ProductID

• Price

... other fields

and the LineItems table has:

• ProductID

• Price

... other fields

The relationship is:

LineItems::ProductID = Products::ProductID

Now, the Price field in LineItems is defined to lookup the value from Products::Price. This value is then stored in the LineItem record, and will NOT change when prices are modified in the Products table.

[mpau]

Unfortunately, in my case I have 4 prices for each product: retail, wholesale, drop-ship, and to-the-trade. So the price in my orderline table is actually a auto-enter case calculation where the result depends on what type of sale is being placed. So, if its a wholesale price, the price refers to the wholesale price on the product page, and so on. This works, allows override on entry, and permits me to change the price per product without altering old records. The only problem with this is that if I need to change the item ordered on that particular line I'll have to manually enter all the data as it only auto-enters on creation.

Edited October 22, 2008 by Guest

[comment]

Well, that's a complication, but still the price in the lines table must NOT be a calculation field. As I said before, you can make it auto-enter a calculated result or - preferably - lookup from a Prices table where each price has a unique record.