
1. Re: Help with calculations
philmodjunk Apr 16, 2013 12:29 PM (in response to MikaGoodfriend)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:
Currencies<Expenses
Expenses::Currency = Currencies::Currency AND
Expenses::cYear = Currencies::YearFor 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?

2. Re: Help with calculations
MikaGoodfriend Apr 16, 2013 12:36 PM (in response to MikaGoodfriend)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.

3. Re: Help with calculations
philmodjunk Apr 16, 2013 4:15 PM (in response to MikaGoodfriend)Not what I'd name as "total" but hey! it's your database and you can name things as you want!
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 )

4. Re: Help with calculations
MikaGoodfriend Apr 16, 2013 4:35 PM (in response to MikaGoodfriend)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.

5. Re: Help with calculations
MikaGoodfriend Apr 16, 2013 4:40 PM (in response to MikaGoodfriend)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?

6. Re: Help with calculations
MikaGoodfriend Apr 16, 2013 5:19 PM (in response to MikaGoodfriend)Figured it out, the column wasn't large enough to fit the amount. Thanks again for your help!