I have a set of related FMP 5.5 databases for billing. They include a "Line Items" file that picks out the appropriate price for a product from a related 'Products' file based on a case calculation (each product has three possible prices.) This works great, except when prices need to be adjusted. I need to be able to change the prices (for future records) without affecting the prices on old line items. I think I'm missing a key concept in the way these files are related.

Any suggestions for how to fix this would be most welcome. Thanks.

For things like this, you are better off using lookups for the prices rather than a direct related value. Looked up values in your line items file will not change when the value in the price file changes.

Thanks! I knew it was something obvious I was overlooking. Any advice on how to make the switch from using related records to lookups?

There are 2 things to do (besides backing up first), not necessarily done in this order.

1. Change the Price field from a calculation to a number field.

2. Set the field, in all records, to the calculation you're using now.

Best done on a copy of the file. Or on the file opened locally with no guests, after backing up.

Method A:

1. Copy your current calculation (best save it as text).

2. Change the Price field from a calculation to a number field.

All data will disappear, because it's relying on a related field.

3. Replace into all records, in the Price field, using the calculation.

There is no Undo after Replace.

Method B (same but safer):

1. Copy your current calculation (best save it as text).

2. Create a 2nd field in the same file, Price2, number.

3. Replace into all records, in the Price2 field, using the calculation.

4. If it looks OK, change your original field, Price's calculation to = Price2. Close Define Fields.

5. Open Define Fields. Change Price to a number field?When you change a calculation field to a number field, and all parts of the calculation are local, the result of the calculation will become the value; it won't be lost.

6. Delete Price2

Both:

1. Define Auto-enter Options, Lookup, based on ProductID to the Product file , or to a Product_Prices file with prices (this would be separate from Products, with possibly several entries per product; it depends on your price structure).

I don't remember what the calculation was. The above will probably need to more complex, to implement the same business logic as your orig. calc..

Because a lookup is based on a relationship, it has 1 field on the left matching 1 field on the right. So you have to incorporate your logic into this field, which will likely be a calculation, on the left.

On the right you may need several record entries per price, so that a match will pick the correct one. Several fields will not work, because the lookup can only pick from one field. It probably cannot be calculated in that file, because the Prices file is unaware of what's happening in the invoice line items file (unless you script data entry).

There's also the possible use of lookup "next lower" or "next higher," if no match.

Much depends on what your original calculation is, and what your price structure is.

Thanks Fenton! Now I understand where to start.

With help from this thread and this thread I understand that I need to have the lookups for each type of price in the line items file along with a calc that chooses the appropriate price.

Thanks to all who contributed to these threads.

Mel