This Servoy Tutorial is on how to build a table with expandable/collapsible nodes, like a tree. True, you can probably find a CSS, JQuery or DHTML control that does it better, but this is done using the native Servoy table view.
Photo Credit: Denis Collette via Compfight
I first did this back in Servoy 4, and have used it on almost every project I have worked on. Seems like there is always a need to show related records (or child records) in table views. It’s not always appropriate to use a tree view, and as I mentioned in my Servoy tutorial The Demise of the Tree View, users hate the tree view (in my experience). So, I thought I would share the basics here, in the hopes that it will help someone else, maybe even inspire a more elegant solution (using objects?), that in turn, is also shared with the community. I’m sure others have had a need for a table with child nodes, so let’s take a look how to implement this.
In almost every table I add to a database, there are some standard columns that get added. Columns like “created_by_id”, “created_date”, “modified_by_id”, “modified_date” are the classic ones (left out of this example for simplification), but I also always add columns called “sequence_nr”, “sequence_icon”. I use these two columns for sorting records in all table views, as discussed in my earlier Servoy tutorial Optimized Table Shuffle. In this implementation, we add two additional columns, called “parent_id”, “parent_icon”, which will be used for handling the node expand/collapse effects. Any table in your database, that you want to use this technique with, needs these four columns.
Shown below is a table view that will be used to demonstrate the node expand/collapse effect. Notice that row 2, 6 and 10 are showing a different icon in column 1 then the other rows. The icon in column one is the value of “parent_icon” from our table. I’m also showing the “tree_id”, “parent_id” and “sequence_nr” columns from the table, so that we get some insight as to what is going on.
When this form is shown, the scopes.utils.tableTreeView_refresh() method is called. This method takes care of refreshing all the values of “parent_icon”, and restores the view to show nodes depending on whether they were last collapsed or expanded. In this case, we see all the parent nodes (with the arrows) are collapsed.
When we click on the arrow icon for row 2, the scopes.utils.tableTreeView_toggleParent() method will be called. It will expand the parent node and show the children. The children get a new “parent_icon” that shows whether they are a child or the last child. In our example, 2b is the last child of parent 2.
Clicking collapsed parent nodes 6 and 10 reveals the remaining children. We can now see all the records with no children, as well as all the parents and their children. If you leave this view and come back, it will be restored to exactly as shown below.
I’m not going to go through the code and try an explain it. Suffice it to say it does a lot of array manipulations. I stripped out as many of the extra features as I could, so you have the basic example to study.
There are several things that have been removed, that would be a good exercise for you to work on, if you need this type of view (or maybe just hire someone to help you?). For example, often data needs to be filtered by other keys, because the records in the table view is filtered, so you need to take that into consideration. Also, you may want to save the expand/collapsed view by user, so that the view is restored to its prior state for the logged in user. What about expanding children of children (more than one level deep)? Hmmm…
/** * @author: Gary Dotzlaw, 2013-12-15 * @description: Calculates the parent/child icons * * @param {String} sFormName - Name of form * @param {Boolean} [bSkipLoad] - Optional to skip the loading of the form * @return {Boolean} * @public * @AllowToRunInFind */ function tableTreeView_refresh(sFormName, bSkipLoad) { var iSize = 0, i = 0, k = 0, /** @type {JSFoundSet} */ fsTable, /** @type {JSFoundSet} */ fsParents, aParents = [], iParentsSize = 0, /** @type {JSFoundSet} */ fsChildren, aChildren = [], iChildrenSize = 0, /** @type {JSFoundSet} */ fsFind, aKeys = [], sKey = "", aSort = [], /** @type {JSRecord} */ rRec, /** @type {JSRecord} */ rRec_next, sPk = "", sCurrentPk = "", iIndex = 0; if (!sFormName || !forms[sFormName].foundset) return false; // Get a copy of the forms foundset fsTable = forms[sFormName].foundset.duplicateFoundSet(); iSize = fsTable.getSize(); iIndex = fsTable.getSelectedIndex(); //Do not do anything if only one record present if (iSize < 2) return false; // Get the PK column for the table; nice method chaining? sPk = databaseManager.getTable( scopes.enums.DB.SERVER, fsTable.getDataSource().split(”/”)[2] ).getRowIdentifierColumnNames()[0]; // Save the selected record sCurrentPk = forms[sFormName].foundset[sPk]; // Find the children’s foundset and sort them fsChildren = fsTable.duplicateFoundSet(); if (fsChildren.find() || fsChildren.find()) { fsChildren.parent_id = ”!^”; fsChildren.search(); } iChildrenSize = fsChildren.getSize(); fsChildren.sort(“parent_id, sequence_nr”); // Find the parent’s foundset and sort them fsParents = fsTable.duplicateFoundSet(); if (fsParents.find() || fsParents.find()) { fsParents.parent_id = ”^”; // find null fsParents.search(); } iParentsSize = fsParents.getSize(); fsParents.sort(“sequence_nr”); // Loop through the children, building an array of parents, marking the last child for a parent for (i = 1; i <= iChildrenSize; i++) { // Get the record rRec = fsChildren.getRecord(i); // Get the next record if any left if (i <= iChildrenSize - 1) { rRec_next = fsChildren.getRecord(i + 1); } else { rRec_next = ""; } // If this is the first record, then create an associated array for the children if (i === 1) { // First one aParents.push(rRec.parent_id); aChildren[rRec.parent_id.toString()] = []; } // Check for last child if (aParents[aParents.length - 1] === rRec.parent_id && rRec.parent_id === rRec_next.parent_id) { // This record and the next have the same parent rRec.parent_icon = scopes.enums.icon_nodeChild; } else if (aParents[aParents.length - 1] === rRec.parent_id && rRec.parent_id != rRec_next.parent_id) { // This record is the last child for this parent rRec.parent_icon = scopes.enums.icon_nodeChildLast; } else if (rRec.parent_id != rRec_next.parent_id) { // This record has a new parent and is the last child aParents.push(rRec.parent_id); rRec.parent_icon = scopes.enums.icon_nodeChildLast; aChildren[rRec.parent_id.toString()] = []; } else { // This record has a new parent aParents.push(rRec.parent_id); rRec.parent_icon = scopes.enums.icon_nodeChild; aChildren[rRec.parent_id.toString()] = []; } // Push the child’s PK into an associated array by parent id aChildren[rRec.parent_id.toString()].push(rRec[sPk]); } // Now loop through the parents and insert children where the parent is expanded for (i = 1; i <= iParentsSize; i++) { rRec = fsParents.getRecord(i); aSort.push(rRec[sPk]); if (rRec.parent_icon === scopes.enums.icon_nodeExpanded && aChildren[rRec[sPk].toString()]) { iSize = aChildren[String(rRec[sPk])].length; // Insert the children into the sort array for (k = 0; k <= iSize - 1; k++) { aSort.push(aChildren[rRec[sPk].toString()][k]); } } // Check to see if the parent has any children and set the icon appropriately if (!aChildren[rRec[sPk].toString()]) { // Has no children rRec.parent_icon = scopes.enums.icon_node; }else{ // Has children, so show the collapsed icon if not expanded if (rRec.parent_icon != scopes.enums.icon_nodeExpanded) rRec.parent_icon = scopes.enums.icon_nodeCollapsed; } } // Now load all the records, select the ones we are using, and set their sort order fsFind = fsTable.duplicateFoundSet(); fsFind.loadAllRecords(); iSize = aSort.length; for (i = 0; i < iSize; i++) { sKey = aSort[i]; if (!fsFind.selectRecord(sKey)) { if (fsFind.find() || fsFind.find()) { fsFind[sPk] = sKey fsFind.search(); } } fsFind.sequence_nr = i + 1; aKeys.push(sKey); } // Now load the records we need if (!bSkipLoad) forms[sFormName].controller.loadRecords(databaseManager.convertToDataSet(aKeys)); // Reset the selected record if (sCurrentPk) { if (!forms[sFormName].foundset.selectRecord(sCurrentPk)) { forms[sFormName].foundset.setSelectedIndex(iIndex); } } else { forms[sFormName].foundset.setSelectedIndex(1); } return true; }
/** * @author: Gary Dotzlaw, 2013-12-15 * @description: Calculates the parent/child icons * * @param {String} sFormName - Name of form * @param {Boolean} [bSkipLoad] - Optional to skip the loading of the form * @return {Boolean} * @public * @AllowToRunInFind */function tableTreeView_refresh(sFormName, bSkipLoad) {
var iSize = 0, i = 0, k = 0, /** @type {JSFoundSet} */ fsTable, /** @type {JSFoundSet} */ fsParents, aParents = [], iParentsSize = 0, /** @type {JSFoundSet} */ fsChildren, aChildren = [], iChildrenSize = 0, /** @type {JSFoundSet} */ fsFind, aKeys = [], sKey = "", aSort = [], /** @type {JSRecord} */ rRec, /** @type {JSRecord} */ rRec_next, sPk = "", sCurrentPk = "", iIndex = 0;
if (!sFormName || !forms[sFormName].foundset) return false;
// Get a copy of the forms foundset fsTable = forms[sFormName].foundset.duplicateFoundSet(); iSize = fsTable.getSize(); iIndex = fsTable.getSelectedIndex();
//Do not do anything if only one record present if (iSize < 2) return false;
// Get the PK column for the table; nice method chaining? sPk = databaseManager.getTable( scopes.enums.DB.SERVER, fsTable.getDataSource().split("/")[2] ).getRowIdentifierColumnNames()[0];
// Save the selected record sCurrentPk = forms[sFormName].foundset[sPk];
// Find the children's foundset and sort them fsChildren = fsTable.duplicateFoundSet(); if (fsChildren.find() || fsChildren.find()) { fsChildren.parent_id = "!^"; fsChildren.search(); } iChildrenSize = fsChildren.getSize(); fsChildren.sort("parent_id, sequence_nr");
// Find the parent's foundset and sort them fsParents = fsTable.duplicateFoundSet(); if (fsParents.find() || fsParents.find()) { fsParents.parent_id = "^"; // find null fsParents.search(); } iParentsSize = fsParents.getSize(); fsParents.sort("sequence_nr");
// Loop through the children, building an array of parents, marking the last child for a parent for (i = 1; i <= iChildrenSize; i++) {
// Get the record rRec = fsChildren.getRecord(i);
// Get the next record if any left if (i <= iChildrenSize - 1) { rRec_next = fsChildren.getRecord(i + 1); } else { rRec_next = ""; }
// If this is the first record, then create an associated array for the children if (i === 1) { // First one aParents.push(rRec.parent_id); aChildren[rRec.parent_id.toString()] = []; }
// Check for last child if (aParents[aParents.length - 1] === rRec.parent_id && rRec.parent_id === rRec_next.parent_id) {
// This record and the next have the same parent rRec.parent_icon = scopes.enums.icon_nodeChild;
} else if (aParents[aParents.length - 1] === rRec.parent_id && rRec.parent_id != rRec_next.parent_id) {
// This record is the last child for this parent rRec.parent_icon = scopes.enums.icon_nodeChildLast;
} else if (rRec.parent_id != rRec_next.parent_id) {
// This record has a new parent and is the last child aParents.push(rRec.parent_id); rRec.parent_icon = scopes.enums.icon_nodeChildLast; aChildren[rRec.parent_id.toString()] = [];
} else {
// This record has a new parent aParents.push(rRec.parent_id); rRec.parent_icon = scopes.enums.icon_nodeChild; aChildren[rRec.parent_id.toString()] = []; }
// Push the child's PK into an associated array by parent id aChildren[rRec.parent_id.toString()].push(rRec[sPk]); }
// Now loop through the parents and insert children where the parent is expanded for (i = 1; i <= iParentsSize; i++) {
rRec = fsParents.getRecord(i); aSort.push(rRec[sPk]);
if (rRec.parent_icon === scopes.enums.icon_nodeExpanded && aChildren[rRec[sPk].toString()]) {
iSize = aChildren[String(rRec[sPk])].length;
// Insert the children into the sort array for (k = 0; k <= iSize - 1; k++) {
aSort.push(aChildren[rRec[sPk].toString()][k]); } }
// Check to see if the parent has any children and set the icon appropriately if (!aChildren[rRec[sPk].toString()]) {
// Has no children rRec.parent_icon = scopes.enums.icon_node; }else{
// Has children, so show the collapsed icon if not expanded if (rRec.parent_icon != scopes.enums.icon_nodeExpanded) rRec.parent_icon = scopes.enums.icon_nodeCollapsed; } }
// Now load all the records, select the ones we are using, and set their sort order fsFind = fsTable.duplicateFoundSet(); fsFind.loadAllRecords(); iSize = aSort.length;
for (i = 0; i < iSize; i++) {
sKey = aSort[i];
if (!fsFind.selectRecord(sKey)) { if (fsFind.find() || fsFind.find()) { fsFind[sPk] = sKey fsFind.search(); } } fsFind.sequence_nr = i + 1; aKeys.push(sKey); }
// Now load the records we need if (!bSkipLoad) forms[sFormName].controller.loadRecords(databaseManager.convertToDataSet(aKeys));
// Reset the selected record if (sCurrentPk) { if (!forms[sFormName].foundset.selectRecord(sCurrentPk)) { forms[sFormName].foundset.setSelectedIndex(iIndex); } } else { forms[sFormName].foundset.setSelectedIndex(1); }
return true;}Here is the method that fires when column 1, parent_icon, is clicked. It takes care of setting a parent to either expand or collapse, and then calls the refresh method to load the appropriate children.
/** * @author: Gary Dotzlaw, 2013-12-15 * @description: Expand or collapses a parent in a table tree view * * @param {JSEvent} event * @param {String} [sKeyField] - Optional key field to restrict the foundset to * @param {String} [sKeyValue] - Optional key value to restrict the foundset to * @return {Boolean} * @public * */ function tableTreeView_toggleParent(event, sKeyField, sKeyValue) { var sFormName = "", iIndex = 0, /** @type {JSFoundSet} */ fsTable, /** @type {JSRecord} */ rRec; // Get the form name from the event if (!event) return false; sFormName= event.getFormName(); if (!sFormName) return false; // Grab a copy of the foundset fsTable = forms[sFormName].foundset.duplicateFoundSet(); iIndex = fsTable.getSelectedIndex(); // Get the record that was toggled rRec = fsTable.getRecord(iIndex); if (rRec.parent_icon === scopes.enums.icon_nodeExpanded) { rRec.parent_icon = scopes.enums.icon_nodeCollapsed; } else if (rRec.parent_icon === scopes.enums.icon_nodeCollapsed) { rRec.parent_icon = scopes.enums.icon_nodeExpanded; } else { // Don’t need to do anything, there are no children return false; } // Refresh the view tableTreeView_refresh(sFormName); forms[sFormName].foundset.setSelectedIndex(iIndex); return true; }
/** * @author: Gary Dotzlaw, 2013-12-15 * @description: Expand or collapses a parent in a table tree view * * @param {JSEvent} event * @param {String} [sKeyField] - Optional key field to restrict the foundset to * @param {String} [sKeyValue] - Optional key value to restrict the foundset to * @return {Boolean} * @public * */function tableTreeView_toggleParent(event, sKeyField, sKeyValue) {
var sFormName = "", iIndex = 0, /** @type {JSFoundSet} */ fsTable, /** @type {JSRecord} */ rRec;
// Get the form name from the event if (!event) return false; sFormName= event.getFormName(); if (!sFormName) return false;
// Grab a copy of the foundset fsTable = forms[sFormName].foundset.duplicateFoundSet(); iIndex = fsTable.getSelectedIndex();
// Get the record that was toggled rRec = fsTable.getRecord(iIndex);
if (rRec.parent_icon === scopes.enums.icon_nodeExpanded) {
rRec.parent_icon = scopes.enums.icon_nodeCollapsed;
} else if (rRec.parent_icon === scopes.enums.icon_nodeCollapsed) {
rRec.parent_icon = scopes.enums.icon_nodeExpanded;
} else {
// Don't need to do anything, there are no children return false; }
// Refresh the view tableTreeView_refresh(sFormName); forms[sFormName].foundset.setSelectedIndex(iIndex);
return true;}That concludes this Servoy tutorial; short and sweet. Hopefully someone out there finds the technique shown here useful and can learn a thing or two from the example. I hope you enjoyed it, and I look forward to bringing you more Servoy tutorials in the future.