Forum Discussion

DonnaTroestler's avatar
DonnaTroestler
Qrew Cadet
7 years ago

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

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?
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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;
  • DonnaTroestler's avatar
    DonnaTroestler
    Qrew Cadet
    7 years ago
    Thanks! I'll check this out. So, dumb question, but where do I put this - in front of the formula?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.
  • DonnaTroestler's avatar
    DonnaTroestler
    Qrew Cadet
    7 years ago
    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.....
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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]);
  • DonnaTroestler's avatar
    DonnaTroestler
    Qrew Cadet
    7 years ago

    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.....

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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>