formula using summary fields?

  • 0
  • 1
  • Question
  • Updated 1 month ago
  • Answered
Am i able to create a formula that uses summary fields?

I have a summary field that shows the projects completion as a percent. 

Here is what i have so far, but i keep getting an error.

Formula error -- Bad or missing arguments in function cal

Is it because the field is not a number or a text, but is a summary? I have tried both "case" and "if" as well as tried with and without quotations around the numbers.

The last line is meant to show the go to source icon if percent is equal to 0.

If([Percent Complete], "25","<img src=\"https://images.quickbase.com/si/16/504-spanner.png\">>";,
[Percent Complete], "50","<img src=\"https://images.quickbase.com/si/16/440-dollar-1.png\">>";,
[Percent Complete], "75","<img src=\"https://images.quickbase.com/si/16/211-check_all.png\">>";,
"<img src=\"https://images.quickbase.com/si/16/778-go_to_source.png\">>")
Photo of mkosek

mkosek

  • 776 Points 500 badge 2x thumb

Posted 1 month ago

  • 0
  • 1
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,754 Points 5k badge 2x thumb
Hi Mkosek,

Is your Percent Complete field at Text field with a fixed set of values like 25, 50, 75? Or is it a numeric field that you are calculating against? If you are trying to calculate against a numeric lookup you would want something like

If([Percent Complete]>=75, image,
If([Percent Complete]>=50, image,
If([Percent Complete]>=25, image,
image)))

If it is a text field type then you would want to construct it as

If([Percent Complete]="75", image,
If([Percent Complete]="50", image,
If([Percent Complete]="25", image,
image)))

That would at least be your basic starter If statement for structure of this formula that you could then customize to fit your specific images and needs. I hope these examples are helpful.  
Photo of mkosek

mkosek

  • 776 Points 500 badge 2x thumb
The first example you posted did not give me an error which is good. However no matter what the [percent complete] field actually shows, the icon is still showing it as if it were 0.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
When you have a value of 75% it is stored on quick base, same as excel as 0.75

So that is what you need to test against.


Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,754 Points 5k badge 2x thumb
Ahhh ok try them as decimals then like .75 and .50, etc with the rest of the formula exactly the same
(Edited)
Photo of mkosek

mkosek

  • 776 Points 500 badge 2x thumb
worked perfect. thanks!
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,266 Points 2k badge 2x thumb
If, as Evan says, your percent field is text use this - your syntax is wrong and one of your icon links was not correctly formatted: 

Case([Percent Complete],
"25","<img src=\"https://images.quickbase.com/si/16/504-spanner.png\">",
"50","<img src=\"https://images.quickbase.com/si/16/440-dollar_1.png\">",
"75","<img src=\"https://images.quickbase.com/si/16/211-check_all.png\">",
"<img src=\"https://images.quickbase.com/si/16/778-go_to_source.png\">"
)

If your Percent field is numeric; here's the formula for that.

Case([Percent],
0.75,"<img src=\"https://images.quickbase.com/si/16/211-check_all.png\">",
0.5,"<img src=\"https://images.quickbase.com/si/16/440-dollar_1.png\">",
0.25,"<img src=\"https://images.quickbase.com/si/16/504-spanner.png\">",
"<img src=\"https://images.quickbase.com/si/16/778-go_to_source.png\">"
)

(Thanks Mark for reminding me about the decimals on the percent field).
FYI - Updated because the forum put semi-colons on every line when there aren't any in my formula (??)  Clearly the screenshot vs. what pasted into the forum were not the same thing - nor was mine when I pasted my formula in.  Something is awry with the Forum... @Evan Martinez!
(Edited)