I'm wondering it it's possible to get the values that display with a descending sort instead of an ascending sort in a prompt like the &CHOOSE_DATE prompt in the CAR file example below.

-* File date_prompt_car.fex
APP HOLD IBISAMP
TABLEF FILE GGSALES
BY HIGHEST DATE
ON TABLE NOTOTAL
ON TABLE HOLD AS CAR_DATE_LIST FORMAT FOCUS
ON TABLE SET HTMLCSS ON
END
-RUN
-SET &&CAR_DATE = &CHOOSE_DATE.(OR(FIND DATE IN CAR_DATE_LIST)).Choose Date.;
-TYPE &&CAR_DATE

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

Did you try by LOWEST?

I did, but it does not seem to make any difference. No matter what, the prompt displays the dates starting with 1996/01/01, 1996/02/01, counting up to 1997/12/01.

What I want is to start with 1997/12/01 and count down to 1996/01/01.

I also tried putting LOWEST here:

-SET &&CAR_DATE = &CHOOSE_DATE.(OR(FIND HIGHEST DATE IN CAR_DATE_LIST)).Choose Date.;

but that results in no values at all in the prompt.

Joan,

My belief is that the code you have (without HIGHEST or LOWEST) is the only acceptable code for that use. I think that you will find that the Autoprompt JavaScript uses a similar method to that employed within launch pages to obtain field values for use within the select control with a "sourcetype" attribute of "typeMaster".

For example, using your posted code - OR(FIND DATE IN CAR_DATE_LIST) - firstly we know that it's a multiple select control, and therefore the control will be given the attribute "multiple".

From the rest of the code piece we can determine that the MAS is CAR_DATE_LIST and the Fieldname is DATE. Therefore the code used to return the object array used to populate the control would be -

TABLE FILE CAR_DATE_LIST
SUM FST.DATE
BY DATE
ON TABLE PCHOLD FORMAT XML
END

If you included the HIGHEST in your code you will understand that it will be interpreted as a fieldname. As that doesn't exist in your MAS then the XML returned would be empty and therefore you will have no options shown in your select control.

I think (please correct me someone if I am wrong) that there, currently, is no way you can effect the order you desire. You could try an NFR but your best method (imho) would be to control everything yourself and build your own launch page instead of relying upon amper autoprompting.

It is what I do most of the time for the very reason you have encountered. I actually have a frameset split into two sections (as per autoprompt page) with the left hand frame being populated by the bespoke launch page. For select controls with special sorting requirements, I use a "sourcetype" attribute of "typeFex" and control the sorting myself.

T

Scratch that, I have been playing

Looking at the code that is executed to derive the options for the select control, there are four parameters passed known internally to the JS being accessed as file, field, pre and where. Now for the amper autoprompting the last two are set to null and therefore not accessible. The code that is run for the option list is -

SET LINES=5000
SET PAGE=NOPAGE
SET HOLDLIST=PRINTONLY
SET XMLTYPE=DISPLAY
TABLE FILE [file]
BY [field]
ON TABLE PCHOLD FORMAT XML
END

So I got to thinking that providing the Field value is interpreted as a single group of words or token, the process should work if you used -

-SET &DUMMY = &CHOOSE_DATE.(FIND 'HIGHEST DATE' IN GGSALES).Choose Date.;

and it does!

So there's your answer. If you want HIGHEST or LOWEST sort order, just include it with the field name in single quotes.

And there's more. You can even code 'HIGHEST 10 [fieldname]' to control the number of options.

T

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

Joan

Maybe the double ampersand is the reason of your problem?

-SET &&CAR_DATE = &CHOOSE_DATE.(OR(FIND HIGHEST DATE IN CAR_DATE_LIST)).Choose Date.;

Tony,
Thanks so much. That is the coolest thing. And it works! You are my hero.
Joan

Frank,
Thanks for your idea. Turns out the && will still work with Tony's fix. This is cool because I will be able to make an "all purpose" prompt to use with -INCLUDE that passes the variable chosen by the prompt! Hooray.
Joan

Frank / Tony,

Cancel that post to Frank - When I run the following and make a selection, the selection does not show up in the variable:

FEX:
-* File date_prompt_car.fex
APP HOLD IBISAMP
TABLEF FILE GGSALES
BY DATE
ON TABLE NOTOTAL
ON TABLE HOLD AS CAR_DATE_LIST FORMAT FOCUS
ON TABLE SET HTMLCSS ON
END
-RUN
-SET &CAR_DATE = &CHOOSE_DATE.(OR(FIND 'HIGHEST DATE' IN CAR_DATE_LIST)).Choose Date.;
-TYPE &CAR_DATE

SELECTION:
1996/01/01

RESULT:
-INCLUDE date_prompt_car.fex
-* File date_prompt_car.fex
APP HOLD IBISAMP
TABLEF FILE GGSALES
BY DATE
ON TABLE NOTOTAL
ON TABLE HOLD AS CAR_DATE_LIST FORMAT FOCUS
ON TABLE SET HTMLCSS ON
END
-RUN
1
0 NUMBER OF RECORDS IN TABLE= 4317 LINES= 4194
0
-SET &CAR_DATE = ;
-TYPE

Joan,

The difference between && and & is that && creates a GLOBAL variable while & creates a LOCAL variable. A global variable will remain active as long as your session remains active. A local variable remains active as long as your program (.fex) remains active. If you are using autoprompt a && may not prompt you for input on a second (or subsequent) run.

Pat,
I do like that there is an && option. I was hoping to use just that feature to save me from having to repeat code like that in my example in multiple fexes.

I'm hoping someone has an answer now for my problem with actually returning a value using the 'HIGHEST fieldname' as described by Tony.

In my testing, the && does not seem to make a difference. And I do not want the variable prompted more than once. I do want the value to be passed from the -INCLUDEd fex to the calling fex.

Anyone have ideas about the missing values with the 'HIGHEST fieldname'?

Joan

In the web world, there is less of a difference now between && and &. In each case, there is a completely new connection made on subsequent runs so even if && was resolved the first time you ran the code, when you run it again, it connects in a new sessions, and && would need to be resolved again.

My rule of thumb would be that there are very few times when you would need to use an && variable in a WF report. In my thousands of pragrams I've coded, I can on think of maybe a couple where it was used. Anyone have any ideas as to when an && variable SHOULD be used in a WF report?

Sorry all, but PLEASE CAN WE MAKE THIS POST NOT ABOUT && VS &????

Joan,

The one question that I would ask you is to explain to yourself how your process would run.

Bear in mind that the parser will scan your code looking for unresolved variables (only single &) and as necessary build the autoprompting frameset.

When you have made a selection for your variables your code will be parsed with the DM being executed first before the TABLE requests etc.

You will understand, therefore, that the autoprompting will occur before your code to build the temporary table from which your date selection is to be gleaned. In short, your master and data files need to exist before you even run your fex. Using the code you posted, this will not be the case.

Your solution is to do what Susannah does all the time (but has not told us of late ) and perform all your extract files for lookups, compare lists etc. overnight so that they exist when you need them.

Good luck

T

Oh, and the real reason that &CAR_DATE is not populated? That's not the variable you are prompting for - it's actually &CHOOSE_DATE.

Replace -TYPE &CAR_DATE with -TYPE &CHOOSE_DATE to see what I mean.

T

Tony,
I was hopeful, but I'm afraid I'm still not there. Even if I do the line table in a different fex (which I plan to do), the problem is still not resolved. (I put all the code on one for demo purposes.)

Version A Fex:
-* File date_prompt_car.fex
-SET &&CAR_DATE = &CHOOSE_DATE.(OR(FIND DATE IN CAR_DATE_LIST)).Choose Date.;
-RUN
-TYPE &CHOOSE_DATE
-TYPE &&CAR_DATE

Version A Results:
-INCLUDE date_prompt_car.fex
-* File date_prompt_car.fex
-SET &&CAR_DATE = 19960101;
-RUN
-TYPE 19960101
19960101
-TYPE 19960101
19960101

Version B Fex:
-* File date_prompt_car.fex
-SET &&CAR_DATE = &CHOOSE_DATE.(OR(FIND 'HIGHEST DATE' IN CAR_DATE_LIST)).Choose Date.;
-RUN
-TYPE &CHOOSE_DATE
-TYPE &&CAR_DATE

Version B Result:
-INCLUDE date_prompt_car.fex
-* File date_prompt_car.fex
-SET &&CAR_DATE = ;
-RUN
-TYPE
-TYPE

Note in the Version B Result that the -SET line has no value, while in Version A Result the -Set line displays the evaluated value.

Joan

quote:

TABLEF FILE GGSALES
BY HIGHEST DATE
ON TABLE NOTOTAL
ON TABLE HOLD AS CAR_DATE_LIST FORMAT FOCUS
ON TABLE SET HTMLCSS ON
END

You need to use TABLE (not TABLEF).

Sorry. That was a typo.

It still does not work.

Please assume the following.

I am creating CAR_DATE_LIST in a separate focexec.

I want to resolve problems with sort order and passing variables as described elsewhere in this thread.

Joan,

I've been looking at the underlying JS and the XML that is produced as a result of the OR(etc. etc. and cannot identify why the variable(s) are not being returned.

It's nothing to do with the single quotes around the 'HIGHEST DATE' as using 'DATE' works OK.

I'll try an ascertain why the problem exists when I get more time, but for now it looks like this won't work as much as we would like it too

T

Version A works - that's great!
Version B will not work, because there is no field called 'HIGHEST DATE' in the master file.
As far as I know you can't use sorting commands in the FIND statement, just fields that exist in the master file.

Hope this helps ...

Gamp,

If you enclose HIGHEST DATE in single quotes within the code then the dropdown list is populated OK. It's just that the variable value is not passed on form submission. My thought is that it is dropped in the creation of the &QUERY_STRING value but haven't had time to prove / disprove.

T

Gamp,
I wonder if you can try this code out. It does actually display a prompt with the values on my system. (WF 7.1.6)

If you're right about sorting, I guess I'll have to see if they would put this into a request for a new release. When there are 36 months or more of data, users notice when they have to scroll through that many months to get to the current month.

joan

Tony,
Thanks so much for your interest in this. I was wondering if somehow an EVAL would help, but couldn't find where to put it.
Joan

Tony / All,

I found out that this works if I take out the (OR...).

Focexec:

-SET &CAR_DATE = &CHOOSE_DATE.(FIND 'HIGHEST DATE' IN CAR_DATE_LIST).Choose Date.;
-TYPE &CAR_DATE

Result:
-INCLUDE date_prompt_car.fex
-* File date_prompt_car.fex
-SET &CAR_DATE = 19971201;
-TYPE 19971201
19971201

So as long as there is no need for more than one month of data, I have what I need. That happens to be the case for my immediate need.

I do think there may be a need for more than one month, however, so if there is a way to make the (OR...) work that would be even better. Maybe this gives us a clue about what's happening?

Joan

I'm using this technique in our current version to get a descending list of dates and it works fine. We're planning to upgrade to 7.6.9 and it seems to ignore the find command and just displays a text box. Have they changed the behavior or could there be something else I have to do in 7.6.9 to make this work?

At this point I'm thinking I'll have to create a launch page to get this funtionality, but any assistance making it work with autoprompt would be greatly appreciated.

For anything fancy, I highly recommend a launch page. Autoprompt is for testing and quick and dirty.

Another way to get the list sorted is to use a program to populate the box in a launch page.

In this paricular case, I have a set of very simple reports with just the single date prompt. I was hoping to get away with using autoprompt, but it appears a launch page is inevitable.

Our company is fairly new to WF. Is it common for undocumented things like this to stop working in a new version?

Code gets tightened and if the code construct you are using doesn't match the syntax in the manual, you are bound to get bitten.

You can create a very simple launch page in minutes with the HTML Composer.