I have a report requirment that will allow the user to key in company, customer and customer name and all of them are not required field. When the user selected a company then all customers under the selected company will be included in the report and when the user select or key in a customer number then that particular customer will be in the report and when the use keys in a part of the customer name(ex. WEB) then all customer with "WEB" in the customer name will be included in the report.

I tried the solution below that I found here in focal point but it doesnt work.

1. Solution 1
-IF &ID EQ '' GOTO :NO_ID
WHERE ITEM_ID EQ &ID
:NO_ID

-IF &COLOR EQ '' GOTO :NO_COLOR
WHERE COLOR EQ &COLOR
:NO_COLOR

-IF &SIZE EQ '' GOTO :NO_SIZE
WHERE SIZE EQ &SIZE
:NO_SIZE

2. Solution2
You will need to write some DM code for this.

It could look something like this:
-IF &ID EQ '' GOTO :NO_ID
WHERE ITEM_ID EQ &ID
:NO_ID

-IF &COLOR EQ '' GOTO :NO_COLOR
WHERE COLOR EQ &COLOR
:NO_COLOR

-IF &SIZE EQ '' GOTO :NO_SIZE
WHERE SIZE EQ &SIZE
:NO_SIZE


thanks a lot.
Noel

When you say it doesn't work...what doesnt work?

In addition to what Prarie said, what do you get when you -TYPE your variables after using the launch page to run your fex?

For the partial search, you are going to have to append a wildcard to the string selected by the user in order to find everything that starts with WEB.

LIKE field EQ 'WEB%'

Are you wanting to allow the user to limit the output by choosing all some or none of the selection criteria? If so, here is one way I do it.

-SET &COUNTRY  = 'ALL';
-*  ALL
-*  ENGLAND 
-*  FRANCE 
-*  ITALY 
-*  JAPAN 
-*  W GERMANY 
-SET &CAR      = 'ALL';
-*  ALL
-*  ALFA ROMEO 
-*  AUDI 
-*  BMW 
-*  DATSUN 
-*  JAGUAR 
-*  JENSEN 
-*  MASERATI 
-*  PEUGEOT 
-*  TOYOTA 
-*  TRIUMPH 
-SET &BODYTYPE = 'CONVERTIBLE';
-*  ALL
-*  CONVERTIBLE 
-*  COUPE 
-*  HARDTOP 
-*  ROADSTER 
-*  SEDAN 
-SET &COST     = 10000;
-*  0 => ALL
-* VARIABLE
-*
-SET &CNTRY      = IF &COUNTRY = ALL THEN 'A'' OR ''E'' OR ''I'' OR ''O'' OR ''U' ELSE &COUNTRY;
-SET &CCAR       = IF &CAR = ALL THEN 'A'' OR ''E'' OR ''I'' OR ''O'' OR ''U' ELSE &CAR;
-SET &CBODYTYPE  = IF &BODYTYPE = ALL THEN 'A'' OR ''E'' OR ''I'' OR ''O'' OR ''U' ELSE &BODYTYPE;
-SET &CCOST      = IF &COST = 0 THEN 999999999 ELSE &COST;
-*
SET BYDISPLAY = ON
-*
TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY BODYTYPE
WHERE COUNTRY CONTAINS '&CNTRY'
WHERE CAR CONTAINS '&CCAR'
WHERE BODYTYPE CONTAINS '&CBODYTYPE'
WHERE RETAIL_COST LE &CCOST
END

quote:

I have a report requirment that will allow the user to key in company, customer and customer name and all of them are not required field. When the user selected a company then all customers under the selected company will be included in the report and when the user select or key in a customer number then that particular customer will be in the report and when the use keys in a part of the customer name(ex. WEB) then all customer with "WEB" in the customer name will be included in the report.

This may help:

-DEFAULT &COMPANY = ' ', &CUST_NAME = ' ', &CUST_NUMB = ' '
-SET &CUST_NAME = IF &CUST_NAME EQ ' ' THEN ' ' ELSE 'WHERE CUSTOMER_NAME LIKE %' || '&CUST_NAME.EVAL' || '%;';
-SET &COMPANY   = IF &COMPANY   EQ ' ' THEN ' ' ELSE 'WHERE COMPANY EQ ' | '&COMPANY.EVAL' || ';';
-SET &CUST_NUMB = IF &CUST_NUMB EQ ' ' THEN ' ' ELSE 'WHERE CUSTOMER_NO EQ ' | '&CUST_NUMB.EVAL' || ';';

TABLE FILE WHATEVER
SUM
   COLUMN_NAMES
  BY COLUMN_NAMES
&CUST_NAME
&COMPANY
&CUST_NUMB

ETC...

If the customer number is numeric, remove the quotes.

Have fun!

Tom

Tom,

You're a peach!

I was in the midst of writing a report where the user could choose a state or no state (for all) when I read your reply.

I made a slight change to your example which simplified my code immensely.

Thank-you, Thank-you, Thank-you!!!

-SET &INPS  = 'ALL';
-SET &STATE = IF &INPS EQ 'ALL' THEN '' ELSE 'WHERE AGSTE EQ ' | ''&INPS.EVAL''';';
 

Hey Glenda,

Welcome! Welcome! Welcome!

A little modification:

I would use -DEFAULT instead of -SET; it will "ALWAYS" be ALL with the -SET.


That is what this forum is all about!!

Have a great weekend...Tom

Tom,

I tried your solution but now I cannot edit my procedure using developer studio tool. The error message is "Error parsing report request".

Thank you all for the help!

Noel,

No, once you add Dialogue Manager outside of the GUI, it balks. That is why I don't use it, ever.

You can edit the code by right-clicking on the fex and select EDIT IN TEXT EDITOR...

I'm not a GUI person, so, I can't really assist competently, BUT, I know the GUI allows DM commands:

Left-click the diamond and DM is at the bottom; maybe you can place them in the proper order so the GUI likes it (doubt it!)

Good Luck

Tom

I would use the code FOC_NONE

you get this

-DEFAULT &CNTRY = FOC_NONE; 
-DEFAULT &CAR = FOC_NONE; 
-DEFAULT &BODYTYPE = FOC_NONE; 
-* SET BYDISPLAY = ON 
-* 
TABLE FILE CAR 
PRINT RETAIL_COST 
BY COUNTRY 
BY CAR 
BY BODYTYPE 
WHERE COUNTRY EQ '&CNTRY' 
WHERE CAR EQ '&CAR' 
WHERE BODYTYPE EQ '&BODYTYPE' 
END

Now the user is prompted for the country, car and bodytype.
If he leaves the FOC_NONE the WHERE statement is omitted and the result will show all the countries.

quote:

once you add Dialogue Manager outside of the GUI, it balks

You can add DM code OUTSIDE of a table request and DevStudio will handle it OK (for the most part.) However, by placing DM command somwhere between the TABLE FILE and END, DevStudio will no longer parse your request. You're on your own, which is where you were to start with, so I would forget about the DevStudio from this point forward.

Frank's suggestion works great AS LONG AS each WHERE statement it on its OWN line. Otherwise things get ignored that you don't want. Additionally, it would not handle the LIKE functionality requested by Noel.

I frequently use Tom's technique of pre-manufacturing all of my WHERE statements based on parameters used and then use the &parmname.EVAL in place of the WHERE.

In the original question of the posted code "not working" remember that any DM -IF statements need a ; at the end.

All,

FOC_NONE is NOT the end-all of WHERE;
there are many, many nuances/caveats where it is not a functional option.

Additionally, I try to guide people with under 30 posts the benefits of the core language and Dialogue Manager. I am of the opinion that Dialogue Manager is the "MOST" powerful/efficient piece of the core language.

Additionally, if the code is ever tightened, -DEFAULT "NEVER" ends with a semi-colon; WHERE statements "ALWAYS" end with a semi-colon.

It's documented...

One more thang!: the AUTOPROMPTER is not used as much as HTML forms/JavaScript, which is much more efficient, IMO...



Tom

This message has been edited. Last edited by: Tom Flynn,

Thanks Tom, but your solution does not work with the GUI and that is what many beginning users prefer and Information Builders predicts.

There is no course where users learn the coding.
My code with FOC_NONE works great and without the ";" I sometimes get error warnings.

Frank,

There are courses where developers can learn the coding syntax, Basic Reporting (102), Dialogue Manager (125), Intermediate Reporting (126), MATCH File (213), Advanced Reporting (210).

Maybe these courses are not available in the Netherlands but they are available and listed in the course catalog from IBI.

quote:

-SET &STATE = IF &INPS EQ 'ALL' THEN '' ELSE 'WHERE AGSTE EQ ' | ''&INPS.EVAL''';';

FYI,
If you are using amphere variables in a computed WHERE statement, it is not necessary to concatenate the pieces together. The above can become:

  
-SET &STATE = IF &INPS EQ 'ALL' THEN '' ELSE 'WHERE AGSTE EQ  ''&INPS.EVAL'';';

It is a whole lot easier to code than trying to match all of the quotes especially if you are comparing to multiple values.

Thanks Pat. Every time I shorten the code, I reduce my error percentage. Every little bit of knowledge helps.

This thread reminds me of an issue we had with the AutoPrompt facility: i.e. control of the sequence of the prompts. By default the fields are prompted for in the sequence in which the parsing routines determine that no value is supplied. This is usually the top-to-bottom sequence in which &vars are encountered in the procedure.

We found a 'neat' way around this by mentioning them in "dummy" -SET statements at the start of the procedure. An extension of this practice leads to a natural inclusion of the old prompting syntax that defines the content of an AutoPrompt select list.

The only problem sometimes found with this approach is that a long selection list can exceed the allowable line length of older WF releases.

Combining this with the capability of 'FOC_NONE' we get a method by which one can control the order of Autoprompting, the selectable values in the prompts, and still allow for 'ALL':

(I know the use of lower/mixed case &vars is frowned upon, but it makes the presentation look nice.)

  
APP PATH IBISAMP
 
-SET &X = '&Country.(ALL,ENGLAND,FRANCE,ITALY).';
-SET &X = '&Car.(ALL,JAGUAR,TOYOTA,DATSUN).';
-SET &X = '&BodyType.(ALL,SEDAN,COUPE).';

-SET &Country =IF &Country  EQ 'ALL' THEN 'FOC_NONE' ELSE &Country;
-SET &Car     =IF &Car      EQ 'ALL' THEN 'FOC_NONE' ELSE &Car;
-SET &BodyType=IF &BodyType EQ 'ALL' THEN 'FOC_NONE' ELSE &BodyType;

TABLE FILE CAR 
PRINT RETAIL_COST 
BY COUNTRY 
BY CAR 
BY BODYTYPE 
WHERE COUNTRY  EQ '&Country' 
WHERE CAR      EQ '&Car' 
WHERE BODYTYPE EQ '&BodyType' 
END

..and back to your original post...

quote:

-IF &COLOR EQ '' GOTO :NO_COLOR
WHERE COLOR EQ &COLOR
:NO_COLOR

...should be ...

-IF &COLOR EQ '' GOTO :NO_COLOR;
WHERE COLOR EQ &COLOR
-:NO_COLOR  

Dialog Manager labels need a dash in front of them, as well as the semi-colon after the -IF.

quote:

the use of lower/mixed case &vars is frowned upon

Really? Depends upon whose standards you follow.
Myself, I'd prefer readable, maintainable code and always use mixed case variable names for my own code, and whatever my "Client at the time" prefers for their code

And Noel, take note of what Karen "the Piipster" has mentioned regarding DM syntax.

T