Adding flair with some HTML and Quickbase sorcery

Quickbase is truly an exceptional platform. Our in-memory database allows builders to create value in 1/4th - 1/5th the time of traditional technologies or low-code solutions. That being said, success in software implementations heavily relies on the human aspect, user experience and change management. Plenty of our research has shown that when builders put time into the end user's experience, the app is stickier and more successful. Lisa Sawyer, our XD Manager, did an Empower 2020 session about this which was wildly successful.

Most mission critical workloads running on Quickbase use date fields one way or another, usually to drive workflow. @Ryan Pflederer and I were cooking some things up recently and thought this one was cool enough to share with the wider community.



The problem we aimed to solve with this formula is similar to what many of you do in your own apps today: How can I visually show my users the status of a project at a glance?

Rather than a single field displaying a stop light and status, or a simple summary field showing # of late tasks, we wanted to provide something a little more thorough. And since this is Quickbase, we want to make sure that users can take action on their insights, so each of these circles is a hyperlink to the respective task.

What is cool about this is we designed it in a way to really leverage the power of Quickbase formulas and summary fields, to be flexible enough to handle the varying schemas out there. All of the following are dynamic:

• Number of circles
• Colors
• Text content

All of this is done with just some minor HTML and CSS, and not a single line of JavaScript, PHP or C#. Just the Quickbase engine doing the heavy lifting. What I'm exceptionally passionate about is not only how cool this is today, but how excited I am for the future. Concepts like this will continue to get not only easier to implement, but more powerful at the same time as we make step-change improvements to our formula language and core capabilities.

So, let's back our way into how this works.

HTML Formula

The main formula here is pretty simple. In the above, that is the field "Timeline Dynamic". The container styling is defined in this formula and relies on the actual circles to be filled in by a Quickbase combined text field.

var text timelineStyle = "style='list-style-type: none; display: flex; align-items: center; justify-content: center'";
var text children = SearchAndReplace(SearchAndReplace(ToText([Combined Text Rich Text formula]),"| ; ",""),"|","");

"<div style='display: flex; justify-content: center; font-family: arial; color: #758D96; margin:-10px 0px -25px -30px'>" &
"<ul style='list-style-type: none; display: flex; '>" &
$children &
"</ul>" &
"</div>"

By using search and replace in our formula above, we can swap out the semicolon (;) Quickbase uses to delineate a combined text field with some helpful HTML. Now, let's look at the output of that field [Combined Text Rich Text formula].


Basically, this summary field is aggregating some HTML we defined on the child table. Each child record has its very own HTML, which represents one "circle" on the timeline. 

Rich Text Formula

Below, you'll see the visual output, the generated HTML on this record and the formula that makes it possible.

var text red = "#f25e30";
var text green = "#2c9382";
var text yellow = "#d69900";
var text grey = "#ddd";
var text darkgrey = "#758D96";

var text textColor = 
If(
[Status]="Complete", $green,
[Due Date]<=Today(), $red,
[Due Date]-Days(2)<=Today(), $yellow,
$darkgrey
)
;
var text cirColor = 
If(
[Status]="Complete", $green,
[Due Date]<=Today(), $red,
[Due Date]-Days(2)<=Today(), $yellow,
"white"
)
;
var text lineColor = 
If(
[Status]="Complete", $green,
[Due Date]<=Today(), $red,
[Due Date]-Days(2)<=Today(), $yellow,
$grey
)
;

var text divOne = "margin-bottom: 20px; padding: 0px 20px; display: flex; flex-direction: column; align-items: center; font-weight: 100; text-align: center;";
var text divTwo = "padding: 0px 20px; display: flex; justify-content: center; border-top: 2px solid "& $lineColor &"; position: relative;";
var text circle = "width: 19px; height: 19px; background-color: "& $cirColor &"; border-radius: 100%; border: 1px solid "& $lineColor &"; position: absolute; top: -12px;";
var text hFour = "font-weight: 600; color:"& $textColor &";";

var text topVal = UserToName([Assigned To],"FF") & "<br>" & ToFormattedText([Due Date],"MMDDYYY"); // Text to display above the icon
var text botVal = [Task Name]; // Text to display below the icon
var text order = ToFormattedText([Due Date],"YYYYMMDD"); // Used to ensure the proper order of the records
var text link = URLRoot() & "db/" & Dbid() & "?a=dr&rid=" & [Record ID#]; // Hyperlink URL when clicking the icon


"<li id='" & $order & "'>" &
"<div style='" & $divOne & "'>" & $topVal & "</div>" &
"<a href='" & $link & "' style='text-decoration: none;' title='View task'>" &
"<div style='" & $divTwo & "'>" &
"<div style='" & $circle & "'></div>" &
"<h4 style='" & $hFour & "'>" & $botVal & "</h4>" &
"</div>" &
"</a>" &
"</li>|"

Hopefully some of you find this valuable. Have a cool pattern in Quickbase you'd like to share? We would love to hear about it!

------------------------------

Harrison Hersch
------------------------------