How to use CDX-Index as a FILTER

[ukoenig]

Hello,

There are questions, how to use CDX - Index as a filter
I have created the logic of this sample in my customer software
and it runs without problems in a network.

Code: Select all  Expand view  RUN


// get your EXE-path
// ------------------------
c_dir   := GetModuleFilename(GetInstance(),"SOFTWARE.EXE" + CHR(0), 255)
c_path   := left ( c_dir, rat( "\", c_dir) -1 )

// For Filter-Index use 1-4 Caracter from the Username
// You can store the Var in your user LOGIN-DBF ( Passwords )
// Every user got 4 ( or more ) caracters to create his own Filter-Index
// -------------------------------------------------------------------------
cINDEXPART := substr(WNetGetUser(),1,4)  // NAME 

// the user creates a Private Filter-Index
// -------------------------------------------------
cPRIVIND1 := "SF1_" + cINDEXPART
// The index will be  SF1_NAME

// looks in CDX-Compound index, if there is a old index
// when found destroys it
// --------------------------------------------------------------------
DBSELECTAREA(1)
// the CDX-Index is open, when you select the database

IF !empty( ORDNAME( ORDNUMBER("&cPRIVIND1") ) )
   ORDDESTROY( ORDNUMBER("&cPRIVIND1") )
ENDIF       

// creates a new Filter-Index
// The index will be at the end of the compound-Index
// ------------------------------------------------------------------

// Here you have to create a FOR-Index
// In this sample, the Index includes all Names with beginning "A"

ORDCREATE( ,"&cPRIVIND1",'SUBSTR(LOWER(Name) = "A"', ;
                  {|| SUBSTR(LOWER(Name) = "A" } , .F. )

// Set aktiv order to Filter-index
// -------------------------------------
DBSELECTAREA(1)
// If you want to use the filter-index ( .T. )
IF filterset = .f.
   // Without Filter normal Index
   // ----------------------------------
   DBSETORDER(1)
ELSE
   // Look for the Position of the filter-index
   // in the compound-Index, because in the meantime,
   // another user could add a index
   // ----------------------------------------------------------
   DBSETORDER(ORDNUMBER("&cPRIVIND1"))
ENDIF

// here the private- Index will be inside the Index with the
// same name like the database
// Sample CUSTOMER.CDX can have
// CUST1, CUST2, CUST3 and SF1_NAME
//
// SF1_NAME will be your PRIVAT-INDEX on Position 4
//

// There is another way, how to do it
// --------------------------------------
cNEWINDEX := c_path +"\SF1_" + cINDEXPART + ".CDX"
// extra CDX-File  = SF1_NAME.CDX

ORDCREATE( "&cNEWINDEX","Test1","LOWER(Name)", ;
                     {|| LOWER(Name) } , .F. )

With that, you will have 2 CDX-Files
CUSTOMER.CDX and SF1_NAME.CDX
SF1_NAME.CDX will have the Index => Test1

When you want to use the same filter without changes
You have to open the Index with the name, because
the index is not included in the main-index

The index-files have different names, so there is no
problem in a network and it is very fast.



Regards

Uwe
[/code]

[Otto]

Uwe, thank you for your explanations. I will give it a try.
Regards,
Otto

[ukoenig]

Hello Otto,

have a look once again at the topic
there is still another way how to do it
with a extra index.
I had to change the sample-source a bit,
because in my software, the user can have
a own database as well.

Regards

Uwe

[Maurizio]

Hello Otto

It is faster if you use TEMPORARY INDEX

TEMPORARY
If this option is specified, a temporary index is created which is automatically destroyed when the index is closed. The temporary index may be created in memory only or in a temporary file.

Maurizio

[StefanHaupt]

Otto,

to simulate filters with cdx I suggest this.

First, choose your most important filter condition and create a TAG.

Code: Select all  Expand view  RUN

INDEX ON <IndexExpression> TAG <tag> FOR <filter>

Then you can create your second filter condition with Set Scope...

Code: Select all  Expand view  RUN

SET SCOPETOP TO <First IndexExpression>
SET SCOPEBOTTOM TO <Last IndexExpression>

This way you have a very fast filter. If you play with <IndexExpression>, you can optimize your index.

[ukoenig]

Hello Stefan,

Scope is ok, but what to do in a network.
Each user wants to have his own filter, maybe at the same time ?
When you create a index with different names, there will be no conflict.

Regards

Uwe

[Otto]

http://www.atzwanger.com/marketingliste/marketinglist.html

To all,
Thanks to all for your help.

Here is a little video to demonstrate what I need to do:
I have a customer browser and a marketing – listbox.

I can drag customers to the listbox and the customers are added to the appropriate
marketing group. A double click on the marketing listbox sets a filter in the
customer browser. Only the customers of the group are shown (browser turns to green) – but all the functionality remains: index, seek, I can add a new marketing group – in the example I set a filter on Austria
and the new group is PopStars. Then I drag only from the Austrian customer stars to the
marketing group. A double click shows only the Austrian stars (keep in mind that the data is only demo-
Elton John is not Austrian).
All is working the problem is, that if there are more users working on the same datebase
In a network set filter is slow.
Now I have decided to make a kind of “RECORDSET” I copy the selected customers into an array
and browse the array.


Regards,
Otto

[James Bott]

Otto,

I think you can do this with an index and scopes.

If each person can only be in one group then you can add group field to the person file. Index on the group field and then set top and bottom scopes to that group.

If each person can be in more than one group, then you will need a separate file containing just two fields, group ID and person ID. This way you can have multiple records for each person, each with a different group ID. Index this file on group and set the top and bottom scopes for the selected group. Then link the person IDs to the person file to build the browse.

Both of these use scopes which are much faster than setFilter().

James

[Otto]

Hello James,

thank you very much for your help.
In my case it is the second option what I need - each person can be in more than one group - and I have a dbf
with group ID and person ID.

>Index this file on group and set the top and bottom scopes for the selected group.
>Then link the person IDs to the person file to build the browse.

I don’t understand how to link the person IDs to the person file. Is this done with set relation?

@ 2, 2 LISTBOX oLbx FIELDS ;
…
SELECT person-> groupID FOR TempGroupID TO TempGroupID ;
OF oDlg ;
SIZE 300, 200

If yes, then the ListBox must have the INDEX on GroupID and in the “scoped” view
I can’t use the other Indexes like name, street, … anymore.


Regards,
Otto

[James Bott]

Otto,

There is a little trick. You build the browse using person fields, but the scope is limited to the specified group that is in the group link file. The trick is to do this using the bSkip codeblock like this:


oLbx:bSkip:= {| nRecs | (nSkipped:=oGrplink:skipper( nRecs ), oPerson:seek( oGrplink:personID ), nSkipped ) }

Below I have written a very simple and untested example. If you need more help, then let me know.

Since you have TData I have used TData in the example. It is much easier to code using a database class.

James

Code: Select all  Expand view  RUN

//---------------------------------------------------------------------------//

function browseGroup()

   local oGrplink,oPerson,oLbx,oWnd

   oGrplink:= TGrplink():new()
   oGrplink:setTopScope("2000")
   oGrplink:setBottomScope("2000")
   oGrplink:gotop()

   oPerson:= TPerson():new()

   define window oWnd title "Test group browse"

   listbox oLbx ;
      fields oPerson:last, oPerson:first, oPerson:street,;
      headers "Last","First","Street";
      alias oPerson:cAlias;
      of oWnd
   
   oLbx:bSkip:= {| nRecs |  (nSkipped:=oGrplink:skipper( nRecs ), oPerson:seek( oGrplink:personID ), nSkipped ) }
   
   oWnd:oClient:= oLbx
   
   activate window oWnd

return nil

//---------------------------------------------------------------------------//

class TGrplink from TData
   method new()
endclass

method new() class TGrplink
   super():new(,"grplink")
   ::use()
   ::setOrder(1)
   ::gotop()
return self

//---------------------------------------------------------------------------//

class TPerson from TData
   method new()
endclass

method new() class TPerson
   super():new(,"person")
   ::use()
   ::setOrder(1)
   ::gotop()
return self

//---------------------------------------------------------------------------//

// eof

[StefanHaupt]

Hi Uwe,

Scope is ok, but what to do in a network.
Each user wants to have his own filter, maybe at the same time ?
When you create a index with different names, there will be no conflict.

Every user can have its own scope and its own filter, because these operations are done in the local instance of the application, not on the server. So every user is independent of any other user.

As all users open the dbf with the same alias, you also don´t need different index filenames.