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';