4 Replies Latest reply on Sep 28, 2016 7:55 AM by philmodjunk

    Currency calculations


      Good Morning,


      i have the following table and would like to do an calculation for the Cost field. Mine somehow doesn't work:


      If ( Currency = "CAD"; "" ; If ( Currency = "USD"; Supplier List Price * Products::ExchangeRate_USD; If ( Currency = "EUR"; Supplier List Price * Products::ExchangeRate_EUR; "" ) ) )


      I would like to add a price to the "Supplier List Price" field, mostly it is Canadian Dollar, which i put as a standard in the dropdown menu.

      But sometime we have USD or EUR, so i would like to have a calculation when the dropdown menu says CAD, i can modify the "cost" field by hand, but if it USD or EUR I put the USD Price into the "Supplier List Price" field and then the dropdown on USD and it automatically calculates the Cost.


      I have the current exchange rates on the layout.


      Hope I said it in the right way. Looking forward to hear your solutions.


      Thank you



        • 1. Re: Currency calculations

          When you say it doesn't work, what do you mean? Is it giving you an unexpected result or no result at all?


          How is the Cost field set-up? I assume it is set up with an AutoEnter calculation and not as a Calculation field, since you mention that you want to manually enter the cost in certain situations. Is "Do not replace existing value for field" checked? Note that changes in fields in a related table (Products) do not automatically a trigger an update to a field.


          What is the relationship between your base table and Products::ExchangeRate?

          • 2. Re: Currency calculations

            After reading your comment and kept playing a little bit around and it works now.

            I changed the calculation a little bit:


            If ( Currency = "CAD"; "" ; If ( Currency = "USD"; Supplier List Price * ExchangeRate_USD; If ( Currency = "EUR"; Supplier List Price * ExchangeRate_EUR; "" ) ) )

            * Because it was on the same table and before it was on a different layout.


            The cost field is a number field = Auto-enter Calculation replaces existing value

            * before i had = by calculation, allow override


            Thank you a lot and best regards

            • 3. Re: Currency calculations

              Good, glad to hear that you were able to sort it out. That looks like a better option.

              • 4. Re: Currency calculations

                Here's  format that makes it easier to work with if you have to revisit your calculation at a later date:



                Case ( Currency = "CAD" ; "" ;

                           Currency = "USD" ; Supplier List Price * ExchangeRate_USD ;

                           Currency = "EUR" ; Supplier List Price * ExchangeRate_EUR



                But for an auto-entered calculation, I'd use:


                Case ( Currency = "CAD" ; Self ;

                           Currency = "USD" ; Supplier List Price * ExchangeRate_USD ;

                           Currency = "EUR" ; Supplier List Price * ExchangeRate_EUR



                Using Self, will keep selecting "CAD" from erasing the amount--say if you selected USD and then realized that you really did want CAD.