6 Replies Latest reply on Apr 16, 2013 5:19 PM by MikaGoodfriend

    Help with calculations

    MikaGoodfriend

      Title

      Help with calculations

      Post

           Hello,

           I have been using Filemaker Pro for data entry to keep track of my expenses and income for my sole-proprietorship. I have no experience with scripts or calculations, however, I need to learn how to accomplish a currency conversion task.

           I have a layout called 'Currencies' in which I have the yearly currency conversion (into Canadian dollars), along with the conversion rate.

           In my main 'Expenses' layout, I list amounts, and the currency used, along with the date.

           What I need is a calculation or script that will call on the correct conversion rate based on the currency and date I entered in 'Expenses', and apply it where needed in the 'Total' field. Is there anyone who can help me with this? I've tried to read up on calculations but it's completely over my head and this isn't the easiest calculation scenario.

           Thanks.

           Mika

      example.jpg

        • 1. Re: Help with calculations
          philmodjunk

               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::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?

          • 2. Re: Help with calculations
            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

                   Not what I'd name as "total" but hey! it's your database and you can name things as you want! wink

                   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

                     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

                       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

                         Figured it out, the column wasn't large enough to fit the amount. Thanks again for your help!