index dbf character numbers

[AUGE_OHR]

I get argument error when trying to use strzero.

what Type is your FIELD

[Claudio Ricardo]

Hi...
Maybe you can modify all records in db... (Backup first)

Code: Select all

Local cCode := ""
Use db New
DbGoTop ()
	While .NOT. Eof ()
		cCode := db->Code
		Replace Code With StrZero ( Val ( cCode ),10)
		Skip
	EndDo	
Commit
Use

And search:

Code: Select all

Use db New
Index On Code To iCode
	If DbSeek (cCodeYouFind, .T.)  // Much better to old Seek and no need Go Top previous
		? Found
	EndIf

[franco]

field is character

[edk]

I honestly do not know if I understand correctly, but I am giving an example where in the invoice.dbf database the field "inv_number" is a character type and the properly indexed file browse and searches correctly invoice numbers in ascending numerical order.

Code: Select all

#include "hmg.ch"

Function Main()

SET CENTURY ON
SET DATE TO ANSI

DEFINE WINDOW test AT 0 , 0 WIDTH 400 HEIGHT 600 TITLE "Demo" MAIN NOSIZE NOMAXIMIZE;
	ON INIT PrepareDBF()

	@ 20,10 TEXTBOX tSeek WIDTH 120 VALUE "" INPUTMASK "9999999999" ;
        ON CHANGE Iif( ! Empty( test.tSeek.Value ) , ( DBSEEK (VAL ( test.tSeek.Value )), test.Browse_1.Value := RecNo()) , Nil )
		
	@ 70,10 BROWSE Browse_1 ; 
      OF test ; 
      WIDTH 350 ; 
      HEIGHT 430 ; 
      FONT "Arial" ; 
      SIZE 9 ; 
      HEADERS { "Invoice #","Date", "Customer" } ; 
      WIDTHS { 120,80, 120 } ; 
      WORKAREA INVOICE ; 
      FIELDS { "INV_NUMBER", "INV_DATE", "CustomerID" } ; 
      ON GOTFOCUS Nil;
      ON CHANGE Nil ; 
      ON DBLCLICK Nil ; 
      JUSTIFY { 0,0, 0 } 
	
	
END WINDOW

CENTER WINDOW test
ACTIVATE WINDOW test

Return Nil

***********************************
Function PrepareDBF()
Local aStru := { {"INV_NUMBER", "C", 10, 0}, {"INV_DATE", "D", 8, 0}, {"CustomerID", "N", 10, 0 } }
Local i, k := 1
IF ! FILE ( "Invoice.dbf" )
	WAIT WINDOW "Preparing invoice.dbf, please wait ..." NOWAIT
	DBCREATE( "Invoice.dbf", aStru )
	USE Invoice.dbf NEW
	FOR i := 122222 TO 1 STEP -1
		IF i % 100 = 0
			k++
		ENDIF
		DO EVENTS
		APPEND BLANK
		REPLACE inv_number WITH AllTrim( Str ( i ) )
		REPLACE inv_date WITH Date() - k
		REPLACE CustomerId WITH hb_RandomInt ( 1, 1000 )
	NEXT i
	WAIT CLEAR
	CLOSE DATA
ENDIF

IF ! FILE ( "Invoice.ntx" )
	USE Invoice.dbf NEW
	WAIT WINDOW "Preparing invoice.ntx, please wait ..." NOWAIT
	INDEX ON Val ( inv_number ) TO Invoice.ntx
	CLOSE DATA
	WAIT CLEAR
ENDIF

SELECT 1
USE Invoice.dbf Index Invoice.ntx

RETURN

[franco]

Edk, This works but my invoice numbers started with 555B, then went from 10,000, then went from 100,000.
Your idea works but you have to enter the complete invoice number.
I does not go to first 100000. if your first invoice is 100000 and I enter 1 invoice is not found.
In numeric search it does not soft seek or go to first invoice starting with 1, then 3 and so o if your scanning for invoice 132132.
Can you make the browse reset for each entry.
Thanks
Franco

[edk]

Hi Franco. Is the 555B a fixed prefix? Could you please attach a sample file with invoice numbers?

[mol]

Simply use str

[AUGE_OHR]

hi,

field is character

do you also want to add "Date" (as String)

EDK show you the Way to use VAL() and numeric Index
but if you want to add "more" like Date you need a String for IndexKey()

Code: Select all

 INDEX ON STRZERO( VAL( FIELD->cNumber), 6) + DTOS( FIELD->dDATE)

 @ X,Y GET nNumber PICTURE 999999
 
 cSeek := STRZERO( nNumber, 6) + DTOS( DATE() )

[edk]

Do you want to be able to search by the initial fragment of the invoice number, but to show all the matching results in numerical rather than character-ascending order?
I think that such an index key cannot be built. For this reason, Browse cannot be used. But ... you can build a Virtual Grid, where you first need to create a numeric table with invoice numbers matching the search pattern, sort the table according to the ascending order, and then display the DBF file content in the grid according to the order from the previously created numeric table.

Bez tytułu.png (114.3 KiB) Viewed 2178 times

I only have some concerns about the performance of such a solution in a network environment (the database is searched twice, the first time to create a list, then to display it)

Sample:

Code: Select all

#include "hmg.ch"

Function Main()
Local aDBFInvNum := {}
Private cPrefix := "555B"
SET CENTURY ON
SET DATE TO ANSI

DEFINE WINDOW test AT 0 , 0 WIDTH 400 HEIGHT 600 TITLE "Demo" MAIN NOSIZE NOMAXIMIZE;
	ON INIT PrepareDBF()

	@ 20,10 TEXTBOX tSeek WIDTH 120 VALUE "" INPUTMASK "9999999999" ;
        ON CHANGE If( ! Empty( test.tSeek.Value ) , ;
			( aDBFInvNum := GetInvNumList ( test.tSeek.Value, aDBFInvNum ), ;
			  Grid_1Refresh ( aDBFInvNum ) ), ;
		  Nil )


	@ 70,10 GRID Grid_1 ;
		WIDTH 350 ;
		HEIGHT 430 ;
		HEADERS { "Invoice #","Date", "Customer" } ; 
		WIDTHS { 120,80, 120 } ; 
		JUSTIFY {GRID_JTFY_LEFT, GRID_JTFY_RIGHT, GRID_JTFY_RIGHT } ;
		ON GOTFOCUS Nil ;
		ON CHANGE Nil ;
		VIRTUAL ;
		ITEMCOUNT LEN( aDBFInvNum ) ;
		ON QUERYDATA DBFQuery ( aDBFInvNum ) 		

	
	
END WINDOW

CENTER WINDOW test
ACTIVATE WINDOW test

Return Nil

***********************************
Function PrepareDBF()
Local aStru := { {"INV_NUMBER", "C", 10, 0}, {"INV_DATE", "D", 8, 0}, {"CustomerID", "N", 10, 0 } }
Local i, k := 1
IF ! FILE ( "Invoice1.dbf" )
	WAIT WINDOW "Preparing invoice1.dbf, please wait ..." NOWAIT
	DBCREATE( "Invoice1.dbf", aStru )
	USE Invoice1.dbf NEW
	FOR i := 133333 TO 10000 STEP -1
		IF i % 100 = 0
			k++
		ENDIF
		DO EVENTS
		APPEND BLANK
		REPLACE inv_number WITH cPrefix + AllTrim( Str ( i ) )
		REPLACE inv_date WITH Date() - k
		REPLACE CustomerId WITH hb_RandomInt ( 1, 1000 )
	NEXT i
	WAIT CLEAR
	CLOSE DATA
ENDIF

IF ! FILE ( "Invoice1.ntx" )
	USE Invoice1.dbf NEW
	WAIT WINDOW "Preparing invoice1.ntx, please wait ..." NOWAIT
	INDEX ON inv_number TO Invoice1.ntx
	CLOSE DATA
	WAIT CLEAR
ENDIF

SELECT 1
USE Invoice1.dbf Index Invoice1.ntx

RETURN


******************************************
FUNCTION DBFQuery ( aDBFInvNum )
Local nQRow := This.QueryRowIndex
Local nQCol := This.QueryColIndex

IF nQRow = 0 .OR. nQCol = 0 .OR. nQRow > Len ( aDBFInvNum ) .OR. nQCol > FCOUNT()
	RETURN
ENDIF

SEEK cPrefix + AllTrim( Str ( aDBFInvNum [ nQRow ] ) ) + " "
IF FOUND()
	This.QueryData :=  invoice1->&(FIELD( nQCol ))
ENDIF
Return
******************************************
FUNCTION Grid_1Refresh ( aDBFInvNum )
IF test.Grid_1.Itemcount < Len (aDBFInvNum)
	test.Grid_1.Itemcount := 0
ENDIF
test.Grid_1.Itemcount := Len (aDBFInvNum)
test.Grid_1.value := 1
test.Grid_1.refresh
RETURN 

******************************************
FUNCTION GetInvNumList ( cSeekInvNum, aDBFInvNum )
Local nItem := 0
StopWindowEventProcedure ("test", .T.)
StopControlEventProcedure ("tSeek", "test", .T.)

cSeekInvNum := AllTrim ( cSeekInvNum )
aDBFInvNum := {}
GO TOP
SEEK cPrefix + cSeekInvNum

DO WHILE invoice1->inv_number = cPrefix + cSeekInvNum .AND. !EOF()
	AADD( aDBFInvNum, Val ( StrTran( invoice1->inv_number, cPrefix, "" ) ) )
	SKIP
	Do Events
ENDDO

StopControlEventProcedure ("tSeek", "test", .F.)
StopWindowEventProcedure ("test", .F.)
RETURN ASORT ( aDBFInvNum )

[AUGE_OHR]

hi,

you can use SCOPE for "cPrefix"
you can find "part of" String using $

Code: Select all

INDEX ON FIELD->cPrefix + STRZERO(VAL((FIELD-cNumber),6)

Code: Select all

SEEK(cPrefix)
SET SCOPE TO (cPrefix)
SET FILTER TO LTRIM(STR(nNumber)) $ FIELD->cNumber

BROWSE

after BROWSE you need to "reset"

Code: Select all

SET FILTER TO
SET SCOPE TO