Hello,

I'm trying to limit the values that display in a drop down on a page created with WebFOCUS Designer.

We populate the filters using 'FIND' as in the code below. Apparently you cannot use a WHERE clause with FIND. So with respect to the code below, if I wanted to limit the countries in the drop down to France and England, how could that be done?

-DEFAULT &COUNTRY = '_FOC_NULL';

-SET &COUNTRY = &COUNTRY.(FIND COUNTRY, COUNTRY IN CAR).Country:.;

TABLE FILE CAR
SUM
DEALER_COST
BY MODEL
WHERE COUNTRY EQ '&COUNTRY'
END


Thanks.

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

You're missing the OR:

  
-SET &COUNTRY = &COUNTRY.(OR( FIND COUNTRY, COUNTRY IN CAR)).Country:.;

Could you explain what you mean by you can't use WHERE with FIND? I'm pretty sure it works.

  
TABLE FILE CAR
SUM CAR.BODY.DEALER_COST
BY CAR.CARREC.MODEL
WHERE CAR.ORIGIN.COUNTRY EQ &COUNTRY.(OR(FIND CAR.ORIGIN.COUNTRY IN CAR |FORMAT=A10,SORT=ASCENDING)).COUNTRY:.;
END

I would use AppStudio or Infoassist to generate that report instead of handcoding it just to make sure.

quote:

Originally posted by BabakNYC:
You're missing the OR:

  
-SET &COUNTRY = &COUNTRY.(OR( FIND COUNTRY, COUNTRY IN CAR)).Country:.;

Could you explain what you mean by you can't use WHERE with FIND? I'm pretty sure it works.

  
TABLE FILE CAR
SUM CAR.BODY.DEALER_COST
BY CAR.CARREC.MODEL
WHERE CAR.ORIGIN.COUNTRY EQ &COUNTRY.(OR(FIND CAR.ORIGIN.COUNTRY IN CAR |FORMAT=A10,SORT=ASCENDING)).COUNTRY:.;
END

If I use WHERE as part of the FIND statement I get an error. I tried this to limit the drop down values to France and England.

-SET &COUNTRY = &COUNTRY.(FIND COUNTRY, COUNTRY IN CAR WHERE COUNTRY EQ 'France' OR 'England').Country:.;

The error message that resulted:

Detail:
ERROR AT OR NEAR LINE 3 IN PROCEDURE Procedure3
(FOC224) SYNTAX ERROR: WHERE

-------------------------------------------------------vvvvv
-SET &COUNTRY = &COUNTRY.(FIND COUNTRY, COUNTRY IN CAR WHERE COUNTRY EQ 'France' OR 'England').Country:.;



Just to clarify, I'm working in WF 8.2.03 with Pages as described in this doc:
https://webfocusinfocenter.inf...pdfs3/wfdesigner.pdf

That code I posted in my initial post works fine to create and populate the drop down with all the countries. And when I run the page I can filter the report by selecting a country. I was looking for a way, when using the FIND syntax, to filter out, from the values in the drop down, any countries I may not want to include. Or, to put it another way, include only a subset of the countries I may want, e.g. France and England.

Regarding the 'OR' I left out. Leaving out the OR results in a single select drop down on the page whereas including the OR gives you a multi-select.

I get it now. Well, unfortunately, you can either have FIND COUNTRY IN CAR or a static list of countries to pick from like &COUNTRY.(<'France'>,<'England'>).Country:.;

I don't believe you can combine a WHERE test with the former as you found out.

If your dynamic list is too long and you don't want to sift through 1000's of rows to get FRANCE and ENGLAND, I'd go with a static list. However, if your list is constantly changing, then you can set up a file with a list of values and keep that file up to date. You can even use Scheduler to run a request that creates the list of values file with ON TABLE HOLD. Then you can just use this hold file as your look up instead of the main table.

Yes. We currently use hold files as well as db tables to populate controls. If this alternate approach I was looking for worked it may have come in handy for us.

Thanks.

Greetings,

From the below code is there a way to parameterize the &COUNTRY value.

We have two fex files with same code CAR1.FEX and CAR2.FEX. We are on 8206 and using info-assist designer to create a dashboard.

When I drag and drop CAR1.FEX and CAR2.FEX onto the frame, by default parameters from both the fex files are getting binded together (in this case &COUNTRY).

Now one drop down is controlling both the widgets.

The need is to have two separate filters one for each widget.

  
TABLE FILE baseapp/car
BY CAR.COMP.CAR
BY CAR.ORIGIN.COUNTRY
WHERE CAR.ORIGIN.COUNTRY EQ  &COUNTRY.(<ENGLAND,ENGLAND>, <ITALY,ITALY>).COUNTRY:.QUOTEDSTRING;
END
-RUN

Any suggestions or workarounds will be appreciated. Thank you.

With Regards;
Prbhakar Rao

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

What about WHERE &COUNTRY IN ( FileName );
Where 'FileName' is the HOLD file that you create to keep it dynamic.

quote:

Originally posted by Doug:
What about WHERE &COUNTRY IN ( FileName );
Where 'FileName' is the HOLD file that you create to keep it dynamic.

Thanks for the response. From the below code i would like to parameterize "&COUNTRY." so that I can use the same code in car1.fex and car2.fex. The need is to get two drop down selections. (I am referring to dashboard creation using info assist in 8206)

  
TABLE FILE baseapp/car
BY CAR.COMP.CAR
BY CAR.ORIGIN.COUNTRY
WHERE CAR.ORIGIN.COUNTRY EQ  &COUNTRY.(<ENGLAND,ENGLAND>, <ITALY,ITALY>).COUNTRY:.QUOTEDSTRING;
END
-RUN