Helpful real world Quick Base formula examples

Here are a few formulas you can use to make your reports and QuickBase a bit better.  

First off, many times in reports you have dates but want to view the month name and have the report shown in sequence…here is a simple CASE formula to accomplish just that

 Case(Month([Your-Date-Field]),
1,"01-Jan",
2,"02-Feb",
3,"03-Mar",
4,"04-Apr",
5,"05-May",
6,"06-Jun",
7,"07-Jul",
8,"08-Aug",
9,"09-Sep",
10,"10-Oct",
11,"11-Nov",
12,"12-Dec",
"Error")

Second, using NESTED IF statements will solve many of your issues in QuickBase.  Here is the syntax and an example for a NESTED IF formula to set up what do do based on the field value in the “Status” field…

If ([Status]= "Not Started", DoThis,
If ([Status]= "Started", DoThisInstead,
If ([Status]= "Complete", DoThat, ThisIsTheElse)))

Third, many times you are going to want to make actions based on whether a field is empty or NULL.  This is a bit complicated in QuickBase because you need to do the evaluation differently when the Field Type if TEXT.

For most Field Types you can simply use 

IsNull(Your-Field)

However if the Field Type is TEXT you need to evaluate it differently

If(Trim([Your-Text-Field])="", ThisIsNull, WhatToDoForNotNull

One thing to note, in the Field Properties for NUMERIC fields, there is a tickbox that lets you choose Treat blank values as "0" in calculations.  This is important in the IsNull calculations…you need to weigh up if you need to see Totals in the reports (if you want totals you need to tick this box) or if you want to evaluate this field as Null.

Fourth, it is easy to use colour to highlight a row, but often times we want to set a cell to a colour based on it’s value.  This is accomplished in MS Excel by using conditional formatting, but with Quick Base you need to approach it a bit differently.  First, you can not highlight a cell that is a formula, it needs to be a field type TEXT so create a duplicate field that is a text formula of the field you want and just append (colour) to the field so you know what it is for. Then make sure you tick the field properties tickbox that says “Allow some HTML tags to be inserted in the field”. Finally use a CASE or NESTED IF to generate the highlighted cell.

If ([FieldToEvaluate] < 5, "<div style=\"background-color:#FF6666;\">Bad</div>",
If ([FieldToEvaluate] <= 12, "<div style=\"background-color:#00ff80;\"> Good</div>", 
"<div style=\"background-color:#FF8C00;\">Warning</div>"))

Case([FieldToEvaluate],
"Value-1", "<div style=\"background-color:#00ff80;\">Value-1</div>",
"Value-2", "<div style=\"background-color:#FF6666;\">Value-2</div>",
"Value-3", "<div style=\"background-color:#FF8C00;\"> Value-3</div>",
"")

Last point, here is a quick set of 20 good distinct colours you can pick from to save time.

BLACK = #000000
BLUE = #0082C8
BROWN = #AA6E28
CORAL = #FFD8B1
CYAN = #46F0F0
GREEN = #3CB44B
GREY = #808080
LAVENDER = #E6BEFF
LIME = #D2F53C
MAGENTA = #F032E6
MAROON = #800000
MINT = #AAFFC3
NAVY = #000080
OLIVE = #808000
ORANGE = #F58231
PINK= #FABEBE
PURPLE = #911EB4
TEAL = #008080
WHITE = #FFFFFF
YELLOW = #FFE119