List with Rows from Two Tables Parent <-->> Child

Greetings all.
It’s been over 15 years since using Clarion. I think it may have been v5. So I just started again with v11. Can someone point me to an example where a list box contains rows from a parent and child tables?

Example:

--------------------------------------------------------------
Date            Name            Sign In       Sign Out    
                Guests
---------------------------------------------------------
01/13/25        John Doe        10:30AM
                Sally Smith
                Bob Brown

01/13/25        Bill Baxter     10:34AM
                Sam Shade

Many of my List controls (aka list boxes) have columns from a parent and a child file.
These files are related (as parent/child) in the dictionary.
Typically you should put the child file first on the Data / Tables tab. Sometimes it doesn’t make a difference (depending on keys).
So both are listed under the File-Browsing List Box list, and not under the Other Files list (on the Data / Tables tab).
(I’m not quite sure this was your question).

In my example:
John Doe is a parent record and has 2 child records Sally Smith and Bob Brown. And Bill Baxter is a parent record and has one child record Sam Shade.

It’s slowly coming back to me. Let me try what you’re describing.
Thanks.

Rich,
You’re describing a list tree. There is an example. I’m personally not fond of the built-in tree control template, but it does work.
The abcbrws.app example has a relation tree procedure example.

Rick,
Almost, but without the tree.
I think I need to define a VIEW that joins my parent and child tables together. And then based the ListBox on the VIEW.

Question… Where do I declare the VIEW? As local data to the procedure, or GLOBAL… hmm?

You could do this if you did not use the Browse Template and instead your own Queue and a LIST.

The columns that do not show for the Child records (e.g. Date, Sign In and Sign Out) would need to have Picture with a “B” (like @D01b and @T3b) so blank when Zero, or use a STRING that you leave Blank for Children.

HtmlBrowseBox supports expandable rows with additional info (from child tables for example).

About to use a VIEW, if you follow the tip from softwaretailor, you’ll get a view from Clarion.
The only issue I see is how to blank the date and time columns on the child records…

Maybe by using the browse tree template you’ll don’t have this problem…
I think that you can set on the List formatter to don’t show the tree lines…

Update:

If I was doing this in SQL it would be something like

SELECT
  sig.id
, sig.date
, sig.name
, sig.signin
, sig.signout
FROM sign_in
UNION
SELECT
  gsi.id
, null
, gsi.name
, null
, null
FROM guests_of_signin
;

But I don’t see a way of doing a UNION in a CLARION VIEW.
Then I thought of a LISTAGG

SELECT
  sig.id
, sig.date
, sig.name
, LISTAGG( SELECT gsi.name FROM ......) AS guest_names
, sig.signin
, sig.signout
FROM sign_in

So my latest idea is to load the guest names into a field in the signins table instead of a 1-many relationship. I haven’t started digging yet. Is there an example of a multi-select combo dropdown list control? So the user may select several guest names at once.

Thanks
RPC

Well, that is a different question, so, is that what you’re really trying to achieve?

You might be trying to do this the hard way when it would be easier to have two separate list boxes, one with the bookings and the other with the booking guests, which you could use to do your multiple select from.

Just list all the files under the File-Browsing List Box list, and not under the Other Files list (on the Data / Tables tab) and you’ll get the view from Clarion

Firstly, that is not quite the query you would use to get what you had in the first post: that would give you all the primary signins and all the guests in no particular order; most likely order would be all the signins first, followed by all the guests. You’d have to wrap that in another select and order by id,date nulls last or similar.

Secondly, your listagg query will dump all of the other names into a single string field, possibly with a separator, like a comma, and then the things you are asking below (being able to select one or more names) becomes a lot harder.

If some variant of the first query is what you need, then the easiest way to get that is to write the view in the database and import that view into clarion as a table. In clarion you will need to declare a primary key on that table. Maybe ID, name is good enough, but if John Sr brings John Jr with him, and you have them both entered as John, you will have trouble.

@Flavio_Suarez: you really cannot do what is originally presented in Clarion…the Name column is a union of the name in the signin table and the names in the guests table. What you could do, no problem, in Clarion is:

ID SigninName GuestName1
… GuestName2
… GuestName3

Yep… Got it…
The view on the database seems to be a better way…
I’d do the database view as something like

SELECT
  sig.id as SigninId
, sig.id as GuestId
, sig.date
, sig.name as SigninName
, sig.name as GuestName
, sig.signin
, sig.signout
FROM sign_in
UNION
SELECT
  sig2.id as SigninId
, gsi.id as GuestId
, sig2.date
, sig2.name as SigninName
, gsi.name  as GuestName
, null
, null
FROM guests_of_signin
LEFT JOIN sign_in AS sig2 ON sig2.id = gsi.id
;

Then, in Clarion dct set a key as “date, SigninName, GuestName” and in the browse list show only the GuestName column.
Also, in the Browse’s SetQueueRecord method, when SigninId <> GuestId you can CLEAR the date, signin and signout fields

Hi,

We wrote a simulated Tree way back in C6 when we were working on displaying tree structures on a control in an internet application. What we did was define a record with parent1_id, parent2_id, parent3_id ( as longs), etc for as many levels as you want. Make aliases of this file for use in recursion. Define a QUEUE and populate it from the file - use the file with paren1_id = 0 to display all of the primary elements. After that keep displaying successive children of that parent which have something like parent1_id = 2 and the next level will have parent3 = id of parent2 record. This can go on to as many levels as you want.
This takes some time and coding (especially to build and maintain the list of parents and children) but we built and used a very versatile simulated tree using line indents and icons.

I am building a similar one right now. Here is the primary file structrure:

P_PARTS         FILE,DRIVER('TOPSPEED'),OWNER('rsj4149'),NAME(kpartsname),PRE(PPT),CREATE !Parts for Kiosks
FK_CATEGORY       KEY(PPT:Category_IHID),DUP,NOCASE
SERIAL_KEY        KEY(PPT:Kiosk_SerialNumber),DUP,NOCASE !Serial number
FK_INVENTORY      KEY(PPT:Inventory_IHID),DUP,NOCASE !Foreign key to inventory
FK_REGISTRY       KEY(PPT:K_REG_ID),DUP,NOCASE !FK to Kiosk registry
Level1_KEY        KEY(PPT:Lev1ParentID),DUP,NOCASE
Level2_key        KEY(PPT:Lev2ParentID),DUP,NOCASE
Level3_KEY        KEY(PPT:Lev3ParentID),DUP,NOCASE
Level4_KEY        KEY(PPT:Lev4ParentIHID),DUP,NOCASE
IHID_KEY          KEY(PPT:IHID),PRIMARY,NAME('BRLS_IHID_KEY'),NOCASE
Name_KEY          KEY(PPT:KioskID),DUP,NAME('BRLS_Name_KEY'),NOCASE
FK_MFGR           KEY(PPT:Mfgr_ID),DUP,NOCASE !foreign key to manufacturer
fK_supplier       KEY(PPT:Supplier_ID),DUP,NOCASE !foreign to supplier
record            RECORD
IHID                LONG
KioskID             STRING(100) !kiosk ID
K_REG_ID            LONG !kiosk registry IHID
Kiosk_SerialNumber  CSTRING(20) !our serial number for build
Inventory_IHID      LONG !if we inventory the parts this is the inventory ID number of the part
Category_IHID       LONG
KioksLoc            CSTRING(200)
Part                STRING(51) !sub description
KBuildDate          LONG
....
Part_MEMO           CSTRING(1000)
Lev1ParentID        LONG !level 1 parent ihid
Lev2ParentID        LONG
Lev3ParentID        LONG
Lev4ParentIHID      LONG
levelNumber         LONG
hasChildren         BYTE   ! you can use this to display in icon with a + if there are children
 
                  END
                END

We have partsAlias, PartsAlias2, partsAlias3 which are all related to the parts file and this makes the recursion work!

Maybe that is an example of what you might want to do.

Ron

Thanks Ron.

As I worked on this I realized that I couldn’t keep the parent and multiple children as a single selected row. So I’m going with a “LISTAGG” approach using a Group and setting Name as “Last On Line” so Name and Guests appear in the same column.

So instead of this

--------------------------------------------------------------
Date            Name            Sign In       Sign Out    
                Guests
--------------------------------------------------------------
01/13/25        John Doe        10:30AM
--------------------------------------------------------------
                Sally Smith
--------------------------------------------------------------
                Bob Brown
--------------------------------------------------------------
01/13/25        Bill Baxter     10:34AM
--------------------------------------------------------------
                Sam Shade
--------------------------------------------------------------

I get something like this.

-----------------------------------------------------------------------------------------------
Date            Name                                                  Sign In       Sign Out    
                Guests
-----------------------------------------------------------------------------------------------
01/13/25        John Doe                                              10:30AM
                Sally Smith, Bob Brown, George Jetson
-----------------------------------------------------------------------------------------------
01/13/25        Bill Baxter                                           10:34AM
                Sam Shade
-----------------------------------------------------------------------------------------------

If you want all the names it is certainly possible. You just have to do the UNION first, followed by the listagg. I’ve used a variable called pos (0 for the primary, 1 for any guests), so your primary shows first. This is Oracle syntax, so yours might be slightly different.

create table signin (id number(4),dt date,name varchar2(40),signin date,signout date);
create table guests (sid number(4),guest_name varchar2(40));

insert into signin values(1,date'2025-01-13','John Doe',to_date('2025-01-13 10:30','yyyy-mm-dd hh24:mi'),null);
insert into signin values(2,date'2025-01-13','Bill Baxter',to_date('2025-01-13 10:34','yyyy-mm-dd hh24:mi'),null);

insert into guests values (1,'Sally Smith');
insert into guests values (1,'Bob Brown');
insert into guests values (1,'George Jetson');
insert into guests values (2,'Sam Shade');

with names as
  (select id,listagg(name,',') within group (order by pos,name) guests
  from
    (select id,name,0 pos
    from signin
    UNION ALL
    select sid,guest_name,1
    from guests
    )
  group by id  
  )
select s.dt,n.guests,s.signin
from signin s
join names n on n.id = s.id

Result:

DT GUESTS SIGNIN
2025-01-13 John Doe,Bob Brown,George Jetson,Sally Smith 2025-01-13 10:30
2025-01-13 Bill Baxter,Sam Shade 2025-01-13 10:34

That’s it.

Now I’m trying to create a browse procedure so the user may select 1 or more guests. I see the MARK attribute. But I haven’t found any examples of how to use it. How do I select multiple rows and return those from a call to the browse procedure. I’ve done text searches through all the documentation and example apps for :MARK or BRW1::MARK, etc… but can’t find anything. Can someone point me to an example use of the MARK attribute?

Thanks,
RPC

The MARK attribute alone will not give you any joy with a template driven browse.

I would recommend going with one of the 3rd party tagging addons.

I am curious why a UNION ALL is needed in this Oracle syntax? Using Postgres, I would simply use string_agg & group by in a subquery and join that with the parent table using the group by value.