What is wrong with my formula please?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
Everything works like i want it to except for the last one where the Budget is blank or 0 I want a Grey Point.  Help please....



If( [Budgeted Amount on CAPEx Schedule]<[PAF Amount],"https://images.quickbase.com/si/16/221-point_red.png\" title=\"Overbudget\">", [Budgeted Amount on CAPEx Schedule]=[PAF Amount],"https://images.quickbase.com/si/16/223-point_yellow.png\" title=\"Budget Met\">", [Budgeted Amount on CAPEx Schedule]>[PAF Amount],"https://images.quickbase.com/si/16/222-point_green.png\" title=\"Within Budget\">", [PAF Amount]>0,"https://images.quickbase.com/si/16/220-point_gray.png\" title=\"No Expenses\">")
Photo of Blanca

Blanca

  • 374 Points 250 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,792 Points 3k badge 2x thumb
I would write this as a Case statement as follows (updated):

Case(true,

[Budgeted Amount on CAPEx Schedule]<[PAF Amount],"https://images.quickbase.com/si/16/221-point_red.png\" title=\"Overbudget\">",

[Budgeted Amount on CAPEx Schedule]=[PAF Amount],"https://images.quickbase.com/si/16/223-point_yellow.png\" title=\"Budget Met\">",

[Budgeted Amount on CAPEx Schedule]>[PAF Amount],"https://images.quickbase.com/si/16/222-point_green.png\" title=\"Within Budget\">",

([Budgeted Amount on CAPEx Schedule]=0 or IsNull([Budgeted Amount on CAPEx Schedule])),"https://images.quickbase.com/si/16/220-point_gray.png\" title=\"No Expenses\">"

)
Photo of Blanca

Blanca

  • 374 Points 250 badge 2x thumb
Thank you so much for your response.  When I use that formula it returns a sintax error "A closing parenthesis is missing."  Any ideas?
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,792 Points 3k badge 2x thumb
Yes, sorry on the last line there should be two parentheses which encloses the OR statement as well as the IsNull().

([Budgeted Amount on CAPEx Schedule]=0 or IsNull([Budgeted Amount on CAPEx Schedule])),"https://images.quickbase.com/si/16/220-point_gray.png\" title=\"No Expenses\">"

)
Photo of Blanca

Blanca

  • 374 Points 250 badge 2x thumb
For some reason - the formula still does the same it did before.  On the instances where the PAF Amount is 0 or blank it still gives me a Green point instead of Gray.

Did I do something wrong?  It is a Formula Text field

Case(true,

[Budgeted Amount on CAPEx Schedule]<[PAF Amount],"<img src=\"https://images.quickbase.com/si/16/221-point_red.png\" title=\"Overbudget\">",

[Budgeted Amount on CAPEx Schedule]=[PAF Amount],"<img src=\"https://images.quickbase.com/si/16/223-point_yellow.png\" title=\"Budget Met\">",

[Budgeted Amount on CAPEx Schedule]>[PAF Amount],"<img src=\"https://images.quickbase.com/si/16/222-point_green.png\" title=\"Within Budget\">",

([PAF Amount]=0 or IsNull([PAF Amount])),"<img src=\"https://images.quickbase.com/si/16/220-point_gray.png\" title=\"No Expenses\">")
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,792 Points 3k badge 2x thumb
Try moving the "grey" button to the first line so it becomes the first-evaluated condition.



Case(true,

([PAF Amount]=0 or IsNull([PAF Amount])),"<img src=\"https://images.quickbase.com/si/16/220-point_gray.png\" title=\"No Expenses\">",

[Budgeted Amount on CAPEx Schedule]<[PAF Amount],"<img src=\"https://images.quickbase.com/si/16/221-point_red.png\"title=\"Overbudget\">",

[Budgeted Amount on CAPEx Schedule]=[PAF Amount],"<img src=\"https://images.quickbase.com/si/16/223-point_yellow.png\" title=\"Budget Met\">",

[Budgeted Amount on CAPEx Schedule]>[PAF Amount],"<img src=\"https://images.quickbase.com/si/16/222-point_green.png\"title=\"Within Budget\">"

)
Photo of Blanca

Blanca

  • 374 Points 250 badge 2x thumb
That worked!!! You are THE BOMB.COM!!!  Thank you!
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,792 Points 3k badge 2x thumb
The reason this worked but failed the other way is because the condition which says that the [Budgeted Amount..] is > the PAF Amount would ALSO be true if the PAF Amount were empty (null) or 0.  So to force the grey button output, we have to evaluate first if the PAF Amount is even populated, before evaluating the various conditions on the Budgeted Amount in relation to the PAF Amount.  You could also have written this in the following way (a mix of IF and CASE):

If(
  ([PAF Amount]=0 or IsNull([PAF Amount])),"<img src=\"https://images.quickbase.com/si/16/220-point_gray.png\" title=\"No Expenses\">",
Case(true,
[Budgeted Amount on CAPEx Schedule]<[PAF Amount],"<img src=\"https://images.quickbase.com/si/16/221-point_red.png\"title=\"Overbudget\">",
[Budgeted Amount on CAPEx Schedule]=[PAF Amount],"<img src=\"https://images.quickbase.com/si/16/223-point_yellow.png\" title=\"Budget Met\">",
[Budgeted Amount on CAPEx Schedule]>[PAF Amount],"<img src=\"https://images.quickbase.com/si/16/222-point_green.png\"title=\"Within Budget\">"
   )
)
Photo of Blanca

Blanca

  • 374 Points 250 badge 2x thumb
That makes total sense! #superhappy
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 3,792 Points 3k badge 2x thumb
Very happy to be able to help!