odd lookup problem

[Bruce Easton]

On 11/8/10 9:43 AM, Richard Kreiss wrote:
>
>> -----Original Message-----
>> From: Kenneth Brody [mailto:kenbrody at spamcop.net]
>> Sent: Monday, November 08, 2010 9:25 AM
>> To: rkreiss at gccconsulting.net
>> Cc: Filepro-list at lists.celestial.com
>> Subject: ***SPAM*** Re: odd lookup problem
>>
>> On 11/6/2010 11:40 AM, Richard Kreiss wrote:
>>> Has anyone encountered this:
>>>
>>> I have a lookup:
>>>
>>> 192  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:
>>>          Then: lookup SALES = sales_journal  k=16   i=C -nx
>>> 193  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:         SALES
>>>          Then:         GOTO next_w
>>> 194  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:
>>>          Then: zv="This person is not a member yet\nDo you want to continue
>> entering this sale?\n(Y)es or (N)o ->   "
>>> As you can see, if the record exist, the program braches, if not it drop
>> through and pops up in input box.
>>> However, the lookup actually needs to find the latest sales journal record
>> so I modified the lookup as follows:
>>> ::lv=master_code&"99"
>> The first example uses field 16, while this uses field master_code.
>>
>>> The index is built on the master_code and renewal(2,.0) fields
>> What is the definition of master_code in this file, and what is the definition of
>> it in the sales_journal file?
>>
>>> 192  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:
>>>          Then: lookup SALES = sales_journal  k=lv   i=C -nL
>>> 193  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:         SALES
>>>          Then:         GOTO next_w
>>> 194  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:
>>>          Then: zv="This person is not a member yet\nDo you want to continue
>> entering this sale?\n(Y)es or (N)o ->   "
>>> Here is the interesting problem, I always get a false on line 193 even though
>> there are records in the sales journal for this person.  However, if I modify
>> the code further to:
>>
>> What is the actual value of field lv?
>>
>>> 192  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:
>>>          Then: lv=master_code&"99"
>>> 193  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:
>>>          Then: lookup SALES = sales_journal  k=16   i=C -nL
>> Now you're back to field 16 as the lookup key.
>>
>>> 194  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:         NOT sales
>>>          Then:         GOTO make_zv
>>> 195  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:         SALES  and sales(3) = master_code
>>>          Then:         GOTO next_w
>>> 196  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>> make_zv◄ If:
>>>          Then: zv="This person is not a member yet\nDo you want to continue
>> entering this sale?\n(Y)es or (N)o ->   "
>>> 197  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>> yn_sale◄ If:
>>>          Then: input popup("18","-1") y ""{zv
>>> 198  -------   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -   -
>>>          ◄ If:         @sk = "BRKY"
>>>          Then:         END
>>>
>>> Line 195 now is true and the correct sales journal record is found.
>>>
>>> Can anyone explain why version 1 fails but works when not sales is added?
>> You keep changing the lookup key as well, so it's tough to diagnose where
>> the problem lies, as you keep changing more than one variable.
>>
>> And you never mentioned that the first version failed, just that it "needs to
>> find the latest sales journal record".
>>
>> --
>> Kenneth Brody
>>
> First version on master_code, field 16, worked.  The problem is I need to check for the latest sales journal record, not just the existence of a record.  This required that I change the key to use the master_code and renewal number.  Since the renewal number is not known,  master_code&"99" with a lookup to the next lowest match.
>
> As I indicated, when I made the change, the if SALES right after the lookup statement showed false and dropped through to no sales journal record, make sales routine.
>
> When not sales was added after the lookup statement and before the if: sales, and there were records found, the if SALES line showed true.
>
> What I am asking is, why should it be necessary to have a not sales before the if sales when doing a lookup to the next lowest match?
>
> Richard
On line 193, why are you using 16 instead of the key 'lv' that you 
assigned right before that?  In any case, with too many unknowns and too 
many things changing at once as Ken said, I think it's impossible to 
determine what could have caused the behavior you already seen.

But to answer your last question, I would say because you should always 
have that 'not' test.  There is always the chance that the lookup (of 
any type of match) will not match anything on file and you should 
provide for that.  Otherwise, in this case, if you didn't have the 'not' 
test, you may get a filepro error on the next line since it references a 
field ('sales(3)') on the if line.

But as to why it is or is not matching when you think it shouldn't or 
should, respectively, I believe you'd have to show what the debugger is 
showing for the value of the key and any successful lookup (joining) 
fields from the lookup file, as well as the debugger's true/false 
values, starting with the lookup line and onward through the tests that 
follow (that reference the lookup file/alias).

Bruce


Bruce Easton
STN, Inc.