Skip to main content

I’m trying to make a basic set up for switching between currencies when pricing a product

I’ve got 2 fields; one for € and one for £ and then a formula field that’ll show the price from the £ field or the € field depending if a ‘switch’ is set to £ or €.

I really want to show the displayed price with 2 decimals, but it doesn’t show a price is fx. 2.00.

I tried with the ROUND() function but doesn’t work - is there a way for 2 decimals in the formula field.


Currently, I’m using the following:


IF({Currency switch}=“€”, "€ " & Euro, IF({Currency switch}=“£”, "£ " & ROUND(pounds,2), BLANK()))


This is a little weird. When you use & it converts the currency into a string. So you can’t just format it you have to come up with some string logic:


IF({Currency switch}='€','€ ' & Euro & IF(LEN(Euro&'')=1,'.00',IF(FIND('.',RIGHT(Euro&'',2)),'0')), IF({Currency switch}='£','£ ' & pounds & IF(LEN(pounds&'')=1,'.00',IF(FIND('.',RIGHT(pounds&'',2)),'0'))))

You’ll first notice I am joining both pounds and euros with an empty string constantly. That’s so it gets converted before I use LEN, FIND and RIGHT. After that, I check to see if the total length is 1 (ie: £ 2) if it is I add ‘.00’. Otherwise, I check the right to digits to see if there is a decimal if there is I add a zero.


redit: small fix in the equation]

@Kim_Trager1, you may (or may not, depending on how deeply down the rabbit hole you wish to tumble) want to look at my number and currency ‘pretty-print’ routines for code supporting appropriate right-aligned, fixed-decimal currency values.

@Kim_Trager1, you may (or may not, depending on how deeply down the rabbit hole you wish to tumble) want to look at my number and currency ‘pretty-print’ routines for code supporting appropriate right-aligned, fixed-decimal currency values.


@bdelanghe, Thank you for your explanation. I’m playing around with your suggestions - and trying to wrap my head around how it works. However, it seems that the script no longer adds decimals when the string has more than 2 digits - any suggestions on how to counter this?


@W_Vann_Hall thank you for giving access to your base. The number table seems to be what I need. I need to study it more as the rabbit hole looks very deep.


Merry Christmas to both of you

@bdelanghe, Thank you for your explanation. I’m playing around with your suggestions - and trying to wrap my head around how it works. However, it seems that the script no longer adds decimals when the string has more than 2 digits - any suggestions on how to counter this?


@W_Vann_Hall thank you for giving access to your base. The number table seems to be what I need. I need to study it more as the rabbit hole looks very deep.


Merry Christmas to both of you


Thanks for the Christmas wishes! After a little break it took me a little while as well to understand my formula. (And I found the mistake I made) I’ve added tabs which should help a little:


IF({Currency Switch}='€', //check the switch

    '€ ' & Euro & //start joining the string for Euro

  

    IF(NOT(FIND('.',Euro&'')),'.00', //if you can't find a decimal add it and '00'

    IF(FIND('.',RIGHT(Euro&'',2)),'0')), //otherwise check if the decimal is in the last two add a zero

    //but if everything is okay we are just joining the string to nothing 



IF({Currency Switch}='£',  //same as above, but pounds this time

    '£ ' & pounds &



    IF(NOT(FIND('.',pounds&'')),'.00',

    IF(FIND('.',RIGHT(pounds&'',2)),'0'))))

That helps to show that it’s just the same thing twice, but here’s a screen of a table that breaks out a few of the sub-steps:



Also just incase you want to copy paste here is the formula without comments:


IF({Currency Switch}='€',

  '€ ' & Euro &

  

  IF(NOT(FIND('.',Euro&'')),'.00',

  IF(FIND('.',RIGHT(Euro&'',2)),'0')),



IF({Currency Switch}='£',

  '£ ' & pounds &



  IF(NOT(FIND('.',pounds&'')),'.00',

  IF(FIND('.',RIGHT(pounds&'',2)),'0'))))

Hope this helps. Also don’t forget the string joins inside each formula is important because it converts the number to a string ( ie: pounds&’’ )

Terms & Conditions