Bruce,
The PFC 6.5 u_tvs should not require key columns anymore. U_tv does, however.
We were able to take away that restriction in 6.0 with the exposure of the
datawindow's internal identity column. Thus, external datasources are ok.
Based on my limited understanding of your problem, I would create a datastore
for each level of your tree. The level 1 datastore would have 2 records in it,
Local and Remote. The level 2 datastore would need to be a crossdatabase (2
transaction objects) SQL union type statement. You could first, retrieve each
database DIVMINOR seperately, and denote the location the DIVMiNOR came from as
the source column (0 = remote, 1 = local) and combine all the records
(rowsmove/rowscopy) into 1 datastore and use that datastore as the source for
level 2 with the datasource as the retrieval argument. Your SQL would look
something like this: Select 0, * from remote.DIVMINOR, and select 1, * from
local.DIVMINOR. To combine all the rows, all column types much match...
You would need to do this for each level because of the way you are doing it.
Each level would need keys to the previous level carried down with it (a good
practice to do whenever using a treeview). Thus, your level 3 datastore would
need the 0/1 key, the divminor key in the result set, and these would be the
retrieval arguments to level 3.
I cannot tell for sure, but it sounds to me like if you may already have the
entire result set you want to put into the tree in non-tree form. If this is
the case, you may want to look into the recursive aspect of the tree, or use the
same datasource as each level (1-5) using filtering args (the last arguments to
of_register) to minimize the returned rows. You may run into a PFC problem/bug
using the same dataobject name, and if so, you can get around it by saving the
dataobject 5 times naming it something different each time. Then you would need
5 separate datastores, but could populate the other 4 with a rowscopy vs a
database retrieve.
A final note, if you use a non SQL/Retrieve registration style, the data from
your source is actually cached in the treeview/listview so it doesn't encounter
a null object reference. These registration styles actually use the filter
columns arguments to simulate the retrieval arguments. Look into the
inv_levelsource.of_register() function comments for info on how the filter
arguments work and the inv_levelsource.of_retrieve() function.
Good luck. Let us (the list) know what happens.
Brian Grimm
Bruce Nordstrand <[EMAIL PROTECTED]> on 12/06/99 05:57:00 PM
To: "[EMAIL PROTECTED] (E-mail)" <[EMAIL PROTECTED]>, "[EMAIL PROTECTED]
(E-mail)" <[EMAIL PROTECTED]>
cc: (bcc: Brian Grimm/HOU/ECT)
Subject: PFCSIG RE: u_tvs Driving me Nuts!
Thanks for the answers but it (in my opinion) is not as simple as retrieval
arguments. It may also be a case of working on this for too long and I
cannot see the forest for the trees! I will try and explain to give a little
more insight. (content warning - this is a long post! and I hope all the
little tree diagrams come out ok!).
The database has the tables I need, structured as follows (key columns
only):
DIVMINOR pkid, majordiv_pkid - 5 Records
DIVCAT minordiv_pkid, cat_pkid - 13 Records
CATEGORY pkid - 13 Records
PRODUCT pkid - 341 Records
PROMOTION pkid - 3 Records
PROMPRODUCT promotion_pkid, product_pkid - 3 Records
NOTE: the record counts are only what's in the Local DB now.
The actual tree display would look something like this:
Local Database
Division 1
Category 1
Product 1
Promotion
Promotion
Product 2
Promotion
Category 2
etc
etc
Division 2
etc
etc
Server Database
Division 1
Category 1
Product 1
Promotion
Promotion
Product 2
Promotion
Category 2
etc
etc
Division 2
etc
etc
If I wanted to display all the items from all tables at all levels, then
retrieval arguments would be fine. But the actual controlling datasource is
the joined result of PROMOTION & PROMPRODUCT and from the diagram above
displying 341 Products is not an option, the 3 Promoted Products are from 2
CATEGORIES which corelate to 2 DIVMINOR's. Now, also throw in a column
(sourcedb) in the PROMOTION table that defines (through 0/1) which database
the record is coming from (I am connecting to 2 databases - Local ASA 6.0
and Server DB2/400). Consider also that PRODUCT's may appear on multiple
PROMOTIONS at different dates.
In effect I am looking at this (based on the DB content)
Database
Division 1
Category 1
Product 1
Promotion 3
Product 2
Promotion 3
Division 2
Category 2
Product 3
Promotion 3
As you can see there is an indirect relationship with the higher level
tables that must be derived from data in PROMPRODUCT. I am actually joining
PROMOTION, PROMPRODUCT and PRODUCT to give me prom_pkid, prod_pkid and
cat_pkid. Extending the SQL further to link the upper levels is possible
but I cannot see the point in executing it multiple times, once for each
level just to get description columns for the upper levels. I have tried
this, but on certain Accounts it can take up to 20 secs to execute yet on
others it takes under 1 sec (pass, next question!!) but that is another
issue.
So what I have is an nvo with datastores for each level (except DIVCAT which
is only a joing table). I retrieve the Level 5 datastore
(Promotion/Proproduct join)from each of the databases (of_SetAppend() is
TRUE) which gives me the keys for the 2 levels above indirectly). I then
extract the cat_pkid column and do a Find(...) on cached Category data to
give me the Minor Division key (CATEGORY table is joined with DIVCAT), which
I then use to lookup the name using cached Minor Division data. I do also
this with the product_pkid to give me the Product Name. (By the way this is
similiar to the way it is done in VB where this app is being converted
from.)
As I see it, u_tvs populates each level without regard to the content of the
lower levels and uses retrieval arguments to "filter" only the lower levels
which does not work in my case. u_tvs also requires Key columns to be
present so an external DW for Database level cannot be used. There inlies
the problem.
SO, I hope you didn't get bored while reading this and things are now a
ltiile clearer.
Best regards
+---------------------------------------------------------------------------
------------------------------+
Bruce Nordstrand
KSL Consulting Pty Ltd
P.O. Box 4852, North Rocks, NSW 2151, Australia
Phone: +61 2 9873-6256 Fax: +61 2 9873-6216
Email: [EMAIL PROTECTED] Web: http://www.ksl.com.au
US Fax: (419) 791-3469 - By efax.com (http://www.efax.com)
+---------------------------------------------------------------------------
------------------------------+
-----Original Message-----
From: Bruce Nordstrand [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 07, 1999 1:38 AM
To: [EMAIL PROTECTED] (E-mail); [EMAIL PROTECTED] (E-mail)
Subject: u_tvs Driving me Nuts!
Hi All
HELP! u_tvs is driving me nuts and is rapidly looking like not what I want
at all. Here's what I am trying to do:
There will be 5 levels - Level 1 Source Database (Local/Server), Level 2
Minor Divisions, Level 3 Categories, Level 4 Products and level 5
Promotions. L5 will be linked to a DW displaying the Promotion information
for each Product.
In it's raw form the data for all these levels is held in separate tables
aside from Level 1 which is determined by a flag in the Promotion Table. The
catch is that for levels 2 thru 4 I only want to display the data for
whatever matches what's on Promotion. i.e. out of 709 products, only 20 are
being promoted using 4 out of 13 Categories etc etc.
What I have tried so far is executing a DW that returns the promotion
information for each product. Then I loop through this data and collect the
various parts from nvo's pre-populated with Minor Div, Category and Product
data and plug them into datastores for the various levels. However, the only
thing that doesn't appear at levels 2 thru 4 is the source DB flag. I could
use a calculated field but that is messy to change.
I am sure there is an easier way to do this, but I have never used u_tvs for
anything else other than DB hits.
Can it be done? Any suggestions?
Regards
+---------------------------------------------------------------------------
------------------------------+
Bruce Nordstrand
KSL Consulting Pty Ltd
P.O. Box 4852, North Rocks, NSW 2151, Australia
Phone: +61 2 9873-6256 Fax: +61 2 9873-6216
Email: [EMAIL PROTECTED] Web: http://www.ksl.com.au
US Fax: (419) 791-3469 - By efax.com (http://www.efax.com)
+---------------------------------------------------------------------------
------------------------------+
===========================================================
This List brought to you by DelWare Consulting Group. Visit
our site http://www.DelWare.com today!
Please, no employment posts!
To unsubscribe send mail to [EMAIL PROTECTED]
UNSUBSCRIBE PBPFC YourFirstName YourLastName
> [EMAIL PROTECTED] HOSTED BY IIGG, INC. FOR HELP WITH LIST SERVE COMMANDS,
ADDRESS
> A MESSAGE TO [EMAIL PROTECTED] WITH THE FOLLOWING MESSAGE: help pfcsig
> SEND ALL OTHER INQUIRES TO [EMAIL PROTECTED]
> [EMAIL PROTECTED] HOSTED BY IIGG, INC. FOR HELP WITH LIST SERVE COMMANDS, ADDRESS
> A MESSAGE TO [EMAIL PROTECTED] WITH THE FOLLOWING MESSAGE: help pfcsig
> SEND ALL OTHER INQUIRES TO [EMAIL PROTECTED]
