Begins is not focusing the first record

[philippoommen1]

Hi,
Please review the attached screen shot.
On any-key of search fill-in below code is written
FIND FIRST job-bom WHERE
job-bom.company = "01" AND
job-bom.order-no = "2003-001" AND
job-bom.item-no BEGINS vSearch:SCREEN-VALUE NO-LOCK NO-ERROR.
Any idea why Find First selecting 1492-BC12 instead 1492-ASPH3 ?

TIA
Philip

 

[RealHeavyDude]

For one the compiler might not have picked the index you expected it or the index it picked does not contain all fields necessary to retrieve the result you expect. You could probably work around that issue by specifying the USE-INDEX phrase ( did I really mention that ugly one ??? ) if you have one - but that is bad practice.

Generally FIND FIRST is bad practice. FIND is to retrieve a unique record. If it is not unique and you need a particalar one as the first in a particular order you need to specify a sort criteria - which you obviously can't with FIND. Therefore you should use a query or FOR EACH if order matters for your logic to make it hold water. In any other case it is almost by chance that you get the correct record.

Heavy Regards, RealHeavyDude.

 

[KrisM]

If you do not specify a sorting sequence then the 'find first' simply returns the first record the progress runtime happens to stumble upon. If this is not to your liking you must specify the sequence that must be used to define 'first'.

FOR FIRST job-bom WHERE
job-bom.company = "01" AND
job-bom.order-no = "2003-001" AND
job-bom.item-no BEGINS vSearch:SCREEN-VALUE NO-LOCK BY job-bom.item-no

 

[Rob Fitzpatrick]

If you do not specify a sorting sequence then the 'find first' simply returns the first record the progress runtime happens to stumble upon. If this is not to your liking you must specify the sequence that must be used to define 'first'.

FOR FIRST job-bom WHERE
job-bom.company = "01" AND
job-bom.order-no = "2003-001" AND
job-bom.item-no BEGINS vSearch:SCREEN-VALUE NO-LOCK BY job-bom.item-no

A BY phrase with FOR FIRST doesn't guarantee sort order. To borrow Tom Bascom's example from sports2000:

This shows the range of discount values in the customer table:

for each customer no-lock by discount:
  display custNum name discount.
end.

So this code looks like it returns a customer with a 0% discount:

for first customer no-lock by discount:
  display custNum name discount.
end.

But it does not.

 

[TomBascom]

Any idea why Find First selecting 1492-BC12 instead 1492-ASPH3 ?

Progress is doing exactly what you asked. Your query is the same as:

FIND FIRST job-bom WHERE
     job-bom.company = "01" AND
     job-bom.order-no = "2003-001" AND
     job-bom.item-no BEGINS "14" NO-LOCK NO-ERROR.

There appear to be 4 records that meet that criteria. There is nothing in your query which would identify any of the 4 as being more "first" than any other. For lack of any additional criteria the "first" record is undefined and, essentially, random. This ordering would probably not be repeatable after a dump & load and the order could possibly change if indexes are added, removed or rebuilt.

There does appear to be a "Seq" field that might be related to the order seen on the screen (from the limited data shown it seems to be). So you might be able to do something like:

for each job-bom no-lock where
    job-bom.company = "01" and
    job-bom.oder-no = "2003-001" and
    job-bom.item-no begins "14"
  by
    job-bom.seq:
 
  leave.
 
end.
 
if available( job-bom ) then ...