Using related field in calculation not working.

[FMMadMan]

OK, here's my situation:

Here is what my database looks like:

Product List (filename)

product ID (field)

price (field)

Order Form (filename)

product ID (field)

price (field)

quantity (field)

The two files are related by product ID. I have set up a calculation in the price field of the order form that looks like this:

price =

case(quantity < 25 , Product List: :price , quantity >25 , Product List: :price - 2, 0)

Essentially what I need to be able to do is pull a price into the Order Form from the Product List according to the Product ID. I would then like to be able to change the value of Price in the Order Form according to the value of Quantity in the Order Form.

I am doing this because my company uses a variable pricing structure (i.e., the pricing for each product changes according the quanitity ordered).

If I can calculate price in the Order Form it will keep our product list from having to be so large.

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

/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/

FMMadMan

This *@!%*!%

database is driving me

nuts!

/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/-/

[This message has been edited by FMMadMan (edited October 06, 2000).]

[Chuck]

What are you getting instead of your expected results? It's hard to give any kind of suggestion without knowing the nature of the problem.

But, I can give some tips on troubleshooting equations. First, make sure that each part of the calculation is working as you think it is. Create a calculaiton field and set it to Product List: : price, and see if you get the price from the Product database. Take apart the equation for each argument and make sure that each part is resulting as expected.

Chuck

[This message has been edited by Chuck (edited October 06, 2000).]

[FMMadMan]

Thanks for the tip.

The problem that I am having is this:

The first line item entered works fine. In other words, if I enter a product ID, the description comes in fine as well as the correct price according to the quantity entered. However, when I tab to the next line and enter a product ID, instead of pulling the price for that product and running the calculation on that price I get the price for the item on the previous line. It's as if the relationship is only working for the first item in the repeating field. When I remove the calculation everything works great.

Thanks again!

[Chuck]

quote:

---

Originally posted by FMMadMan:

Thanks for the tip.

The problem that I am having is this:

The first line item entered works fine. In other words, if I enter a product ID, the description comes in fine as well as the correct price according to the quantity entered. However, when I tab to the next line and enter a product ID, instead of pulling the price for that product and running the calculation on that price I get the price for the item on the previous line. It's as if the relationship is only working for the first item in the repeating field. When I remove the calculation everything works great.

Thanks again!

---

It sounds like the problem may be related to the fact that you're using a repeating field. You might want to use what's called a join file instead and instead of a repeating field, use a portal. But this will require a bit of work. Basically, between your Product List and Order Form databases would be a third file called perhaps Line Items. It has a field for the Product List ID and for the Order Form ID and relationships to both. The Order Form database has a relationship based on the Order Form ID with the allowing of related records. Then you view this relationship in a portal. You'll also need some calculation fields in the Line Items database that can give you access to the Product List items (such as price). The quantity field would be in the Line Items database, and there would be a calculation in that database for what you originally were trying to do.

It may sound complex, but it's definiately a better way to do.

Chuck

[LiveOak]

I think Kurt mentioned to me today that a relationship only accesses the first item in a repeating field. I'm with Chuck, dump the repeating fields in your solution! -bd