Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Page filtering in WebFOCUS Designer
Go
New
Search
Notify
Tools
Reply
  
[CLOSED]Page filtering in WebFOCUS Designer
 Login/Join
 
Member
posted
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,


WebFOCUS 8202, 8203, Windows 2012 R2
SQL Server 2016, App Studio 8202, 8203
 
Posts: 16 | Location: Jericho, NY | Registered: February 18, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WebFOCUS 8206, Unix, Windows
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I would use AppStudio or Infoassist to generate that report instead of handcoding it just to make sure.
 
Posts: 226 | Location: Houston,TX | Registered: June 11, 2004Reply With QuoteReport This Post
Member
posted Hide Post
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.


WebFOCUS 8202, 8203, Windows 2012 R2
SQL Server 2016, App Studio 8202, 8203
 
Posts: 16 | Location: Jericho, NY | Registered: February 18, 2014Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8206, Unix, Windows
 
Posts: 1707 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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.


WebFOCUS 8202, 8203, Windows 2012 R2
SQL Server 2016, App Studio 8202, 8203
 
Posts: 16 | Location: Jericho, NY | Registered: February 18, 2014Reply With QuoteReport This Post
Member
posted Hide Post
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,


WebFOCUS 7.7.05 and 8.x
Windows, All Outputs
 
Posts: 27 | Registered: December 13, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
What about WHERE &COUNTRY IN ( FileName );
Where 'FileName' is the HOLD file that you create to keep it dynamic.
 
Posts: 3040 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Member
posted Hide Post
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


WebFOCUS 7.7.05 and 8.x
Windows, All Outputs
 
Posts: 27 | Registered: December 13, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]Page filtering in WebFOCUS Designer

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.