Vlookup equivalent in related Table

[markfmf]

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, 2009 by Guest

[bcooney]

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, 2009 by Guest

[markfmf]

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, 2009 by Guest

[comment]

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