I am trying to let the end user input a name via autoprompt ...

[Peter Vazny]

I am trying to let the end user input a name via autoprompt parameter, without them needing to use a correct case. So far, every solution I found requires a dialog manager entry. Which makes the report not editable in InfoAssist.

Here is a sample code generated by InfoAssit:

WHERE NAME_TABLE.NAME_TABLE.NAME EQ &SEARCHNAME.(|FORMAT=A100).Customer Name:.QUOTEDSTRING;

 

I tried moving the prompt to a DEFINE, but it would come back with incorrect syntax.

I have found solutions in the old WebFocus forums that use Dialog Manager:

-SET &SELECT1 = UPCASE(&INSTR1.LENGTH, &INSTR1, 'A&INSTR1.LENGTH');

WHERE UPCASE(50, TABLE_COLUMN_NAME, 'A50') CONTAINS '&SELECT1'

 

However I dont think that any of that would survive editing by InfoAssist.

EDIT: Provided clarification

[Martin Yergeau]

Why not using a DEFINE where you will use either UPPER or LOWER function

May be to much simple, but you did not gives us a lot of info such as how/from where the user will provide its entry

[Peter Vazny]

I apologize. I edited my submission to hopefully provide more clarity.

I tried putting the amper value/autoprompt into a DEFINE, but it would always error out with incorrect syntax.

[Martin Yergeau]

You may need something such as this

-SET &SRCHNAME = &SEARCHNAME.(|FORMAT=A100).Country:.QUOTEDSTRING;

 

DEFINE FILE CAR

SRCHNME /A100 = UPPER('&SRCHNAME.EVAL');

END

TABLE FILE CAR

SUM SEATS

BY MODEL

HEADING

"Received value: &SRCHNAME.EVAL"

"Defined value: <SRCHNME"

"Retreived value: <COUNTRY"

WHERE COUNTRY EQ SRCHNME;

END

[Peter Vazny]

MartinY:

 

You may need something such as this

 

 

This works on its own and is similar to the solutions I have found before, but the use of the dialog manager (-SET ...) prevents me from editing existing InfoAssist report and adding it there. When trying to save it I get following error:

Parsing error: Encountered "$$$_AMPDUMMY$$$0" at line 1, column 18. Was expecting one of: <sqstr> ... <whsqstr> ... <wsquots> ... <QuotedAmperDummy> ...

[David Beagan]

The following seems to work and be compatible with InfoAssist.

In a DEFINE:

NameMatch/I1=IF UPPER(NAME_TABLE.NAME_TABLE.NAME) EQ UPPER('&SEARCHNAME') THEN 1 ELSE 0;

 

The in the WHERE statement:

WHERE NameMatch EQ 1;

[Peter Vazny]

This works!

I know I am moving the goal posts here, but is there any way to make this work with LIKE/CONTAINS in a way that gets passed to SQL I tried CONTAINS but I get:

(FOC2565) THE OBJECT OF IF/WHERE CANNOT BE CONVERTED TO SQL

(FOC2566) DEFINE NameMatch CANNOT BE CONVERTED TO SQL

(FOC2575) COMBINATION OF OPERANDS FOR CONTAINS CANNOT BE CONVERTED TO SQL

 

I cannot get LIKE to work at all. Ive tried:

NameMatch/I1=IF UPPER(NAME_TABLE.NAME_TABLE.NAME) LIKE UPPER('&SEARCHNAME') THEN 1 ELSE 0;

(FOC36251) SYNTAX ERROR IN LIKE OPERATOR

NameMatch/I1=IF UPPER(NAME_TABLE.NAME_TABLE.NAME) LIKE '%' | UPPER('&SEARCHNAME') | '%' THEN 1 ELSE 0;

(FOC281) ALPHA ARGUMENTS IN PLACE WHERE NUMERIC ARE CALLED FOR: |

NameMatch/I1=IF UPPER(NAME_TABLE.NAME_TABLE.NAME) LIKE UPPER('%' | '&SEARCHNAME' | '%') THEN 1 ELSE 0;

(FOC36251) SYNTAX ERROR IN LIKE OPERATOR

 

EDIT: I am going to open up a new thread.