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