Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 12:09
Joined
Sep 21, 2011
Messages
14,038
Well I managed to delete all the code by pressing OK many many times, but I still cannot delete that module.

I used File to import it. :oops:

I've exported what I needed, renamed it Corrupt Test and gone to backup. Lesson learnt. :)
 

Fran Lombard

Registered User.
Local time
Today, 08:09
Joined
Mar 12, 2014
Messages
132
If you can please post the adodb version.
Will do

Turns out I ported an earlier version that was much simpler - Before all the customer/orders data was in the db. Im porting this current version now and will post when ready.
 

Fran Lombard

Registered User.
Local time
Today, 08:09
Joined
Mar 12, 2014
Messages
132
Will do

Turns out I ported an earlier version that was much simpler - Before all the customer/orders data was in the db. Im porting this current version now and will post when ready.
Here is the ADODB version
There is one Folder that contains three Files in the Zip
1. TreeviewDemo.adp: MS 2007 ADP or AccessProject:
2. TreeviewDemo.bak: MSSQL 2012 Database backup File
3. qryToViews.sql: SQL file of the statements I used to convert the Access queries to Views

Not sure if I clicked every button and tested all the functionality but it seems to be working
Main Changes Needed
1. Replaced References from DAO to ADODB
2 Added a module DBFunctions which includes a set of functions for working with ADODB
3. Calls to Set mRecordSet = CurrentDb.OpenRecordset were replaced with
Set mRecordSet = DBFunctions.GetRecordSet
4. Calls to rs.FindFirst -> Replaced with rs.MoveFirst then rs.Find
5. Calls to re.NoMatch -> Replaced with rs.BOF
6. Calls to rs.FindNext -> Replaced with rs.Find
7. Bookmark Declaration changed to Variant
8. General Cleanup of table/field names - My DB is case sensitive so it barked at non-matching case

In most places I made changes I added a comment Line of ( 'FRL ) my initials so anyone could search on them.

Now I actually have to study the code so I know what I'm playing with.
Like to move from being a Plumber to a Designer

Have fun guys - Hope this helps anyone who plays with SQL in an ADP Project
 

Attachments

  • TreeViewDemoSQL.zip
    517.2 KB · Views: 265

Fran Lombard

Registered User.
Local time
Today, 08:09
Joined
Mar 12, 2014
Messages
132
Here is an update to above Attachment - Found some areas of I had not updated - specifically the 2nd TreeForm which loads customers and orders.
Made the ports and cleaned up the query module.
 

Attachments

  • TreeViewDemoSQLV2.zip
    520.6 KB · Views: 297

dgreen

Member
Local time
Today, 07:09
Joined
Sep 30, 2018
Messages
397
@MajP
Somethings not working right on the sort sequence #. The Level_ID is populating correctly but the sort order isn't. Thoughts just by looking at the output?

1586200693115.png
 

dgreen

Member
Local time
Today, 07:09
Joined
Sep 30, 2018
Messages
397
At quick glance, at the TreeView, at level 3, it appears that the sort order got reversed. I'm not sure if this is related or not.

So I'm going to resort them. Close the TreeView form.
1586202505456.png


Reopen the treeview. For the small group of 3 records that I resquenced, it's correct.

Maybe I did too many records in a single change and it got confused?

Somethings not working right on the sort sequence #
 

Dreamweaver

Well-known member
Local time
Today, 12:09
Joined
Nov 28, 2005
Messages
2,466
Thanks @MajP Going to use this for my next project which will be a Open Code Code Library, I use Total Visual SourceBook 2013 But never built one before :) , I should also be able to get the code looking like the VB editor,

Love the images that will make it look really cool Thanks

P.S. I will test it on my mrs 64 bit system.

keep safe mick
 

isladogs

MVP / VIP
Local time
Today, 12:09
Joined
Jan 14, 2017
Messages
18,186
Sorting alphabetically by the looks of it?
Can you get rid of the leading L ?

That wouldn't stop it sorting alphabetically as the 'number parts' like 3.1.2 are not actually numbers
One way is to sort using a derived number column where e.g. L3.11.2 becomes 31102, L3.1.3 becomes 30103, L3.2=>30200 etc

NOTE I haven't tried any of this code & there may be a better way of sorting already built in ???
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:09
Joined
May 21, 2018
Messages
8,463
@Gasman and @isladogs.
Neither recommendation would be relevant here because it is using the tree position of the nodes as a means to establish a sort order. The Tree allows drag and drop and the ability to move nodes up and down within a level. In order to maintain that defined sort the tree view has an index property. If you read a fully expanded tree from the top down, the indices are defined in that order regardless of their level The code uses the tree node order not any kind of query sorting. It then saves this order to the table on close. This way when you reload the tree it maintains the desired order.
Node '1
----- Node '2
------------Node '3
----- Node '4
Node '5
In this class we also added a feature to Auto Outline. It thens adds the outline numbering once you move things up and down in the tree. But the sort and the numbering are somewhat independent.
 
Last edited:

Lightwave

Ad astra
Local time
Today, 12:09
Joined
Sep 27, 2004
Messages
1,521
Also look at the Pairing table. At first I did not think this was necessary to store the 3 keys (motherID, fatherID, and PairingID), but it had a lot of advantages IMO.

I know its ages since we spoke about this but I came across a discussion about pairing in a stack overflow discussion directly relevant to the above.

How do you constrain a database so there are only unique pairs in a database table?

If Kirk is friends with Victoria if we store that as a partnership we should not store Victoria as a partnership with Kirk.

From the thread we know that for any pairing the combination of the sum and the absolute difference will be unique so you can create an index on two computed columns
we know that A + B = B + A and we know that for absolute values A-B = B-A

we can make a computed column C1 of A+B and we can make a computed column C2 of ABS(A-B)
and then a unique constraint on C1,C2


For genetics maybe doesn't need to be reversable as mother could always be in one field and father in other...

I'm thinking this would be really good in systems where there was children and the parents or guardians may be two males / two females or guardian 1 and guardian 2 - so they are paired but the gender order of the pairing could vary although always unique irrespective of order.. and the indepotent nature of the constraint would mean that you could generate a pairing table from the primary keys of the guardians as you go along.
 
Last edited:

Pac-Man

Active member
Local time
Today, 17:09
Joined
Apr 14, 2020
Messages
408
I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to span something like a file directory with an unknown amount of levels and branches. Common in doing things like Family trees and working with assemblies sub assemblies or tasks and sub tasks. Tree Views are a good way to show these Hierarchies. Think of an outline with multiple levels.

Recursion
So here is a simple example to demo recursion. The procedure is told how many times to loop itself and the counter is incremented each time the procedure is called. It simply adds 10 to the starting value each time it calls itself. Recursion always needs a way to check if it should call itself again or stop (does not call itself). Recursion if often very inefficient and resource heavy, but for certain data structure it may be the only way. (Note the below demo could obviously be done without recursion).
Code:
Public Sub RecursiveAdd10(HowManyTimes As Long, Optional Start As Long = 0, Optional Counter As Long = 0)
   If HowManyTimes = Counter Then
    Debug.Print "Final " & Start
    Exit Sub
   End If
   Debug.Print "Value " & Start & " Counter " & Counter
   Start = Start + 10
   Counter = Counter + 1
   RecursiveAdd10 HowManyTimes, Start, Counter
End Sub
test it
Code:
Public Sub TestRecursive()
  'add 10 5 times
  RecursiveAdd10 6, 50
End Sub
so the output is:

Value 50 Counter 0
Value 60 Counter 1
Value 70 Counter 2
Value 80 Counter 3
Value 90 Counter 4
Value 100 Counter 5
Final 110

Hierarchical data
When working with hierarchical data normally each record references a parentID in the same table. This is often called a "Self Referencing" table.

This would give the structure

Show table

To get this into a Tree View or display the hierarchy you start at the first Root node then recursively find the children. In the demo below I use a tree view to span the nodes to build the tree. To see how this is done look at the class module TreeViewForm.


Tree Views
Working with treeviews is pretty code heavy, but I have created a custom class to make this very easy. If you build a query precisely as instructed, you can then build a tree view with only a couple lines of code. The class also ties database information (Primary Keys) to the nodes making it much easier to work between the treeview and the data. The TreeView is "pseudo-bound" to the data. Read the instruction in the TreeViewForm class module.

I have included Multiple Demos showing different data structures and features. The E2E demo has about all the bells and whistles I added after working with DGreen on a specific solution. However to simply load your own tree view requires you to build a query and a single line of code.
1. Load from common query
2. Drag and drop of nodes and update database
3. Add, edit delete record in database and update tree
4. Delete, edit node and update database
5. Move node up and down in level and update sort order
6. Apply icons to specific records
7. dynamically load icons from folder at runtime
8 Right click on node with pop up command bar
9. Right click off node with different command bar
10. Expand and collapse tree and branch
11. Node selected to synch subform
12. Node double click to add, edit, delete
13. Auto level creates outline numbering
14. Save sort
15. Autolevel levels
16. Full load of nodes or light load and add nodes when expanded

Also included is a demo of Tree Views using MS Forms instead of the Active X.
The Span Files and Directory show a recursive span of a file system logging the information with another recursion to load the tree view.
I have also added to this capability with a class module for loading this Tree View.

View attachment 107131

Other Threads on TreeViews

Other Threads on general Recursion

Part Two to this thread focusing on recursion and shows how to build a simulated tree view with only a listbox.

Hi,

Thanks for so sharing such a great tool. I want to use tree view for one of my folder and used span folder option to populate the tblFileFolder. No of records in the table are 3000. I am then using TreeViewRecordLoader Class to generate my MS Forms tree view which add nodes from the table recursively due to which it takes about a minute or so and during which it seems that access got stuck. Is it possible to load the tree view faster instead of adding one by one node or is there any other way to speed up the tree view generation?

Best Regards
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:09
Joined
May 21, 2018
Messages
8,463
Did you look at this example?

The question is what is taking the time. Are you storing the file structure in a table then loading the tree or are you reading the files and loading the tree at the same time. Both of these take time.
In my example I read from the files and load the data to a table. Then I build the tree. I also do a lot of processing.
There may be more efficient ways to read and load the files to the table.
However once in a table I have the code in the class to do what I call a "light load". I only load the visible nodes. I think the demo has 10,000 nodes but I only load those needed for visibility. Maybe that is a a few hundred. Then when you click on a node it loads the next set of child nodes needed for visibility. This tree loads immediately and expands as you work with it.

If you want, I recommend starting a new thread which is easier and cleaner than adding to this long thread.
 

Users who are viewing this thread

Top Bottom