"Who ya gonna call?" -- Providing multiple selection functions for grid controls
The Hayward LightSpeed™ MultiSelect Grid method comes to the rescue.
Introduction
This article discusses an improvement for the Grid control, called the Hayward LightSpeed MultiSelect Grid method, which provides a new way to offer multiselect capability for your applications.
Larger tables.... More demand for multiselect... Who ya gonna call?
Whenever it happens, you get that sinking feeling in your stomach. You're developing an application, and you have a table to display in your application. Your customer says "We're going to need to select multiple records in that table". Suddenly, you realize it is a big table. You've got some fiendish issues to resolve, and your time-table just blew up. Do you have a ghost of a chance to complete the job on time?
Which control will you use? You can choose either a listbox or a grid control. Of course, you will yearn to use the Grid Control, because it is designed to display an entire table quickly. But, the client has thrown you a dastardly twist -- they want you to provide the user with the ability to select multiple records. Oops, the grid control will let you click on individual records only. So, you start thinking about using the listbox instead.
The Listbox Control does include a built-in multiselect feature. All you have to do is turn it on, like this:
MyList.MultiSelect = .T.
The next two figures provide an example of the speed difference between the Listbox and the Grid. The source table has one and a half million records. On each test, I grabbed the vertical scroll bar and pulled it down to record number 600,000. On the Listbox, it took two minutes and forty seconds before it displayed the record. On the Grid, it was immediate -- when I let go of the mouse button, it displayed the record.
Figure 1 - Test of a Listbox with 1.5 million records. Scrolling 600,000 records took 2 minutes and 40 seconds.
Figure 2 - Test of a Grid with 1.5 million records. Scrolling 600,000 records took one second.
Users turn into desperate zombies when they have to wait for minutes before they can look at another record, and they will not hesitate to tell you how beastly you are to make them wait such unworldly long times.
Listboxes aren't designed for large tables. The way to contain this plodding poltergeist is obviously going to be the Grid control. But, we have to solve the MultiSelect problem.
Listbox is too slow. Grid has no multiselect. Who ya gonna call? Not Microsoft
You can't call Microsoft to get multiselect in the Grid. In the FOOTNOTES section at the end of this article, two of the top decision-makers at Microsoft (Ken Levy and Alan Griver) are quoted as saying they are "not going to be changing the grid control". So, since there are no plans at Microsoft to change the Grid control, we have to try and find another way to make Multiselect come to life in the Grid.
Everyone seems to like the way Microsoft implemented multiselect in Windows Explorer (and in other Microsoft applications, such as Excel). When you click a filename it gets highlighted. When you hold down the SHIFT key, you can highlight the first filename in a group of files, and then when you click another filename all the files in between the first and last get highlighted. And, if you hold down the CTRL key, you can add additional filenames to the group being selected. This is called "Explorer-style multiselect".
Selecting multiple filenames with "Explorer-style multiselect", using the SHIFT and CTRL keys, is very efficient and fairly intuitive. People have become habituated to this style of multiselect. Users like to use this style when they are running other applications.
But, up to now, if a multiselect function is needed in a Grid control, the developer has had two choices, neither of which are optimal.
Choice 1 -- THE ARRAY ghost. Create an array the size of the table, with each element containing a logical true or false. When the user selects a record, switch the element to true. This is like having a ghost for each record, holding the selection status. A one-dimensional array is sufficient until you want to use an index, and then you need two elements for each record -- one element to hold the status (on or off), and the other element to hold the record number (since the records are out of sequence when an index is set). There is one advantage to the nasty ARRAY ghost: You can use this method when you do not have access to the table structure, and thereby blocked from using the ADDED COLUMN approach. However, an array is limited to 65,000 elements, so you have to set up several arrays to get to larger tables, plus you have the accompanying complexity trying to track them all correctly.
Choice 2 -- THE ADDED COLUMN demon. Create an additional field in the table, with each field containing a logical true or false. When the user selects a record, replace the value of the field to true. This is a real devil, because now you have to increase the size of the table, thereby slowing down even more the movement of data across the network connections. And, when you want to find out which records are selected, you have to read the whole table again. Another plodding poltergeist. And, of course, you can't use this approach if you don't have access to the table structure.
How to add multiselect functions to a grid
I was able to develop another approach which provides everything you want. This method simulates the Windows Explorer style of multiselect, and is one-fourth the size of the memory required for the smallest ARRAY approach (even when an index is used), and can be used when the table is not accessible, and does not require an ADDED COLUMN, and is extremely fast.
I'm calling my approach the Hayward LightSpeed™ MultiSelect Grid method. The precise details of my method are the subject of a patent application, so I am going to describe something like it that you can use to achieve similar results, although even though it is fast, it is not as fast as mine. If you want to benefit from my actual method (which can be used for tables up to 16.5 million records, and also can be ported to other languages), you can contact me through Hayward InterAction website. My description will assume that you are fairly knowledgeable about Visual FoxPro programming. (Experts should understand that some of my descriptions below are meant to sketch concepts and is intentionally omitting a number of efficiencies -- I'm letting the programmer do some work here.)
STEP 1 -- Create a text file which has the same number of empty spaces as the size of your table. For example, a table with 100,000 records would require 100,000 empty spaces. Do something like:
lnchars=StrToFile(Replicate(' ',100000),'TEST',.f.)
STEP 2 -- Add some code in the MouseDown method of the text box in each column of the Grid, that determines whether a SHIFT or CTRL key was pressed when the mouse was clicked. Depending on the results of that code, store an 'X' at the position of the record number in the variable lcMultiSelect. For example, if the mouse clicked on the third record, place an 'X' in the string at the third position.
STEP 3 -- Add some code to the DynamicBackColor property of one of the columns in the Grid. Do something like:
Grid1.Column1.DynamicBackColor="Iif(Substr(lcMultiSelect,Recno(),1)='X',; Rgb(192,192,192),Rgb(255,255,255))"
STEP 4 -- create a numeric variable to hold a record number (let's say lnFirstClick). Store a zero in this variable unless a SHIFT key is pressed during a mouse click, then store the record number of that record. Then, when the next record is clicked, you can mark all the records from lnFirstClick to the record you just clicked.
STEP 5 -- Create a method to determine whether the first SHIFT/click combination is physically previous to the second SHIFT/click, or subsequent to it. This is important in the case of the presence of an index, since the sequence of the record numbers won't provide the direction when an index has been set. I leave it to the imagination of the programmer to create the optimal approach here.
When you're done, you should see something like this:
Figure 3 - the Hayward LightSpeed™ MultiSelect Grid method (patent pending) in action. It simulates the Microsoft Windows Explorer multiselect style.
Conclusion
This article discussed an improvement for the Grid control, which provides a new way to offer multiselect capability for your applications. Have fun developing it for your own applications. If you want to save time and get the fastest multiselect grid method available, and contact me and mention this article, you can enjoy a 50% discount on the price of the Hayward LightSpeed™ MultiSelect Grid utility.
Footnotes - Microsoft has no plans to improve VFP Grid control
Excerpts from: Visual FoxPro DevCon 2005 Interview with Alan Griver and Ken Levy http://foxcentral.net/microsoft/VFPDevCon2005_Interview_AlanGriver_KenLevy.htm
by David Stevenson, FoxTalk editor (published August 2, 2005)
Excerpt 1 from Ken Levy: "....If we were to spend a whole lot of time, for instance, on the grid control, it could take a huge amount of time in planning, development and testing. It’s not that easy to just crack open something and make changes."
Excerpt 2 from Ken Levy: "....Just like the whole grid thing, if someone wanted a new column type feature for the grid, we probably would say that’s not on our priority list."
Excerpt from Alan Griver: "....we have a pillar for Sedna. That pillar is interoperability. The minute you say that, it means you’re not going to be changing the grid control."
