I have a launch page that has 3 dropdown lists populated from a single table in a database. They are chained together with the value selected in list A(user_id) determining what is in B(projects) which determines the available values for C(charge_codes). The problem is that they load very slowly. What would be the best solution for doing this? Thanks in Advance.

How are you populating your dropdown lists?

If you are using WebFocus each time a selection is made from a higher level list then you need to go back to the server each time which causes an overhead.

If you generate dynamic Javascript you will see an improvement once the script is generated although the initial generation time will be longer.

Naturally the volumes involved will dictate if is this is a viable option.

Below is an example based on the car file that I first mentioned at the beginning of the year.


-*
-* SET DEFAULT VALUES for required amper variables
-*
-DEFAULT &step=' '
-DEFAULT &COUNTRY='$*'
-DEFAULT &CAR='$*'
-DEFAULT &MODEL='$*'
-*
-* Check if your runnging the actual report of generating the dropdowns
-* and branch to the report if needed.
-*
-IF &step EQ 'runreport' GOTO runreport;
-*
SET HOLDLIST=PRINTONLY
SET PAGE=NOPAGE
-*
-* generate the first dropdown list as a standard option tag/value pair
-* save the output in ALPHA format
-*
DEFINE FILE CAR
OPT1/A100='' | COUNTRY || '';
END
TABLE FILE CAR
SUM
OPT1
BY
COUNTRY NOPRINT
ON TABLE SAVE AS CLIST1 FORMAT ALPHA
END
-RUN
-*
-* Generate the javascript code required for the 2nd dropdown
-* this is a two stage process. 1st extract the values sorted by the
-* value for the first dropdown and 2nd reformat the output using a define
-* to add the required Javascript syntax.
-* pay special attention to the labels and the subhead. The DF ('---- SELECT ----')
-* is very important for the onchange function used in the HTML
-* IMPORTANT save the output in WP format. This is required to ensure that the subhead and subfoot
-* is saved to the output file
-*
TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
FILEDEF CLIST2 DISK .\CLIST2.FTM
-RUN
DEFINE FILE HOLD1
DQ/A100='"' || COUNTRY || '"';
DF/A100='Select2.options[Select2.options.length] = new Option(' | '''' || '---- SELECT ----' || '''' || ');' ;
OPT2/A100='Select2.options[Select2.options.length] = new Option(' | '''' || CAR || '''' || ');' ;
END
TABLE FILE HOLD1
PRINT OPT2 AS ''
BY COUNTRY NOPRINT
BY CAR NOPRINT
ON COUNTRY SUBHEAD
"if (o == "{"
"ON COUNTRY SUBFOOT
"}"
ON TABLE HOLD AS CLIST2 FORMAT WP
END
-*
-* Generate the javascript code required for the 3rd dropdown
-* this is a two stage process. 1st extract the values sorted by the
-* value for the first dropdown and 2nd reformat the output using a define
-* to add the required Javascript syntax.
-* pay special attention to the labels and the subhead. The DF ('---- SELECT ----')
-* is very important for the onchange function used in the HTML
-* IMPORTANT save the output in WP format. This is required to ensure that the subhead and subfoot
-* is saved to the output file
-*
FILEDEF CLIST3 DISK .\CLIST3.FTM
-RUN
TABLE FILE CAR
PRINT MODEL
BY COUNTRY NOPRINT
BY CAR
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
END
DEFINE FILE HOLD2
DQ/A100='"' || CAR || '"';
DF/A100='Select3.options[Select3.options.length] = new Option(' | '''' || '---- SELECT ----' || '''' || ');' ;
OPT3/A100='Select3.options[Select3.options.length] = new Option(' | '''' || MODEL || '''' || ');' ;
END
TABLEF FILE HOLD2
PRINT OPT3 AS ''
BY CAR NOPRINT
BY MODEL NOPRINT
ON CAR SUBHEAD
"if (o == "{"
"ON CAR SUBFOOT
"}"
ON TABLE SAVE AS CLIST3 FORMAT WP
END
-RUN
-*
-* create your selection menu as a WebFocus HTMLFORM
-*
-*
-runform
-HTMLFORM BEGIN



-*
-* Script to run the report after the final selection
-*
<script language="JavaScript">
< !--
function getProds(whereto)
{
window.status = 'Working Please Wait';
form1.step.value = ""+whereto
document.form1.submit();
}
// -->

-*
-* Script which changes the windows message at the bottom of the Browser
-*
<script language="JavaScript">
function wStatus()
{
window.status = 'Screen Ready';
}

-*
-* Script for the 2nd dropdown box which is populated from the second extract at run time
-*
<script language="javascript">
function Select2Options(o)
{
var Select2 = document.form1.Select2;
Select2.options.length = 0;
!IBI.FIL.CLIST2;
}

-*
-* Script for the 3rd dropdown box which is populated from the second extract at run time
-* this script contains extra code to empty the 3rd dropdown if you change your selection
-* from the first dropdown
-*
<script language="javascript">
function Select3Options(o)
{
var Select3 = document.form1.Select3;
Select3.options.length = 0;
if (o == "NULL" )
{
Select3.options[Select3.options.length] = new Option(' ');
}
!IBI.FIL.CLIST3;
}



-*
-* Change the URL to match your webfocus server
-*

TARGET="_top">

Select Country:

-*
-* The 1st dropdown the onchange action runs the 2nd dropdown script and populates the 2nd dropdown box
-* it also runs the 3rd dropdown script and resets the 3rd dropdown box so that it is empty
-*
onchange="Select2Options(document.form1.COUNTRY.options[document.form1.COUNTRY.selectedIndex].text);
Select3Options('NULL');">
---- SELECT ----
!IBI.FIL.CLIST1;


-*
-* The 2nd dropdown this is populated by the action of the 1st dropdown
-* The onchange action runs the 3rd dropdown script and populates the 3rd dropdown box
-*

Select Car:

-*
-* The 3rd dropdown box his is populated by the action of the 2nd dropdown or reset if the 1st dropdown is changed
-* The onchange action runs the final report by calling IBI_ex and passing it the parameter
-* needed to bypass the script
-* processing and branch to the actual report code
-*

Select Model:

-HTMLFORM END
-EXIT
-*
-runreport
-*
-* run the report
-* YOUR REPORT CODE FOLLOWS
-*
TABLE FILE CAR
PRINT COUNTRY CAR MODEL DEALER_COST RETAIL_COST
WHERE COUNTRY EQ '&COUNTRY'
WHERE CAR EQ '&Select2'
WHERE MODEL EQ '&Select3'
END

This message has been edited. Last edited by: <Mabel>,

I'm chaining some drop downs also (2 or 3 on a screen). My goal is to use Resource Layout as much as possible without customizing the generated HTML or using JavaScript. This is intended to simplify future maintenance of our UI's.

I generate a hold file using ReportCaster and read this file to avoid hitting the database on each drop down. I was expecting a performance improvement, but this is not the case. I was told by several experts it was the way to go. I am relatively new to WF so I would appreciate any hints or pointers to improve the performance or efficiency of the code.

Thanks!

Here is the code which populates the file:

-*------------------------------------------------
-* RCTRACKS.FEX
-* This process drops a data file daily
-* (Using ReportCaster)
-* for my track pick list call PL_TRACK
- Result is: pl_track.foc and pl_track.mas
-*------------------------------------------------
APP HOLD TICKETOF
-*
TABLE FILE TIX_ESTABLISHMENTS
PRINT
FST.ESTABLISHMENTFULLNAME
BY ESTABLISHMENTKEY
WHERE ESTABLISHMENTTYPE EQ 'T'
ON TABLE NOTOTAL
ON TABLE HOLD AS PL_TRACK FORMAT XML
END
-RUN

Below is my procedure which dynamically populates
the pick list.

-*---------------------------------------------
-* POPTRACK.FEX:
-* This is the file which populates the pick list
-* using the XML formated data from the above
-* program RCTRACKS.FEX
-*---------------------------------------------
APP HOLD TICKETOF
-*
TABLEF FILE PL_TRACK
PRINT
ESTABLISHMENTKEY
ESTABLISHMENTFULLNAME
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XML
END

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

gypsy,

Even if you use a hold file when you use pure WebFocus to generate subsequent dropdowns it means a server call. That is an overhead.

Resource Layout painter is very heavy and delivers almost unreadable and un-modifiable code.

Using dynamic JavaScript does not mean extra maintenance, is readable and can be supported more easily.

WebFocus is a great product, but to get the best from it, it needs to be blended with other methods.

That has always been the rule ever since Focus first appeared over 30 years ago.

The developers keep enhancing but users always want more.

Hmmm, that's interesting, I was advised by two on-site IBI consultants that this would be a significantly faster method of populating the pick lists.

Just a follow up...

IBI Tech Support says that the number of items in my drop down is causing the performance hit. 3 chained drop downs with total of more than 2200 options between them.

gypsy,

Have you considered a progressive HTML form where each change on the select builds the next form? (Hmm... doesn't sound quite right but hopefully you'll understand what I mean?). That way you don't need to load all the possible values in each of the selects on your single form and chaining isn't an issue. It is also basically what the chaining achieves in a single form anyway.

Edited: In fact just like JG suggested above

For information the chaining is achieved by executing an adhoc fex (which is built in javascript) with the value of the parent select being used to provide the resultant XML output. This is then passed to an activeX object that renders the options into the child select. Now you can see why it can take so long!!

I would analyse the selects and the possible combinations to determine the best method of population. 2200 options could give rise to thousands of permutations. e.g if the options are split 200, 800, 1200 in the selects that would give you a possible 192 million combinations.

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

Thanks for your suggestions and comments. I took out the flat file functionality and returned it to real time database queries. They are a little slower but easier to maintain.

Reposted code due to corruption. Again

-*
-* Create a focexec called dynamicdropdown.fex and paste in the following code.
-*
-* SET DEFAULT VALUES for required  amper variables
-*
-DEFAULT &step=' '
-DEFAULT &COUNTRY='$*'
-DEFAULT &CAR='$*'
-DEFAULT &MODEL='$*'
-*
-* Check if your runnging the actual report of generating the dropdowns
-* and branch to the report if needed.
-*
-IF &step EQ 'runreport' GOTO runreport;
-*
SET HOLDLIST=PRINTONLY
SET PAGE=NOPAGE
-*
-* generate the first dropdown list as a standard option tag/value pair
-* save the output in ALPHA format
-*
DEFINE FILE CAR
OPT1/A100='<OPTION>' | COUNTRY || '</OPTION>';
END
TABLE FILE CAR
SUM
    OPT1
BY
     COUNTRY NOPRINT
ON TABLE SAVE AS CLIST1 FORMAT ALPHA
END
-RUN
-*
-* Generate the javascript code required for the 2nd dropdown
-* this is a two stage process. 1st extract the values sorted by the 
-* value for the first dropdown and 2nd reformat the output using a define
-* to add the required Javascript syntax.
-* pay special attention to the labels and the subhead. The DF ('---- SELECT ----')
-* is very important for the onchange function used in the HTML
-* IMPORTANT save the output in WP format. This is required to ensure that the subhead and subfoot
-* is saved to the output file
-*
TABLE FILE CAR
PRINT CAR
BY COUNTRY
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
FILEDEF CLIST2 DISK .\CLIST2.FTM
-RUN
DEFINE FILE HOLD1
DQ/A100='"' || COUNTRY || '"';
DF/A100='Select2.options[Select2.options.length] = new Option(' | '''' || '---- SELECT ----' || '''' || ');' ;
OPT2/A100='Select2.options[Select2.options.length] = new Option(' | '''' || CAR || '''' || ');' ;
END
TABLE FILE HOLD1
PRINT OPT2 AS ''
BY COUNTRY NOPRINT
BY CAR NOPRINT
ON COUNTRY SUBHEAD
"if (o == <DQ )"
"{"
"<DF"
ON COUNTRY SUBFOOT
"}"
ON TABLE HOLD AS CLIST2 FORMAT WP
END
-*
-* Generate the javascript code required for the 3rd dropdown
-* this is a two stage process. 1st extract the values sorted by the 
-* value for the first dropdown and 2nd reformat the output using a define
-* to add the required Javascript syntax.
-* pay special attention to the labels and the subhead. The DF ('---- SELECT ----')
-* is very important for the onchange function used in the HTML
-* IMPORTANT save the output in WP format. This is required to ensure that the subhead and subfoot
-* is saved to the output file
-*
FILEDEF CLIST3 DISK .\CLIST3.FTM
-RUN
TABLE FILE CAR
PRINT MODEL
BY COUNTRY NOPRINT
BY CAR
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
END
DEFINE FILE HOLD2
DQ/A100='"' || CAR || '"';
DF/A100='Select3.options[Select3.options.length] = new Option(' | '''' || '---- SELECT ----' || '''' || ');' ;
OPT3/A100='Select3.options[Select3.options.length] = new Option(' | '''' || MODEL || '''' || ');' ;
END
TABLEF FILE HOLD2
PRINT OPT3 AS ''
BY CAR NOPRINT
BY MODEL NOPRINT
ON CAR SUBHEAD
"if (o == <DQ )"
"{"
"<DF"
ON CAR SUBFOOT
"}"
ON TABLE SAVE AS CLIST3 FORMAT WP
END
-RUN
-*
-* create your selection menu as a WebFocus HTMLFORM
-*
-*
-runform
-HTMLFORM BEGIN
  <HTML>
  <HEAD>
  <TITLE>Choose Model</TITLE>
-*
-* Script to run the report after the final selection
-*
<script language="JavaScript">
  <!--
function getProds(whereto)
  {
    window.status = 'Working Please Wait';
    form1.step.value = ""+whereto
    document.form1.submit();
  }
  // -->
</script>
-*
-* Script which changes the windows message at the bottom of the Browser
-*
<script language="JavaScript">
    function wStatus()
  {
    window.status = 'Screen Ready';
  }
</script>
-*
-* Script for the 2nd dropdown box which is populated from the second extract at run time
-*
<script language="javascript">
 function Select2Options(o)
  {
  var Select2 = document.form1.Select2;
  Select2.options.length = 0;
  !IBI.FIL.CLIST2;
  }
</script>
-*
-* Script for the 3rd dropdown box which is populated from the second extract at run time
-* this script contains extra code to empty the 3rd dropdown if you change your selection
-* from the first dropdown
-*
<script language="javascript">
 function Select3Options(o)
  {
  var Select3 = document.form1.Select3;
  Select3.options.length = 0;
  if (o == "NULL" )
  {
  Select3.options[Select3.options.length] = new Option(' ');
  }
  !IBI.FIL.CLIST3;
  }
</script>
</HEAD>
<BODY onLoad="wStatus()">
  <FORM  NAME=form1 ACTION="http://localhost/ibi_apps/WFServlet" METHOD="GET"
  TARGET="_top">
  <INPUT TYPE="hidden" NAME="IBIF_ex" VALUE="dynamicdropdown">
  <INPUT TYPE="hidden" NAME="step" VALUE="STEP_4">
  <P><H4><I><font color="6A5ACD">Select Country:</font></I></H4>
-*
-* The 1st dropdown the onchange action runs the 2nd dropdown script and populates the 2nd dropdown box
-* it also runs the 3rd dropdown script and resets the 3rd dropdown box so that it is empty
-*  
  <SELECT  WIDTH="300" STYLE="width: 200px" NAME="COUNTRY" onchange="Select2Options(document.form1.COUNTRY.options[document.form1.COUNTRY.selectedIndex].text);Select3Options('NULL');">
  <OPTION VALUE='$*'>---- SELECT ----
!IBI.FIL.CLIST1;
  </SELECT><BR>
-*
-* The 2nd dropdown this is populated by the action of the 1st dropdown
-* The onchange action runs the 3rd dropdown script and populates the 3rd dropdown box
-*
  <H4><I><font color="6A5ACD"> Select Car:</font></I></H4>
  <SELECT WIDTH="300" STYLE="width: 200px" NAME="Select2" onchange="Select3Options(document.form1.Select2.options[document.form1.Select2.selectedIndex].text);">
  <OPTION VALUE='$*'>
 </SELECT><BR><BR>
-*
-* The 3rd dropdown box his is populated by the action of the 2nd dropdown or reset if the 1st dropdown is changed
-* The onchange action runs the final report by calling IBI_ex and passing it the parameter needed to bypass the script
-* processing and branch to the actual report code
-*
  <H4><I><font color="6A5ACD">  Select Model:</font></I></H4>
  <SELECT WIDTH="300" STYLE="width: 200px" NAME="Select3" onChange="getProds('runreport');">
     <OPTION VALUE='$*'>
 </SELECT><BR><BR>

<BR><BR>
    </FORM>
  </BODY>
  </HTML>
-HTMLFORM END
-EXIT
-*
-runreport
-*
-* run the report
-* YOUR REPORT CODE FOLLOWS
-*
TABLE FILE CAR
PRINT COUNTRY CAR MODEL DEALER_COST RETAIL_COST
WHERE COUNTRY EQ '&COUNTRY'
WHERE CAR EQ '&Select2'
WHERE MODEL EQ '&Select3'
END

This message has been edited. Last edited by: <JG>,

Guys, The while WebFOCUS can retrieve values from a database directly using a Master File Descripition the Key is to insure that WebFOCUS is creating the Best SQL it can going against the source. There are several things that a developer can do, here are two: 1) If you want to use the Master File Directly, the best thing to do if possible is to create a summary lookup table. If this is not an option the second. 2) Is to use a procedure to populate the dropdowns using a PCHOLD FORMAT XML. When you use this option be sure to include the WHERE(S). Use DST.fieldname with a BY statement. Also use SQL Tracing options to verify the SQL and run the procedure before attaching it to the combobox. I was at a customer site where they were running into this same performance concerns because of the size of the table. Using option 2 for them worked the best.

Hope this helps

JG,
I tried you code. It seems to produce the
CLIST1, CLIST2 AND CLIST3 files OK but it just hangs when it goes into the HTML logic. The screen is blank. Do you have any suggestions.

Yes, Stop Mabel from editing posts.

This is the second time she has edited this post
and this is the second time that she has corrupted it by removing the leading code tag.

I've fixed it again so try and copy it now (second version not the first). But if Mabel gets to it before you, send me a mail address via a private message and I'll send the code directly to you.

JG

This message has been edited. Last edited by: <JG>,

Well we're making some progress. At least I can see the three dropdown boxes with ---SELECT --- in the Country box without any countries populated. The other two boxes are not populated as well. I split off the code from the beginning to the HTMLPAGE BEGIN into a fex entitled dynamicdropdown. Any suggestions?

Jerry,

J.G's entire code is required to be placed into the fex called dynamicdropdown.fex for it to function correctly.

T

Thanks Tony, It's a complete standalone.

To use it as a driver system add an extra parameter such as &REPORT,
to either the final select or the getProds script (where &REPORT is
the name of the actual report fex) and substitute

TABLE FILE CAR
PRINT COUNTRY CAR MODEL DEALER_COST RETAIL_COST
WHERE COUNTRY EQ '&COUNTRY'
WHERE CAR EQ '&Select2'
WHERE MODEL EQ '&Select3'
END

with

-INCLUDE &REPORT
(also add -DEFAULTS &REPORT=' ' to the begining of the code to avoid problems)

Or just hardwire it as -INCLUDE REPORTNAME

That seemed to work. Thanks very much for your help.

Hi All,

I was alerted to edit the post because it was extended beyond its range. Since editing the post, I realized that the code is distorted. Some have alerted me it was hard to view the posts.

I apologize for editing the posts and distorting it. I hope without editing the posts, it can still be fitted in the right view range.

Thanks Mabel,

In the good old days when WebFocus and Focus could only read an 80 character
card image it would never have been an issue and and was a real pain on occasions.

But you are quite right I could have wrapped it, my excuse is I use a 19" monitor
at high rez and so do not notice.

Hey JG,

When the eyes get bad, get glasses, not a bigger monitor lol

T (currently in Dallas)

Hope it's raining,

The eyes went years ago. Far to much time spent debugging Focus/WebFocus code,
when I could have being doing something much more useful, like raising chickens or
collecting stamps.

Hi JG,

I know what you mean about the eyes mine too. I now have to move the screen further away

Sorry to disappoint you about the rain, it is cooler today (around 76 ) but we've only had a few spots. Storm forecast for tonight though!

T

quote:

Originally posted by JG:
Reposted code due to corruption. Again

<script language="javascript">
 function Select2Options(o)
  {
  var Select2 = document.form1.Select2;
  Select2.options.length = 0;
  !IBI.FIL.CLIST2;
  }
</script>
END

Hi,

hi, i want to have a drop down with names, but when a name is selected, pass that user's Number ID to my focexec.

how can i do it?

Thanks

Nevermind, mgrackin has already helped me with this (he's the best)

DEFINE FILE SOMETHING
OPT1/A100='<OPTION VALUE="' | ID | '">' | NAME || '</OPTION>';
END
TABLE FILE SOMETHING
SUM
    OPT1
BY
     NAME NOPRINT

ON TABLE SAVE AS LIST1 FORMAT ALPHA
END