Focal Point
Looping through a AMP Variable

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2561048331

March 31, 2005, 05:03 PM
Steve W.
Looping through a AMP Variable
Hi everyone.

I have an input parameter that takes in multiple selections. I am trying to generate a where statement that contains each value selected. Currently I am using an amper variable, but I am stuck with the looping. I can not figure out how to move to the next value selected. Any help?

Steve
March 31, 2005, 05:37 PM
Kamesh
try this code,

-IF &SELVAR = 'ALL' GOTO ENDLOOPB;
-IF &SELVAR.LENGTH GT 1 THEN GOTO LOOPB;

-LOOPB
-SET &CNTR=1;
WHERE DB_FIELD_NAME EQ '&SELVAR'

-LOOPITB
-IF &SELVAR.&CNTR.LENGTH GT 1 GOTO LOOP_AGAINB ELSE GOTO ENDLOOPB;

-LOOP_AGAINB
OR '&SELVAR.&CNTR'
-SET &CNTR=&CNTR+1;
-GOTO LOOPITB;
-ENDLOOPB
March 31, 2005, 06:00 PM
drew billingslea
you might try:

-IF &CUSTOMER1 EQ 'All' GOTO CUSTALL ;
WHERE (CUSTOMER EQ '&CUSTOMER1'
-REPEAT ALLCUST FOR &CTR FROM 2 TO &CUSTOMER0
OR '&CUSTOMER.&CTR'
-ALLCUST
);
-CUSTALL


hth,

drew
March 31, 2005, 06:09 PM
Steve W.
I tried both of these examples and I do not get the list values I am looking for. Instead I am getting numbers or just the name of the AMP variable.
March 31, 2005, 06:17 PM
reFOCUSing
What values is the variable getting?
How is the data delimited?
What is the most number of where statements that could be generated?
March 31, 2005, 06:21 PM
Steve W.
The parameter values are dynamically driven off of the database and displayed in a HTML select object. Here is the code for the parameters:

<SELECT NAME="REQTYPES" SIZE=5; width=5; MULTIPLE ID="Select4">
<option SELECTED>!IBI.FIL.TYPELST;</OPTION>
</SELECT>

The user selects multiple rules and then runs the report.

I am trying to build a where statement on whatever number of rules they selected.

Here is the code in the FEX:

-*
-SET ECHO=ALL;
-SET &CODEREF=&SITECOD;
-SET &RULES=&RULENAME;
-SET &CPT = 1;
-SET &CHECK = IF &REQTYPES0.EXISTS THEN 1 ELSE 0;
-RUN
-IF &CHECK EQ 0 THEN GOTO SINGLE ELSE GOTO MULTIPLE;
-GOTO DONE
-SINGLE
-IF &REQTYPES.EXISTS THEN GOTO NEXT ELSE GOTO NOTYPE;
-NOTYPE
-SET &IFTYPE = '';
-GOTO DONE
-NEXT
-IF &REQTYPES EQ '' THEN GOTO DONE;
-SET &IFTYPE = 'WHERE (REQ_TYPE EQ ''&REQTYPES.EVAL'')';
-SET &IFTYPE = IF &REQTYPES NE '' THEN &IFTYPE
-ELSE '';
-GOTO DONE
-MULTIPLE
-SET &CPT=1;
-SET &TYPESTR = '';
-IF &REQTYPES.EXISTS THEN GOTO FIRST ELSE GOTO NOTYPE2;
-NOTYPE2
-SET &IFTYPE = '';
-GOTO DONE
-FIRST
-SET &IFTYPE = 'WHERE (REQ_TYPE EQ ''&REQTYPES.EVAL'')';
-SET &IFTYPE = IF &REQTYPES NE '' THEN &IFTYPE
-ELSE '';
-SET &TYPESTR = '&IFTYPE';
-IF &REQTYPES0.EXISTS THEN GOTO START ELSE GOTO DONE;
-START
-REPEAT LOOP FOR &CPT FROM 1 TO &REQTYPES0;
-SET &IFTYPE = &TYPESTR.EVAL | ' OR (REQ_TYPE EQ ''&REQTYPES.&CPT.'') ';
-SET &CPT = &CPT + 1;
-LOOP
-DONE
-RUN
March 31, 2005, 06:54 PM
<Pietro De Santis>
Well, here's another example.
The HTML page (multisel1.html):
<HTML><br />
<BODY><br />
<FORM action=
"http://localhost/cgi-bin/ibi_cgi/ibiweb.exe" method="GET" target="ReportWindow"
<INPUT name="IBIF_focexec" type="hidden" value="MULTISEL1">
<SELECT multiple name="Country" size="4">
<OPTION VALUE="ALL">All Countries</OPTION>
<OPTION VALUE="ENGLAND">England</OPTION>
<OPTION VALUE="FRANCE">France</OPTION>
<OPTION VALUE="ITALY">Italy</OPTION>
<OPTION VALUE="JAPAN">Japan</OPTION>
<OPTION VALUE="W GERMANY">Germany</OPTION>
</SELECT><br /><INPUT type="submit" name="Submit" value="Submit">
<INPUT type="reset"  name="reset"  value="Reset"><br /></FORM>
</BODY>
</HTML>
The fex (multisel1.fex):
-SET &ECHO=ALL;
DEFAULT &Country = 'ALL';
>-SET &COUNTER=0;
TABLE FILE CAR
PRINT CAR MODEL BODYTYPE SALES
BY COUNTRY
IF &Country.EXISTS THEN GOTO 
SEL_ONE ELSE GOTO SEL_END;
SEL_ONE
IF &Country EQ 'ALL' GOTO SEL_END;
WHERE COUNTRY EQ '&Country'
IF &Country0.EXISTS THEN GOTO 
SEL_MULT ELSE GOTO SEL_END;
SEL_MULT
REPEAT SEL_MULT_END FOR 
&COUNTER FROM 2 TO &Country0;
<br />OR '&Country.&COUNTER'
-SEL_MULT_END
SEL_END
END

This message has been edited. Last edited by: <Mabel>,
March 31, 2005, 07:11 PM
<Pietro De Santis>
A short note.
Normal HTML form behaviour: if multiple selections are made, the amper variables generated are, for example, the following:<br />
&Country 
= ENGLAND<br />&Country0
= 3<br />&Country1
 = ENGLAND<br />&Country2
 = FRANCE<br />&Country3
= ITALY
If one selection was made, the following amper variable is generated:<br />
&Country
= ENGLAND
Where
&Country is the first
selection made;
&Country0 is the number of
selections made;<br />&Country1
to &CountryN are all the
selections made.
In my example fex, if only one
selection was made, then the
WHERE statement acts on &Country.
If more than one was made, then the
first part of the WHERE statement
acts on &Country and the rest of
the WHERE statement acts on &Country2 to &CountryN.

This message has been edited. Last edited by: <Mabel>,
April 01, 2005, 05:27 AM
Håkan
This is how we've standardised our code, which is -INCLUDEd in every fex that needs it. &SEL_CTRY is the variable coming from the HTML page.

-IF &SEL_CTRY0.EXISTS GOTO :m_ctry;
-SET &SEL_CTRY = IF &SEL_CTRY EQ ' ' THEN '''' | ' ' | '''' ELSE &SEL_CTRY;
-SET &SEL_CTRY0 = IF &SEL_CTRY EQ 'FOC_NONE' THEN 999999 ELSE 1;
-GOTO :exit_ctry
-*
-:m_ctry
-SET &X = 1;
-SET &STR = ' ';
-REPEAT :end_ctry &SEL_CTRY0 TIMES
-SET &STR = &STR | '''' | &SEL_CTRY.&X | '''';
-SET &STR = IF &X LT &SEL_CTRY0 THEN &STR | ' OR ' ELSE &STR;
-SET &X = &X + 1;
-:end_ctry
-SET &SEL_CTRY = &STR;
-*
-:exit_ctry

If 2 countries were selected, &SEL_CTRY will now be expanded to:

' ENGLAND OR ITALY'
April 01, 2005, 07:53 PM
jbmuir
Here's another solution that involves copying the multi-valued parameter into a file and then using the useful FOCUS construct: IF PARM EQ (DDPARM)

The code below relies on a multi-valued parameter called DHPARAMS being set up that contains the names of all the parameters being passed to the WFServlet, so it has a layer of indirection in it that you may not want.

Setting up the DHPARAMS parameter name list cannot be done in the WFServlet, and the folks at IBI have not provided us with this useful functionality. Nice, huh? Life with webfocus would be a little easier if they did pass in a multi-valued list of parameter names, since then you could write generic code to handle parameters.

To work around the limitations of the webfocus product you have to extend tomcat, which is not for the faint of heart. You can create a "tomcat filter". Once done you have a really easy way to select multi-valued parameters, but this is probably more complicated that anything you were looking for. Refer to the following posting for info about the java side of things:

https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/
6791097331/r/6791097331#6791097331

Here's the code that builds the files used to handle selection of multi-valued parameters. Perhaps you can revise it to suit your needs.

-* WCMLTPRM - Create multi-valued parameter lists for legacy reports.
-*
-* Inputs: &DHPARAMS - Parameter array courtesy of the ParamFilter.
-*
-* &DHLEGACY - 'Y' => legacy report.
-*
-* Outputs:
-*
-* The following items are created by this procedure for a multi-
-* valued parameter called MYPARM:
-*
-* 1) A new amper variable: &MYPARM
-* 2) A new file named: myparm.dat
-* 3) A FILEDEF named: MYPARM
-*
-* This procedure takes multi-valued parameters passed to webfocus and
-* writes them to a file so that the file can then be used to select
-* parameter values using the useful focus construct:
-*
-* IF MYPARM EQ &MYPARM
-*
-* A new amper variable with the name of the parameter being passed in
-* is created that has the value '(MYPARM)'. This value uses a ddname
-* created by this procedures to access the values in the file.
-*
-*----------------------------------------------------------------------
-* 10/21/2004 James Muir Written for webfocus.
-*----------------------------------------------------------------------
-*SET &ECHO='ALL';
SET MSG=OFF
-RUN
-*
-*------------------------------- Setup default values.
-DEFAULTS &DHLEGACY = 'N';
-*
-*------------------------------- Is this a legacy report?
-IF &DHLEGACY NE 'Y' THEN GOTO WCMLTPR.DONE;
-*
-*--------------------------------Get number of parameters to check.
-SET &LIMIT1 = &DHPARAMS0;
-*
-*--------------------------------Loop through all the parameters.
-REPEAT WCMLTPLOOP1 FOR &COUNTER1 FROM 1 TO &LIMIT1 STEP 1
-*
-*--------------------------------Get a parameter name from DHPARAMS.
-SET &MYPARM = 'DHPARAMS' | &COUNTER1;
-SET &MYAMPNAME = '&' || &MYPARM;
-SET &MYNAME = &MYAMPNAME.EVAL;
-*
-*--------------------------------Do we have a multi-valued parameter?
-SET &TEST = '&' || &MYNAME || '0';
-*
-IF &TEST.EVAL.EXIST EQ 0 THEN GOTO WCMLTPLOOP1;
-*
-*--------------------------------We have a multi-valued parameter.
-*--------------------------------Create a FILEDEF for the file that
-*--------------------------------will contain the multiple values for
-*--------------------------------this parameter.
-SET &NAMELEN = &MYNAME.LENGTH;
-SET &FILENAME = LOCASE(&NAMELEN,&MYNAME,'A&NAMELEN.EVAL') || '.dat';
FILEDEF &MYNAME DISK &FILENAME (APP RECFM V
-RUN
-*
-*--------------------------------Get number of values to write.
-SET &LIMIT2 = &TEST.EVAL;
-*
-*--------------------------------Loop through all the values.
-REPEAT WCMLTPLOOP2 FOR &COUNTER2 FROM 1 TO &LIMIT2 STEP 1
-*
-*--------------------------------Get the value of a parameter.
-SET &MYMULTI = &MYNAME | &COUNTER2;
-SET &MYAMPVALUE = '&' || &MYMULTI;
-SET &MYVALUE = &MYAMPVALUE.EVAL;
-*
-*--------------------------------Write it to the file.
-WRITE &MYNAME &MYVALUE
-WCMLTPLOOP2
-*
-*--------------------------------Create a new amper variable that
-*--------------------------------contains the DDNAME reference that
-*--------------------------------the user can use to select records
-*--------------------------------based on the values of the multi-
-*--------------------------------valued parameter.
-SET &MYSET = '-SET &' | &MYNAME | ' = ''(' | &MYNAME | ')'';';
&MYSET.EVAL
-*
-WCMLTPLOOP1
-GOTO WCMLTPR.DONE
-*
-*--------------------------- Clean up.
-WCMLTPR.DONE

This message has been edited. Last edited by: Kerry,
April 02, 2005, 04:21 PM
GCohen
In Release 5 there is a simple way to get a list of values for a WHERE statement with the ' OR ' value between the items.

WHERE STATE EQ &STATE.( OR ( FIND list IN File)).

If no items are selected the entire line is removed at run time from the fex.
Lots of other options, see the doc under
WHERE Amper