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]
