Add Euro symbol or the letters EUR to the left of dollar amount in a formula (Part 2)

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
We have three different Price Lists that we use to create Price Quotes for customers, and we needed to include EUR to the left of European prices. User QuickBaseCoach App Dev./Training developed the following Formula (a Formula - Rich Text field) for me in order to accomplish this (field name is [Price]).

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]);

Formula URL field, which does not automatically add a comma to a number or align the number to the right
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;

"<div align='right'>" 

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

However, the next step is that I now I need to take the results of this field and multiply it by the [Quantity] field to get a total for each line item. In spoken words, I need a Formula that does this:

If [Price] contains "EUR " at the beginning of the value, then multiply [Quantity] times everything but the EUR(space), otherwise just multiply [Quantity] times [Price].

I'm not sure what field type would be used (maybe Formula - Numeric?), but what I've got so far for a Formula is this:

If(Contains(Left([Price],3),"EUR "), NotLeft([Price],4), [Quantity]*[everything but EUR(space)],
[Quantity]*[Price])

I may be way off on this, but can anyone help me with the missing piece or piece(s)?
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb

Posted 4 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
All those gyrations were to get a text string which looks how you want it. It is not a number. The last thing you would want to to is to try to parse out the numeric portion.

You should just make a formual numeric like



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]);

[Quantity] * $PriceToUse 
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
Thanks, Mark, for explaining this. I couldn't figure that out. Also, I didn't indicate clearly that I still need to have the EUR symbol display in my results as well. I'll try this.

P.S. I'm going to contact you in the next day or two about a project....
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
If you need to add a Euro symbol, then you will make a new field to do that as the extended price needs to remain as a numeric field for other purposes like total order price.
Photo of Donna Troestler

Donna Troestler

  • 324 Points 250 badge 2x thumb
Makes sense. You just saved me a bunch more time spent "spinning my wheels." :)