Calculation using related field

[FMMadMan]

Here is what I need to accomplish:

I need an order entry screen which includes the following items:

Quantity

Product ID

Description

Price

Extended Price

I need to be able to make multiple entries for products (like a standard order form). The Description and Price fields are related from a Product file using Product ID as a match field. The only fields actually entered by the user are Quantity and Product ID.

Here's where I'm confused. I need the price for each item to change according the Quantity ordered (i.e., if Quantity<25 then Price=$5.00, if Quantity>25 then Price=$3.75, etc.)

Because I need to enter more than one line of data I am using repeating fields for the fields in the Order file (Quantity, Product ID, Description, Price

Extended Price). However, the price calculation will only run on the first entry and this won't work.

I've been told that I need to use a "Line Items" join file and portal in the Order file for this to work. I don't understand this. I can see how it would work for an Invoice file where you are displaying the items ordered for a particular invoice #, but I don't see how this will work with individual line item entry on the order screen.

Please Help

Thanks for the help!

[Kurt Knippel]

quote:

---

Originally posted by FMMadMan:

Here is what I need to accomplish:

I need an order entry screen which includes the following items:

Quantity

Product ID

Description

Price

Extended Price

I need to be able to make multiple entries for products (like a standard order form). The Description and Price fields are related from a Product file using Product ID as a match field. The only fields actually entered by the user are Quantity and Product ID.

Here's where I'm confused. I need the price for each item to change according the Quantity ordered (i.e., if Quantity<25 then Price=$5.00, if Quantity>25 then Price=$3.75, etc.)

Because I need to enter more than one line of data I am using repeating fields for the fields in the Order file (Quantity, Product ID, Description, Price

Extended Price). However, the price calculation will only run on the first entry and this won't work.

I've been told that I need to use a "Line Items" join file and portal in the Order file for this to work. I don't understand this. I can see how it would work for an Invoice file where you are displaying the items ordered for a particular invoice #, but I don't see how this will work with individual line item entry on the order screen.

---

You have two DISTINCT problems, solve them seperately and you will have a much easier time of this.

Do not use REPEATING fields!! They will only complicate your life. Use a line items file in the same manner as you understand it for "invoice #", after all you are probably already working with an invoice or order number.

First problem:

Two easy methods can be used for the data entry:

If the relationship is set to allow creation of related records, then a blank line will appear in the portal, and you can simply enter data into that row.

Otherwise you can have a button on the screen which goes to the related file and creates a new record, via a script in that file.

There are also a whole slew of more complicated methods, which accomplish the same thing, but allow for trapping of various types of errors and make for a cleaner implementation of this process, however either of the above will work in the case.

Second problem:

Sliding pricing based on quantity is easy to implement in a couple of different ways:

You can make each quantity grouping a seperate product in your products files (I assume you have one of these from which you are getting the product information in the first place).

You can simply make the user enter the appropriate pricing.

You can make the unit price a calculated value (i.e. UnitPrice = Case (Qty < 25, 5, Qty >= 25, 3.75).

The most complicated to develop and most flexible/scalable and easy to maintain is to actuall have another file which stores the pricing information. So that a record in this file stores the productID, Qty Level, Price information.

for example:

Product A, 1-24, 5.00

Product A, 25+, 3.75

Product B, 1-10, 15.00

Product B, 10-25, 12.50

Product B, 25+, 9.75

etc...

------------------

=-=-=-=-=-=-=-=-=-=-=-=-=

Kurt Knippel

Consultant

Database Resources

mailto:[email protected]

http://www.database-resources.com

=-=-=-=-=-=-=-=-=-=-=-=-=