:Setfilter from a defined substring ?

[ukoenig]

Hello,

I must use oCust:setfilter from a substring

That works perfect

oCust:ORDSCOPE(0, cLast )
oCust:ORDSCOPE(1, cLast )

but I want to show the solution using oCust:SetFilter( ... )

// the index
oCust:CreateIndex( "CUSTOMER", "CUST1", "UPPER(LAST)", .F. )

// the defined get to be filtered ( substring of field oCust:last using < clast > )
@ 55, 700 GET oGet[1] VAR cLast SIZE 100, 22 PICTURE "!!!!!!!!!!" PIXEL UPDATE // 10 chars of field < last > C 20

// the function call
ACTION ( SET_FILTER( "Filter", nOption, ALLTRIM(cLast ), nAge1, nAge2, dDate1, dDate2, lMarried1, lMarried2 )

// field < oCust:last > needed to be filtered
// 'AC' must be replaced with the trimmed -> cLast ( from the get-field )
// 2 must be replaced with LEN( cLast )
// works with fixed values
oCust:SetFilter( " SUBSTR( UPPER( Last ), 1, 2 ) == 'AC' " ) // is OK and is filtering -> Acker

another working filter shows the a range of age
oCust:SetFilter( " Age >= " + STR( nAge1 ) + " .and. Age <= " + STR( nAge2 ) )

I tested different solutions but didn't work

regards
Uwe

[nageswaragunupudi]

oCust:SetFilter( " SUBSTR( UPPER( Last ), 1, 2 ) == 'AC' " ) // is OK and is filtering -> Acker

This can be written as:
oCust:SetFilter( "UPPER(LAST) = 'AC'" ) // single "=". Not "=="

After getting cLast, you can build the filter like this:
oCust:SetFilter( "UPPER(LAST) = '" + Trim( cLast ) + "'" )

[ukoenig]

Works perfect
thank You very much

sometimes I'm thinking to complicated

After getting cLast, you can build the filter like this:
oCust:SetFilter( "UPPER(LAST) = '" + Trim( cLast ) + "'" )

regards
Uwe

[ukoenig]

May I still ask for another solution ?
I need a filterstring for a date-range

field Hiredate from customer.dbf
defined date-vars dDate1 and dDate2 to filter a date-range

that means for a empty dDate2
1) oCust:Hiredate >= dDate1

and with defined dDate1 and !empty dDate2
2) oCust:Hiredate >= dDate1 .and. oCust:Hiredate <= dDate2

I tested different string-combinations without any useful results.
adding this missing test I can post a new download

Thank You very much

regards
Uwe

[Massimo Linossi]

Have you tried with

my_filter = "dtos(oCust:Hiredate) >= dtos(" + ctod(dDate1) + ")"
oCust:SetFilter(my_filter)

[nageswaragunupudi]

Code: Select all  Expand view

  cFilter     := ""
   if !Empty( dDate1 )
      cFilter  := "HIREDATE >= " + TRANSFORM( DTOS( dDate1 ), "@R {^ 9999/99/99}" )
   endif
   if !Empty( dDate2 )
      if !Empty( cFilter )
         cFilter  += " .AND. "
      endif
      cFilter  += "HIREDATE <= " + TRANSFORM( DTOS( dDate2 ), "@R {^ 9999/99/99}" )
   endif

   ? cFilter
 

[ukoenig]

Tested and works perfect

all types of vars for the filtering are covered now
and I can finish the job



thank You very much

regards
Uwe