![]() |
|
Published 2003-05-30 Printer-friendly version
Advertisement
|
The Clarion Reference Library Programming Objects In Clarion
|
Clarion has a template for building a tree list control, called the RelationTree. One of the disadvantages of this control is that the maximum tree level is fixed by the number of files you use in it.
In this article, I will outline the very simple, basic idea for a non-level-limited, page loaded tree, using a standard browse control. I will also describe the Clarion basics for displaying tree levels in a browsebox, which can be applied to a hand-coded list box too.
You can use a tree when the application has records, which can be shown in a certain logical order from a user point of view. Take for example the organization chart in Figure 1:

Figure 1 shows a corporation with three departments. Note that I'm not referring to employees in this organization, but to departments. The employees will come later.
To model this using the ABC tree template, you need to create a dictionary which contains at least two files: One for all corporations, and one for all departments (see Figure 2).

On the right side I have drawn the dictionary relationship. By the way, I use Microsoft Visio to draw my dictionaries, even before I start working on the dictionary itself.
A corporation record has zero or more departments (1:N relation), and a department record belongs to exactly one corporation record (N:1 relation). This relationship is exactly the same as an order-orderline relationship: you can't add an orderline (item) without having added an order (customer, delivery date etc) first.
But of course you can add multiple corporations, each with their own departments. And each corporation does not have to have exactly three departments.
In a window, the tree looks like Figure 3.

On the left is the tree, on the right are the records for both
files. Note that the ID field is a primary, autonumber
key field. The CorpID field is a foreign key field,
and refers to the ID field in the corporation record.
Note that because both corporations have a Sales department, I add
this record twice, so the department name can't be a unique
key.
Now this all looks like fun! With the RelationTree template you define a primary file (corp) a secondary file (department) and two update procedures, and there you go...
So you have your application ready and shipped. Then your client calls and says, hey, I want to enter a customer which has not only departments, but subdepartments too (1:N relation). And a subdepartment is linked to a department. In fact, a department can have zero or more subdepartments.
So, you go back to the dictionary, add a subdepartment file, and link it to the department. You also go back to the RelationTree control, and add the subdepartment file here too, as shown in Figure 4.

If the client wants yet one more level, you have to go back to the drawing table again. Clearly, when you provide something as flexible as an org chart, with a variable number of levels, you can't use the RelationTree control template, unless you're absolutely sure the maximum depth is fixed.
Even if the fixed levels are no problem, the RelationTree template design has another big disadvantage, which I will explain using an example.
Imagine you want to add employees. An employee works in an organization, and you want to link an employee to a department. As an employee can only work in one department, and a department can employ multiple employees, you link the employee to a deparment, as in Figure 5.

But, employees can also work in a subdepartment! So you need
another link in the employee file to the subdepartment. Now how do
you know if the employee works in a department or in a
subdepartment? The employee file now needs two fields, one to
reference to a department (EMP:DepartmentID), and one
to reference to a subdepartment (EMP:SubDepartmentID),
as in Figure 6. You may also need to write code to prevent an
employee from being shown as working in a department and a
subdepartment at the same time (The business rule for the update
employee is that either the EMP:DepartmentID field or
the EMP:SubDepartmentID cannot be zero, and that both
fields can't be unequal to zero at the same time.)

The same story goes for invoices. You can send an invoice to a company (corp), to a department or to a subdepartment. You end up having links all over the place and lots of extra code to prevent double relations.
In the sections above I have shown that the standard relation tree control is not particularly flexible, and that the maximum number of levels is restricted to the number of files you have defined in the database design. So, if you want to avoid these issues, you can define the requirements as:
Before I can go into details about the solution, I need to show how a listbox is instructed to display tree levels. Let's go back to the basics.
A listbox control uses a queue to display its data. If you populate a browse box control, you can go to the list box formatter and add the database fields. The template generates a local queue in the procedure, with fields like your database fields. The browse class fetches records and adds them to the queue. So the queue contains content, but the queue itself knows almost nothing about how to display the content.
If your database contains a LONG field for example,
which is actually a date displayed using the @D6-
picture, the queue just contains a LONG field. In the
listbox formatter, you set the picture, which can be
@D6-, @D01 or @D02.
These and other column or field settings are translated by the
IDE to a format string. A Clarion listbox gets information about
how to display the queue fields by interpreting that string. The
format string is assigned to a listbox by either setting
PROP:Format or by using the FORMAT
property on a LIST control in the window editor.
Window WINDOW('No title'),AT(,,100,200)
LIST,AT(5,5,90,190),USE(?List),|
FORMAT('52L~ID~@D6-@'),FROM(Queue:Browse)
END
For a date column without any other settings, the minimum format string is something like this:
52L~Date~@D6-@
This string tells the list control that the width of the column
is 52 (pixels or DLUs), the title is "Date" and the
display picture is @D6-.
So, what about tree levels? Figure 7 shows what happens if you
use a Name field, without a tree.

Notice that the Tree property is not checked,
there's no tree, and the format string is
80L(2)|M~Name~@S39@. Now the generated queue is:
Queue:Browse:1 QUEUE
ORG:ID LIKE(ORG:ID)
ORG:Name LIKE(ORG:Name)
Mark BYTE
ViewPosition STRING(1024)
END
You see that the queue fields are named exactly after the field
names in the dictionary. But remember, these are queue fields,
which do not yet have any relationship with the file being browsed!
It's the underlying BrowseClass which copies the file
buffer fields to the queue fields.
Notice that while I have only added two fields in the window
formatter, two more fields are added to the queue. The
Mark field is a legacy field originally designed for
tagging purposes. The ViewPosition is used by the
BrowseClass as a unique reference to a record in the
view.
Now, go to the listbox formatter, and check the Tree property. You'll see a tree emerge in the listbox, for that field. And, most important, you'll see your format string change too, as shown in Figure 8..

Notice the "T". The listbox control in the runtime
library interprets the T as: "Hey, on this field I
need to display tree nodes."
Now go to the source and look at the generated queue:
Queue:Browse:1 QUEUE
ORG:ID LIKE(ORG:ID)
ORG:Name LIKE(ORG:Name)
ORG:Name_Level LONG !Tree level
Mark BYTE
ViewPosition STRING(1024)
END
Because the listbox control needs to know the tree level of the
record, it adds a long field in the queue, and adds
_Level to the name of the field.
The listbox control now knows:
T) it should display
nodesBut where do you assign a value to the queue's
Name_level field? Notice that it's not defined as a
LIKE field in the database, so there's no evidence of
a direct link being made by the templates for the
BrowseClass. There's no template prompt in the
BrowseClass for this level field. So you have to go
back to an embed in the generated code.
Remember that the BrowseClass fetches a record and
copies the used fields from the file buffer to the queue? This is
actually done by the BrowseClass.SetQueueRecord
method. In the BrowseClass embed, SetQueueRecord, after
parent call, you need to set the tree level queue field.
Here's the source:
BRW1.SetQueueRecord PROCEDURE ! Start of "Browser Method Data Section" ! [Priority 5000] ! End of "Browser Method Data Section" CODE ! Start of "Browser Method Code Section" ! [Priority 1300] ! Parent Call PARENT.SetQueueRecord ! [Priority 5500] Queue:Browse:1.ORG:Name_Level = 1 ! Set all levels to 1 ! End of "Browser Method Code Section"
Now of course you won't set all levels to 1, but to the level needed. So it's time to look at the final page loaded, browse tree solution.
In the org chart described earlier, you have seen that modeling different levels in separate files is not an option. You want the content (all levels of all corporations) in a single file. You also want this because then you can easily link a single file to other files (see Figure 9).

Here, the organization file contains corporation, department and subdepartment records (and more levels if needed).
Now you have two problems:
To solve this, you add a single STRING (or
CSTRING) field to the organization record. Nowadays I
prefer CSTRING fields, because you can concatenate
them without using the CLIP() function.
As I always add a unique ID, I have a minimum record layout like this:
Organization FILE,PRE(ORG) ID ULONG Name STRING(20) SeqNo CSTRING(255) END
And I define three keys:
KEY_ID (Primary key with autonumber) KEY_Name (Non-Unique key, excluding empy keys) KEY_SeqNo (Non-Unique key, excluding empy keys)
Now, lets go back to the Grolsch-Heineken example, as shown in Figure 10.

Notice that the ID fields don't match the example
given earlier, but as the ID field is just a unique
field which has nothing to do with the tree structure itself, you
can ignore that completely. Also notice the special contents of the
SeqNo field. These contents provide all that's
necessary to maintain the order in the file, and to calculate each
level.
Typically, an end-user won't see the SeqNo field
content.
Notice also that corporate levels use only four characters: '0001' and '0002'. The department levels add five characters to that (Grolsch.Sales = '0002.0001'). How de you calculate each level? The length of the string gives the level in the tree:
Queue:Browse:1.ORG:Name_Level = INT(LEN(ORG:SeqNo) / 5) + 1
I use the INT() function because the queue field is
a long, which can only contain integer numbers. This way, the
"corporate" level is INT(LEN('0001') / 5) + 1 = 1 and
a "department" level is INT(LEN('0001.0001') / 5) + 1 =
2.
An advantage of this solution is that when I want to process all departments of 'Grolsch', I set a filter to process all records where the first four characters are '0002', because they all are departments or sub(sub(sub))departments of 'Grolsch'.
Unfortunately, there are some disadvantages too:
If you choose to use four characters (and a fifth for the '.',
which is only added for readability by the way) you can add a
maximum of 9999 organization records. If you use five characters,
you can add 99999 records (you could go hexadecimal to get more
levels). In return for this maximum restriction, the tree level is
highly flexible, depending on the length of the SeqNo
field:
With 255 characters (256-1 for '<0>', because
it's a CSTRING), you can go 255/5 = 51 levels deep,
but you still link other files, like employees, to a single file in
the dictionary.
After a long story and a lot of theory, it's time for implementation.
First define a file, with a SeqNo field (or
whatever you call it), and create an ascending key for this
field.
Create a procedure with a browse box and populate the name field. Be sure to check the Tree property on the Appearance tab in the listbox formatter.
Now go to the embeds, and check the name of the
Name_Level field in the generated queue. Normally,
this is Queue:Browse.Name_Level.
Scroll down or find the SetQueueRecord method for
the browseclass. After the parent call, add the line:
Queue:Browse.Name_Level = INT(LEN(ORG:SeqNo) / 5) + 1
When you add a record, it should appear below the currently
selected record, so you need to pass the current SeqNo
field (pass by value) to the update procedure.
Here comes a little problem: The
BrowseUpdateControls template generates different code
for a standard update call, or a call to a procedure when passing a
parameter.
If you don't use a parameter, the BrowseClass.Ask
method is called internally, and here, a new record is primed
(autonumbered key fields are set).
If you use a parameter to call the update, the
BrowseClass.Ask method is not called, and autonumbered
key fields are not set. In fact, the fields of the new record are
not even cleared.
And you do want to pass a parameter, because new records should
know what their parent is in the tree. In fact, you want to prime
the ORD:SeqNo, based on the contents of
ORD:SeqNo from the parent.
You need to write some code, at the ?Insert, accepted embed, after generated code:
OF ?Insert
ThisWindow.Update
! Start of "Control Event Handling"
! [Priority 6000]
SEQNo = ORG:SeqNo
IF Access:Organization.PrimeRecord() <> LEVEL:Benign
CYCLE
END
Req = InsertRecord
GlobalRequest = Req
UpdateOrganization(SeqNo)
Response = Globalresponse
BRW1.ResetFromAsk(Req, Response)
! End of "Control Event Handling"
This code is taken more or less from the
BrowseClass.Ask method (\libsrc\abbrowse.clw), to
prime autonumbered keys. The ResetFromAsk method is PROTECTED, so
normally you can't use it. Unfortunately you must go to
libsrc\abbrowse.inc and remove the PROTECTED attribute. Also,
because the BrowseClass.ResetFromAsk method takes
variables passed by address, you need to define these two variables
locally. You also want to store the current (parent)
ORD:SeqNo field temporarily:
ThisWindow.TakeAccepted PROCEDURE ReturnValue BYTE,AUTO ! Start of "WindowManager Method Data Section" ! [Priority 3500] Req BYTE,AUTO Response BYTE,AUTO SeqNo LIKE(ORG:SeqNo)
Now that you have written the embedded code, you configure the
BrowseUpdateButtons template to call the update
procedure. Go to the browse window in the window formatter, add the
update buttons, and go to the Actions tab of the update buttons, as
shown in Figures 11 and 12. Do not use the Update
Procedure prompt; instead, select Call a
Procedure.


Select the requested file action (Insert/Change/Delete) and fill out the prompts as shown. Unfortunately, you have to do this for all three update buttons separately.
Notice that you only need to pass ORD:SeqNo contents if you insert a record. But because the update procedure expects a string to be passed, you set the parameters for the change and delete action to '' (empty string).
Now, the browse is ready.
As the update procedure receives a string, you need to change the prototype. Open the update procedure and type at the prototype prompt:
(STRING SeqNo)
Copy this to the parameter prompt too, so they both contain the same text.
In the update procedure go to the WindowManager, Primefields embed and add this code:
ThisWindow.PrimeFields PROCEDURE
! Start of "WindowManager Method Data Section"
! [Priority 5000]
NewSeqNo ULONG(1)
CurSeqNo LIKE(ORG:SeqNo),AUTO
SaveID USHORT,AUTO
! End of "WindowManager Method Data Section"
CODE
! Start of "WindowManager Method Executable Code Section"
! [Priority 3800]
CurSeqNo = ORG:SeqNo
SaveID = ACCESS:Organization.SaveFile()
ORG:SeqNo = ORG:SeqNo &'.9999'
SET(ORG:KEY_SeqNo, ORG:KEY_SeqNo)
IF ACCESS:Organization.Previous() = LEVEL:Benign AND |
SUB(ORG:SeqNo,1,LEN(CurSeqNo)) = CurSeqNo THEN
IF LEN(ORG:SeqNo) <> LEN(SeqNo) THEN
NewSeqNo = SUB(ORG:SeqNo, LEN(ORG:SeqNo)-3,4) + 1
END
END
ACCESS:Organization.RestoreFile(SaveID)
ORG:SeqNo = CurSeqNo & CHOOSE(CurSeqNo='','','.') |
& FORMAT(NewSeqNo,@N04)
Basically this code defines your own autonumber method. The
SaveFile() and RestoreFile() calls are
needed because a new record has already been added to the file
because of the autonumber on ORG:ID.
The RelationTree 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.
The solution described above is more flexible, but requires a lot of embedded code. The solution is not perfect yet, because if you delete a level, sublevels must be modified or be deleted too. In my next article, I will show you how to delete records, and write a template, based on the code I've described here.
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.
|
Posted on Friday, May 30, 2003 by Paul Konyk Great article.
Posted on Saturday, May 31, 2003 by Ronald van Raaphorst I guess that is a mistake then. Remove either Protected and/or Private, and it should compile.
Posted on Saturday, May 31, 2003 by David Harms My mistake - I actually started to correct that in the text before publication, but somehow got sidetracked and didn't post the updated doc.
Posted on Tuesday, June 03, 2003 by David Podger If you wait long enough, an answer will eventually arrive. "A Tree in One File", published 1997-09-01 in COL promised a solution (to be written by the then editor) but the requirement that the file be page loaded made the task too difficult. Now you have provided an elegant, economic solution. Many thanks.
Posted on Tuesday, June 03, 2003 by Bruce Johnson "These Protected/Private keywords are there so that SV can change these methods if the needs arises." - this is slightly inaccurate. Private does perform this function, but Protected does not. Since protected methods and properties can be used in derived classes they don't give SV "protection" to change stuff. All they do is limit where you can embed code. IMO in clarion, "protected" has an extremely limited usefulness, and should be avoioded. Posted on Saturday, June 07, 2003 by James Cooke Ronald - very timely article! Thanks, you saved me a buncha time! Posted on Monday, August 29, 2005 by Stephen Bottomley To overcome the Protected compile message without altering the base classes.
Posted on Tuesday, August 30, 2005 by Stephen Bottomley No mess fix for updating.
|
To add a comment to this article you must log in.
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