index dbf character numbers

[franco]

I started invoice 10000. Then later thought it would be wise to change when I changed from clipper to hmg to 100000 to keep then more uniform.
Can anyone think of a way to index these in order. This is a character field so 10002 comes after 100001.
This was a big mistake when searching in an index the numbers are all mixed up.
Thanks, Franco

[dragancesu]

Invoice number is numeric, why store in character field. Index number field os ok, it's in clipper and hmg

[franco]

I use this thru program so was better a character, now is a delema.
I also have a date field in table. I have tried indexiny in inv + date but forgot to change date format to yymmdd I am going to try this.

[SALINETAS24]

I started invoice 10000. Then later thought it would be wise to change when I changed from clipper to hmg to 100000 to keep then more uniform.
Can anyone think of a way to index these in order. This is a character field so 10002 comes after 100001.
This was a big mistake when searching in an index the numbers are all mixed up.
Thanks, Franco

Hola Franco, para que el indice por un campo "ALFABETICO" tenga el mismo comportamiento que un indice por campo "NUMERICO", debes rellenar los espacios por CEROS "0"..., quedaria algo asi

0000001
0000002
0000003
0000010
0000011
0000012
0000101
0000102
0000110
0001000
0001001
0002010
0002030
0010000
0010001
0010002

Saludos y vamos con una cervecita bien fresca

[AUGE_OHR]

ho,

i do use STRZERO() when convert Numeric to String to "hold LEN()" of String which a Index need

Code: Select all

   cSeek := STRZERO(nNum, nLengh_of_Field) + DTOS(dDate)

[serge_girard]

Franco,

Once this occured to me also. I added new field (eg: NEW_INV_NO) with correct settings; filled it up with correct values; changed programs, indexes etc.

Serge

[franco]

SALINETAS24, I can not add 0 before number because can not seek not knowing where number starts.
Serge, I have customers with 25 years of invoices and invoice items. It's hard to change their history.
the date did not work for me because 100898 come right after 10089.
Jimmy I will try to index on value then seek value.
Thanks to you all.

[AUGE_OHR]

hi,

Jimmy I will try to index on value then seek value.

create new Index this Way

Code: Select all

   USE BLABLA EXCLUSIVE
   INDEX ON STRZERO(FIELD->ABC, 10)+DTOS(FIELD->DDATE) TO XYZ

so you can SEEK() this

Code: Select all

   cSeek := STRZERO(nNum, 10) + DTOS(dDate)
   SEEK(cSeek)

[edk]

hi,

Jimmy I will try to index on value then seek value.

create new Index this Way

Code: Select all

   USE BLABLA EXCLUSIVE
   INDEX ON STRZERO(FIELD->ABC, 10)+DTOS(FIELD->DDATE) TO XYZ

so you can SEEK() this

Code: Select all

   cSeek := STRZERO(nNum, 10) + DTOS(dDate)
   SEEK(cSeek)

(...) This is a character field so 10002 comes after 100001.(...)

IMHO it should be like this:

Code: Select all

INDEX ON Val ( Inv_Number ) TO indexfile

and Seek just like:

Code: Select all

SEEK Val ( cSeek_Inv_Number )

[franco]

I get argument error when trying to use strzero.
I can get seek to work. but I always browse the table before picking. This is when the order is not indexed by length of field.
browse gets
10001
10002
100021
10003