Hi ,

I have four list-boxes which was populated the data using the chaining method in HTML composer. My metadata for these listboxes was an oracle transaction table earlier which contain more than 2 laks records and there was a huge performance issue in loading the list-boxes.So I decided to create four lookup tables by selecting distinct of values from the transaction table and created metadata of these lookup tables.

create table ocm as select ocm from trans1;
(16 records created)
Note- First level.

create table bg as select ocm,bg from trans1;
(111 records created)
Note- Second level.Added parent ocm also to chain

create table bu as select bg,bu from trans1;
(161 records created)
Note- Third level.Added parent bg also to chain

create table cc as select bu,cc from trans1;
(6137 records created)
Note- Fourth level.Added parent bu also to chain.

I have created metadata for all these lookup tables and tried to make a chain like ocm=>bg, bg=>bu,bu=>cc. My intension here is that if a value in the ocm is changed the cascading effect should be there till the cc. I have noticed only ocm and bg (first chain) only populated and chained , other 2 are not even populating. I removed the chaining from all the boxes and then the data populated in all boxes independently.

The doubt , I need to clarify here is that do we need to create a metadata which contain all the participating items (all the 4 levels) in that ? Or the current logic I tried (the immediate child has its parent reference in it) can work ?

If I need to include all the 4 level in a single metadata then the record count will be more than 10000 and it is still a performace overhead. (create table all_list as select distinct ocm,bg,bu,cc from trans1).

Any suggestions ?

Thanks and Regards,
Johney Joseph.

This message has been edited. Last edited by: Kerry,

Hi,

My listboxes are getting populated by below given code

TABLE FILE OCM
SUM FST.OCM.OCM.OCM
BY OCM.OCM.OCM
// TODO: Add your filters here to replace defaults
ON TABLE PCHOLD FORMAT XML
END
---------------------------
TABLE FILE BG
SUM FST.BG.BG.BG
BY BG.BG.BG
// TODO: Add your filters here to replace defaults
ON TABLE PCHOLD FORMAT XML
END

TABLE FILE BU
SUM FST.BU.BU.BU
BY BU.BU.BU
// TODO: Add your filters here to replace defaults
ON TABLE PCHOLD FORMAT XML
END

Any one level of chaining will work

Johney.

Johney,

I've seen this behaviour with list boxes whilst I do not see the same with combo boxes.

The solution is to add WHERE clauses into the internal procedures (those things in your second posting), providing you have bound the parameters to the respective list boxes.

If, for instance, your list boxes have the "name" attributes "OCM", "BG" and "BU" respectively, your internal procedures would become -

TABLE FILE OCM
SUM FST.OCM.OCM.OCM
BY OCM.OCM.OCM
// TODO: Add your filters here to replace defaults
ON TABLE PCHOLD FORMAT XML
END

TABLE FILE BG
SUM FST.BG.BG.BG
BY BG.BG.BG
// TODO: Add your filters here to replace defaults
WHERE OCM EQ &OCM
ON TABLE PCHOLD FORMAT XML
END

TABLE FILE BU
SUM FST.BU.BU.BU
BY BU.BU.BU
// TODO: Add your filters here to replace defaults
WHERE OCM EQ &OCM
WHERE BG EQ &BG
ON TABLE PCHOLD FORMAT XML
END

You need to be very careful with the WHERE clauses for a couple of reasons.

1. If you've got alpha numeric data then the default for multi selects are to enclose each value within single quotes.
2. If you've clicked to allow an "ALL" option, then the value passed for a parameter will be 'FOC_NONE' (You guys know how to pronounce that do you not? )

In the second instance, the entire where clause will be "ignored" if you have WHERE OCM EQ &OCM AND BG EQ &BG on a single line.

However, if you have it across a couple of lines -
WHERE OCM EQ &OCM
AND BG EQ &BG
and you have ALL selected for OCM, then a syntax error will occur because your clause will effectively be AND BG EQ &BG with no preceding WHERE.

T

Hi Tony,

Thanks for the reply. My doubt now is that , why do we require an additional WHERE clause when the chaining is taking care of trimming the data in the next level? Is it not doing the action of a WHERE clause only?

I am attaching my sample mas and acx along with the oracle tables and its records. Please download chaining.sql and execute it in any test schema and copy the mas and acx (change the connection) and execute the chaining.htm to see what my actual issue is. Each level I have a common field to chain but only the first level chaining is happening. Data not even populated in the 3rd box. If we remove the chaining all the box get data. Could you please look in to this ?

Thanks and Regards,
Johney Joseph.

Hi,

I could not find any option to upload files. Can somebody help me in doing that ?

I have raised my issue as a case to IBI under case no.63212510 also and all the files were uploaded there.

Thanks and Regards,
Johney Joseph.

This message has been edited. Last edited by: johney,

Johney,

This forum is not IBI technical support, although there may be several posters who are IB employees.

If you have raised a case on InfoResponse then I would suggest continuing the support from there only.

T

Tony,

I'm having trouble with chaining that I'm hoping you can help with.
I have 2 comboboxes with autocomplete and focus set to the first box. When I tab from the first box to the second chaining will not work. If I mouse click to select in the first box then the chaining will work. So, I think it's something with the autocomplete. I just can't figure it out. I tried taking off the "ALL" option and that didn't make any difference. My comboboxes are populated with fexs. I need to have all possibles in the first name. This is a directory project. So if you want to see everyone employed by the first name of Michelle then you that would be the only combobox to make a selection from. But if you want to find me specifically then you would select the last name and ideally get the limited selections for the first name. Like I said, it will work if using the mouse but now when there is focus on the first combobox(lastNameCB) and I tab to the next combobox(firstNameCB), I still get all the first names and not the limited selection. I would appreciate any insight you have.
Thanks

My understanding is that it only ripples through the chains as a result of an "onchange" event so that would be why you only see the updates after you have changed the selection.

You could try adding another event action to your control such as "onblur" but you would need to add the correct call to the javascript as per the "onchange" event.

The fact that you're on 7.7 (which sub release?) means that the methods I used to use to add these functions etc. might not be the correct method in this release and I haven't got the time to be able to check this out. I suppose what I would do myself (apart from reading through JS code!!) would be to initiate a read of the DOM at page load and interogate each combo that is found, check the "onchange" event call and add and additional "onblur" event for that combo to the same javascript function. Easy enough if you know how to do this, not so easy if you do not. If your JS is up to it then try it, if not I wouldn't suggest adding code that would be unsupportable from your companies perspective.

T

This message has been edited. Last edited by: Tony A,

Thank you Tony. I asked you because I know you know what you are doing and I obviously do not. I'm not sure how to initiate the read of the DOM but I will figure it out. I had thought about the onblur but wasn't sure of the Javascript code to put in the function. At least now you have given me an idea where to look. Thanks again.
Michelle