You need a relationship to link your expenses table to your Currencies table by Currency type and year.
Add a calculation field named cYear to your Expenses table:
Year ( Date )
select number as the result type.
Define this relationship:
Expenses::Currency = Currencies::Currency AND
Expenses::cYear = Currencies::Year
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Now calculations in Expenses can refer to Currencies::Conversion Rate to calculate a converted value.
I'd give you an example, but I don't know what "Total" is supposed to calculate. Is it a summary field to compute a total over multiple records? or does it total multiple fields in expenses?
Total is supposed to calculate each entry independently. So for the 2nd field in 'Expenses' it would check the date, 2010, and then reference that to the 2010 rate for USD and give a 'total' of $394 in Canadian dollars. So the 'Total' is the converted rate into Canadian Dollars from each individual entry. Does that help?
An example would be very helpful. Thanks.
In that case you can multiply Amount by the related conversion factor in currencies:
Amount * Currencies::Conversion Rate
If you have amounts in canadian dollars, you may want to add entries in Currencies for them with a conversion rate of 1 or you can include an If function like this:
If ( Currency = "CAD" ; Amount ; Amount * Currencies::Conversion Rate )
Thanks so much, works like a charm. (and I've renamed 'Total')
There is however a problem, when I choose leading subtotal for the 'CAD Conversion' field, Filemaker is coming up with '?' Is there a way to have a total displayed as I need that information.
Interestingly, when I narrow the search paramaters down to each quarterly period, the leading subtotal works. It's only when I grow the search to all of 2012 does the '?' pop up. There's 201 entries for 2012, any ideas?
Figured it out, the column wasn't large enough to fit the amount. Thanks again for your help!