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
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