Related-Table-Data On ABC BROWSES

First, on the topic of related-table-data displaying on ABC Browses and Forms, we'll explain how it's possible to display local variables containing concatenated field data from related tables into an ABC browse and have the browse still sort, search and filter the columns containing them as if they were data fields from the browse VIEW.



Below, using a combination of pictures and words we'll outline how that's done using a standard ABC browse enhanced with CHT HandyMarkerBrowse -- though what we're showing here works just the same using CHT ExplorerBrowse or CHT LocatorOverRideControl.

IMPORTANT: Clarion VIEW Structures - What They Do

A Clarion VIEW structure is generated into your procedure code when you drop an ABC browse template on any ABC window and populate fields into the browse, or add "Hot Fields" for the purpose of concatenating to locals.

Below is an image of just such a view generated for the BrowseEnrollments() procedure in our SQLite demo HNDSCHOOL.APP, a demo we constructed based on the modified SCHOOL.DCT and SCHOOL.APP demo provided by SoftVelocity. Its also important to note that Clarion views are ISAM/SQL agnostic. They're meant to work the same way, at least from the developer's perspective, on an ISAM file as on an SQL table.

Quite a number of years ago, when CHT first introduced browse column sorting on ISAM data columns without file keys, Clarion developers wondered how we were doing this! Well that little miracle was based on the design incorporated into Clarion's VIEW structure from the outset. Views may have a PROP:ORDER property assigned to them as we explained above. That means the view SORTs the incoming data-set according to the order property being applied whether the source file or table has a key on the designated sort-order column(s) or not.
At the time, well before we all began to migrate our applications to use SQL, the concept of keyless, indexless order columns had long been a given! But to Clarion guys, familiar only with ISAM files, it was a revelation. As it turns out, Clarion's file drivers were sorting this ISAM data-set internally, and on the fly, whenever a keyless column was assigned to PROP:ORDER an incoming view-based data set. It wasn't necessarily always blazingly fast, but on smallish, local data tables it changed the Clarion browse design paradigm for us at CHT dramatically.
Clarion VIEWs are actually based on the concept of SQL VIEWs which similarly treat multiple, related tables as a single, navigable flat file. Bruce Barrington, the father of several early Clarion versions, wrote about this extensively when the concept was first incorporated into Clarion file drivers and made to apply to ISAM as well as SQL in a transparent-to-the-developer kind of way.

CHT Development staff realized - and leveraged with several ABC browse extensions - the magic of Clarion VIEWs long before other 3rd-party toolmakers did, even well before Clarion as SoftVelocity introduced the concept of multi-column browse sorting into their base browse template.


As explained in the word-balloon on the image, a Clarion VIEW is a structure that describes a set of table interrelationships. This structure can be OPENed and CLOSEd like a file and traversed UP and DOWN respectively with PREVIOUS() and NEXT().

Clarion VIEW structures also have a PROP:FILTER property that accepts an expression which causes it to logically select certain records from the tables while rejecting others. There is also a PROP:ORDER property which accepts a direction setting as well as a column name or column names, which determine the order in which the view is processed moving either forward or backward through the source table(s).

To your code, as a VIEW is traversed, it looks like a flat file. The fields come in when you NEXT() through the view as if they were coming from one table instead of from mutiple tables. The built-in wonders of Clarion file driver code handles all of the details of keeping the various files or tables underlying the view in synch, assuming, of course, that they've been properly joined as all good, normalized data should be.

In the next screen shot, is an SQL SELECT (issued from Clarion's SQLite Driver) for the same Clarion VIEW structure pictured above. Note that the image is somewhat clipped and therefore incomplete. But the concept should be clear enough.


Concatenating VIEW Data For Insertion To The Browse

Near the beginning of this article is a screen-snap of two local variables Fullname and CourseInfo which have been inserted into our BrowseEnrollments() procedure. Here is part of that image again to refresh your memory.


These two locals contain four VIEW fields from three different underlying tables. The purpose for doing this via formatted concatenation, beside the fact that we can, is to provide a maximum of information on the browse using a reaonable amount of browse list box real estate.

By the way "because we can" is never a good criterion to apply when implementing any "nifty" new technical tool or skill. If pigs could be made to fly, probably the only outcome would be sky-high bacon prices.

The FullName variable, applied to the "Student" column consists of the field STU:LastName, a comma, a space plus the field STU:FirstName from the "Students" data table.

The CourseInfo variable, applied to the "Course" column consists of the field COU:Description, an opening square bracket, a space, plus the field CLA:ScheduledTime from the "Classes" data table, then closing square bracket.

We've made sure via "ABC HotFields" that these three fields (amongst others) were included in the VIEW structure - check and see for yourself, above, if you feel like it - so they are available for use in those parts of the procedure where ABC is populating browse data to the list box.


The concatentaion code is embedded into a procedure embed point located in an ABC Browse Class function called SetQueueRecord(). This is the same place that ABC incorporates listbox customizations such as color and font into the list box structure. Below is the actual code:


As the text-balloon indicates, this code is not "hand-embedded", it was inserted into this position [Priority 1400] in the browse class SetQueueRecord() method using the IDE's Forumulas Editor. The next sequence of images illustrates how that works.




FINAL STEP: Sorting And Searching Columns With Local Variables

This part of the implementaion relieves the developer of having to code anything at all, when they are using a CHT browse extension as we are, in this case, a CHT HandyMarkerBrowse.

All of our ABC browse extension templates provide a dialog called "Forced Sorts" where it is possible to cause a local variable to act as if it was a table variable. This happens again through the wonders of the Clarion VIEW structure with its PROP:FILTER and PROP:ORDER properties.

The CHT "Forced Sorts" dialog looks as in the following screen-snap. Here you indicate to our template, which then writes the code for you, which back end data field should be surrogated whenever the user ORDERs or FILTERs the VIEW by this local-variable column.

Easy as pie, right? You tell the template which table variable(s) to sort the view by and CHT code issues the correct PROP:ORDER to the view on your behalf. That's exactly what ABC does when you populate a table variable to the browse. CHT code just intervenes on your behalf whenever you insert a local that has been "Force Sort" adapted to surrogate for a designated table field.



This same "Forced Sorts" dialog tells the CHT search and query mechanism that the same column should be searched and queried using this back-end table variable whenever queries and locates are issued from one of our many plug-in templates to one of these columns containing local, display variables.


The SQL output generated by Clarion's VIEW in combination with the Clarion SQLite file driver, filters and orders the browse as if the "Student" column contained STU:LastName instead of local variable "StudentInfo".


The CHT browse extension's, PROP:ORDER property was further improved by subsorting on back-end field STU:FirstName to ensure correct sortation in the event of multiple, identical last names. And finally, a subsort was added to include ENR:ID, the Enrollment table unique ID, to ensure column uniqueness for refetch, during a reset.

This last little detail fixes an ABC browse reset, duplicate record display bug with SQL implementations by ensuring that this column's refetch criterion is unique to the back-end table being queried. This stops the ABC Browse Class from appearing to duplicate this record in the browse when the browse resets by giving SQL something with which to positively identify the browse record by including a unique ID field in the sort/fetch properties applied to the Clarion VIEW.

That's all for this topic.

To review, in brief, it is possible with all CHT browse extension templates to insert local variables into the list box queue and have the ABC browse operate when querying and ordering, as if the column containing that variable was a back-end table field.


Related-Table-Data On ABC FORMS

If you've been following our forum posts about SQLite and HNDSCHOOL.APP, the idea of publishing this "tip" at this time, came up as we were working on a CHT demo called, HNDSCHOOL.APP. It's a way to display related-file information on a standard ABC update form with a minimum of code. This technique pertains to all ABC forms regardless of back-end data file types, ISAM or SQL. It's something we've practiced for years.

We've chosen to incorporate this technique, once again, in the HNDSCHOOL.APP SQLite application, as the data tables are fairly highly "normalized". We should let you know beforehand, that we didn't design these data layouts, so the field and key naming conventions are a bit counter intuitive, but workable, nevertheless.

The Enrollment table on which we're building an ABC edit form, in particular contains two joins to other tables and not much "displayable" information, only the IDs of related tables. When editing a form based on what is essentially a "join table" it's always helpful to the user to display something from the related tables to help orient them. You'll see what we mean in a minute.

Here, first, are the Enrollment and Student table definitions:



ENR:StudentNumber is a join field from the Students table primary ID STU:Number
ENR:ClassNumber is a join field from the Classes table primary ID CLA:ClassNumber


The Classes table joins to the "Courses" tale via CLA:CourseNumber = COU:Number and a class "Scheduled Time" needs to be displayed from CLA:ScheduledTime while the Courses table contains the "Course Description" in COU:Description.

You probably know that an ABC data form uses the ABC Window Manager Class method called ThisWindow.Reset() to "reset" or "synch" the update form.

Inside this reset method the form pulls together all the related pieces to be displayed and managed on the window. What happens inside this reset method depends very much on how much stuff like fields, relations, browses and controls you have populated on the window. This is also where things can go woefully wrong when the window is just too busy for its own good or when the data design in the dictionary is inadequate for the job.

That's another story worthy of some words of caution about the merits of streamlined window design over traditional "Clarion-Tab-Gothic" windows which tend to be overpopulated and try to do everything on one form that should be divided up amongst several forms, using well-considered work-flow-based design.

In this particular example there's not much happening on the window, only five Enrollment file fields need to be completed, that's all. No big deal. And because, as we stated earlier, due to the highly "normalized" state of the data tables, the user is left in the dark unless some fields from related tables are displayed to clarify the WHO, WHAT and WHEN aspects of the Enrollment record.

In other words:
• Who is enrolled?
• What course are they enrolled in?
• When is the class running that offers this course?


In the screen shot above, we see the "update form file tree" established on the form. This particular layout is possible because these tables are joined in the dictionary in either MANY:1 or 1:MANY relationships.

If you follow the red arrow from the circled area on the upper left, it points to some code that the ABC template writes into ThisWindow.Reset() that resolves the joins by fetching the related records from the joined tables. The text balloon on the right explains what's happening.

In the embed point immediately following this ABC template-embedded code after [Priority 2800], we are able to update any local variables located on the window to display concatenated values from related-table fields -- unless we display the fields directly, in which case we wouldn't need to embed or concatenate anything. You'll see what we're doing in the next image.


Rather than populating four fields from the related tables, we chose to concatenate and format these four fields into two local variables "CourseInfo" and "StudentFullName". You can see that happening, followed by a DISPLAY() in the [Priority 2800] embed point.

To avoid actually "coding" at all, we could have entered our embed using the Formulas Editor provided by the IDE. That drops an embed into the "Refresh Window ROUTINE After Lookups" on your behalf, though not close to the "reset" action taking place in this specific embed point. But it will work too as that routine is called when the window resets.

It's really your choice, if you prefer to use the Forumlas Editor to assist you. Most developers can likely type faster than they can navigate that editor's user interface. And with code assist enabled in the standard Embeditor there's no real advantage using the Forumulas Editor.


In this final image you can see a browse, and below it, the form under discussion. The two string fields pointed to by the data balloons are the local variables into which the related-file information is concatenated.

The form synchronizes with the tables when necessary and keeps these values current with the related tables whether you're in insert mode or in change mode, and you add or change a student name with the student lookup button (upper) or add/change a class name with the class lookup button (lower).

That's all we need to say on the topic of ThisWindow.Reset() concerning ABC Update forms and displaying related-file data. We should add that we prefer to display related data on forms in "formatted" fashion, via concatenation. On ABC forms this simple embed method (or forumla insertion if you prefer) can handle that job handily.