Recursive Relationships: Our Gang Demo

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
https://haversineconsulting.quickbase.com/db/bnzd357bm?a=td

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
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=694

Notes:

(1) ...