vvutility_suggest extra option where condition

[ThierryC]

i have a file that contains similar data for different companies/bookyears.

in the combo i'd like to only suggest records based on a certain company, bookyear..; there for i need to be able to specify additional where clause.

i would be a great help if it where possible to add an optional 'filter' clause on the vvutility_suggest...

i've already modified this myself in the vv_sources.. but for sake of updates and future version, it would be good to have it integrated in the cnx-version


thx

thierry

[sean.lanktree]

Excellent idea....yes, we will definitely incorporate this.

[ThierryC]

Ok, great.... perhaps consider also extending the stmt-variable... it is now only 200a

these are my changes ..



d stmt s 200a varying
...
...
...
p vvUtility_suggest...
p b export
d pi
d inValue 150a const varying options(*varsize)
d inFieldName 10a const varying options(*varsize)
d inFileName 10a const varying options(*varsize)
d inNumToReturn 3 0 const
d inWhereCnd 512a const varying
d options(*varsize:*nopass)


d fetchNum s 4 0
d compare s 150a
d WhereCnd s 512a varying
/free

Clear WhereCnd;
If (%parms> 4);
WhereCnd = InWhereCnd;
Endif;

// if the requester asked for more rows than allowed....
// set the number to the max
//
if inNumToReturn>SUGGESTMAX;
fetchNum=SUGGESTMAX;
else;
fetchNum=inNumToReturn;
endif;
compare = vvUtility_convertCase(inValue:TOUPPER);

clear suggestArray;

// build the sql statement...
stmt='select '+%trim(inFieldName)+' from '+%trim(inFileName)+
' where upper('+%trim(inFieldName)+') like '+SQ+
%trim(compare)+'%'+SQ;
If (WhereCnd <> *blanks);
Stmt += ' and ' + %trim(WhereCnd);
Endif;
Stmt += ' order by '+%trim(inFieldName)+
' fetch first '+%char(fetchNum)+' rows only';