2 Replies Latest reply on Jul 6, 2015 10:00 PM by philmodjunk

    Calculation depending on currency

    AndrewJudd

      Title

      Calculation depending on currency

      Post

      HI all, I am attempting to list the total retail amounts that belong to contacts (in my case artworks that are consigned from). I have a portal to my inventory in contacts which is working fine. I have also managed to get a general total working by using -

      Sum (ARTWORK 2::RETAIL)

      However this is a bit limiting as artworks are sold in different currencies - I have a simple currency field to allow different records to be given a different currency at time of entry.

      I have tried using calculation fields from with artwork - If (CURRENCY = "£" ; Retail Summary)

      With Retail Summary then set as a summary field of Retail. 

      I have also tried a calculation field from within Clients - If (ARTWORK 2::CURRENCY= "£" ; Sum (ARTWORK 2::RETAIL))

      Both return totals of all retail whatever the currency. Attached is my relationship graph if this is needed for background.

      Any ideas?

      thank you

       

      Screen_Shot_2015-07-05_at_12.06.53.png

        • 1. Re: Calculation depending on currency
          keywords

          Probably your ARTWORK 2 relationship is not filtering the records. If ( testField = "yourCurrencyDelimiter" ; Sum ( currencyFilteredTO::retailSale )  ; whatever false result you want )

          • 2. Re: Calculation depending on currency
            philmodjunk

            When a calculation refers to a field in a related table where there are multiple related records, the reference refers only to the "first related record" unless this reference is in the form of an aggregate function, the values of all other related records are ignored. So both of your methods, will either return a total of all related records or no value at all.

            There are multiple methods to get a "selective sum" of related records (Selective meaning that you only want a sum of some, not all related records). ExecuteSQL is one option. A relationship that only matches to records where currency is of a specific type is another. And a filtered portal with a summary field from the portal's table is a third.

            It's also possible in many cases to set up a summary report where you group your records pulled up for the report by currency type and use a single summary field inside a sub summary part to show a sub total for each currency type.