Even as a manager of the QuickBase software development team I still get impressed every day with the little things you can do in QuickBase to add value to whatever problem you are solving. I needed to add color to a QuickBase that I’m using to manage a process on our team. I know about Row Colorization that is invokable for any report. Row Colorization, while quite effective, can be rather stark when you have lots of columns, and you may want to use a report to indicate more than one color. For example, you may want to show that task is in trouble by saying it is currently red, but you may also want to indicate that the trend is green. Row colorization doesn’t really allow that kind of information. Also, if you have a multi-line text field in one of the columns (let’s say an append only field with history), you could get into a situation where you have this huge block of red, but really it’s just one item. I suppose you could hide the field, write a formula field that summarized the append only field et cetera.
In my case I did want to show the current status and the trend, so I would need two different colors. I poked around the Online Help and found a little feature that really makes a report pop, and allows you to indicate more info on one row than a single color. You can find more info in the Online Help topic Color the background of a Field using HTML
What you see here is that we have a simple to do list that allows us to assign a status to that item. For fun I have assigned Green, Yellow and Red to our ability to get those things done. The problem here is that I can read Red, but I don’t see Red. and having that whole line Red would hide the text (unless I changed it to white for example)
I think it would look much better if I could just change the color of the cell in status, so I could look like this:
Actually what I did was quite simple. Any text cell can have its background color changed based on the contents by allowing HTML tags to be set in the field properties. But that’s where I ran into a little hitch. I can only do that with text and formula text field, but QuickBase doesn’t allow that for multiple choice fields. So I got around that by creating a second forumula text field that had the colorization in it as you can see in the picture below.
And the settings for the status color field are here:
Notice that the HTML checkbox is checked and that the formula checks the value of the corresponding “status” field and uses the case statement to give you the color. The html text formula is the following:
BTW be careful with the quotes. Make sure that they are not the stylized quotes but the plain double quote next to the Return/Enter key on your keyboard. A couple of my other blog posts got messy with the stylized quotes but they seem to be correct here.
Now the cute things about this is the way that the actual form handles those fields. Basically you have a pulldown in the form for Red Yellow and Green, and a separate entry for the forumula field, which will change color as you change the text field. What I did in my real app and not this toy app was place the formula field at the top of the default form so that visually, the first thing you see for any record if you view or edit it is the color of the status. Down near date fields and append-only text description fields below, I put the pulldown.
Also as you see above, I hid the pulldown field in the second picture. This also means that Grid Edit won’t allow you to change the color unless you 1. add the field before going into grid edit or 2. make the default grid edit form contain both fields. I leave that as an exercise for the reader.
As I said in my problem statement above, I needed two fields with color to both give the immediate and the trend status option to the project managers. As you can tell, having both colors up there (lets say red status and yellow trend) might give people an indication of an improving situation or in reverse a deteriorating situation. You can also use this for things other that status such as credits/debits (red and black…then you’ll definitely need the special incantation for white lettering in the Online Help), weather reporting (white for snow, yellow for sunny, gray for rain) or whatever color scheme suits you.
BTW the QuickBase online help has all this information in it, so happy coloring…
ArmenPosted in How To's