Extending SetScope()

[TimStone]

I have a datafile that I use for "parts used on invoices".

For quick filterning, I use SetScope functions. ( top and bottom ). It uses the invoice number as the value for both. Thus when the invoice is open, only those parts used for that specific one will display in the xBrowse control. It all works perfectly.

Now I have a client who wants that same ability, but he wants to have the numbers "searchable" which would be accomplished by placing them in partnumber order.

Does anyone have a suggestion on how to do this without messing up the scope ?

Tim

[paquitohm]

Hello,
In my case I use TSBrowse 6.0 with dbf table search.
When the user searches for articles, the program does an ordwildseek() and compiles the records (recnos()) into an array. Then oBrowse switches to "ARRAY" mode and displays the records of the collected array. The advantage of the process is that it is very fast. In addition, OrdWildSeek() does two searches, first a search for those that "start with" and then a search for those that "contain" so that first those that "start with" appear and then those that "contain". It would be very interesting if Mr. Nages added this feature for those who may be using xBrowse, which does not eliminate the possibility that the fwh developer can implement it more or less easily

[Original in spanish]
Hola,
En mi caso uso TSBrowse 6.0 con busqueda de tabla dbf.
Cuando el usuario hace una busqueda de artículos, el programa hace un ordwildseek() y recopila los registros (recnos()) en un array. Luego el oBrowse conmuta a modo "ARRAY" y muestra los registros del array recopilado. La ventaja del proceso es que es muy rapido. Ademas hace dos busquedas OrdWildSeek(), primero una busqueda para los que "empiezan por" y luego una busqueda para los que "contienen" de manera que primero aparecen los que "empiezan por" y despues los que "contienen". Interesantísimo sería que Mr. Nages añadiera esta característica para los que puedan estar usando xBrowse, lo cual no quita la posibilidad de que el desarrollador fwh lo pueda implementar más o menos facilmente

[karinha]

Use INDEX TEMPORARY/MEMORY

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=44476&p=269865&hilit=TEMPORARY&sid=4fd10da1789049fb5a17e330f466a2c9&sid=b63b008973d7938070eed9d9de140adb#p269865

Regards, saludos.

[nageswaragunupudi]

I have a datafile that I use for "parts used on invoices".

For quick filterning, I use SetScope functions. ( top and bottom ). It uses the invoice number as the value for both. Thus when the invoice is open, only those parts used for that specific one will display in the xBrowse control. It all works perfectly.

Now I have a client who wants that same ability, but he wants to have the numbers "searchable" which would be accomplished by placing them in partnumber order.

Does anyone have a suggestion on how to do this without messing up the scope ?

Tim

When we are using SetScope(), we can not change the index order. That is our constraint now.
There are quite a few alternatives, including reading into an array, etc. but they reduce overall speeds and also require major changes.
We need to look for simpler solutions without much change in our existing software.

1. Use Filter instead of Scope:

I am sure you will not like this idea at all.
Most of us are brain-washed to believe that filters are very very slow.
Not so.
Situation changed 3 decades back when FoxPro introduced Rushmore technology and our present DBFCDX uses similar technology to optimize filters.
Since we already have and index on the field "INVOICE",

Code: Select all  Expand view

INCOICE="ABCDEF"

is highly optimized.
That means we can use filters instead of Scopes to limit visibility of parts table pertaining to an invoice using filters also.
Once we use filters instead of scopes, we can change the index order of the parts browse to any indexed column and do seeks.

Note: Filters are still a bit slower than Scopes though the difference in speeds may not be perceptible.

2. Continue using scopes

(a) Use compound index "INVOICE+PARTNUMBER" instead of "INVOICE"
Still SetScope on invoice number works the same way even with this compound index.
At the same time, within the browse partnumbers appear in the ascending order.
When you want to search a part number, seek "INCVOICE" + "PARTNUMBER". We can do incremental search also.

(b) Continue your same program as it is. Instead of SEEK, use LOCATE to search the part number. I can assure that this also will be fast enough

You may consider options 2(b), 2(a), 1 in that order.

Please feel free to ask if you like us to support you with a sample program in any of these alternatives.

[Marc Venken]

Several members had filter, scopes etc. questions last period, so samples of optimised filters are VERY welcome.
Allmost all actions in my program are filter based.

[karinha]

Marc, how do you make your filters? I always prefer using these commands:

Marc, ¿cómo haces tus filtros? Siempre prefiero usar estos comandos:

Code: Select all  Expand view

// C:\FWH\SAMPLES\SETSCOPE.PRG

#include "FiveWin.ch"

ANNOUNCE RDDSYS
REQUEST OrdKeyNo, OrdKeyCount, OrdCreate, OrdKeyGoto
REQUEST DBFCDX, DBFFPT

FUNCTION Main()

   FIELD AGE

   LOCAL cAlias, aGeIni := 20, aGeFin := 30

   RDDSETDEFAULT("DBFCDX")
   RDDREGISTER( "DBFCDX", 1 ) // RDT_FULL

   // The following example illustrates the SET SCOPE command:
   CLOSE DATABASE

   USE CUSTOMER NEW

   INDEX ON AGE TO CUSTOMER

   // Only part numbers between 20 and 30 will be used
   SET SCOPE TO 20, 30  // AGES: MIN 20 MAX 30

   GO TOP

   XBROWSE()

   SET SCOPE TO

   cAlias := ALIAS()

   // Using INDEX ON... MEMORY / TEMPORARY
   // Only part numbers between 20 and 30 will be used
   INDEX ON AGE TAG 02 TO MARCTEMP FOR ( .NOT. EOF() ) .AND. ;
         ( cAlias )->AGE >= aGeIni                     .AND. ;
         ( cAlias )->AGE <= aGeFin MEMORY // TEMPORARY

   GO TOP

   XBROWSE()

   OrdDestroy( "MARCTEMP" )

   CLOSE DATABASE

RETURN NIL

/*
// Seealso

// ORDSCOPE(), SET SCOPEBOTTOM, SET SCOPETOP
*/

// FIN / END - kapiabafw@gmail.com
 

Regards, saludos.

[Marc Venken]

I use several.... because I saw the samples on the forum. therefore that the explenation of Mr. Rao could give me the final tip for the best.

Thinks I active use : (code like they are)

oBrw[1]:bChange := { || orderfilter("factart","factart",factinfo->document),orderfilter("detail","factart",factinfo->document),oBrwFol2[1]:gotop(),oBrwFol2[1]:refresh() }

Code: Select all  Expand view

function orderfilter(cAlias,cTag,cStart)
   local cOld_alias:=Alias(), NTag:= &cOld_alias->(indexord())
   default cStart:=""
   select &cAlias
   set order to tag &cTag
   if !empty(cTag)
      set scope to alltrim(cStart)
   endif
   &cAlias->(dbgotop())
   select &cOld_alias
   &cOld_alias->(dbsetorder(nTag))
return NIL

 

or this

Code: Select all  Expand view

  netopen("brwsetup","Code","TempXb")
  select TempXB
  set filter to brwnaam = cData
  tempXB->(dbgotop())
  hBrw:= FW_RecToHash()  // Start using Hash for all replaces below

 

:bBarGetAction := {|| ( oBrw:cAlias )->( MARC_SETFILTER( oBrw ) ) } // this for show the bitmap on the get and associated a action

Code: Select all  Expand view

FUNCTION MARC_SETFILTER( oBrw ) // My filter for Xbrowse heather gets.

   LOCAL cFilter := ""
   LOCAL n, oCol, uVal, cType, cVal1, cVal2, nGevonden
   LOCAL nZoekKnop, cExprt
   FOR n := 1 TO Len( oBrw:aCols )
      oCol  := oBrw:aCols[ n ]
      IF ! Empty( uVal := oCol:uBarGetVal )
         IF !Empty( cFilter )
            cFilter  += " .AND. "
         ENDIF
         cType    := ValType( uVal )
         if cType = "C"
           nGevonden = at("++",uVal) //  used because I want to look for 2 items in one get
         endif
         if cType = "C"
           nZoekKnop = at("²",uVal)  // used because I set a function key to this to start the search, so no click needed
         endif

         DO CASE
          CASE cType == 'C' .and. nZoekKnop > 0  
            uVal     := Upper( AllTrim( uVal ) )
            cVal1 = substr(uVal,1,nZoekknop-2)
            cFilter += '"' + cVal1 + '" $ UPPER( ' + oCol:CExpr + " )"
          CASE cType == 'C' .and. nGevonden > 0
            uVal     := Upper( AllTrim( uVal ) )
            cVal1 = substr(uVal,1,nGevonden-1)
            cVal2 = substr(uVal,nGevonden+2)

            cFilter += '"' + cVal1 + '" $ UPPER( ' + oCol:CExpr + " )"
            cFilter += " .AND. "
            cFilter += '"' + cVal2 + '" $ UPPER( ' + oCol:CExpr + " )"
         CASE cType == 'C'
            uVal     := Upper( AllTrim( uVal ) )
            cFilter += '"' + uVal + '" $ UPPER( ' + oCol:CExpr + " )"
         CASE cType == 'D'
            cFilter  += oCol:cExpr + " = " + ( uVal )
         OTHERWISE
            cFilter  += oCol:cExpr + " == " + cValToChar( uVal )
         ENDCASE
      ENDIF

   NEXT

   IF Empty( cFilter )

      IF ! Empty( dbFilter() )
         dbClearFilter()
         oBrw:Refresh()
      ENDIF

   ELSE

      IF !( dbFilter() == cFilter )
         oClp:SetText(cFilter) //  used because I generate filters on the fly with Xbrowse, copy paste into a lookup table for later use
         if !empty(alias())
           SET FILTER TO &cFilter
           oClp:SetText(cFilter)
           GO TOP
         else
           msginfo("Error.... Geen Alias beschikbaar")
         endif

         oBrw:Refresh()
      ENDIF

   ENDIF
   oBrw:maketotals()
   oBrw:SetFocus()

RETURN NIL
 

and this

oBrw[2]:bChange := { || Scope("factinfo","document","factart","factart"),oBrw22:refresh() }

Code: Select all  Expand view

function Scope(cParent,cData,cChild,cChildTag,cFoto)
  LOCAL cZoekdata
  LOCAL aScope1, aScope2
  DEFAULT cFoto:=""

  cZoekdata = cData
  if left(cZoekdata,1) = "#" //  breakpoint in order to look for 2 items in one field
    cZoekdata:= substr(cZoekdata,2)
  else
    cZoekdata:= upper( (cParent)->&cData)
  endif


  if cZoekdata = sys_scope
     return NIL
  endif
  sys_Scope = cZoekdata

  (cChild)->(dbsetorder(cChildTag))

  (cChild)->(ORDSCOPE(0, "" ) )
  (cChild)->(ORDSCOPE(1, "" ) )

  (cChild)->( ORDSCOPE(0, cZoekData ) )
  (cChild)->(ORDSCOPE(1, cZoekData ) )
  (cChild)->(DBGOTOP())

   //aScope := FW_DbfToArray( "JAN,FEB,MAR,APR,MAY", { || FIELD->REGION = cRegion } )
   //aScope1 := (cChild)->( FW_DbfToArray() )

  do case
    case upper(cChild) = "NOFOTOART"
      nofotoart->(dbsetorder("ID"))
      nofotoart->(ORDSCOPE(0, "" ) )
      nofotoart->(ORDSCOPE(1, "" ) )
      nofotoart->( ORDSCOPE(0, cZoekData ) )
      nofotoart->(ORDSCOPE(1, cZoekData ) )
      nofotoart->(DBGOTOP())
      aScope2 := (cChild)->( FW_DbfToArray() )
      ACopy(aScope1,aScope2)
      xbrowser(aScope2)

    case cFoto = "NOFOTO"
       Sys_wnd_foto = getorgfoto(nofoto->orgfoto)
       oSys_image:refresh()
  endcase
Return NIL

 

[Marc Venken]

The magic lies in the Optimized filter with CDX, but when is it optimized ?

By setting the indexes ?
You see option 2b of Mr. Rao ? Using Locate ? didn't know it )))

I think if more members request the samples from Mr. Rao he will help us

[TimStone]

Thank you.

I decided to use 2(a) because having them in partnumber order is sufficient at this time. However, I will happily explore the FILTER option.

I should mention that I'm using database objects, and though I tried the filter first, it wasn't working. I'm sure it was how I implemented it.

I, too, would appreciate some examples of these. Links to previous references on this forum would be fine.

Tim

[Marc Venken]

https://forums.fivetechsupport.com/viewtopic.php?f=3&t=38884
https://forums.fivetechsupport.com/viewtopic.php?f=3&t=41682
https://forums.fivetechsupport.com/viewtopic.php?f=3&t=44476&sid=e05ae0a7bc7aed81ed4b549cd3c6ee5f

[karinha]

Marc, SET FILTER TO &cFilter, doesn't this make the FILTER extremely SLOW? Some clients of the company I work for have giant databases, and using SET FILTER TO was driving them crazy, it took up to 30 minutes to make a simple FILTER. I changed everything to INDEX ON...MEMORY/TEMPORARY.

Marc, SET FILTER TO &cFilter, ¿esto no hace que el FILTRO sea extremadamente LENTO? Algunos clientes de la empresa para la que trabajo tienen bases de datos gigantes, y usar SET FILTER TO los estaba volviendo locos, les llevó hasta 30 minutos hacer un FILTRO simple. Cambié todo a ÍNDICE EN...MEMORIA/TEMPORAL.

Gracias, tks.

Regards, saludos.

[Marc Venken]

I have a dbf with 380.000 records, 32 fields and 7 tags. (242 Megabite)

the filter look like :

"WIC" $ UPPER( TAG ) .AND. "POLO" $ UPPER( NAAM ) .AND. "WHITE" $ UPPER( KLEUR )

This filter is build with the above function Marc_Setfilter using Xbrowse header gets.

The active tag =

upper(reflev)+upper(kleur)+upper(size)

If I try 10 filtercombinations the results are between 4-20 secs, not knowing why slower some times.

Thats why Mr. Rao's presentation will be great.

[Marc Venken]

Marc, I changed everything to INDEX ON...MEMORY/TEMPORARY.

This I want to try .... Just made a tmp index once in a while but as you confirm, it is fast.

Have you acode snipped where you use Xbrowse Header gets and use temp index instead of filter.
You would make

local ...
Field Tag, Naam, Kleur

Filter =
"WIC" $ UPPER( TAG ) .AND. "POLO" $ UPPER( NAAM ) .AND. "WHITE" $ UPPER( KLEUR )
Index will be like
index on upper(TAG)+upper(NAAM)+upper(KLEUR) tmp (sample, not the correct syntax)

cData = upper(Tag)+upper(naam)+upper(kleur)
ean->(dbseek(cData))

this is what you mean ?

[Enrico Maria Giordano]

upper(reflev)+upper(kleur)+upper(size)

I suggest this optimization:

Code: Select all  Expand view

upper(reflev+kleur+size)

[Marc Venken]

upper(reflev)+upper(kleur)+upper(size)

I suggest this optimization:

Code: Select all  Expand view

upper(reflev+kleur+size)

I see that the index will be faster since upper is from 3 to 1, but is it also nessesary to change how the filter will be created ?

Current filter :

"WIC" $ UPPER( TAG ) .AND. "POLO" $ UPPER( NAAM ) .AND. "WHITE" $ UPPER( KLEUR )