A Tree In A Page Loaded Browse

by Ronald van Raaphorst

Published 2003-05-30    Printer-friendly version

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.

Standard RelationTree implementation

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. A simple organization chart

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).

Figure 2. Org chart and the database design

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.

Figure 3. RelationTree control and the database record contents

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...

Maintenance on RelationTree design

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.

Figure 4. Extending the org chart

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.

Figure 5. Database design: Employees added to departments

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.)

Figure 6. Database design: Employees added to departments and subdepartments

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.

Requirements

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:

1. The number of levels is not tied to files defined in the design.
2. There must be only one relationship when linking a tree record to another file.
3. The file should be page loaded, preferably using a standard browse box (and browse class).

Tree level basics

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.

Figure 7. List box formatter settings without tree levels

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..

Figure 8. List box formatter settings with tree levels

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:

• from the format string (T) it should display nodes
• that the tree level is stored in the queue field directly following the field itself.

But 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.

A maintainable 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).

Figure 9. Merging all organization levels in one file

Here, the organization file contains corporation, department and subdepartment records (and more levels if needed).

Now you have two problems:

1. An organization record must know its level
2. When displaying the records, a the right order must be preserved: A department record should be displayed below it's corporation record.

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.

Figure 10. Organization content

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.

The Browse

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.

Figure 11. Configuring BrowseUpdateButtons for Insert

Figure 12. Configuring BrowseUpdateButtons for Change and Delete

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.

The update procedure

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.

Conclusion

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.

Download the source

---

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.

Printer-friendly version

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