AnsweredAssumed Answered

Filtering multiple related records

Question asked by matthijsbeekman on Apr 15, 2011
Latest reply on Jun 27, 2011 by matthijsbeekman


Filtering multiple related records


My problem is the following

In my main table i have the following fields:

Date, Item1Price2

Now i have a second table which is a price list.

It has the following fields: mutation Date, item, price 1, price  2, price 3 (Where the different prices depict different ammounts of the  item)

My goal is to set the Item1Price2 field in het main table to the current price as set in the pricelist.

At first I related the two tables using the Date and Mutation date (Date >= Mutation Date).  This way, the price changes in the future are not used and records from  the past are not changed with today's prices. I can't however figure out  how to make a subselection of the related tables. At default FMP seems  to take the first one. I would like to first cancel out all the other  items and then pick the most current record. Setting multiple  relationships won't do since i would like to retrieve prices for  multiple items.

Attached is a picture showing the price list. For example, the date in the main tabel is 5/6/11 and i would like to get price 2 for item 1 from the price list. This should then be €1.70 (first ruling out all the dates after 5/6/11, then selection only the records for item 1 and then taking the most recent record and retreiving price 2).

I hope somebody can help me with this.