Discussions

Expand all | Collapse all

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

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

    Posted 07-17-2018 14:56
    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)?


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

    Posted 07-17-2018 15:01
    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 


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

    Posted 07-17-2018 15:19
    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....


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

    Posted 07-17-2018 15:21
    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.


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

    Posted 07-17-2018 15:26
    Makes sense. You just saved me a bunch more time spent "spinning my wheels." :)