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:

• Query Writing 101 for Quickbase

More Formula Query Help:

• Oh So Fantastic Formula Queries
• Quickbase documentation

HTML & Formula Training:

• Mastering the Text Formula
• HTML Formula Crash Course
• Formula Bundle

Feeling like a Junkie?  Subscribe Now!