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 c...
Forum Discussion
Try:
If(
[Cleared]=0, "Outstanding",
[Cleared] = [Check], "Cleared",
[Cleared] != [Check], "Issue",
"No Status"
)
Looks like the numeric fields are zero if blank.
If(
[Cleared]=0, "Outstanding",
[Cleared] = [Check], "Cleared",
[Cleared] != [Check], "Issue",
"No Status"
)
Looks like the numeric fields are zero if blank.
- 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.
- Good tip. Thanks
- 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
- I would just create a report formula for that to make it easy. Something like this should do it. You need to make sure they are only dates and not date/time. For mine here I had to change it, you may not. This is a Formula - Duration.
If(
[status] = "Outstanding", Today()-ToDate([Date Created]), null
)
You will also need to put that column on the report. It would just be blank for other records or you could add to it. It isn't best practice, on the form would probably be better. A Formula Duration would work for that too. - Another way would be to have your formula field calculate the days duration,Today()-ToDate([Date Created], and then filter your report to only show [status] = "Outstanding
