vexing index issue
Mitch Theophila/MGT Computer Solutions
theophila at compuserve.com
Wed Aug 3 23:45:36 PDT 2005
hi,
recently i had an keyed lookup behave unexpectedly during retrieval of records
known to exist and match my key criteria. the lookup consistently reported
that it had succeeded and found its matching record, but the contents of the
looked-up record did not match my specified search criteria.
filepro v.4.08.09
OS suse linux v.6.3
in an inventory file, index 0 is built on field 9 (vendor) and field 1 (PartNo)
field
1 PartNo 25 allup
: : :
9 Vendor 30 allup
: : :
this demand index is rebuilt before each execution of the problem process.
the pertinent code (following) is in a rreport -fp process that executes on the
first record only (see 'exit' stmt on line 13). the problem lookup is on line 17.
|
6 ------- - - - - - - - - - - - - - - - -
¦ If: x="" ¦
Then: x(len(9),edit(9),g)="ACME" ¦
7 ------- - - - - - - - - - - - - - - - -
¦ If: 'input count recs skipped recs added recs updated ¦
Then: AA(5,.0,g)="0" ; AB(5,.0,g)="0" ; AT(5,.0,g)="0" ; AU(5,.0,g)="0" ¦
8 ------- - - - - - - - - - - - - - - - -
¦ If: 'partno desc price ¦
Then: xa(len(1),edit(1),g)="" ; xc(len(20),edit(20),g)="" ; xe(len(4),edit(4),g)=0:
9 ------- - - - - - - - - - - - - - - - -
loop ¦ If: ¦
Then: ¦
10 ------- - - - - - - - - - - - - - - - -
¦ If: @sk eq "BRKY" ¦
Then: msgbox "Add cancelled after reading"<AA<"records."; exit ¦
11 ------- - - - - - - - - - - - - - - - -
¦ If: ¦
Then: gosub ShowIt ¦
12 ------- - - - - - - - - - - - - - - - -
¦ If: 'read import record ¦
Then: import ascii imp=allied.txt f=~ r=\n ¦
13 ------- - - - - - - - - - - - - - - - -
¦ If: not imp 'end of import file ¦
Then: msgbox "Records added:"<AT<"updated:"<AU ; exit ¦
14 ------- - - - - - - - - - - - - - - - -
¦ If: 'store import data into memory ' in 2005-07, only 2 fields ¦
Then: xa=imp(1);xe=imp(2) ¦
15 ------- - - - - - - - - - - - - - - - -
¦ If: '1:PARTNO 2:COST ¦
Then: AA = AA + "1" ¦
16 ------- - - - - - - - - - - - - - - - -
¦ If: ¦
Then: xi = x & xa ¦
17 ------- - - - - - - - - - - - - - - - -
¦ If: 'test for existence of this item ¦
Then: lookup chk=(filename) k=(xi) i=0 -nx ¦
18 ------- - - - - - - - - - - - - - - - -
¦ If: chk ¦
Then: goto upditem ¦
19 ------- - - - - - - - - - - - - - - - -
newitem¦ If: ¦
Then: ¦
20 ------- - - - - - - - - - - - - - - - -
¦ If: ¦
Then: AT = AT + "1" ¦
21 ------- - - - - - - - - - - - - - - - -
¦ If: ¦
Then: filename=@FI ¦
22 ------- - - - - - - - - - - - - - - - -
¦ If: 'get a free record ¦
Then: lookup inv=(filename) r=free -e ¦
23 ------- - - - - - - - - - - - - - - - -
¦ If: 'post variables to free record ¦
Then: inv(21)=xa;inv(4)=xe;inv(30)="Y";inv(9)=x ' inv(20)=xc ¦
24 ------- - - - - - - - - - - - - - - - -
¦ If: ¦
Then: inv(1) = inv(21) ¦
25 ------- - - - - - - - - - - - - - - - -
¦ If: ' get next import record ¦
Then: goto loop ¦
------- - - - - - - - - - - - - - - - -
¦
¦
29 ------- - - - - - - - - - - - - - - - -
upditem¦ If: ¦
Then: ¦
30 ------- - - - - - - - - - - - - - - - -
¦ If: ¦
Then: ' msgbox chk(9) & chk(1) {":"{ xi { ""; goto loop ¦
31 ------- - - - - - - - - - - - - - - - -
¦ If: chk(9) ne x ¦
Then: msgbox "no vndr match" < chk(9) < xi { "" ; AB = AB + "1"; goto loop >
32 ------- - - - - - - - - - - - - - - - -
¦ If: chk(1) ne xa ¦
Then: msgbox "no part match" < chk(1) < xa { "" ; goto newitem ¦
33 ------- - - - - - - - - - - - - - - - -
¦ If: ¦
Then: AU = AU + "1" ¦
34 ------- - - - - - - - - - - - - - - - -
¦ If: ' post variables to item record ¦
Then: chk(4)=xe ' chk(21)=xa;chk(20)=xc;chk(30)="Y";chk(9)=x;chk(1)=xa ¦
35 ------- - - - - - - - - - - - - - - - -
¦ If: ' get next import record ¦
Then: goto loop ¦
36 ------- - - - - - - - - - - - - - - - -
¦ If: ¦
Then: ¦
37 ------- - - - - - - - - - - - - - - - -
SHOWIT ¦ If: ¦
Then: show "Records read:" < AA & " added:" < AT & " skipped:" < AB &>
38 ------- - - - - - - - - - - - - - - - -
¦ If: ¦
Then: return ¦
39 ------- - - - - - - - - - - - - - - - -
@DONE ¦ If: ¦
Then: msgbox "DONE - Records read:" < AA & " added:" < AT & " skipped>
40 ------- - - - - - - - - - - - - - - - -
during excution of the loop starting at line 9, xa and another variable are set
(line 12) to values read from an external ascii file, one (new) set of values
for each time thru the loop.
the constant vendor name, "ACME", is valid and there are instances of ACME parts
records in the inventory file.
the imported part numbers may or may not already be in the file; we want to
update partno records already there and add records not already there.
here is the problem: the lookup on line 17 always succeeds, chk is always true,
and process flow always branches to upditem.
in upditem, lines 31 and 32 test the conditions of success of the lookup. in
every case vendor matches but part doesn't, even though some vendor/part
combinations are known to exist. (this assertion was verified by the msgbox on
line 30, commented in the exhibit.) so in every case the branch to newitem is
executed and a new record is added, even though it may duplicate a part record
that already exists.
if the process were reexecuted with the same data, the expected result is that
all records should be updated and none added, since after the first run, all
the part records present in the data have been added to the file. however all
part lookups fail to match on part number, as above, and the entire set of
records is readded again.
i don't see what the issue could be. did i hit an 'undocumented feature'
particular to this version (4.08.09), or am i ignorant of something fundamental
about filepro? i am hoping that someone here can illuminate my understanding.
the problem was worked around by using another index, an automatic index built
on field 1 only, and using getnext in the lookup to search for records with the
desired vsndor when a match on partno didn't bring up the right vendor. worked
great. but all that should have been unnecessary, if i could have used the
compound key in the way i describe.
Any ideas?
thanks in advance,
.mitch theophila/MGT Computer Solutions
/apologies for the long post
More information about the Filepro-list
mailing list