Optimized Table Shuffle

This Servoy Tutorial demonstrates how to shuffle rows in a table view, and looks at different approaches to determine the most efficient. Determining the most efficient approach for the shuffle, also reinforces some best practices for working with a foundset of records in Servoy.

Table views are an important part of any user interface, and many times a table is used to show data in a specific order. For example, the data might represent a production que, in which specific jobs have to be completed in a certain order. The user might need to change the order depending on the circumstances that arise, and you don’t want them going into a dialog to edit a sort order. Drag and drop might be nice, but there is are alternatives, particularly if you are using a native Servoy table view.

In this Servoy tutorial we take a look at an approach I have used for many years, on every project I have ever been involved in. It allows the user to interact with the table and manually change the sort order of the records. This example will serve another purpose in our tutorial, which is to demonstrate the most efficient way to work with a set of records in a table view.

Before we get into the code and start optimizing things, let’s have a look at the final result we are going to achieve. Here is a simple table view with our event records from our Servoy tutorial on Event Driven Architecture. I have purposely exposed the “sequence_nr” column in the view so that we can see what is happening. Notice the gray shuffle arrows next to the trash can on the right-hand side. These will be used to reorganize the sort order of the records in the table view.

When a user wants to change the order of the records, all they need to do is click on the shuffle icon for the record they want to move. This record’s shuffle icon turns red, and all others turn green. The red indicates this is the record that will be moved, and the green are the available locations it could be moved to. The user picks the destination location (in this example the first record with “sequence_nr” = 1.

Instantly the record that was slated to be moved, is moved to the target destination location, all the records are renumbered, and all the icons turn back to green, ready for the next move sequence.

Okay, pretty straightforward to visualize, right? But, let me ask you, how would you go about moving that record? You see, sometimes, there are 100’s of records in the table view, and speed is important. You don’t want the user grabbing lunch while your sort finishes, if you know what I mean. So, choosing the right approach is essential, and it reinforces a best practice when working with a group of records in the user interface. What is that best practice? Never, ever, use the controller, to manipulate and loop through the records. It’s the worst thing you could possibly do.

while (i <= iRows) {			
	forms.events_tbl.controller.setSelectedIndex(i);
	forms.events_tbl.controller.sequence_nr = i;
	i += 1;
}

All kinds of things happen when you use the controller; the UI updates each time you touch a record, the “onRecordSelect()” event will fire for each record you touch, and all kinds of other things happen (calculations and row rendering), that will slow your routine down.

Instead, what you need to use is the foundset. The foundset is the same group of records, but in memory, so when you touch the records in the foundset, you do not affect the UI. This is a very important point to understand, so I will repeat it. Do not use the controller, use the foundset; it is the same records but in memory and does not affect the UI.

Where is the foundset? The foundset is found in the “DatabaseManager” node of the “Solution Explorer” view. You will see it has pretty much the same methods as the controller has, with the exception of some UI specific things the controller can do (like set focus to a field in the UI). You can get the foundset simply using any one of the following techniques:

var fs;

// Get the foundset from a form
fs = forms.events_tbl.foundset;

// If you just want the foundset for the form you are on
fs = foundset;

// If you want the foundset from a related table to the customer 
// record you are on
fs = customer_to_contact.foundset;

// etc.

Okay, so now we know we should use a foundset. So there are three ways that I can think of to loop through that foundset, which is something we need to do quickly, if we want to shuffle large sets of records.

The first approach is the classical foundset implementation; loop through the foundset using the “setSelectedIndex()” method. This moves the current index in the foundset, allowing you to change the icon, or update the sequence number, as shown below. I use a while loop here, but you could use a for loop, if you like typing. Nothing really wrong with this approach, but it is the slowest of the three methods I will suggest.

while (i <= tableShuffleSource) {			
	fsTable.setSelectedIndex(i);
	fsTable.sequence_nr = i;
	i += 1;
}

The next approach uses the “FoundSetUpdater”. The “FoundSetUpdater” has an API (also located in the “DatabaseManger” node of the “Solution Explorer” view), and excels at batch updating an entire foundset, and is much faster than if you tried to make the same change to each record by iterating over them as in the prior “foundset.setSelectedIndex()” example. The “FoundSetUpdater” can also be used to loop over the records as shown below, and again, although there is nothing technically wrong with this approach, it is not the fastest way to handle our task.

fsTable.setSelectedIndex(i);
fsUpdater = databaseManager.getFoundSetUpdater(fsTable);
while (i <= tableShuffleSource) {
	fsUpdater.setColumn('sequence_nr', i);
	i += 1;
	fsUpdater.next();
}

The fastest method for iterating over the records and updating the icon and the “sequence_nr”, turned out to be the “foundset.getRecord()” method. Rather than moving the index record by record across the foundset to update each record, as in the prior two examples, we simply grab the record from the foundset at a specific index. This proved to be the fastest approach to optimizing the shuffle technique. Grab the record using “foundset.getRecord()”, set the values you need, and when the  database manager saves the data, the record will be saved, your foundset will have the latest data, and the controller in the UI will show the latest data on the next refresh. You have the ability to force the save of each record individually using “databaseManager.saveData(rRec)” (not recommended as it will fire often), or the entire modified foundset using “databaseManager.saveData(foundset)”.

while (i <= tableShuffleSource) {	
	rRec = fsTable.getRecord(i);
	rRec.sequence_nr = i;
	i += 1;
}

In a moment we will see the three main routines that are used to handle the shuffling of the records in the table view. Each of these methods was coded using each of the three approaches, and then tested 10 times with 400 records. The averages are shown in the table below. Clearly, the JSRecord approach is the winner in this scenario, but I am certain that the FoundSetUpdater would have won if we were just doing a batch update (the same update to all records).

I purposely did not test the performance of looping through the records using the controller. It is just so wrong an approach, that I will not dignify it by wasting my time.

Now that we know we should use the foundset to loop through records, and even better, work with the record from the foundset, we can take a look at the routines that do the shuffling of the records in the table view. The routines are located in scopes.utils, and can be called from any table view.

The routine uses two scopes.utils variables to keep track of the source and the destination.

/**
 * @type {Number}
 * @private 
 */
var tableShuffleSource = 0;

/**
 * @type {Number}
 * @private 
 */
var tableShuffleDest = 0;

Here is the main shuffle technique. This is called from the icon onClick() event in the table view. It receives the JSEvent from the click, gets the foundset of records from the calling form, and then determines what to do. If it is the initiation of a move sequence, then the one record, the source, needs to be set to red, and all others to green. If, however, there is already a source record identified, then it moves the record to the destination, renumbers all the records, and sets all the icons back to gray. Two helper methods take care of the setting of the icons to red, green and gray.

/**
 * @author: Gary Dotzlaw, 2013-12-15
 * @description: Shuffles the rows in the table view
 * @param {JSEvent} event
 * @return {Boolean}
 * @public
 */
function tableShuffle(event) {
	var iSize = 0,
	    i = 0,
	    /** @type {JSFoundSet<db:/test/t_event>} */
	    fsTable,
	    /** @type {JSRecord<db:/test/t_event>} */
	    rRec,
	    sFormName;

	if (!event) return false;

	// Get the form and its foundset
	sFormName = event.getFormName();
	fsTable = forms[sFormName].foundset;
	if (!fsTable) return false;

	iSize = fsTable.getSize();

	//Do not do anything if only one record present
	if (iSize < 2) return false;

	if (tableShuffleSource === 0) {

		//Set the source to red and all others green
		tableShuffleSource = fsTable.getSelectedIndex();
		tableShuffleGreenRed(fsTable, iSize);

	} else {

		//Set the destination tab
		tableShuffleDest = fsTable.getSelectedIndex();

		//Are the source and destination the same?
		if (tableShuffleSource === tableShuffleDest) {
			//Yes, source and destination are the same, so set everything to gray
			tableShuffleGrey(fsTable, iSize);

		} else {

			if (tableShuffleSource > tableShuffleDest) {

				i = tableShuffleDest;

				// Update using JSRec
				while (i <= tableShuffleSource) {	

					rRec = fsTable.getRecord(i);
					i += 1;
					rRec.sequence_nr = i;
				}

				rRec = fsTable.getRecord(tableShuffleSource);
				rRec.sequence_nr = tableShuffleDest;

			} else {

				i = tableShuffleSource;

				// Update using a Rec
				rRec = fsTable.getRecord(i);
				rRec.sequence_nr = tableShuffleDest + 1;
				i += 1;

				while (i <= tableShuffleDest) {	

					rRec = fsTable.getRecord(i);
					rRec.sequence_nr = i;
					i += 1;
				}
			}
			// Sort the records
			fsTable.sort("sequence_nr asc");

			// Set them all to grey
			tableShuffleGrey(fsTable, iSize);
		}
	}
	forms[sFormName].controller.loadRecords(fsTable);

	return true;
}

This method turns icons either to red, if it is the source record, or green, if an available destination record.

/**
 * @author: Gary Dotzlaw, 2013-12-15
 * @description: Sets the row icons to red or green
 * @param {JSFoundSet<db:/test/t_event>} fsTable - the foundset
 * @param {Number} iSize - the size of the foundset
 * @return {Boolean}
 * @private
 */
function tableShuffleGreenRed(fsTable, iSize) {
	var i = 1,
	    /** @type {JSRecord<db:/test/t_event>} */
	    rRec;

	if (!fsTable || !iSize) return false;

	// Update using a Rec (good place to use FoundSetUpdater)
	while (i <= iSize) {	

		rRec = fsTable.getRecord(i);
		rRec.sequence_icon = scopes.enums.icon_shuffleGreen;
		i += 1;
	}

	// Set the one selected column to red
	rRec = fsTable.getRecord(tableShuffleSource);
	rRec.sequence_icon = scopes.enums.icon_shuffleRed;

	return true;
}

This next method is called when all the icons in each row needs to be set to gray. If you needed to, you could also modify this method to accept a JSEvent, and then call it directly from a form, to refresh all icons to gray.

/**
 * @author: Gary Dotzlaw, 2013-12-15
 * @description: Sets the row icons to grey
 * @param {JSFoundSet<db:/test/t_event>} fsTable - the foundset
 * @param {Number} iSize - the size of the foundset
 * @return {Boolean}
 * @private
 */
function tableShuffleGrey(fsTable, iSize) {
	var i = 1,
	    /** @type {JSRecord<db:/test/t_event>} */
	    rRec,
	    sSourcePK = "",
	    sDestPK = "";

	if (!fsTable || !iSize) return false;

	// If the first record is already the Grey icon, 
	// and there is no source, then exit 
	//(no reset of the shuffle icons is required)
	rRec = fsTable.getRecord(1);
	if (tableShuffleSource === 0 && rRec.sequence_icon === scopes.enums.icon_shuffleGrey) return false;

	// Get the source record pk
	sSourcePK = fsTable.getSelectedRecord().getPKs().pop();

	// Get the destination record pk
	if (tableShuffleDest > 0) sDestPK = fsTable.getRecord(tableShuffleDest).getPKs().pop();

	// Update using a Rec (good place to use FoundSetUpdater)
	while (i <= iSize) {	

		rRec = fsTable.getRecord(i);
		rRec.sequence_nr = i;
		rRec.sequence_icon = scopes.enums.icon_shuffleGrey;
		i += 1;
	}

	// Select the destination record
	if (tableShuffleDest > 0) {

		fsTable.selectRecord(sDestPK);

	} else {

		fsTable.selectRecord(sSourcePK);
	}

	// Clear the scopes.utils variables
	tableShuffleDest = 0;
	tableShuffleSource = 0;

	return true;
}

So that is my old battle tested shuffle technique. It’s so heavily used, that almost every table I create gets a “sequence_nr” and “sequence_icon” column, along with the usual columns “created_by”, “created_date”, “modified_by” and “modified_date”.

That concludes this Servoy tutorial. It was an interesting way to discuss working with the JSFoundSet to iterate through a set of records, and specifically the optimized approach of using the JSRecord. I also hope that for those of you that know all about working with JSFoundSets and JSRecords, the boredom wasn’t too overwhelming, and that the shuffle technique itself was of some interest to you. I hope you enjoyed this article, and I look forward to bringing you more Servoy tutorials in the future.