Wednesday, December 8, 2010

The Importance of Data and Data Order

I received a refresher course on the importance of Data Order recently which also reminded me of a central truth about data that I came up with years ago. I thought it worthwhile to share the experience.

Here is the scenario: I have a cursor that I build from scratch inserting records from various tables during a long process. The last process in building the table is to "articulate" some credits and pushing the articulated credits to a 4th level node. The end result is a table that is then presented to the user in a dbi treeview control. The end result is a college degree plan with subject groups (requirements) and credit records which meet the subject group requirement.

Some colleges want to articulate transfer credits and other nontraditional credits into their own courses. That is what I am calling articulation.

In the image below is the end result in the treeview. The articulating courses are in bold with their respective articulated courses beneath them; articulated course rows also have a light green background.



On the form that has the treeview is a command button called Import New. When clicked, credit records that have been added since the degree plan was last saved are inserted into the underlying treeview cursor in the correct position. The image below shows the treeview after importing new but not articulating any imported new credits. Newly imported records have a forecolor of blue and it's icon has a yellow backcolor.



This has worked fine and the users have the ability to manually articulate credit records if they so desire within the treeview control.

Wouldn't it be great to automatically articulate credit records during the import new process. I thought so and so did the users; that means less manual articulation. Below are the initial results.



This is an absolute mess!!! There are credit records that are children of other credit records and the articulation records have lost their children. I think there might be a problem with the order. But why, this has always worked before. I didn't make any changes to the Import New or to the Articulation processes.

Here is the code I found at the end of the processing where I create an index on the cursor before filling the treeview control with the data.



I can see why the order got mixed up now. If plImportNew is TRUE, the index being built just doesn't cut it. Before I ever started doing articulation during processing this all made sense. When I stated doing articulation during processing I had to create a crazy looking index to get the order correct for display. I never messed with the index if plImportNew was TRUE because I didn't allow articulation during Import New.

So, I decided that in order to fix this I would take the IF statement out. I could use the same index all the time. Below is what I got for this simple and wonderful solution.



Not quite what I was hoping for. In some ways it is better; it's not as mixed up as it was and some records actually look correct, but there are still misplaced records and the old records that were correct now look misplaced. What is going on? My index looks correct.

That's when it hit me. Look at the data! I have this saying, "The data never lies". In order to get to the heart of the matter, look to the data and it will show you the truth. So I did, and the data showed me what the problem was. I have a field called nParent which holds the recno() of the parent node. This value held the correct value upon the last save. But after importing new records and inserting them into the cursor the parent's recno changed. The child's nParent value held the old value, not the new value. So, when the index was created to set the correct display order the records ended up out of order because of the wrong values.

In order to correct this problem I had to replace the nParent value of the child records after the Import New process but before the Articulation process. This is only done for records that are "articulated" records and are 4th level nodes in the treeview. The code is shown below.



Now the results look as expected and are shown below.



The data is now in the order that it should be. The index is working correctly. In order to get there, I had to look at the data. The data tells the truth. Now I am very close to having a program that is ready for release. We all know the importance of the order of data; it can give new meaning to the displayed results just by changing the order of the records. When the displayed results are not quite what you had hoped for, try looking at the raw data to see if the answer lies there.

Note:
Before I start getting messages about how I shouldn't use recno() for field values or for indexing let me say this is an exception. I have created a cursor to hold data. The data is actually stored in a memo field when the degree plan is saved. The user has the ability to drag and drop nodes all over the place within the treeview control. I didn't want to have to mess with record order in a normal table. By saving to the memo field row by row the order in the treeview is preserved and it is easy to reload the treeview form the memo field.