How to Create Nice Looking Bar Graphs Inside QuickBase Table Reports

Tips & Tricks
Mar 24, 2016
11 Min Read

QuickBaseExpertsBlogBannerThis 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-150 Paul Lanterman
OMS Photo[/caption]

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!

Getting Started

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.

[caption id="attachment_32742" align="alignnone" width="360"]Image of QuickBase data table Figure 1: Our initial movies table[/caption]


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:

The Setup

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:

  1. Create a new table (called “Images” or “Assets” or whatever you'd like)
  2. Add a File Attachment field to the form in the new table and save it
  3. Create a new record and upload an image of a blue bar image (feel free to use this one) to the File Attachment field. Hint: you can drag and drop the image onto the “Choose File” button.
  4. Save the record
  5. Right click on the file attachment in the saved record and select “Copy Link Address.” You now have the image URL you'll need for the next steps.

Creating our Formula URL Field

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:

  1. In the table with the movie data, create a new Formula-Text field. I called mine “BarGraph.”
  2. Check the “Allow some HTML tags to be inserted in the field” box.
  3. Add the formula shown below, using the URL of your image in place of the URL shown here. [Get this code hereFigure 2 illustrates the different components of the formula.

[caption id="attachment_32747" align="alignnone" width="640"]formula.01 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.

Adding the New Field To The Report

Next, we'll add the BarGraph field to the table report, as shown in Figure 3.

[caption id="attachment_32743" align="alignnone" width="452"]chart.02 Figure 3: The BarGraph field displays the values in the Ratings field as a bar[/caption]


Now we're getting somewhere. But I think we can do even better.

Enhancing our Graphic Display

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:

  1. Add a new File Attachment field to the table in which you stored the blue bar image. Upload a small vertical bar (feel free to use this one).
  2. Update the formula in the BarGraph field as shown in Figure 4, remembering to replace the URLs with those of your horizontal and vertical bars [Get this code here]:

[caption id="attachment_32748" align="alignnone" width="640"]formula.02 Figure 4: This formula adds and calculates the display of the horizontal bar, vertical bar, and a data label (click to enlarge)[/caption]


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.

[caption id="attachment_32744" align="alignnone" width="516"]chart.03 Figure 5: The new formula field renders enough detail that the Rating field is no longer necessary[/caption]

Even More Control

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

[caption id="attachment_32749" align="alignnone" width="640"]formula.03 Figure 6: DIV tags help position elements in a formula (click to enlarge)[/caption]


The results are a much more readable and appealing report, as shown in Figure 7.

[caption id="attachment_32745" align="alignnone" width="516"]chart.04 Figure 7: A bit of extra code yields a better-looking display[/caption]

When Things Get Iffy

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]

[caption id="attachment_32750" align="alignnone" width="640"]Figure 8: A little native QuickBase can change the display based on any variable (click to enlarge) Figure 8: A little native QuickBase can change the display based on any variable (click to enlarge)[/caption]

The Final Product

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.

[caption id="attachment_32746" align="alignnone" width="516"]Figure 9: Data optimized for fast visual display Figure 9: Data optimized for fast visual display[/caption]


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.



Recomended Posts