Get Expert Help

Quick Base Junkie Blog

Short videos and posts to skyrocket your Quickbase skills!

Get tips in your inbox

Join the Quick Base Junkie Community email list!

Back to the Blog

Magical Mini Query Tables in Quickbase

advanced formula queries formulas how to html Jul 21, 2022

Subscribe on YouTube

Relational databases like Quickbase are great and I'm obviously a fan!

But...

Because there are so many tables and layers of data, sometimes it can be hard to see what you need to see at a glance.

Take this relationship for example:

Accounts < Opportunities < Activities

While looking at a table report of Accounts, I can't see what activities may be past due and need attention. For that, I'd either have to look at a summary on the Opportunities table or go directly to the Activities table.

But what if there was a way to display a mini table of the past due activities directly on the Account record (and reports)?

In this video, I'll show you how it is all possible thanks to the use of Formula Queries and HTML formulas.

 

 

Caution:

  • Queries on large tables may not render
  • Mini tables do not export well

 

 

 

 Formulas from the video:

Activity Table: Activity Summary - Text Formula

"<tr>" &
"<td>" & [Record ID#] & "</td>" &
"<td>" & [Type] & "</td>" &
"<td>" & [Due Date] & "</td>" &
"<td>" & [Status] & "</td>" &
"<td>" & UserToName([Assigned to],"FF") & "</td>" &
"</tr>"

 

Accounts Table: Summary of Past Due Activities - Rich Text Formula

var text QUERY = "{23.EX.'" & [Company] & "'}AND{15.LT.'today'}";
var number NUM = Size(GetRecords($QUERY,[_DBID_ACTIVITIES]));

If($NUM > 0,
"<table rules='rows' cellpadding=3 cellspacing=0 style='border: 1px solid black'>" &
"<tr style='background:blue; color:white;'>" &
"<td>ID#</td>" &
"<td>Type</td>" &
"<td>Due Date</td>" &
"<td>Status</td>" &
"<td>Assigned to</td>" &
"</tr>" &
SearchAndReplace(ToText(GetFieldValues(GetRecords($QUERY,[_DBID_ACTIVITIES]),28))," ; ","") &
"</table>")

 

 Alternative for Related Tables:

If your tables are related, there is no need to use formula queries (which can drag a bit on performance).

Instead, do this:

  1. Create a text formula field similar to the Activity Table Activity Summary field as shown above in the 'child' table
  2. Create a new Combined Text Summary of the field created in step 1 above. Include any conditions, such as Past Due.
  3. Create a rich text formula field in the 'parent' table with the formula below (updating the [Combined Past Due Activities] reference to the field created in step 2 above).
If(totext([Combined Past Due Activities])!="",
"<table rules='rows' cellpadding=3 cellspacing=0 style='border: 1px solid black'>" &
"<tr style='background:blue; color:white;'>" &
"<td>ID#</td>" &
"<td>Type</td>" &
"<td>Due Date</td>" &
"<td>Status</td>" &
"<td>Assigned to</td>" &
"</tr>" &
SearchAndReplace(ToText([Combined Past Due Activities])," ; ","") &
"</table>")

 

 

 

 RESOURCES:

Query Writing:

More Formula Query Help:

HTML & Formula Training:

Feeling like a Junkie?  Subscribe Now!

Recent Posts & Videos from the Blog