Relookup Automatically w/ Related Match Field

[cbsteven]

Hi,

I have a problem with my current database design. I stupidly made it so the shipping cost field is a simple calculation which just updated whenever anything changed. However, now I want to raise shipping rates. but of course I cannot do this without changing the totals for old orders.

I would like a solution which would update the shipping cost automatically whenever:

An item is added (via a portal)

The shipping method is changed (via a pop-up)

Whenever the location is changed (via text fields)

I am somewhat flexible on this, those three things are listed in order of importance (the top being most important). As far as I know the only way to do this is with a calc.

I know that I should just have a button somewhere that says "Update Total", which the user would press that would trigger a script to calculate shipping cost. However our employees are not all computer-savvy and I know they would simply forget quite often. This would not be a problem because I could force them to update it before closing out, but the system is being used while customers are on the phone, and they always have to have an accurate answer to the question "So how much will shipping be on this?" Whenever it is asked.

At first I thought the solution to this would be to use a lookup to a shipping table, using a match field which would be the total number of items in an order (and worry about the other two variables later). At first this seemed perfect because anytime the match field is changed, the relookup is performed automatically. Then I made a test file to check it out, and found that apparently a relookup won't occur automatically if the match field in question is a calculation which uses information from a related file.

I know that all of these criteria may make this impossible, but I'm still hopeful.

Thanks!

[LiveOak]

I'm not sure I follow the exact setup of your files. It's hard to give you an exact method without knowing exactly what fields are where. Some general observations are:

1) If you are using the term "match" field to indicate a key field, calculated key fields for relationships can't use related field in their calculation. To work they must be "indexable" and calculations based upon related fields are not.

2) What you need to do is capture the shipping rate information at the point in time the order is generated. This generally means you must use a calculation based upon a lookup of shipping rates, rather than based upon related fields (you don't want this to automatically change when the rate file is updated). This is common stuff for order/invoice system. The same is true for the customer's address, you want it to be a lookup and NOT be updated when the address in your contact file changes. Orders and invoices become historical documents which need to reflect addresses, rates, etc. in effect when the order is created.

3) Given 2), the update can be accomplished by doing a relookup on the field which triggers the lookup of the proper rates from the rate table. Adding related items should update the shipping total automatically, if it is a calculation. If it doesn't, it is probably the dreaded "portal refresh" problem. If this is the case, the calculation can be forced to refresh with a script. I like the "Enter Preview Mode" followed by "Enter Browse Mode" step sequence.

-bd