![]() |
|
Published 2003-06-23 Printer-friendly version
Advertisement
|
The Clarion Reference Library Clarion Tips & Techniques Vol 3
|
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.
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:
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:
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.

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.
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:
|
Before assignment |
After assignment |
||
|
Name |
SeqNo |
Name |
SeqNo |
|
Heineken |
0001 |
Grolsch |
0002 |
|
Sales |
0001.0001 |
Sales |
0002.0001 |
|
Production |
0001.0002 |
Production |
0002.0002 |
|
Grolsch |
0002 |
R&D |
0002.0003 |
|
Sales |
0002.0001 |
Heineken |
0002.0004 |
|
Production |
0002.0002 |
Sales |
0002.0004.0001 |
|
R&D |
0002.0003 |
Production |
0002.0004.0002 |
|
Budweiser |
0003 |
Budweiser |
0003 |
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.
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.
|
Before delete action |
After delete action |
||
|
Name |
SeqNo |
Name |
SeqNo |
|
Heineken |
0001 |
Grolsch |
0002 |
|
Sales |
0001.0001 |
Sales |
0002.0001 |
|
Production |
0001.0002 |
Production |
0002.0002 |
|
Grolsch |
0002 |
R&D |
0002.0003 |
|
Sales |
0002.0001 |
||
|
Production |
0002.0002 |
||
|
R&D |
0002.0003 |
||
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.
|
Before delete action |
After delete action |
||
|
Name |
SeqNo |
Name |
SeqNo |
|
Heineken |
0001 |
Grolsch |
0002 |
|
Sales |
0001.0001 |
Sales |
0002.0001 |
|
Production |
0001.0002 |
Production |
0002.0002 |
|
Grolsch |
0002 |
R&D |
0002.0003 |
|
Sales |
0002.0001 |
Budweiser |
0003 |
|
Production |
0002.0002 |
Sales |
0004 |
|
R&D |
0002.0003 |
Production |
0005 |
|
Budweiser |
0003 |
||
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.
|
Before delete action |
After delete action |
||
|
Name |
SeqNo |
Name |
SeqNo |
|
Grolsch |
0002 |
Grolsch |
0002 |
|
Sales |
0002.0001 |
Sales |
0002.0001 |
|
Production |
0002.0002 |
Production |
0002.0002 |
|
R&D |
0002.0003 |
R&D |
0002.0003 |
|
Heineken |
0002.0004 |
Sales |
0002.0004 |
|
Sales |
0002.0004.0001 |
Production |
0002.0005 |
|
Production |
0002.0004.0002 |
Budweiser |
0003 |
|
Budweiser |
0003 |
||
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.
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.
Ronald van Raaphorst studied Chemical Engineering at the University of Enschede (UT), the Netherlands. But he found programming was more fun than designing a chemical plant, and when a roommate asked him to help start a software company, he found the choice easy to make. Ronald has used Clarion since 1994, beginning with Clarion 3 for DOS. Compad Software, which he co-owns, sells software to a small group of bakeries, he spends a considerable amount of time on the phone helping users, finding (and creating) new bugs, writing manuals, and of course programs. Ronald is in charge of developing Compadıs products, and his colleague is on the road selling.
Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.
Clarion Magazine ISSN 1718-9942
One year: $189
(includes all back issues since '99)
Renewals from $139
Two years: $289
Renewals from $239