Example: I have 2 FM files.

In the first file I have Layout with 3 fields (US Dollar rate, AU Dollar rate, NZ Dollar rate). Record keeps currency rates for every day.

In the second file I have Layout with fields Item, Cost, Date, Currency, Converted Cost.

Currency is a text field (pop up list: US Dollar, AU Dollar, NZ Dollar).

What I need: If I create new record in the second file, enter Item name, Date, Cost and choose currency, program will look up for the Currency rate (specified in Date) in the first file, calculate cost and put it in Converted Cost.

Question: Is FM able to do that?

Thanx!

Version: v6.x

Platform: Windows XP

I would make file 1 have the fields Date, Currency, Rate and a calculated text field Date Currency = Date & " " & Currency. This would now require 3 records for each date.

Add the calculated text field Date Currency = Date & " " & Currency to file 2. Make a relationship in file 2 to file1 Date Currency = ::Date Currency. Use this relationship for a lookup of rate. Converted Cost would be a calculation.

Thanx! I will make it the way you described.