Forum Discussion

JamesMackay's avatar
JamesMackay
Qrew Member
6 years ago

Creating status using if statement

Building a check register in Quickbase, and want to be able to status checks as either cleared, outstanding, or issue.  Cleared would be when the amount in the cleared field equals the amount in the check field, outstanding would be when their is no value in the cleared field, and issue would be when the amount it the cleared field is not equal to the check amount.
Formulas and functions
  • AustinK's avatar
    AustinK
    Qrew Commander
    6 years ago
    If(
    [cleared amount] = [check amount], "Cleared",
    [cleared amount] = null, "Outstanding",
    [cleared amount] != [check amount], "Issue",
    "No Status"
    )

    Something like that will do it. You might have to play with the null or the != parts or re-order the lists so the right things get checked first but that should get you much closer. The reason you might have to play with the order of them is QuickBase takes the first True in the formula and runs with it. So you might have to be more explicit on some lines or it might just work as-is.
  • AdamKeever1's avatar
    AdamKeever1
    Qrew Commander
    6 years ago
    Try:

    If(
    [Cleared]=0, "Outstanding",
    [Cleared] = [Check], "Cleared",
    [Cleared] != [Check], "Issue",
    "No Status"
    )

    Looks like the numeric fields are zero if blank.







    • AustinK's avatar
      AustinK
      Qrew Commander
      6 years ago
      Blank values are treated as 0 only if this option for the field is selected, "Treat blank values as "0" in calculations". That might be default but not everyone uses the defaults. Probably better to have it calculate as 0 in this case but just something to watch for.
    • JamesMackay's avatar
      JamesMackay
      Qrew Member
      6 years ago
      Thank you for all your help?  One follow up question if I wanted to calculate for only checks with a status of Outstanding how many days they have been outstanding how would I filter that.  I know the formula would be today() - [check date], but if I don't want a value to calculate for checks cleared how would the filter work