Ok I am somewhat at a loss. There is hopefully an easy solution that I have missed.

I have two tables.

Table 1: Products

PID

Table 2: Prices

fPID

Date

Price

Table 1 has a one-to-many relationship to prices. That is one product may have many different prices.

I would like to create a field in the Products Table called Total Amount, which is = Amount (entered by user) and Last Price (Calculation Field).

I am having problems getting the Last Price calculation. Ideally I would like to have a Vlookup function (equivalent) into the Prices table, that gives me the price as at the Maximum Date.

Anyone know how to do this.

To provide an Example:

Table 1:

PID1

Table2

fPID Date Price

PID1 31/01/2009 10

PID1 30/01/2009 9

I would like to create a calculation field in Products table that gives me the latest price.

In this case it would be 10 (because it is the most updated price). I would then like to be able to use this calculated field in another calculation to get the total amount (= Last Price * Amount (constant) )

Thank you in advance.

R

Mark

Edited June 24, 200916 yr by Guest

If you sort the relationship (in the RG d-click the relationship) by Prices:Date desc, then FM will "see" the price with the highest date as the first related record.

Your calc in Products for LastPrice then equals sortedRelationship:Price.

In any InvoiceLineItem, you should use the lookup feature to "copy" the LastPrice into the InvLI record.

Edited June 24, 200916 yr by Guest

Good thank you.

Lets add some more complexity to this.

I have a third table called Transactions which is related to the Products table. One product can have many transactions such that

Transactions > --- Products ----< Prices

fPID PID fPID

Can I create a Last Price calc in the Transactions table that essentially gives me the last price for fPID in the Prices Table?

Thank you

Mark

Edited June 24, 200916 yr by Guest

I would define a new relationship between Transactions and another occurrence of Prices.