Okay this is kind of complicated for me at least. I need to be able to do a calculation based off a specific field in another table. and which field is determined by a value list on another table

For example:

I have a table called currencies with fields as:

Currency Name

Currency symbol

Exchange Rate

I have lets say 3 records

1 is Dollars,USD, 1

2 is Pounds, BPD, (current rate)

3 is Euro, EUR, (current rate)

Now I have another table

Product Price (all of these are in dollar values only)

I have fields of

Retail Price

Cost of Goods

Current Margin

these are easy math no problems here

BUT:

I have a product Catalog table

that has a calculating field for Retail price.

and also has a value list for currencys

so i need to know whats the best way to do the following:

grab the retail price from the correct records field and then based on the valuelist choice select the right record and field in the currency table and calculate a conversion?

I figure i can do it with

Case ( currencys_ID=1; (get the record/field and multiple it by the other record/field; currencys_ID=2; (get the record/field and multiple it by the other record/field; currencys_ID=3; (get the record/field and multiple it by the other record/field))

How do i grab a specific field in a specific record?

pc_1.0_copy.fp7.zip

Take a look at this post.

As for:

How do i grab a specific field in a specific record

GetNthRecord will help you do this but I think that you do not have to use it for your solution.