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.

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