Hi All, Does anybody who worked with u_tvs and inv_levelsource noticed, if you populate treeview via RETRIEVE/SQL (with retrieve arguments, not filter) using datastores, then datastore (ads_obj) accumulates rows as you expand several branches of the same level. This creates a problem only if you share data between ads_obj and dw to display it in the right pane and mimic 'Windows Explore'. As a result I had a problem on delete and other events, when instead of removing one (deleted) row from the right pane, it displayed accumulated number of records minus the deleted one. I found the way around it, by finding out the parent key and filtering the dw in the right pane, on top of the select. I do not like this solution, but cannot figure out the better one. Please let me know if you need more information. Thanks, Stella Kravchenko ____________________Reply Separator____________________ Subject: RE: PFCSIG RE: u_tvs Driving me Nuts! Author: [EMAIL PROTECTED] Date: 12/14/99 11:06 AM Hi Brian What I meant by Level 5 was that the window is "Windows Explorer" like in the fact that the Treeview (left side) provides the heirarchy and the partnering DW (right side) actually shows the Promotion information when a Product (TV Level 4) is selected. I originally had the Promotion number displaying as Level 5 but switched to the Explorer look to keep the promotion details together. In the selectionchanged event of u_tvs I grab the level (inv_levelsource.of_GetLevel()) and if it is level 4,I then get a reference to the datastore (inv_levelsource.of_GetDataRow()), extract the Product PKID and the SourceDB and pass it to the DW to display the Promotions for the Product, switching txn objects as required. Not elegant but it works! - that is until I do some refactoring later on. This whole exercise is for display purposes only - the only real info is contained in the Promotion detail on the DW. The client wanted it this way and some idiot said - yea! no problem!! 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: Brian Grimm [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 15, 1999 3:41 AM To: Bruce Nordstrand Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] (E-mail) Subject: RE: PFCSIG RE: u_tvs Driving me Nuts! Bruce, Glad to be of help. I don't get out very much anymore... Thanks for letting us know your solution. My idea of the union accross transaction objects can essentially be reduced to what you did with the n_ds and of_setappend. I had forgotten this nice and easy method of combining data. My main point of the example was that a good u_tvs practice is to have columns in your resultset that represent the arguments that the prior level of the tree uses to filter with. May be a little bit of extra data coming back in the resultset that result in less headaches down the development road... I don't quite understand your last statement about level 5 and the selectionchanged event... This doesn't have to do with the tree does it? It sounds like when they select an item on level 4, you change a dw (somewhere on the window) to show the level 5 information instead of showing it in the tree. If it is not this then please elaborate to me what you are trying to accomplish... as you shouldn't have to be doing anything in the selectionchanged event to help the population of the treeview along. Good Luck, Brian Bruce Nordstrand <[EMAIL PROTECTED]> on 12/14/99 09:24:29 AM To: Brian Grimm/HOU/ECT@ECT cc: [EMAIL PROTECTED], "[EMAIL PROTECTED] (E-mail)" <[EMAIL PROTECTED]> Subject: RE: PFCSIG RE: u_tvs Driving me Nuts! Hi Brian and others that posted suggestions. I finally got around to concentrating more on my problem with u_tvs (see below...). I must admit that I was somewhat blinded by the "forest & trees" bit, having worked on this and other problems for way too long! As a result of the replies and other suggestions, I finally ended up with 4 (dropped level 5) datastores (n_ds), each pre-retrieved to supply the required data for each level. Level 1 is an external DW manually populated. (Thanks Brian for pointing out the non-requirement for Primary keys - my assumption based on u_tv more than trying it with u_tvs!) Each of the others have of_SetAppend set to TRUE and a quick switch of txn objects pulls data from both databases. Filter arguments then manage the linking of the various levels in the treeview. Level 5 is now DW linked to the selectionchanged event for Level 4. Although at this stage I am testing the server DB against DB2 V5.2 on NT (haven't got my remote connection to the AS/400 setup for testing yet), performance is pretty good. I also tried a single DB/DW call and looped through the resultset populating each level manually which, while acceptably fast enough, was ultimately a pain in the butt. One question I have for Brian - you mentioned doing a Union across transaction objects - is this from embedded SQL or DW?, can't seem to figure this one out but it sounds interesting! Thanks again to everybody that replied 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: Brian Grimm [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 08, 1999 5:00 AM To: Bruce Nordstrand Cc: [EMAIL PROTECTED] Subject: Re: PFCSIG RE: u_tvs Driving me Nuts! 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) +--------------------------------------------------------------------------- ------------------------------+ > [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]
