Filtering multiple related records
My problem is the following
In my main table i have the following fields:
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.