Forum Discussion

RyanHaberman's avatar
RyanHaberman
Qrew Trainee
7 years ago

Rich Text Formulas Using Numeric Ifs

Hi Let me first start with thanks to everyone who posts in these forums I have learned so much from you. I do NOT code but I am real good a trial and error also google. So you have been a life saver.

I am trying to use a rich text field to test conditions and give TEXT response.

Like are these 2 things equal if not "BAD" if yes then check the next condition and so on.

I think my problem is I am mixing numbers from calculated fields and summary. I have read about needing ToText, etc. 
I get the same answer, TRUE even when it should be false

I have tried so many permutations of this:
Final Sale Price is a Numeric Formula that resolves currency rounded .01
the PO fields are manual entry Numeric Currency Fields

I have tried a ton of stuff but here are just some:
If([Final Sale Price]-[PO Product Total]-[PO Install Total]-[PO Shipping Total]=0,"Good","Bad")

If([Final Sale Price]-[PO Product Total]-[PO Install Total]-[PO Shipping Total]<>0,"Bad","Good")

If(ToNumber([Final Sale Price]-[PO Product Total]-[PO Install Total]-[PO Shipping Total])=0,"Good","Bad")

If(ToText([Final Sale Price]-[PO Product Total]-[PO Install Total]-[PO Shipping Total])<>"","Bad","Good")

Thanks in advance for your help. Also if there is a complete guide Rich Text that would be awesome!!! 

****Also also huge shout out to 
Styled Text Formula Builder
Created By Brian Cafferelli

If you do not know how to code but want to add some color or change fonts this thing is the best!



Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    I suspect that your intermediate calculations for the values like [Final sales price] etc are not being rounded to pennies and hence when you do those subtractions, there are small decimal remainders that are not exactly zero.

     A proper solution would be to check the source formuals of the values to round them to pennies but an expedient solution would be to round your test calculation

    If(Round(
    [Final Sale Price]-[PO Product Total]-[PO Install Total]-[PO Shipping Total], 0.01) =0,"Good","Bad")






  • RyanHaberman's avatar
    RyanHaberman
    Qrew Trainee
    7 years ago
    So Rounding the whole argument totally worked!!!! Thank you. That was actually one of my early thoughts so I added a .01 round to the formula in [Final Sale Price] and made sure the PO fields were currency 2 decimal places and treated blanks as Zero, which as I type this may be the issue. Since some are blank it might have tried 0 NOT 0.00. Either way it works now so thanks!