Recursive Relationships: Our Gang Demo

  • 38
  • 1
  • Question
  • Updated 3 months ago
  • Doesn't Need an Answer
  • (Edited)
Perhaps you have needed to model a recursive relationships such as (1) an organization chart, (2) a family tree (3) a hierarchical bill of manufacture or assembly or (4) a hierarchy of tasks and sub-tasks. You can certainly accomplish this with QuickBase by relating a table to itself. However, it can be difficult or impossible to query, process or copy records in a table that is related to itself. This demo use script and some ES6 features to demonstrate how these types of models can be made fairly simple to use. Additionally, the same scripting techniques can  be extended to manipulate collections of hierarchically related tables.

For our demo we will use a simple organization chart for seven members of the Our Gang troupe and the task we will set for ourselves is to recursively calculate the total salary of the organization:

Our Gang ~ Members Home

For simplicity we will enter our script through the console for quick testing but the script with modifications can easily be integrated into various workflows. Here is an abreviated version of the script that demonstrates how we will iterate through the table to calculate the cumulative salary
(async () => {
  const dbid = "bnxssrg9s";
  const dbidMembers = "bnzd357bm";
  const apptoken = "cnurzq5cgchgjgdsdiup6cgkq6ct";
  $.ajaxSetup({data: {apptoken}});
  let ridRoot = "1";
  let relatedFid = "9";
  let iterTraverseTreeBF = genTraverseTree(dbidMembers, ridRoot, relatedFid, "BF");
  let totalSalary = 0;
  console.log("Breath First: Name, Salary, Cumulative Salary");
  for await (const {rid, record: {fids, labels}} of iterTraverseTreeBF) {
    totalSalary += parseFloat(labels["Salary"].value);
    console.log(rid, fids["6"].value, labels["Salary"].value, totalSalary);
  async function* genTraverseTree(dbid, ridRoot, relatedFid, mode) {/* ... */}
The part of the script that we are omitting above is the definition of the asynchronous generator genTraverseTree which will iterate through the series of promises that visit each related record.

The key part of this code is the for await loop that traverses (ie visits) each related record (identified by relatedFid=9) starting from the ridRoot=1 record (ie Spanky's record). During each iteration of the loop all the fields of the record rid are retrieved and made available through two objects fids and labels. Using fids[fid] you can access a field value by passing the field's fid. Similarly using labels[label] you can access the field value by passing the field's label.

Here is a screen shot of pasting the full script into the console:

Note that by two different methods of traversing the relationship tree (breadth first and depth first) we calculate the cumulative salary of all Our Gang members. If you look closely at the output you can distinguish the slight difference in the order that the members are visited:

Breadth First:

Depth First:

The full code is a bit complicated but is quite general purpose and you can test it yourself by visiting the Our Gang application and pasting the code into the console.

Pastie Database


(1) ...

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,644 Points 20k badge 2x thumb

Posted 3 months ago

  • 38
  • 1
Awesome Dan. You are the ultimate!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,644 Points 20k badge 2x thumb
>Are you saying the script will generate those diagrams or is that just for illustration purpose

The script could be adopted to generate those diagrams but they were manually created. By virtue of traversing the relationship tree and retrieving all the relevant fields you can perform arbitrary actions for each node visited. By actions I mean arbitrary JavaScript actions not native QuickBase actions. One way to generate a diagram would be to render each node into a segment of GraphViz code. See this post:

IMA Graph Theorist - Does QuickBase Support GraphViz Fields?

GraphViz Fields
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 29,644 Points 20k badge 2x thumb
FWIW, I quickly tried to generate the GraphViz code for the org chart. I don't know how to include the image but all you have to do is generate a small amount of GraphViz for each node/record you visit in the for loop. See:
Awesome Dan. Thanks again.