Lookups or Relationship Calcs

[geod]

Debated over where to post this topic, but since it covers fields, what kind to make and where, I have put it here. Situation is: FileMaker Server 5.5, clients using FM5. A mailorder system is being developed, the files concerned here are the invoicing files, header and lines and the product file. My employers want to offer some of the items in our catalog on sale. Like "buy 4 or more of this item get it for this reduced price." I have placed fields in the Product file, Sale field: toggles between Yes or No to set the item on sale or not. Sell Number field is a number field which determines the number of the item they must buy to qualify for the sale price. And Sale Price field is the sale price. This has to be made totally automatic and transparent to the operator. My question is what is the best way to go: 1. Create the sale fields from the Products DB in the Invoice Lines DB and do a lookup at data entry time to get the values and then perform the needed calculations in the Invoice Lines DB or 2. Have the calculations in the Invoice Lines DB get their values via a relationship to the Products DB. I have it working on my standalone deveopment machine, via method 2. But I am still having a debate in my head over the best way. The Invoice Lines DB tends to be the biggest file with the most records so I am cautious about putting more data fields in it. Bu the other way involves getting values thru relationships in a multiuser setting. Anyway I am fretting abit, hope I have been clear and will appreciate any responses.

Thanks

geod

[LiveOak]

In invoicing applications, we usually end up with a fair number of lookup fields in the invoice and invoice line items files. This is because information on invoices is historical. If a customer address changes, we DON'T want the invoice updated. It is a historical document which must reflect the transaction at a point in time. The same would be true of product pricing. If you change prices in your Product file, you don't want the prices to update on all your invoices. Lookups are, however, not the only way to accomplish this. You can have a pricing file and never change any prices in product, just add new entries to the pricing file and cause new invoices to relate to new entries in pricing, where as old invoices stay pointed at old pricing records.

-bd

[Kurt Knippel]

Many times the seperate pricing file is the best way to go. Especially in your situation. Think about this, you may wanna have many level of sales prices: ProductA sold 1-4 is 10.00, 5-12 is 9.00, 13-22 is 8.00 and so on. By storing this in a seperate file you can have as many of these level as you want, with each product have varing levels of pricing.

[geod]

Thanks to you both. I now have a way forward. Live Oak describing the invoice as

a historical document was the piece missing from my train of thought. I probably

will not be creating a price file as the people I work for say they cannot forsee a time when they will need more than two pricing levels. Thanks again.