A Tree In A Page Loaded Browse: Update And Delete Logic

Posted June 23 2003

Printer-friendly version

In my previous article I showed that the Clarion RelTree template is not designed to contain a flexible number of levels in a tree, and database design is difficult when a single file links to multiple levels. I then presented another solution, which instead uses one file for all levels, and links the levels by the contents of the SeqNo field. To display the tree, I use the standard (page loaded) BrowseClass, and use the SeqNo field to determine the order and level of each record.

In that article I also discussed how to add new records to the tree browse. In this article I will examine record actions more closely.

Record actions

Part 1 dealt mostly with how to displaying the records in tree format; now it's time to look at the Add, Change, and Delete actions in more detail. This will be a discussion of the coding requirements; in Part 3 I'll look at the implementation.

NOTE: In Part 1 I talked about Organization, Department and Subdepartment records. For simplicity, in this article I will call Organization records (or Level 1 records) root records, and records with a higher level number child records. Note that this has nothing to do with file relations, as the root and the child records are stored in the same file. I'll also be talking about child-to-child records, which are what I previously called subdepartments.

Now lets have a closer look at the three record actions:

Inserting records

If there are no records at all when a record is inserted, the first record is primed and the SeqNo field is set to '0001'. If this record is selected and a new record is inserted, the new record automatically becomes a child of the first record. So how do you insert new records which are not children of the first record? That's currently not possible. So, first, you have to find out if the user wants to insert a child record or insert a new root record.

Some possible options are:

• Add a new button for adding a root record
• Use a popup or dropdown menu
• Left click on the Insert button to add a child, right click on the Insert button to add a new root record (not standard Windows behavior).
• Take action based on the currently selected record. If the current record level is 1 or if no record is selected, ask the question; if the record level is 2 or higher, add a child record.

As I hate to add a button for every new function, I'd like to reuse the Insert button, and extend its functionality a little. I therefore will use one Insert button with double functionality, as shown in Fig. 1. The button text changes depending on what the user selects from the dropdown menu.

Figure 1. Insert button with extra functionality

Once you pick an option, you use that option until you select the other one.

For simplicity, records are always inserted at the end of the currently selected level. See Part 1 for the code to prime a SeqNo field according to a parent record.

Changing records

To maintain file integrity, changes in the SeqNo field should be monitored and any changes cascaded. Typically, an end-user can't see or change this field directly, so there are no problems here.

On the other hand, maybe you want to give the end-user the ability to move a child record to another parent record. This way, you can move a department to another department.

Take a look at what happens if you assign a record to another (root or child) record. In comparison to the previous article, I added Budweiser as a third root record. Assume that Heineken is bought by Grolsch, so Heineken ('0001') becomes a child record of Grolsch:

Because Grolsch is the new parent record, which is a level 1 record, I locate the last child on the next (second) level, and add one to its sequence. The result is '0002.0004'. This is basically the same autonumbering action as described in my previous article for inserting a new child record. As I don't want to write code twice, and because I'm some kind of an Architecture Astronaut, I'll just have to reuse some of that code!

After the SeqNo of the Heineken ('0001') record is changed to '0002.0004', you can replace the '0001' part of the SeqNo field of all records where the SeqNo starts with '0001'with '0002.0004'. So the SeqNo field of the Heineken Sales department ('0001.0001') becomes '0002.0004.0001' etc.

In other words, prototyping the process gives:

SeqNo_Old = '0001'
SeqNo_New = '0002.0004' 
LOOP All child records ( SUB(SeqNo, 1, LEN(SeqNo_Old)) = SeqNo_Old )
  SeqNo = SeqNo_New & SUB(SeqNo, LEN(SeqNo_Old)+1, LEN(SeqNo))
  Do the same for child records of this record 
END

Or, filled in for the child sales ('0001.0001'):

LOOP All child records (SUB('0001.0001', 1, LEN('0001')) = '0001'
  SeqNo = '0002.0004' & SUB('0001.0001', LEN('0001')+1, LEN('0001')) equals
  '0002.0004' & SUB('0001.0001', 4 + 1, 5) equals
  '0002.0004' & '.0001'
  Do the same for child records of 'Sales' 
END

Notice that this will work even if Heineken was moved to Grolsch > R&D. In this case, SeqNo_Old = '0001' and SeqNo_New = '0002.0003.0001'.

It would be very nice to drag and drop records, and thus link them to another record. As I first have yet to dive into the technical implementation, I can't promise this functionality yet.

You also may want to give the end-user the ability to move child records up and down. As this functionality typically will take place in the browse window, you need to give the end-user access to it. I will add Move Up and Move Down to the Browse's popup menu..

Moving a record up or down within a level means switching the last four characters, for these characters determine the display order. The length of the SeqNo field (and thus the level displayed) stays the same.

Deleting records

If you delete a record, you have a designer's choice to either delete all child records, or to move all child records one level up. This way, you can delete a level, but keep the children This I would ask the user every time, because it also gives the user the chance to cancel the action.

What are the actions you must take if you want to delete all child records? Assume you want to delete the '0001' (Heineken) record and its children. Table 2 shows the records before and after the delete action.

The 'source' pseudocode can be written as:

SeqNo_Old = '0001'
LOOP All child records ( SUB(SeqNo, 1, LEN(SeqNo_Old)) = SeqNo_Old )
Delete record
END

It's a little harder if you only want to delete the root record '0001', but keep the child records, as shown in Table 3.

The Heineken Sales and Production records have moved up a level from 2 to 1. But you can't simply remove the first four characters, because the SeqNo field is unique. So you need to move the child records up a level, then autonumber them on that level, and update all of that record's children.

To visualize this, take the result of Table 1, where Heineken is a child of Grolsch, and imagine you still want to delete the Heineken record. Table 4 shows the records before and after the delete action.

In this example, the SeqNo of the Heineken Sales record is changed from '0002.0004.0001' to '0002.0004'. In this case, it looks like I have removed only the last characters, but that is a coincidence and may be a bad example.

Here's some source for deleting a record.

SeqNo_Deleted = '0002.0004'
SeqNo_Parent = SUB(SeqNo_Deleted,1,LEN(SeqNo_Deleted)- |
CHOOSE(LEN(SeqNo_Deleted)=4, 4, 5))
LOOP All child records ( SUB(SeqNo, 1, LEN(SeqNo_Deleted)) = SeqNo_Deleted)
Autonumber, based on parent (SeqNo_Parent), and add this record
Assign child records to new SeqNo
END

And verify the Parent calculation:

SeqNo_Deleted = '0002.0004'
SeqNo_Parent = SUB('0002.0004',1,LEN('0002.0004')- |
CHOOSE(LEN('0002.0004')=4, 4, 5)) equals
SUB('0002.0004', 1, 9 - CHOOSE(9 = 4, 4, 5)) equals
SUB('0002.0004', 1, 9 - 5) equals
'0002'

Notice the CHOOSE function in the SeqNo_Parent calculation. If you are deleting a root record, its length equals four characters, and the result is zero. Thus, SeqNo_Parent becomes ''.

If you are deleting a child record, the condition of the CHOOSE statement equals false, the result of the CHOOSE statement equals five, and thus the last five characters (incl. numbers and separating period) are removed.

Conclusion

What looked like a simple and elegant solution for a tree in a page loaded browse box, becomes a little more complex if you take a look at the record actions you will want to do.

Because of the recursive nature of the actions (loop (child)records and their children), you can't simply write a template and add some code to embeds. Though it is possible to code recursive loops as a single loop and mess with variables, I like the clean code of a single class method calling itself. So my next article in this series will be about designing and implementing a class to handle these requirements.

Article comments

Post a comment

You must be logged on to post comments.