[AccessD] Treeview Problems

Hi Arthur

Yes I can see your problem at connecting level 3 and 4.

Although your stomach may face a hard time I think that's a possible
route, though you - as far as I can see - wouldn't need (to fill) a
bridging table but could use a view or query where you bring the PK of
the orderdetail record forward together with the list of all
orderdetail types.
Then you will at level 4 have that PK and all order types and can
select any of these even if the picked combination doesn't carry any
actual orderdetails of that type.

I haven't your code at hand so I cannot verify if this is possible.

/gustav


> Thanks for replying, Gustav. The part I'm having difficulty getting my
> head around is this. Suppose 4 levels in the tree:

> Customer
>         Order
>                 All Possible Detail Types
>                         Specific Detail of type x

> My code can handle levels 1 and 2 with one statement each. No problem.
> Level 3 I can write with individual "Add" statements. But now what? How
> can I relate level 4, which should be as easily handled as levels 1 and
> 2, i.e. with one statement that mentions the parent key? I don't have a
> parent key, or maybe I do, but I can't see it.

> At the moment, I'm thinking that the only way to achieve what I want is
> to manufacture a bridge table called OrderDetailTypes and automatically
> add N rows to this every time a new Order is created. Such a solution
> makes my stomach turn, but at the moment I don't see another solution.

> Any advice appreciated,
> Arthur

> P.S.
> Thanks for the rationalization (justification) you offered. Next time I
> fight with my girl friend, I'll request more rationalizations :)


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Thursday, July 22, 2004 4:27 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Treeview Problems


> Hi Arthur

> Your problem is that the tree structure you aim for is not a true
> branching tree structure as you at level 3 wish to list all possible
> order detail types instead of those actually used.

> However, it could be easily solved; you don't even need a union query,
> you just need to - as you state - list all possible types, regardless if
> they are used or not (that means just list them). I guess it will break
> the system in your fancy tree code but you can justify that for yourself
> by remembering that the code was created for the clean tree structure. 

> /gustav


>> Some of you may have read my stuff about data-driven treeviews. Lately

>> I have run into a problem using my own code, and am looking for a 
>> graceful way out.

>> If you've read or at least run the code, then you know that it reduces

>> the population of any given level to a simple select statement, using 
>> a query, a table, a view or a UDF.

>> However, my current problem is this:

>> Let's suppose that you have parent table P and child table C, which 
>> references an FK F (i.e. OrderDetailType.... Assuming that there can 
>> be several types of OrderDetail, say CarRental, CampGroundSite, 
>> CanoeRental, etc. ((hey, it's summer and I need to revisit Killarney 
>> Park, one of the most beautiful places in the world, so forgive my 
>> examples)).

>> Assume that Treeview level 1 is Customer. TV level 2 is Orders. 
>> Treeview Level 3 is all the possible order detail types. TV 4 is the 
>> detail types arranged under TV3.

>> I.e.

>> 1:
>> ABC Company
>> DEF Company

>> 2:
>> Order 123 from ABC
>> Order 125 from ABC
>> Order 126 from DEF
>> Order 139 from DEF

>> 3:
>> CarRental
>> CampGroundSite
>> CanoeRental
>> Etc.

>> 4:
>> Specific instances of CarRental, CampGroundSite, CanoeRental, etc.

>> My code so far can handle levels 1 and 2 easily, one statement per 
>> level.

>> The problem is at level 3. I want to populate the treeview at level 3 
>> with all the order detail types (CarRental, CampGroundSite, 
>> CanoeRental,
>> etc.) for each Order. It's a simple UNION query to do this, but where
> do
>> I get the unique key?

>> Generalizing my problem, I have a simple (and dare I say it pretty 
>> cool) way of populating the data-driven levels of a treeview. But 
>> that's insufficient for my current problem. I need to populate the 
>> OrderDetailTypes level with all possible order detail types and then 
>> sub-populate level 4 with the corresponding children. I'm at a loss 
>> how to achieve this.

>> One way to do it, which would fall conveniently into my current 
>> scheme, is to populate a bridge table (OrderDetailTypes) with one of 
>> each OrderDetailType... But that sucks, IMO. It stores a bunch of data

>> that is most of the time unnecessary. I HATE unnecessary detail! I'll 
>> go there if I have to, but it makes me gag.

>> Generalizing the problem....

>> Given: code that can populate any tree-level with a simple select How 
>> to insert a level that is not data-driven or alternatively is 
>> data-driven but not with related FKs Treeview nodes insist upon unique

>> keys (understandably so). But how should I manufacture said unique 
>> keys given that no physical rows correspond to them.
>> Maybe the generalization is: how to relate a grandchild to a parent in
>> the absence of parental data? That might be an incorrect phrasing, but
>> that's what I mean.

>> I hope that I have stated the problem clearly enough for you to follow

>> the logic. If not, please reply for further clarification.

>> Any suggestions much appreciated.

>> TIA,
>> Arthur