Discussions

Expand all | Collapse all

Add Euro symbol or the letters EUR to the left of dollar amount in a formula

  • 1.  Add Euro symbol or the letters EUR to the left of dollar amount in a formula

    Posted 06-12-2018 15:49
    How to format a formulaic field (called "Price") to display a Euro symbol or the letters EUR to the left of a product price? This field is used to calculate the Total Price of a line item by multiplying the "Quantity" field X "Price" field, and, of course, I have no problem doing this in a non-formulaic field through the properties for that field. I've read past posts about this in the Community Forum related to this, but am not grasping how to achieve this.

    Below are two formulas I tried and the error message they return.

    Below formula formatted as Formula Numeric-Currency, Formula-Numeric, and Formula-URL fields basically returns an error of "Expecting number"

    If ([Price List to Use]="US List Price", [US List Price],
    [Price List to Use]="Asia Dist Price", [Asia Dist Price],
    [Price List to Use]="Asia List Price", [Asia List Price],
    [Price List to Use]="EUR Dist Price", €&[EU Dist Price],
    [Price List to Use]="EUR List Price", €&[EU List Price])

    Below formula formatted as a Formula-Rich Text Field returns numbers that are not lined up at the decimal point and there is no comma between every three numbers (the [Euro Symbol] field was added to hold the symbol)

    If ([Price List to Use]="US List Price", "$"&ToText([US List Price]),
    If ([Price List to Use]="Asia Dist Price", "$"&ToText([Asia Dist Price]),
    If ([Price List to Use]="Asia List Price", "$"&ToText([Asia List Price]),
    If ([Price List to Use]="EUR Dist Price", ToText([Euro Symbol] & [EU Dist Price]),
    If ([Price List to Use]="EUR List Price", ToText([Euro Symbol] & [EU List Price]))))))

    Can anyone help with this? Or, is there another way to do this?


  • 2.  RE: Add Euro symbol or the letters EUR to the left of dollar amount in a formula

    Posted 06-12-2018 16:00
    I have a collection of formuals to change a currency numeric value to text.

    Try this one

    var number Value = Round([Open Amount],0.01);
    var text Decimals = "." & Right(ToText(Int($value * 100)),2);

    var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));

    var text Hundreds=Right(ToText(Int($Value)),3);

    var text Words = 
    "$" & List(",",$Thousands,$Hundreds) & $Decimals;


  • 3.  RE: Add Euro symbol or the letters EUR to the left of dollar amount in a formula

    Posted 06-12-2018 19:04
    Thanks! I'll check this out. So, dumb question, but where do I put this - in front of the formula?


  • 4.  RE: Add Euro symbol or the letters EUR to the left of dollar amount in a formula

    Posted 06-12-2018 19:50
    Here is a shot at a complete formula.  But it will still have the problem that the decimals will not line up.


    var text PriceToUse = Case([Price List to Use],
    "US List Price", [US List Price],
    "Asia Dist Price", [Asia Dist Price],
    "Asia List Price", [Asia List Price],
    "EUR Dist Price", [EU Dist Price],
    "EUR List Price",  [EU List Price]);

    var number Value = Round($PriceToUse ,0.01);
    var text Decimals = "." & Right(ToText(Int($Value * 100)),2);
    var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));
    var text Hundreds=Right(ToText(Int($Value)),3);
    var text Words = 
    "$" & List(",",$Thousands,$Hundreds) & $Decimals;


    Case([Price List to Use],
    "US List Price", "$" & $Words,
    "Asia Dist Price", "$" & $Words,
    "Asia List Price", "$" & $Words,
    "EUR Dist Price", [Euro Symbol]  & $Words,
    "EUR List Price", [Euro Symbol]  & $Words)

    But maybe there is something we can add to right justify this once you get this part working.


  • 5.  RE: Add Euro symbol or the letters EUR to the left of dollar amount in a formula

    Posted 06-12-2018 20:07
    Fantastic! Many thanks. I put this formula in and am getting the error "expecting text but found #" and the [US List Price] in the formula is highlighted yellow.....


  • 6.  RE: Add Euro symbol or the letters EUR to the left of dollar amount in a formula

    Posted 06-12-2018 21:05
    The type on the formula variable was incorrect

    var number PriceToUse = Case([Price List to Use],
    "US List Price", [US List Price],
    "Asia Dist Price", [Asia Dist Price],
    "Asia List Price", [Asia List Price],
    "EUR Dist Price", [EU Dist Price],
    "EUR List Price",  [EU List Price]);


  • 7.  RE: Add Euro symbol or the letters EUR to the left of dollar amount in a formula

    Posted 06-13-2018 14:50

    Fantastic! Below at the bottom is what I've got now, and it's working great, other than the align right issue.

    (1) Changed THIS: var text Words =  "$" & List(",",$Thousands,$Hundreds) & $Decimals;

    TO THIS: var text Words = List(",",$Thousands,$Hundreds) & $Decimals;

    (This was causing an extra ampersand on every line.)


    (2) Changed THIS: var number Value = Round($PriceToUse ,0.01);

    TO THIS:  var number Value = Round($PriceListToUse ,0.01);


    This is what's working:

    var number PriceToUse = Case([Price List to Use],
    "US List Price", [US List Price],
    "Asia Dist Price", [Asia Dist Price],
    "Asia List Price", [Asia List Price],
    "EUR Dist Price", [EU Dist Price],
    "EUR List Price",  [EU List Price]);

    var number Value = Round($PriceToUse ,0.01);
    var text Decimals = "." & Right(ToText(Int($Value * 100)),2);
    var text Thousands = If($Value>=1000,ToText(Int($Value/1000)));
    var text Hundreds=Right(ToText(Int($Value)),3);
    var text Words = List(",",$Thousands,$Hundreds) & $Decimals;

    Case([Price List to Use],
    "US List Price", "$" & $Words,
    "Asia Dist Price", "$" & $Words,
    "Asia List Price", "$" & $Words,
    "EUR Dist Price", [Euro Symbol]  & $Words,
    "EUR List Price", [Euro Symbol]  & $Words)


    Any more tips on getting the numbers to align right? I was playing around with this a little, but no luck.....



  • 8.  RE: Add Euro symbol or the letters EUR to the left of dollar amount in a formula

    Posted 06-13-2018 14:59
    you can try this for that last block

    "<div align='right'>"

    Case([Price List to Use],
    "US List Price",   "$" & $Words, 
    "Asia Dist Price", "$" & $Words,
    "Asia List Price", "$" & $Words,
    "EUR Dist Price", [Euro Symbol]  & $Words,
    "EUR List Price", [Euro Symbol]  & $Words)
    &
    "</div>


  • 9.  RE: Add Euro symbol or the letters EUR to the left of dollar amount in a formula

    Posted 06-13-2018 15:22
    Works perfectly! Many, many thanks, Mark.


  • 10.  RE: Add Euro symbol or the letters EUR to the left of dollar amount in a formula

    Posted 06-13-2018 15:33
    Great to hear, thx for letting me know.