Hi all,

Does anyone know of a way to move the 'ALL' option of a dynamically populated list for a drop-down control down the list that is generated? I don't want multi-select. I want to retain single-selection but start the filter on any of the options populated first but still have the 'ALL' as an option that they could select after the initial refresh and load of my HTML page content.

Has anyone ever sought to do this and found a solution for such?

Any help is much appreciated! Thanks!

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

Could you set the default to a different value? That way the list will always show that value instead of All.

Populate with a fex:

-DEFAULTS &INSTANCE = 'PROD'
ENGINE SQLORA SET DEFAULT_CONNECTION &INSTANCE.EVAL
SQL SQLORA PREPARE SQLOUTX1 FOR
SELECT DISTINCT
PTRBCAT_CODE,
PTRBCAT_DESC
FROM PTRBCAT
;
END
DEFINE FILE SQLOUTX1
SORT/A1 = '1';
PTRBCAT_CODE/A8 = 'FOC_NONE';
BCAT_CD_DESC/A130 = 'ALL';
END
TABLE FILE SQLOUTX1
SUM
PTRBCAT_DESC NOPRINT
BCAT_CD_DESC
BY SORT
BY PTRBCAT_CODE
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
DEFINE FILE SQLOUTX1
SORT/A1 = '2';
PTRBCAT_CODE/A8 = PTRBCAT_CODE;
BCAT_CD_DESC/A130 = PTRBCAT_CODE || (' - ' | PTRBCAT_DESC);
END
TABLE FILE SQLOUTX1
SUM
BCAT_CD_DESC
BY SORT
BY PTRBCAT_CODE
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
END
-RUN
TABLE FILE HOLD2
PRINT
BCAT_CD_DESC
BY HIGHEST SORT NOPRINT
BY PTRBCAT_CODE
ON TABLE PCHOLD FORMAT XML
MORE
FILE HOLD1
END
-RUN

BabakNYC,

Sorry. I should of made things more clear.

So what I have are a series of chained single-select drop-downs as follows:

Division
District (populates based on division selection)
Banner (populates based on district selection)
Store (populates based on banner selection)

Division, district, and banner all have ALL as the default selection upon first load of the page. That's what I want. For the store drop-down however, I still want the ALL option, but for it to be somewhere else in the list of options other than as the default value selected upon load. If I get rid of it, only one store will be selected upon load, which is great, but then there is no ALL option any longer. End goal is I want all stores for all divisions, etc. to be visible within the store drop-down upon load, but only one store selected at first, but still have the ALL option somewhere in the list, just not as the default.

Any ideas?

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

Add No Selection(HTML) instead of All and add -DEFAULTS &3RD_COMBO_DROPDOWN = FOC_NONE or _FOC_NULL to the fex, whichever one you are using....

Tom,

So, you are taking all distinct values for category codes and descriptions from your Oracle source; then creating a request using that data set and adding DEFINEs for a sort value, a new code and description and then holding the sort value, new code, and new description in your first hold; then creating a new request from your Oracle source, with another set of DEFINEd fields for a new sort, assigning the original list of codes to itself, and a description field that has those codes and their descriptions tied together (like I've done similarly in mine), then holding the secondly defined sort value, the original list of codes, and the concatenated list of both in a second hold. Finally, you begin with the second hold, and print the concatenated list from such for the display values, and the codes for the return values, and then append the first hold output to the end of the list? So the MORE command comes after the XML line, eh? Time to try this sucker out! hahaha Thanks Tom!

Will post my findings/success/failure here as soon as I can.

Tom,

In regards to your other post:

So, use a No Selection option in my HTML (got that part), and then add a -DEFAULTS &....whah?? So what does doing this do for me again? Sorry. Trying my hardest to follow along.

Then you won't see ALL, and, if they run without selecting a store, they'll get ALL stores, selecting a store, will get that store.

Assuming you have a WHERE statement in the calling fex:
-IF &STORE EQ 'FOC_NONE' GOTO SKIP_STORE;
WHERE STORE EQ "&STORE';
-SKIP_STORE

Here's my XML population request as is currently (if this helps):

DEFINE FILE AFSTEMP
ACCNO/I5=DSACCTNO;
DSACCTNAME/A40=EDIT(ACCNO)|'-'|DSNAME;
END
TABLE FILE AFSTEMP
SUM
MAX.DSACCTNAME
BY DSACCTNO
WHERE (DSDIVISION EQ &DSDIVISION);
WHERE (DSDISTRICT EQ &DSDISTRICT);
WHERE (DSBANNERGL EQ &DSBANNERGL);
WHERE (DSACCTNO   NE 0);
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
BY DSACCTNO
BY DSACCTNAME
ON TABLE PCHOLD FORMAT XML
END
-RUN

When they hit the run button, you'll need this in called fex:

-DEFAULTS &STORE = 'FOC_NONE'
TABLE FILE WHATEVER
SUM/PRINT
ETC.
WHERE (DSDIVISION EQ &DSDIVISION);
WHERE (DSDISTRICT EQ &DSDISTRICT);
WHERE (DSBANNERGL EQ &DSBANNERGL);
-IF &STORE EQ 'FOC_NONE' GOTO SKIP_STORE;
WHERE STORE EQ "&STORE';
-SKIP_STORE

Tom's suggestion is probably the cleanest. Just remember to make sure that the input is "Not Required". This is a property for the HTML.

In the past, whenever I needed to add a value that didn't exist in the table to a drop down, I would use "MORE". One table file would just be a request against car, where I would create defines/computes as needed that contained the value I wanted to show.

Here is what the help file says about more:

quote:

The MORE phrase, which is accessible within the TABLE and MATCH commands, specifies how to concatenate data from sources with dissimilar Master Files.

{TABLE|MATCH} FILE file1main request
MORE
FILE file2
subrequest
MORE
FILE file3
subrequest
MORE
.
.
.
{END|RUN}
where:

TABLE|MATCH
Begins the request that concatenates the data sources.

file1
Is the name of the first data source.

main request
Is a request, without END or RUN, that retrieves the first data source and defines the data fields, sorting criteria, and output format for all data. WHERE and IF criteria in the main request apply only to file1.

When concatenating files within the TABLE command, you can also define calculated values for the first data source.

MORE
Begins a subrequest. There is no limit to the number of subrequests, other than available memory.

FILE file2
Defines file2 as the second data source for concatenation.

subrequest
Is a subrequest. Subrequests can only include WHERE and IF phrases.

END|RUN
Ends the request.

quote:

Originally posted by Tom Flynn:
-IF &STORE EQ 'FOC_NONE' GOTO SKIP_STORE;
WHERE STORE EQ "&STORE';
-SKIP_STORE

Tom, I'm confused. Why add the goto? It seems unnecesary as "FOC_NONE" will ignore the filter anyways. Although judging from when I remember CoolGuy joining the forums, he probably has the current standard of '_FOC_NULL' (FOC_NONE I think is from version 7.6.x and older) that he should be using.

Because it's in quotes, and, 8.07 processed it...

hmm, strange. I don't have that issue in 8.0.08.... whats the ignore value set to in the Administration Console? Is it FOC_NONE for you? Default since at least 7703 has been _FOC_NULL. If its different for you in 8007, someone would have had to intentionally changed it.

A little JQuery might do the trick:

// Get first option, which is "ALL"
var all = $("#combobox1 option:first");

// Move it to the bottom of the option list
$("#combobox1").append(all);

// Moving it does not deselect it,
// so we must select the first option
// (which is no longer "ALL")
var select = $("#combobox1 option:first");
$("#combobox1").val(select.val());

The only catch is I don't know when this code should fire. I would try putting it in the "After Load" event for the dropdown (In my example, "combobox1").

Right, 500 legacy programs and we're going to change code to _FOC_NULL??? Don't think so...
In the HTML properties Document section, we set Ignore Filters to FOC_NONE so they wouldn't break...
Ridiculous change...

_FOC_NULL in quotes is fine, as usual.

Try this:

TABLE FILE CAR
PRINT *
WHERE COUNTRY EQ 'FOC_NONE';
END
-EXIT

Pick your poison...

This message has been edited. Last edited by: Tom Flynn,

My suggestion works for static dropdowns in Chrome and Firefox, but not IE 9 (The "ALL" does get moved to the bottom in IE 9, but there is no default selection visible after that happens). I don't have access to IE browsers above 9 at the moment...

And the code does need to go in the dropdown's "After Load" event.

Tom,

So, here's my attempt to replicate what you were doing with my own data source:

-DEFAULTH &DSDIVISION = _FOC_NULL;
-DEFAULTH &DSDISTRICT = _FOC_NULL;
-DEFAULTH &DSBANNERGL = _FOC_NULL;

DEFINE FILE AFSTEMP
SORT/A1 = '1';
DSACCTNO/A8 = '_FOC_NULL';
ACCT_DESC/A30 = 'ALL';
END
TABLE FILE AFSTEMP
SUM
DSNAME NOPRINT
ACCT_DESC
BY SORT
BY DSACCTNO
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN

-*-------------------------------------------

DEFINE FILE AFSTEMP
SORT/A1 = '2';
DSACCTNO/A8 = EDIT(DSACCTNO);
DSACCTNAME/A40 = DSACCTNO || (' - ' | DSNAME);
END
TABLE FILE AFSTEMP
SUM
DSACCTNAME
BY SORT
BY DSACCTNO
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
END
-RUN

-*-------------------------------------------

TABLE FILE HOLD2
PRINT
DSACCTNAME
BY HIGHEST SORT NOPRINT 
BY DSACCTNO
WHERE (DSDIVISION EQ &DSDIVISION);
WHERE (DSDISTRICT EQ &DSDISTRICT);
WHERE (DSBANNERGL EQ &DSBANNERGL);
WHERE (DSACCTNO   NE 0);
ON TABLE PCHOLD FORMAT XML
MORE
FILE HOLD1
END
-RUN

Here's the output I get:

0 NUMBER OF RECORDS IN TABLE=        1  LINES=      1
 0 ERROR AT OR NEAR LINE     14  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: DSACCTNO
 0 NUMBER OF RECORDS IN TABLE=    61976  LINES=     62
 ACCESS LIMITED BY FILTERS
 0 ERROR AT OR NEAR LINE     31  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: DSACCTNO
 0 ERROR AT OR NEAR LINE     32  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC279) NUMERIC ARGUMENTS IN PLACE WHERE ALPHA ARE CALLED FOR
 0 ERROR AT OR NEAR LINE     36  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC003) THE FIELDNAME IS NOT RECOGNIZED: DSACCTNAME
 BYPASSING TO END OF COMMAND
 (FOC009) INCOMPLETE REQUEST STATEMENT
 0 ERROR AT OR NEAR LINE     45  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD2
 BYPASSING TO END OF COMMAND

Do you see where I went wrong at all???

Update: I think it has something to do with DSACCTNO being an I11 in the master file. Hmmm...
Still can't get it to work after adding in a DEFINE with EDIT(DSACCTNO).

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

What is the incoming format of DSACCTNO?

I11.

Need to understand the concatenation of data, formats have to match. Also, don't give the DB name the same name in the DEFINE

-DEFAULTH &DSDIVISION = _FOC_NULL;
-DEFAULTH &DSDISTRICT = _FOC_NULL;
-DEFAULTH &DSBANNERGL = _FOC_NULL;
SET ASNAMES = ON
DEFINE FILE AFSTEMP
SORT/A1 = '1';
DSACCTNAME/A40 = 'ALL';
END
TABLE FILE AFSTEMP
SUM
COMPUTE NEW_COL/A11 = '_FOC_NULL'; NOPRINT
DSACCTNAME
BY SORT
BY TOTAL NEW_COL AS 'DSACCTNO'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN

-*-------------------------------------------

DEFINE FILE AFSTEMP
SORT/A1 = '2';
DSACCTNO_X/A11 = EDIT(DSACCTNO);
DSACCTNAME/A40 = DSACCTNO_X || (' - ' | DSNAME);
END
TABLE FILE AFSTEMP
SUM
DSACCTNAME
BY SORT
BY DSACCTNO_X AS 'DSACCTNO'
WHERE (DSDIVISION EQ &DSDIVISION);
WHERE (DSDISTRICT EQ &DSDISTRICT);
WHERE (DSBANNERGL EQ &DSBANNERGL);
WHERE (DSACCTNO NE 0);
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
END
-RUN

-*-------------------------------------------

TABLE FILE HOLD2
PRINT
DSACCTNAME
BY HIGHEST SORT NOPRINT
BY DSACCTNO

ON TABLE PCHOLD FORMAT XML
MORE
FILE HOLD1
END
-RUN

This message has been edited. Last edited by: Tom Flynn,

Made some changes above...

Tom,

Here's the output from the modified code you supplied just above this post:

0 ERROR AT OR NEAR LINE     11  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC32535) INVALID _FOC_NULL USAGE
 (FOC009) INCOMPLETE REQUEST STATEMENT
 BYPASSING TO END OF COMMAND
 0 ERROR AT OR NEAR LINE     25  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC279) NUMERIC ARGUMENTS IN PLACE WHERE ALPHA ARE CALLED FOR
 0 ERROR AT OR NEAR LINE     29  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC003) THE FIELDNAME IS NOT RECOGNIZED: DSACCTNAME
 BYPASSING TO END OF COMMAND
 (FOC009) INCOMPLETE REQUEST STATEMENT
 0 ERROR AT OR NEAR LINE     42  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD2
 BYPASSING TO END OF COMMAND

It didn't like your use of _FOC_NULL in your COMPUTE...

OK, try this, there was another error in my example:

-DEFAULTH &DSDIVISION = _FOC_NULL;
-DEFAULTH &DSDISTRICT = _FOC_NULL;
-DEFAULTH &DSBANNERGL = _FOC_NULL;
SET ASNAMES = ON
-RUN
DEFINE FILE AFSTEMP
SORT/A1 = '1';
DSACCTNAME_X/A40 = 'ALL';
NEW_COL/A11 = '_FOC_NULL';
END
TABLE FILE AFSTEMP
SUM
DSACCTNAME_X AS 'DSACCTNAME'
BY SORT
BY NEW_COL AS 'DSACCTNO'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN

-*-------------------------------------------

DEFINE FILE AFSTEMP
SORT/A1 = '2';
DSACCTNO_X/A11 = EDIT(DSACCTNO);
DSACCTNAME/A40 = DSACCTNO_X || (' - ' | DSNAME);
END
TABLE FILE AFSTEMP
SUM
DSACCTNAME
BY SORT
BY DSACCTNO_X AS 'DSACCTNO'
WHERE (DSDIVISION EQ &DSDIVISION);
WHERE (DSDISTRICT EQ &DSDISTRICT);
WHERE (DSBANNERGL EQ &DSBANNERGL);
WHERE (DSACCTNO NE 0);
ON TABLE HOLD AS HOLD2 FORMAT ALPHA
END
-RUN

-*-------------------------------------------

TABLE FILE HOLD2
PRINT
DSACCTNAME
BY HIGHEST SORT NOPRINT
BY DSACCTNO

ON TABLE PCHOLD FORMAT XML
MORE
FILE HOLD1
END
-RUN

Tom,

I think you were closer that time. Trying to figure out what the issue is on my end too.

Here's the new output:

(FOC493) NO ACCESSIBLE FIELDS ARE MENTIONED IN THE REQUEST
 0 ERROR AT OR NEAR LINE     26  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD: DSACCTNAME
 0 ERROR AT OR NEAR LINE     30  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC003) THE FIELDNAME IS NOT RECOGNIZED: DSACCTNAME
 BYPASSING TO END OF COMMAND
 (FOC009) INCOMPLETE REQUEST STATEMENT
 0 ERROR AT OR NEAR LINE     43  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD2
 BYPASSING TO END OF COMMAND

then DSNAME is more than 11(DSACCTNO) + 1('-')
So A40 isn't enough. Need to know your data.

DSACCTNAME/A40 = DSACCTNO_X || (' - ' | DSNAME);


IF DSNAME = A60,
THEN
DSACCTNAME = 11 + 1 + 60 = 72

Tom,

DSNAME has the format A30.
DSACCTNO has the format I11.

So, you would think A41 would be enough? Tried that and it didn't work...

Oh!! I forgot about the 3 characters for the dash!

Used A44 and that took care of that issue.

Here's the new output:

(FOC493) NO ACCESSIBLE FIELDS ARE MENTIONED IN THE REQUEST
 0 NUMBER OF RECORDS IN TABLE=    64945  LINES=     66
 0 ERROR AT OR NEAR LINE     53  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HOLD1
 BYPASSING TO END OF COMMAND

Forgot the dash. 11 + 1 + 30 = 42
Make it A50, it's HTML, won't matter...

Tom,

Modified my above post. Now it's saying there is no HOLD1 and no accessible fields??

Make sure there is a -RUN after END for each HOLD file, mandatory...

For the HOLD1 file, do this:

SET ASNAMES = ON
-RUN
DEFINE FILE AFSTEMP
SORT/A1 = '1';
DSACCTNAME_X/A40 = 'ALL';
NEW_COL/A11 = '_FOC_NULL';
END
TABLE FILE AFSTEMP
SUM
DSNAME NOPRINT
DSACCTNAME_X AS 'DSACCTNAME'
BY SORT
BY NEW_COL AS 'DSACCTNO'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN