Quick Base Discussions

Expand all | Collapse all

formula using summary fields?

  • 1.  formula using summary fields?

    Posted 10-05-2018 20:24
    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\">>")


  • 2.  RE: formula using summary fields?

    Top
    Contributor
    Posted 10-05-2018 20:31
    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.  


  • 3.  RE: formula using summary fields?

    Posted 10-05-2018 20:40
    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.



  • 4.  RE: formula using summary fields?

    Posted 10-05-2018 20:43
    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.




  • 5.  RE: formula using summary fields?

    Top
    Contributor
    Posted 10-05-2018 20:45
    Ahhh ok try them as decimals then like .75 and .50, etc with the rest of the formula exactly the same


  • 6.  RE: formula using summary fields?

    Posted 10-05-2018 21:06
    worked perfect. thanks!


  • 7.  RE: formula using summary fields?

    Posted 10-05-2018 20:47
    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!