Forum Discussion

AlexBennett3's avatar
AlexBennett3
Qrew Trainee
5 years ago

Formula Help (Currency field to display Text)

Hey Everyone,

Wanted to see if I could get some advice on changing my formula here.

Right now I have a formula to read what Type the user has selected, then do the math and display a total, otherwise display 0 for the currency amount.

If([Selection]="Included" or [Selection]="Optioned" or [Selection]="Included in Change Order", [Qty]*[Cost], 0)
//If([Selection]="TBD",[QTY]*[Cost],TBD)

The "TBD" is another selection that a user can specify, however, i'm trying to figure out how to say if the User selects TBD then the Cost is also TBD, not 0. Not the best with formulas so could use a second pair of eyes!

Thanks,

Alex



------------------------------
Alex Bennett
------------------------------
Formulas and functions
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    5 years ago

    Unfortunately, you can't display text in a numeric formula - this would have to be a text formula and you would then have to present your numbers as text. You'll need to determine if you will need to do anything with the values later - such as Summary or Lookup fields - or if you just need them as a visual. That will determine how you will need to move forward.



    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------
  • DonLarson's avatar
    DonLarson
    Qrew Elite
    5 years ago

    Alex,

    Expanding a bit on Blakes answer, you need two fields.

    One is Numeric Currency field so that you can continue do calculations on the money.  

    • If([Selection]="Included" or [Selection]="Optioned" or [Selection]="Included in Change Order", [Qty]*[Cost], 0)

    This gives you either the Quantity * Cost or zero.   Use this for summary or table reports where you  then need it to be added with other records.

    Then you need  Formula Text Field to show on Forms or special reports where you do not expect the column to add up to a total

    var number CalculatedCost = If([Selection]="Included" or [Selection]="Optioned" or [Selection]="Included in Change Order", [Qty]*[Cost], 0)l;

    If ( $CalculatedCost<>0, "$ " &ToText($CalculatedCost), "TBD")



    ------------------------------
    Don Larson
    Paasporter
    Westlake OH
    ------------------------------