Hi all,

I am creating a launch page with two listboxes that are chained together. The first in the chain is a REGION list and the second in the chain is a COUNTRY list.

Now, if I dynamically populate these listboxes by pointing them to the MS SQL Data Source I get a blank in the list because the columns they reference contain NULL values. Despite the blanks, each listbox is populated in it's own alphabetical order and the chaining works. So you might see the lists looking like...
(Sorry if the formatting doesn't look right. I tried to line these up )
REGION -- COUNTRY
China -- Argentina
Emerging Markets -- Australia
Europe -- Austria
Japan & Korea -- Belarus
... -- ...
and so on -- and so on

To eliminate the NULL values I created a procedure that selects the data for the lists. That looks like this...

  
SET ASNAMES = ON
TABLE FILE LOOKUPS
PRINT DST.SALES_REGION
DST.SALES_CNTRY
BY SALES_REGION
BY SALES_CNTRY
WHERE SALES_REGION NE '';
WHERE SALES_CNTRY NE '';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD FORMAT XML
END

While this has eliminated the blanks I now have a different problem. The COUNTRY list is sorted according to it's grouping within REGION values. So now the lists look like this...
REGION -- COUNTRY
China -- China (belongs to China Region)
Emerging Markets -- Australia (belongs to Emerging Markets Region)
Europe -- Botswana (belongs to Emerging Markets Region)
Japan & Korea -- India (belongs to Emerging Markets Region)
North America -- ... (skipping a bunch of list items to demonstrate the point)
Other -- Austria (belongs to the Europe Region)
South America -- Belarus (belongs to the Europe Region)
... -- Belgium (belongs to the Europe Region)
and so on -- and so on

This obviously is not user-friendly if I want to select a specific COUNTRY. Especially if I don't know what REGION it belongs to. Now I have to 'search' through the COUNTRY listbox instead of jump to the starting letter(s) of the specific country.

So, my question is this - is it possible to either 1) eliminate the blank when the parameter is populated directly from the MS SQL Data Source or 2) change the sort order of the COUNTRY listbox after it has retrieved its values?

P.S. - The issue of the blank item in the listbox does not seem to happen if the data source is FOCUS but I'm not 100% sure of that. It seems like the issue is only when I use MS SQL Data Sources.

Thanks for any light you can shed on this!

Best Regards,

Dan

Dan,

1. Yes, try adding a predicate that omits null values

2. Within Javascript, yes. However I would control it before it gets there because it's a lot easier.

Treat each fex for each combo differently. Something like -

-* Region fex
TABLE FILE LOOKUPS
  SUM FST.SALES_REGION
   BY SALES_REGION
WHERE SALES_REGION NE ''
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD FORMAT XML
END

-* Country fex
-DEFAULT &Region = 'FOC_NONE'
-* This variable will be the NAME attribute of your first combo
TABLE FILE LOOKUPS
  SUM FST.SALES_CNTRY
   BY SALES_CNTRY
WHERE SALES_REGION EQ '&Region'
WHERE SALES_CNTRY  NE ''
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD FORMAT XML
END

and you should be cookin'

T

Tony,

Very clever. I did not realize you could pass a variable to a lookup procedure like that.

I'm still having a challenge. When I change the NAME attribute to the first listbox to &Region the PARAMETER for the control becomes Unassigned. If I set the parameter back to the Region field then the NAME attribute is reset. Seems like it wants to be one way or the other but not both. When it is unassigned I get 'An Element Is Missing An Argument'.

By populating the listboxes with separate procedures I also get this behavior: When the REGION listbox has ALL selected I see the COUNTRY list. As soon as I select a specific REGION, the COUNTRY list goes blank.

Thanks again,

Dan

Dan,

When you have ALL selected in Region, the value of FOC_NONE is passed to the subsequent retreivals in the chain, so the where clause becomes WHERE SALES_REGION EQ 'FOC_NONE' which is effectively ignored in the execution so you get all country values.

When you select a region value then that should be passed. The question is what value is being passed?

If you run the first fex interactively you should get two values for each XML node. Check that these are correct and then substitute one for the &Region value in the subsequent fex to see what is returned. My guess is that you have some values in there that are not valid?

T

Tony,

On their own, your lookup procedures are working perfectly. Just to be sure there wasn't an issue with any of the &Region values I ran the second one and pasted in each Region value from the first. Each time I got the proper response.

At least I've narrowed it down to the HTML Launch Page and the chaining. I'll play with the listbox controls and see if I can figure out why it won't work. Perhaps some characters (single quotes or something) are added to the value when it is passed to &Region.

Stay tuned...

Dan

Dan,

One way I check for what parameter value is being passed, is to run a standard fex that I have permanently set up called "showvars.fex". All this fex contains is "-? &". I change the launched fex and then just make the selection in the first combo in the chain, and run the report. The output will show me what value is being passed throughout the chaining. Compare this to the output of just running the combo population fex and the value should be the same. If it isn't then ...........

T

Tony

I understand from your note that we can easily debug dropdown procedures , I mean what values are passing to the other dropdown.

Could you please tell what the "showvars.fex" contains...so that i can debug

Thanks,
Saikumar.

Are you doing this in Dev Studio HTML Layout Painter? If you are, you can set the sort...however there is a bug where the second and subsequent sorts in a chain (like in your case) will still sort eroneousely (ie. sort within region). This will be fixed in HF 4 or ask them for the ibirls3.js file - refer to case 60532021...

I have it and it works great.

Saikumar,
The contents are given in my previous post -

quote:

"showvars.fex". All this fex contains is "-? &".

T