Using WebFOCUS 7.68 Maintain on Windows.

I am knew to WebFOCUS Maintain and trying to build an applicatiion.
My problem is that I am trying to dynamically filter a data stack.
From what I understand there is no "WHERE FIELD LIKE '%'" in WebFOCUS Maintain.

Basically I want to give the ability to search for a record.
There are four dropdowns JOB_CODE, JOB_TITLE, PAY_LEVEL, OCCUPATIONAL_GROUP.
The users selections are passed to a maintain variable SearchResult in the function SetSearchValues.
In the function SetSearchValues I am dynamically building the WHERE statement based on the user's selections.
I store this dynamic WHERE statement in the variable SearchFiler.
Then I call the function SearchJob which searches the data source.

How do I get maintain to recognize my WHERE statement?
Is my approach completeley wrong?



Here is Maintain program I have been working on.

  
MAINTAIN FILE job_codes

$$Declarations
Declare JobGroup / A50 ;
Declare PayLevel / A10 ;
Declare JobCode / A10 ;
Declare JobTitle / A50 ;
Declare SearchFilter / A250 = " " ;

Case Top
Infer job_codes.JOB_CODES.OCCUPATIONAL_GROUP into AddJobCodeStk;
Winform Show MainForm; 
EndCase

Case AddJob
For all include job_codes.JOB_CODES.ID from AddJobCodeStk ;
Stack clear AddJobCodeStk ;
EndCase

Case SearchJob
Reposition job_codes.JOB_CODES.ID ;
Stack clear SearchResultsStk ;
For all next job_codes.JOB_CODES.ID into SearchResultsStk
SearchFilter;

TYPE SearchFilter;
Winform Show SearchResultsForm;
EndCase



Case SetSearchValues
COMPUTE SearchResult/A125 = SearchForm.Triggervalue;
COMPUTE TempSearch/A250 = 'Where ';
JobTitle = GETTOK(SearchResult,125,1,'$',50,JobTitle);
JobCode  = GETTOK(SearchResult,125,2,'$',10,JobCode);
PayLevel = GETTOK(SearchResult,125,3,'$',10,PayLevel);
JobGroup = GETTOK(SearchResult,125,4,'$',50,JobGroup);


IF JobTitle EQ 'FOC_NONE' THEN 
    COMPUTE TempSearch = TempSearch || ' ';
ELSE 
IF JobTitle NE 'FOC_NONE' AND ((JobCode NE 'FOC_NONE') OR (PayLevel NE 'FOC_NONE') OR (JobGroup NE 'FOC_NONE')) THEN
    COMPUTE TempSearch = TempSearch || ' (JOB_TITLE EQ JobTitle) AND ';
ELSE
    COMPUTE TempSearch = TempSearch || ' (JOB_TITLE EQ JobTitle) ';
 
 
IF JobCode EQ 'FOC_NONE' THEN 
     COMPUTE TempSearch = TempSearch || ' ';
ELSE 
IF JobCode NE 'FOC_NONE' AND ((PayLevel NE 'FOC_NONE') OR (JobGroup NE 'FOC_NONE')) THEN
    COMPUTE TempSearch = TempSearch || ' (JOB_CODE EQ JobCode) AND ';
ELSE
     COMPUTE TempSearch = TempSearch || ' (JOB_CODE EQ JobCode) ';
 
 
IF PayLevel EQ 'FOC_NONE' THEN 
     COMPUTE TempSearch = TempSearch || ' ';
ELSE 
IF PayLevel NE 'FOC_NONE' AND ((JobGroup NE 'FOC_NONE')) THEN
    COMPUTE TempSearch = TempSearch || ' (PAY_LEVEL EQ PayLevel) AND ';
ELSE
     COMPUTE TempSearch = TempSearch || ' (PAY_LEVEL EQ PayLevel) ';
 
 
IF JobGroup EQ 'FOC_NONE' THEN 
   COMPUTE TempSearch = TempSearch || ' ';
ELSE
IF JobGroup NE 'FOC_NONE'
   COMPUTE TempSearch = TempSearch || ' (OCCUPATIONAL_GROUP EQ JobGroup) ';


IF (JobTitle EQ 'FOC_NONE') AND (JobCode EQ 'FOC_NONE') AND (PayLevel EQ 'FOC_NONE') AND (JobGroup EQ 'FOC_NONE')
    COMPUTE TempSearch = ' ';

COMPUTE TempSearch = TempSearch || ';';
SearchFilter = TempSearch;
SearchJob();
EndCase

END

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

Max,

We implement search type functions by using an EXEC'ed focexec.

Pass the search parameter(s) to the focexec, let it do the WHERE processing, and configure your return stack as you would like it and make sure the TABLE matches that configuration.

Works pretty smooth for us !

I understand the concept you explained. Create a report and hold it as HTML.
The HTML will get inserted into the stack.
This is similar to what was explained in Maintain Getting Started DN4500828.0908 Pg. 138.

I am able to pull the data from the report into a HTML Object Control using ON TABLE PCHOLD FORMAT HTMTABLE.
I am unsuccessful at pulling the data into a Grid Control using ON TABLE HOLD FORMAT HTML.

The reason I want to use a Grid Control is so that I can modify the records that report produces.

I keep getting this error message.
(FOC03690) Called Proc, CARTABLE, wants 0 Output parms but was called w/ 1.

My test project is named CarProject.
It has to forms Form1 and Form2. Form1 has one button on it named Button1. Form2 is blank.
I created a data stack named HTMLStack it has the columns COUNTRY, CAR, and MODEL to match the structure of cartable.fex

Here are the files.

-*cartable.fex  
TABLE FILE CAR
PRINT
     COUNTRY
     CAR
     MODEL
ON TABLE HOLD FORMAT HTML
END

 
-* start.mnt 
MAINTAIN

$$Declarations

Case Top
Compute HTMLStack.CAR / A16 ;

Compute HTMLStack.COUNTRY / A10 ;

Compute HTMLStack.MODEL / A24 ;

Winform Show Form1; 
-* Replace the Winform Show command with the following code
-* when to display your form in a non-persistent state
-* Winform Show_And_Exit Form1;
EndCase

Case RunReport
Exec cartable into HTMLStack; 
EndCase

END

-* Button1 click event on Form1
Case OnButton1_Click
Perform RunReport( );
Winform Show Form2;
-* Replace the Winform Show command with the following code
-* to display your form in a non-persistent state
-* Winform Show_And_Exit Form2;
EndCase

This message has been edited. Last edited by: Mighty Max,

Max,

Correction: The data is not really held, it is transferred to a Maintain stack in an XML format.

You need ON TABLE PCHOLD, not HOLD FORMAT HTML, in your Focexec to make the maintain connection

And the field names, formats, and field order need to be the same as the stack, that is receiving the data, is defined, as you seem to have done.

Now you can bind the stack to an HTMLTable object on your form, and see the retrieved data. but it is static, you can't really update the values without playing some fancy games.

If you really want to to use the Grid the data must be in a stack to bind it to the grid.
Then you should probably have 2 or 3 similar stacks to control the movement of data from the database, into the grid, and back to the database, plus any programmatic manipulation in between. Grids can get complicated, as well as being limited to ActiveX environments

Another approach is to use the HTMLTAble object to select one record from a list of search result records, and link a Click event to another Form with an Editbox for each field and Add/Update/Delete/Return Pushbuttons.

It's a simpler way to get started and make all the rest of the stuff work, before tackling a grid

This message has been edited. Last edited by: Dave Ayers,

Good Morning
There are actually 3 ways to accomplish what you want to do. While Maintain is robust, it is not dynamic. We cannot build statements on the fly. This first technique allows criteria to be entered or selected from one, two or three boxes. Using the Movies file, the user can select or enter, Moviecode, Title or Category.

This technique creates an upper and lower bound for each criteria. If the user does not select or enter a value for one of the criteria, this technique matches all values, technically omitting that criteria.

Case Starts_With
COMPUTE MCODE/A6=MOVSTK().MOVIECODE;
MCODEZ/A10 = MCODE || '9999';
COMPUTE MTITLE/A39=MOVSTK().TITLE;
MTITLEZ/A40 = MTITLE || 'Z';
COMPUTE MCATEGORY/A8 = MOVSTK().CATEGORY;
MCATEGORYZ/A9 = MCATEGORY || 'Z';
REPOSITION MOVIECODE
STACK CLEAR DISPSTK
FOR ALL NEXT MOVIECODE INTO DISPSTK
WHERE MOVIECODE GE MCODE
AND MOVIECODE LE MCODEZ
AND TITLE GE MTITLE
AND TITLE LE MTITLEZ
AND CATEGORY GE MCATEGORY
AND CATEGORY LE MCATEGORYZ
EndCase

Next, if you ONLY are allowing one criteria and not multiple criteria then just use and IF - THEN - ELSE statement, determine which criteria has been selected and branch to that NEXT with WHERE statement.

Lastly, as Dave points out, using EXEC with a Table may be the most elegant. You can pass many variables to the Table and actually retrieve three different kinds of stacks. If you use:

ON TABLE PCHOLD
Then the data is just returned and placed into whatever stack you have waiting. This is the same as using the NEXT statement.

ON TABLE PCHOLD FORMAT HTML
Returns a fully formatted report and that can be displayed in an HTML Object

There is also a FOCMSG.MSG stack that you don't need to retrieve. Anything typed or displayed to the screen during and EXEC can be displayed in that stack. This is very helpful for debugging, but also for getting output from EXEC'd procedures that don't retrieve records from a database.

Mark

Thanks for the information.
I will begin testing apply the techniques you described.

ON TABLE PCHOLD works perfectly.
Someone needs to fix the ON TABLE HOLD FORMAT HTML typo in the Maintain Getting Started manual.

I will alert Doc Services now.

Mark