This QuickBase Experts Series post was written by Paul Lanterman, Creative Technical Specialist at OMS Photo in Cincinnati, OH.
I work at a photography studio, so my end users are visually-oriented folks. With the exception of our bookkeeper, our employees spend their time taking, fixing, sorting, printing, editing, or archiving images.
It's what we do.
So when I started using QuickBase for keeping track of jobs and productivity and scheduling — all the business stuff that we needed help taming — the results were great! But showing the results to my boss and other users? That wasn't as successful. My users don't like to look at Excel lists. They don't like spreadsheets. They don't like to wade through columns and rows to get information.
[caption id="attachment_32845" align="alignright" width="150"] Paul Lanterman
I needed to step it up a bit. The native QuickBase data displays didn't offer the level of refinement I was looking for. So I cooked up a few tricks using HTML and CSS formula fields to display our data in a way that shows a real impact. (Side note: I am NOT an HTML or CSS wizard, and I'm sure these concepts can be done cleaner and more simply by those who are).
In this post, I'll show you one method for creating attractive bar graphs inside QuickBase table reports to give end users a fast, visual reference point. And, I've included my actual code — copy and paste away!
I've tossed together a sample table using data from movie-review site Rotten Tomatoes. This table includes movies by Year Released, Rank, Tomatoes Rating, Number of Voters, and (so I had some random numbers) Length of Title. The report in Figure 1 is filtered by titles less than 8 Characters.
Without any visual cues, figuring out which movies are the highest-rated requires a bit of squinting. What if we had a visual indicator that shows the "good" movies? Here are the steps to making it happen:
We'll start by uploading a single image — a blue bar. You can use a server or content delivery network (CDN), or you can upload it directly into QuickBase. To do that, you'll need to:
Next we need to create the field that will display the image in a way that makes sense. We are going to use some HTML and some math in a Formula-Text field. Our first goal is to make the image's width be based on the value we want to display — the Rating percentage. To do that:
[caption id="attachment_32747" align="alignnone" width="640"] Figure 2: Our formula for creating a blue bar image of standard height, but variable width based on a calculation (click to enlarge)[/caption]
You will notice I added a calculation that will change the width of the blue bar based on the % listed in the Ratings field, while keeping the height the same.
NOTE: This formula only works if the Rating field is Numeric - Percentage and not a Text field.
Next, we'll add the BarGraph field to the table report, as shown in Figure 3.
Now we're getting somewhere. But I think we can do even better.
Next we'll use the same process to add a vertical bar and a bold data label to help viewers more quickly distinguish the widths of the bars in the table report. We'll also use some CSS to formatting the % values. To do that:
Now things are starting to come along! Not only is the graphical display easier to read (Figure 5), but now we can consolidate our report by removing the Rating column.
We've come a long way, but the graphics are still a bit messy. The numbers don't line up right (and nobody likes jumbly numbers).
We're going to use DIV tags in HTML to straighten things out. A DIV tag stands for “divider” and is essentially a box of stuff. The box will have width, height, and contents. You can specify how the box is displayed, and where it is positioned relative to other objects.
QuickBase is fussy about DIVs — not all standard cut-and-paste code will work. You may have to use trial and error to get where you want to go, but it's worth it in the long run.
We'll use the same parts as the last step — we're just reworking how they behave. Getting even more control is as simple as updating the formula with the code shown in Figure 6, remembering to change the URLs and field names if necessary. [Get this code here]
(I usually separate my code with line breaks — it helps me keep my thinking straight.)
The results are a much more readable and appealing report, as shown in Figure 7.
Now let's get really crazy and let QuickBase manage which information really gets highlighted. Since we're working with a QuickBase formula, we can still use all the other elements of native QuickBase.
For example, let's add an IF statement that changes the display of a graph based on the rating itself. Change the code in your BarGraph field to the below to see what I mean (remember to change those URLs and field names!). See Figure 8 for an illustrated breakdown of what's happening. [Get this code here]
As you can see in our final product (Figure 9), we've taken a boring numeric column in a table report and created an engaging graphical display that is simple, clean, and obvious at a glance.
I hope I've got you thinking about ways to simplify your information for maximum impact. Every case is unique, and you may have to count on trial and error, but the results of these methods are worth the effort.