globals for prices

[ggh]

I'm updating an old FMP 4 database that used to be used by a single user. The database keeps track of food orders. I used to have a layout with all prices (set annually)for items contained in global fields. This database will now be hosted. My understanding is globals will hold as set before the database is hosted, but the users will not be able to change prices without working off the server. Is this correct, and is there a better way to make this information available to all tables.

[comment]

Create a table where each product has a unique record. Lookup the price from this table. I believe this is the preferred method, whether the file is hosted or not.

[ggh]

I've spent time trying to wrap my head around how to do this with lookups and I'm not getting anywhere. Maybe I'm stuck with an old construct in mind, or maybe I didn't give enough information.

I have one layout that contains a food order for a child. They select the number of each item they would like. There is a calculation field that generates the fee for the item (price*number_ordered). Another field then calculates the total food fees. The number of items ordered is used in summary reports used by the staff purchasing the food. Again, I used to have the prices set into globals on a layout. I get that this is cludgy and not the right way to do it. I get that I need a prices table. One record for each makes some sense, but there is often different pricing structures for different item. As an example hotdogs are $1 each - simple. Pizza is $3 a slice but $2 for each additional slice. This makes a simple "price" field not workable. Thoughts? Suggestions?

[David Jondreau]

Pizza is $3 a slice but $2 for each additional slice. This makes a simple "price" field not workable. Thoughts? Suggestions?

If you want to keep it simple, have two records for pizza slice. First Slice @ $3 and Add'l Slice @ $2 and train the cashier to enter it properly.

[ggh]

Suggestions for setting up the relationship if orders for each kind of food is one one record?

[David Jondreau]

Don't have them as one record.

If someone orders 3 slices, make two records, one for the first slice, one for the other two.

[comment]

there is often different pricing structures for different item.

It's difficult to give a one-answer-fits-all on the basis of a single random example. There are methods to deal with price structuring, but they only fit when the pricing of all products can be forced to conform to some common logic.

A simple way to deal with your pizza example would be to have two fields in the product table: BasePrice, and UnitPrice. The pizza slice has a BasePrice of $1 and a UnitPrice of $2. You need to look up both fields and calculate the price as BasePrice + Quantity * UnitPrice. Note that the hotdog has a BasePrice of $0 and a UnitPrice of $1.

With a more complex pricing structure you would have a PriceList table as a child of the Products table, where each quantity (or rather quantity boundary) has a separate record, e.g.

ProductID: 123

FromQuantity: 1

Price: $10

ProductID: 123

FromQuantity: 100

Price: $8

ProductID: 123

FromQuantity: 500

Price: $6

You lookup the price from this table using a relationship that matches the ProductID and looks for FromQuantity that is smaller or equal to the actual quantity purchased.

[ggh]

Ok, I'm getting the idea, and rethinking the entire structure of this project. Thanks for everyone's help. More relational tables will make this much easier to maintain. And will simplify the needed reports.

Now for the next question(s). I've attached an partial image of the current order form. It gives examples of the kinds of situations for the orders. Smoothies have two choices, and the price is the same for each and only one can be ordered. Pizza has two choices and the price varies depending upon the number ordered (1=2.50, 2=3.50, 3=4.50, 4=5.50). Culvers has 4 choices (each item a different price) and if you order an item, you can get an additional item at a lower price.

I'm thinking the fee displayed for each item order could be a calculation that used case the case function to account for all of the pricing variables. Does this make sense?

1) How could I deal with the add-ons.

2) If I create each item ordered as a separate record, I need a clean way to display the choices. A portal with all options would be confusing. I'm thinking a portal with two relationships - a constant and maybe the "Day". Any other ideas?

Again...thanks for all of the help. I'm feeling like a real armature with this.

[comment]

I'm afraid that's a bit too much all at once. To address just a few points:

• You should definitely have a separate record for each ordered item, display issues aside.

• Any calculation of the price must be based on fields from the item record only - otherwise your history records will be modified when prices change. That's why a lookup is necessary.

• It's hard to tell from the picture what the workflow is, and what other variations there are. Maybe you need to build a special layout for entering an order, then have a script take it from there and create the necessary "real" records.

[ggh]

• Any calculation of the price must be based on fields from the item record only - otherwise your history records will be modified when prices change. That's why a lookup is necessary.

• It's hard to tell from the picture what the workflow is, and what other variations there are. Maybe you need to build a special layout for entering an order, then have a script take it from there and create the necessary "real" records.

Ok, here are a few more details. The price isn't such an issue. This is a once a year order placed with a school's PTA. Each year, the orders are cleared and the prices reset. I want parents to be able to access the database via IWP and place orders for each of their kids (They'll have one opportunity to modify their orders once placed. I'm thinking two records for this tagged with - original/current.).

I suppose I could create a layout similar to what they're used to seeing with all globals, then have a script generate actual records.

[comment]

Possibly - these kind of questions require a detailed knowledge of the solution's requirements and the intended workflow.