I have to live in the GUI World.

I'd like to populate a Year-Month-Day set of chained dropdown lists from an external procedure that reads a time dimension table. I created an XML file that contains Date, Year, Month and Day columns. I then set up the properties for three chained dropdown lists populated by Dynamc External Procedure (the same one for all three), assigning the appropriate columns.

When executing the parameter screen, the three dropdown lists are populated, and I was hoping that chaining them would allow me to automatically have intelligent dropdown lists - if 2008, February was selected, I'd see 1 to 29 in the day dropdown; if 2011, September was selected, 1 to 30 in the day dropdown.

The chaining appears to make no difference - I always see 31 days in the Day dropdown list.

Am I barking up the wrong tree?

Thanks,

No one?

Francis

thanks for your reply to my posting, have'nt had time 2 try it, but this sounds a little bit like my problem. Maybe chaining was'nt supposed to work on more than 2 connected boxes....

Håkan

I'll give it a try with the proverbial CAR file and see if I can make it work. Good luck with your composter, hope you don't have too many worms.

Francis,

I encountered the same problem with a set of three drop-down list boxes using Dev Studio 7.7.03. I chained the three together hoping selections in the first two drop-downs would dynamically alter the third, which was a list of year/months from the same data source as the first two criteria. The first two drop-downs performed as expected, but I could find no way to get the third to act as desired. I even rummaged around in the HTML trying to discern some missing/incorrect setting that might cause the third drop-down to act differently than second one - to no avail. I finally gave up and created the launch page using release 7.6.7, where chaining the three together quickly produced the desired result. Unfortunatly, that means I cannot sort my drop-down dates in descending order (most recent on top), a nice feature not available in 7.6.7.

One step forward, two steps back!

If your external procedure sorts the dates in descending order, do they get resorted in ascending order when displayed in the drop-downs?

I'm using "embedded procedures" to populate my drop-downs, and in 7.6.7 WF insists on sorting the values in ascending order, no matter how they are sorted in the source. And there is no visibility to the code that WF generates to create the drop-down lists.

In 7.7.03, as you've probably noticed, there is a Properties and Settings radio button that can be selected to sort the values in descending order. In reality, it doesn't seem to work. But because the code that generates the drop-down lists is embedded in the HTML, you can manually add BY HIGHEST to the code to get the list in descending order.

Using Dev Studio 7.7.03 I modified my launch page to use an external procedure instead of an embedded procedure to populate my three drop-down list boxes. Now the chaining does work correctly and the third drop-down dynamically changes as selections are made in the first two. Why this doesn't work with embedded procedures is a mystery. But despite the fact that my external procedure sorts the date data in descending order and despite the fact that I have also selected the descending order radio button on the Poperties and Settings dialog box, the dates are displayed in ascending order in the drop-down box. So I have the choice of using an external procedure and having chained drop-downs with poorly sorted dates, or using embedded procedures and having de-chained drop-downs with nicely sorted dates.

One dance partner doing the mambo, while the other does the cha-cha-cha!

I've had a case (71092513) open with IBI about, I think, the very same sorting issue.

Apparently we're supposed to use the sorting checkboxes in the properties for the dynamic procedure (which does the sorting in javascript) as opposed to using the sorting from the procedure results.

Worse, if you explicitly sort the results of an embedded procedure different than what the generated code (from selecting a master and telling the interface what columns to use), then your chaining won't work.

I haven't been able to convince the support people who worked on this case that this is undesirable, I hope you have better luck.

Does this mean if I use embedded code, three chained list boxes will work?

Well, they do here. We don't usually use list boxes, but rather use dropdown lists, but I just tested with the CAR sample database and it seems to just work:

<HTML>
<HEAD>
<META id=mycharsetmeta http-equiv=Content-Type content="text/html; charset=ISO-8859-1">
<META id=Generation content="Created in release 7611, Generation 3.0">
<SCRIPT id=IBI_RelCallBack type=text/javascript>function AdjustChildrenPosition(){
}
</SCRIPT>

<SCRIPT id=IBI_OptionsScript type=text/javascript>
var cgipath = "cgipath";
var ibirls = "ibirls3";

var rltdyncalendar = "rltdyncalendar";
var gmap = "ibigmap";
var olap="olap";
var olappanebase="olappanebase";
var olapdrill="olapdrill";

var ibixmltree="ibixmltree";

var ibiOptions = new Array(cgipath,ibirls);
</SCRIPT>

<SCRIPT id=IBI_nls src="/ibi_html/javaassist/nls.js" type=text/javascript></SCRIPT>

<SCRIPT id=IBI_nlsVars src="/ibi_html/javaassist/nlsvars.js" type=text/javascript></SCRIPT>

<SCRIPT id=IBI_ibigbl src="/ibi_html/javaassist/ibi/html/js/ibigbl.js" type=text/javascript></SCRIPT>

<SCRIPT id=IBI_ibigblloadCss type=text/javascript>
ibigblloadCss(null);
addIntlTranslatedJS("composertrans.js");
</SCRIPT>
<TITLE>HtmlPage</TITLE>
<SCRIPT id=clientEventHandlersJS type=text/javascript>
//Begin function window_onload
function window_onload() {
 
UpdateData();
 
// TODO: Add your event handler code here
//add onInitialUpdate() function to make changes before initial run of the reports
}
//End function window_onload
</SCRIPT>

<SCRIPT for=window eventname="onload">window.onload = function() { window_onload(); }</SCRIPT>

<META content="MSHTML 6.00.5730.13" name=GENERATOR></HEAD>
<BODY style="OVERFLOW: auto" persistentuniqueid="compUid_1" nextelementuniquenumber="4" elementtype="21" edaconnectionrequired="true">
<SELECT id=listbox1 style="Z-INDEX: 1; LEFT: 21px; WIDTH: 100px; POSITION: absolute; TOP: 18px" tabIndex=1 size=3 persistentuniqueid="compUid_2" defaultselection="1" name="listbox1"></SELECT> 
<SELECT id=listbox2 style="Z-INDEX: 2; LEFT: 149px; WIDTH: 100px; POSITION: absolute; TOP: 18px" tabIndex=2 size=3 persistentuniqueid="compUid_3" defaultselection="1" name="listbox2"></SELECT> 
<SELECT id=listbox3 style="Z-INDEX: 3; LEFT: 280px; WIDTH: 100px; POSITION: absolute; TOP: 18px" tabIndex=3 size=3 persistentuniqueid="compUid_4" defaultselection="1" name="listbox3"></SELECT>   
<INPUT id=layoutinfo style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden>
<INPUT id=ibiapp_app style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden value="alban general ibisamp baseapp" name="ibiapp_app">
<xml id=focus_xmlelement>
<script type="text/xml">
	
<rootxmlnode top="50" left="10" width="150" height="130">
		
<variables></variables>
		
<input_controls>
			
<input_control bindcontrolid="compUid_2" elementtype="9" name="listbox1" id="listbox1" multiple="0" top="20" left="30" width="60" height="20" inbinding="1" unresolved="0" onetimepopulated="0">
				
<link linktype="default">
					
<condition default="1" name="Default" whattodowithcontrol="0" valuescompareoperator="0" parameterscompareoperator="0" conditionmultiselectoperator="0">
						
<data_info sourcetype="typeMaster" datatype="1" selectedvalue="" ibiapp_app="alban general ibisamp baseapp" datasource="car.mas" displayfield="CAR.ORIGIN.COUNTRY" ibiformat="A10" dfformat="A10" datafield="CAR.ORIGIN.COUNTRY" modifiedrequest="0" addalloption="1" dynalldisplayvalue="ALL">
							
<![CDATA[TABLE FILE car
SUM FST.CAR.ORIGIN.COUNTRY
 BY CAR.ORIGIN.COUNTRY
-*insert_filters_here
ON TABLE PCHOLD FORMAT XML
END
]]></data_info></condition></link></input_control>
			
<input_control bindcontrolid="compUid_3" elementtype="9" name="listbox2" id="listbox2" multiple="0" top="20" left="140" width="60" height="20" inbinding="1" unresolved="0" onetimepopulated="0">
				
<link linktype="default" from="compUid_2">
					
<condition default="1" name="Default" whattodowithcontrol="0" valuescompareoperator="0" parameterscompareoperator="0" conditionmultiselectoperator="0" resolveparameterfq="CAR.ORIGIN.COUNTRY" resolveparameter="COUNTRY">
						
<data_info sourcetype="typeMaster" datatype="1" selectedvalue="" ibiapp_app="alban general ibisamp baseapp" datasource="car.mas" displayfield="CAR.COMP.CAR" ibiformat="A16" dfformat="A16" datafield="CAR.COMP.CAR" modifiedrequest="0">
							
<![CDATA[TABLE FILE car
SUM FST.CAR.COMP.CAR
 BY CAR.COMP.CAR
-*insert_filters_here
ON TABLE PCHOLD FORMAT XML
END
]]></data_info></condition></link></input_control>
			
<input_control bindcontrolid="compUid_4" elementtype="9" name="listbox3" id="listbox3" multiple="0" top="20" left="250" width="60" height="20" inbinding="1" unresolved="0" onetimepopulated="0">
				
<link linktype="default" from="compUid_3">
					
<condition default="1" name="Default" whattodowithcontrol="0" valuescompareoperator="0" parameterscompareoperator="0" conditionmultiselectoperator="0" resolveparameterfq="CAR.COMP.CAR" resolveparameter="CAR">
						
<data_info sourcetype="typeMaster" datatype="1" selectedvalue="" ibiapp_app="alban general ibisamp baseapp" datasource="car.mas" displayfield="CAR.CARREC.MODEL" ibiformat="A24" dfformat="A24" datafield="CAR.CARREC.MODEL" modifiedrequest="0">
							
<![CDATA[TABLE FILE car
SUM FST.CAR.CARREC.MODEL
 BY CAR.CARREC.MODEL
-*insert_filters_here
ON TABLE PCHOLD FORMAT XML
END
]]></data_info></condition></link></input_control></input_controls>
		
<other_bound_objects></other_bound_objects>
		
<requests></requests></rootxmlnode></script>
</xml></BODY>
<SCRIPT id=IBI_loader type=text/javascript>
doBeforeLoad();
</SCRIPT>
</HTML>

Yes, this works! Thanks very much. I'll try to use this method with my Year-Month-Day chained data.

(I think only HTML Composer distinguishes between list boxes and dropdown lists - the only difference between them is the MULTIPLE tag)

I tried a chained three-dropdown box set of controls with embedded procedures and this does not work for me.

In the Country-Car-Model example above, what ties them together? The fact that the data comes from the same table for the three embedded procedures?

I have a chained Year-Month-Day set of controls and I have had no luck - embedded or external.

No, they can be from different procedures/tables just fine. I use that all the time.
Perhaps there's some difference between the procedures we perform to chain them that causes them to break for you?

What I did is this:

• Drag three listboxes into the page.
  
• Set the unique ID and name properties for each (not strictly necessary, but makes them much easier to find later on).
  
• Change to the 'Parameters' tab.
  
• Select the top-level list-box property and change it to "dynamic" + "embedded procedure".
  
• Select the master and fields. DO NOT modify the generated TABLE FILE command in the text area! That opens up a world of hurt.
  
• Go to the next level property and do the same.
  
• And again for the bottom-level property.
  
• Now select both the top-level property and the next property by ctrl-clicking them.
  
• "Chain" them.
  
• Click the chain-line between them to verify that their chaining properties are set correctly, modify if needed. Again, DO NOT change the TABLE FILE definitions.
  
• Repeat this for the mid-level and bottom-level properties.
  
• Save and run it to see if it works.
  

Doing that with the CAR file and the same fields, you should get the exact same HTML that I posted above. If you don't, it's probably of interest to figure out where the differences are. Unfortunately, HTML (or XML) is not very diff-friendly...

In some cases the generated content of an embedded procedure does not result in the output that you need:

• Often it is just the sort order that is wrong. Whatever you decide to do, DO NOT swap the SUM FST.xxx and BY yyy fields around in the generated TABLE FILE code - that's a sure-fire way to break it. It should not break in that case IMO, but IBI disagrees with me there.
  
• If you need extra WHERE clauses, in my experience adding them to the TABLE FILE body causes no end of grieve. It seems to work if you test it superficially, but if you pay attention you will find that often the chaining gets broken. For such cases we have found two solutions:
  - Use an external procedure. It works pretty much the same way as an embedded one, but is a bit trickier to get right.
  - Create a HOLD file before executing the -HTMLFORM and use that instead of the unfiltered master. You need to store the HOLD file somewhere in the path of the project, or the file selection dialog for the master file can't find it.
  

Thanks so much for reaching out of the screen and holding my hand while I pointed-and-clicked into nirvana!

Your steps worked in a test HTML Composter file.

Now I have to apply it to the real world, with filters.

Thank you!!!

External fexes with EXACTLY the same code did not work.

Adding exactly the same filter to the embedded procedure breaks the chaining of the third control.

Subsequently removing this filter from the three controls leaves the chaining broken!

The only difference between the html file with the added/removed filter and the (thankfully) backed-up version is:

<data_info checkForDuplicateValues="0" sourcetype="typeMaster" datatype="1" modifiedrequest="0" ibiapp_app="#" datasource="ifds_time_dim.mas" displayfield="IFDS_TIME_DIM.SEG01.CAL_DAYA" ibiformat="A2" dfformat="A2" datafield="IFDS_TIME_DIM.SEG01.CAL_DAYA">
<data_info checkForDuplicateValues="0" sourcetype="typeMaster" datatype="1" modifiedrequest="0" ibiapp_app="#" datasource="ifds_time_dim.mas" displayfield="IFDS_TIME_DIM.SEG01.CAL_MONTH_TEXT_EN" ibiformat="A2" dfformat="A9" datafield="IFDS_TIME_DIM.SEG01.CAL_MONTHA">
<data_info checkForDuplicateValues="0" sourcetype="typeMaster" datatype="1" modifiedrequest="0" ibiapp_app="#" datasource="IFDS_TIME_DIM.mas" displayfield="IFDS_TIME_DIM.SEG01.CAL_YEARA" ibiformat="A4" dfformat="A4" datafield="IFDS_TIME_DIM.SEG01.CAL_YEARA">

and

<data_info checkForDuplicateValues="0" sourcetype="typeMaster" datatype="1" modifiedrequest="1" ibiapp_app="#" datasource="ifds_time_dim.mas" displayfield="IFDS_TIME_DIM.SEG01.CAL_DAYA" ibiformat="A2" dfformat="A2" datafield="IFDS_TIME_DIM.SEG01.CAL_DAYA">
<data_info checkForDuplicateValues="0" sourcetype="typeMaster" datatype="1" modifiedrequest="1" ibiapp_app="#" datasource="ifds_time_dim.mas" displayfield="IFDS_TIME_DIM.SEG01.CAL_MONTH_TEXT_EN" ibiformat="A2" dfformat="A9" datafield="IFDS_TIME_DIM.SEG01.CAL_MONTHA">
<data_info checkForDuplicateValues="0" sourcetype="typeMaster" datatype="1" modifiedrequest="1" ibiapp_app="#" datasource="IFDS_TIME_DIM.mas" displayfield="IFDS_TIME_DIM.SEG01.CAL_YEARA" ibiformat="A4" dfformat="A4" datafield="IFDS_TIME_DIM.SEG01.CAL_YEARA">

modifiedrequest="0" versus modifiedrequest="1".

Of course I shouldn't even know about this because in the GUI World this is invisible to me.

I finally have this working.

I created a temp hold file in foccache and then CAREFULLY modified the code to change the table name in the HTML Composer file.

I don't know if I feel like closing this thread just yet...

Yeah, I think the process is a little more fragile than it should be, considering that we should be able to do this all using just the gui.

When using external procedures for population, you are responsible for coding all the WHEREs in the FEX for the chaining. Unless you use one procedure to populate multiple controls, which then you need 'cache runtime data' and we do the WHEREs dynamically.

This is all in the documentation.

Ah yes, I forgot about that. With external procedures you need to code lines like

WHERE COUNTRY EQ '&COUNTRY';

The &COUNTRY from above example is what gets supplied by the chaining code as a HTTP-parameter when the external procedure gets called (using a Javascript XmlHttpRequest()-call).
I'm not sure how we're supposed to figure out the names of those variables, but that's probably in de docs that David refers to.

I have been happily using external procedures in v7.6.* and when I moved to v7.7.* I noticed something changed for chained controls populated by external procedures: The higher-level controls had duplicate values - one for each of the values in the lower-level controls. In v7.6.* this did not happen. To fix this I had to set the Check for duplicate values checkbox.

Hi Francis,

Just a quick note: for your HTML Composer questions, since they are not easy how-to questions, please open a case for each individual issue to be properly addressed by programming and product management. The phone number is 1-800-736-6130, or access online at InfoResponse.

Cheers,

Kerry