Jump to content

Adjusting Prices in Related Billing Files


Melanitta

This topic is 7358 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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

Link to comment
Share on other sites

This topic is 7358 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.