help with index

[Judi201]

Hi All!

I often need to find a record that contains jobsnumb C(8) and ordrnumb C(8) so I want an index that will SEEK or SCAN FOR or whatever. The keys will come from textboxes where user inputs such data as jobsnumb 11098 and ordrnumb 9-1. I cannot seem to create an index that will find the combination of jobsnumb + ordrnumb. Would someone please show me the right way to do this? I think I have tried every combination of trim and pad I can think of and it still will not 'find' the record

Thanks for your response.

Judi

 

[bborissov]

CREATE CURSOR Test (JobsNum C(8), OrderNum C(8))
INSERT INTO Test VALUES('123456','1')
INSERT INTO Test VALUES('213456','1')
INSERT INTO Test VALUES('11098','921')
...
INDEX ON JobsNum+OrderNum TAG Test
LOCATE
lcJobToSeek = PADR('11098',8)
lcOrdToSeek = PADR('921',8)
SEEK lcJobToSeek+lcOrdToSeek
BROW NORMAL

Is that what you want?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[brigmar]

CREATE CURSOR  thing (jobsnumb c(8), ordnumb c(8))
INDEX ON (jobsnumb+ordnumb) TAG job_ord

INSERT INTO thing VALUES ("11098","9-1")
INSERT INTO thing VALUES ("11098","9-2")
INSERT INTO thing VALUES ("11098","9-3")
INSERT INTO thing VALUES ("22334","9-1")
INSERT INTO thing VALUES ("22334","9-2")
INSERT INTO thing VALUES ("22334","9-3")
INSERT INTO thing VALUES ("1109","89-2")

ls_jobnum = "11098"
ls_ordnum = "9-2"

toseek = PADR(ls_jobnum,8)+PADR(ls_ordnum,8)
IF SEEK( toseek )
  ? "FOUND [&toseek] AT RECORD\"+TRANSFORM(RECNO(),'999')
ELSE
  ? "COULDNT FIND [&toseek]"
ENDIF

When the index is created, the PADR() is implicit.
When searching, you should explicitly PADR() each field.

 

[Judi201]

Borislav, brigmar,

Thanks for your prompt responses. I can make either of your examples work and they are exactly what I have been trying to do. I wish I could understand what little things I was doing wrong, but have changed too many times to try to post code to ask for your help.

I am using Borislav's code because it is exactly what I would do (except yours works .)

When the index is created, the PADR() is implicit.

brigmar, you answered one of the things I had wondered about, but don't think I have seen it in print.

Many thanks to you both.

Judi

 

[brigmar]

I'm learning as I go...

From the VFP 9.0 Help file --> index expressions --> considerations:

"Visual FoxPro does not support variable length index keys. If you attempt to build an index with a key that varies in length, the key is padded with spaces."

(which rules out using TRIM() and it's family)

"Visual FoxPro pads index keys based on Character, Varchar, Varchar (Binary), and Varbinary fields and expressions."

(which tells me about implicit padding)


and it was only through trial-and-error that I came across the correct padding to employ at SEEK time....

 

[Judi201]

brignar,

Thanks for that. I have this bad habit of scanning something in help and thinking 'I know that' and not reading closer and picking up the inferences. Bad habit that I will work to correct.

Thanks again.

Judi