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

  • 2
  • 1
  • Question
  • Updated 5 months ago
  • Answered
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?
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb

Posted 5 months ago

  • 2
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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;
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
Thanks! I'll check this out. So, dumb question, but where do I put this - in front of the formula?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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.
(Edited)
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
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.....
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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]);
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb

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

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
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>"
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
Works perfectly! Many, many thanks, Mark.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,486 Points 50k badge 2x thumb
Great to hear, thx for letting me know.