
Servoy Tutorial Photo Credit:
Pericomart via Compfight
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.
1 2 3 4 5 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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.
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 | 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)”.
1 2 3 4 5 | 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).
Method | ms |
---|---|
JSFoundSet | 266 |
FoundSetUpdater | 243 |
JSRecord | 67 |
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.
1 2 3 4 5 6 7 8 9 10 11 | /** * @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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | /** * @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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | /** * @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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | /** * @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.
Please subscribe to receive notification when new articles are published, and share your comments below.
Harjo Kompagnie
Dec 23, 2013 -
Hi Gary,
great tutorial, as always! 😉
I do not quite understand where those icons come from: scopes.enums.icon_shuffleGrey?
we have a similar approach, but the trouble starts, when you doing this inside a (Servoy) transaction: databaseManager.autoSave = false.
than you can’t use fs.sort() because that will do a save!
So you have to use a foundset in-memory sort!
And while doing that, in web-client this will give painting issues:
Look here: https://support.servoy.com/browse/SVY-5627 (point 2)
Gary Dotzlaw
Dec 23, 2013 -
Thanks Harjo! I appreciate the kind words.
The icon comes from my scopes.enums and is simply a variable that points to my media:
var icon_shuffleGrey = 'points to the image media url'; (cant seem to put the actual url here)
Yes, if you are doing database transactions, then it is more complicated because you cannot do a sort with unsaved records. I typically do not permit the user to sort rows in tables until they have saved the records (not in Edit mode). I show a nice dialog that informs the user they need to save the records first, and exit edit mode, before they can begin sorting records. Easy to explain records cannot be sorted until changes/new records are committed.
I only work with the web client and have not run into any painting issues, so it must be related to the in memory sort you are doing.
Steve H
Oct 13, 2014 -
Hi Gary,
Thanks for the great explanation above.
I have one question (not having tried to implement this yet so it may be a dumb question!):
When the user first clicks the record and the icons turn green and red, will this not cause a data broadcast and all other users viewing the same foundset get the effect of having their icons change as well?
Thanks
Steve
Gary Dotzlaw
Oct 20, 2014 -
Yes it would broadcast the color change to all other users as well. Most of my clients these days do not use the color change, and keep all icons gray. There are a few reasons for this; changing the icons takes time, and there is a performance hit to refresh the icons that is noticeable on larger lists; the color change is not really necessary for the shuffle function to work as the user can just click on the one to move and click on the destination.
If you did want to use the color change in the icon with multiple users, then you have to tie the table view to a user specific record, that holds the icon colors for the data rows, similar to what we do these days with table view persistence using an object (restore the users column order, column size, sort). Honestly, not sure it is worth the performance hit just to do the icon colors.