6 Replies Latest reply on May 25, 2017 2:34 AM by neildecort

    Calculate a new price in whole dollars

    neildecort

      Hi,

       

      I've inherited an FM12 database which converts a United States Dollar price to a rounded Canadian Dollar price:

       

      Priceamount06=

      If (PriceAmount02="";"";

      Let( [

      convertedAmount = (PriceAmount02 * Priceamount06exchangerate) ;

      remainder = Mod( convertedAmount ; 1 )

      ] ;

       

      Case(

      (remainder > .475) and (remainder ≤ .95) ;

      Round( convertedAmount - remainder ; 0 ) + .95 ;

       

      remainder > .95 ;

      Round( convertedAmount - remainder ; 0 ) + .95 ;

       

      Round( convertedAmount - remainder ; 0 ) - .05

       

      )

       

      ))

       

      Where Priceamount06 is the CAD price, Priceamount02 is the USD price and Priceamount06exchangerate is the conversion rate.

      This works fine, but recently I have discovered that if the USD price is a round figure (eg. $45.00) then the CAD price needs to be a round figure too; this calculation always gives a $@@.95 price. Being a novice at this I can't see how I can adjust this so that a $@@.95 USD price gives a $@@.95 CAD price, and a $@@.00 USD price gives a $@@.00 CAD one.

       

      Can any one please help?

      Thank you.

        • 1. Re: Calculate a new price in whole dollars
          Orlando

          Hi neildecort,

           

          Just start by saying if you can, move this post to general discussion, you will have more people see and get back to you with a solution.

           

          On that note, I am not sure if you need to change the result in any way, but if you are trying to do a straight currency conversion from USD to CAD then you do not need the Case statement that is changing the outcome and causing the $@@.95 price value.

           

          You could try simplifying it with the following

           

            If ( isEmpty (PriceAmount02 ) ; "" ;

           

            Let (

           

            convertedAmount = ( PriceAmount02 * Priceamount06exchangerate ) ;

           

            Round ( convertedAmount ; 2 )

           

            ) )

           

          Hope this helps

           

          -- Orlando

          • 2. Re: Calculate a new price in whole dollars
            beverly

            TSPigeon may wish to move this to the Discussions space for other to more easily see it.

            • 3. Re: Calculate a new price in whole dollars
              neildecort

              Hi,

               

              I tried this, but it rounds to 0.54 and 0.94, rather than 0.95, I', afraid.

               

              As for moving to the General, great idea but I'm not sure how (apologies for incompetence!)

              • 4. Re: Calculate a new price in whole dollars
                philmodjunk
                I can't see how I can adjust this so that a $@@.95 USD price gives a $@@.95 CAD price, and a $@@.00 USD price gives a $@@.00 CAD one.

                I can't see how, doing that could possibly be a correct conversion of these values.

                 

                I think that you'll need to provide more info as this requires changes to what is otherwise a very simple algorithm for converting currency.

                • 5. Re: Calculate a new price in whole dollars
                  Orlando

                  So do you need the result to always be either a $##.00 or $##.95 value?

                   

                  Looks like the original calculation is always rounding up or down to .95, if this is correct and you want to amend it so if you input a $##.00 value the result is rounded to $##.00 then maybe this will do the trick.

                   

                  If ( IsEmpty ( quickTest::PriceAmount02 ) ; "" ;

                   

                  Let( [

                   

                  convertedAmount = ( PriceAmount02 * Priceamount06exchangerate ) ;

                   

                  remainder = Mod ( convertedAmount ; 1 ) ;

                   

                  isWholeNumber = Mod ( PriceAmount02 ; 1 ) = 0 ;

                   

                  amend = Case ( isWholeNumber = 1 ; 0 ; remainder > .475 ; .95 ; -.05 )

                   

                  ] ;

                   

                  Round( convertedAmount - remainder ; 0 ) + amend

                   

                  ))

                   

                  If not then some examples of input, rate and expected output values will be helpfull to understand what you are expecting.

                   

                  -- Orlando

                  • 6. Re: Calculate a new price in whole dollars
                    neildecort

                    This has done it, thank you; a .95 input converts to a .95 output and a .00 input gives a .00 output, just what I needed, thank you very much!