complimentary lookup criterion question

[Bruce Easton]

Mark,   if you build an index across the fields separately, where the 
first field(s) is/are the ones that must match exactly to the key you 
supply, and with the last two fields as modified date then time (where 
each of those fields have filepro date, time system edits), then you 
should be able to find the most recently modified records with a key 
similar to this:

lookup lastm=(myfile)  k=(myexactkeys&"12/31/299923:59:59") i=A  -nl

(assuming that your date edit to match this example would be mdyy/ and a 
system time edit).

Since it's not an exact match lookup, you would want to check 
immediately after the lookup, both

If: not lastm
Then: 'do something but don't fall to next test where it would be 
illegal to test lastm(##)

and

If: lastm(##) ne myexactkeys     'found next less, but no longer matches 
keys that must match exactly
Then: 'do something

Similar code could be used for 8-digit dates where you would probably 
want to set the key value for the year portion of the lookup to be as 
high as possible (instead of my arbitrarily-high "2999") within the 
PFCMARK range (by getting the value for PFCMARK and I think you have to 
subtract one year from that and use that as the two-digit highest year 
value).



Bruce



On 7/25/13 10:25 AM, Fairlight wrote:
> I need to look up a record from within input processing by two criterion:
>
> 1) The absolute value of a field matching (key field lookup)
> 2) I need the most recently modified record which matches criterion #1, as
> well as has a particular value in a secondary field.
>
> I have date and time fields, and there's even an index built across both,
> in descending order for both.  Unfortunately, I don't think that does me a
> lick of good, since a key field lookup will be using the index for that
> field, not the date/time index.
>
> I thought about the possibility of a four-field index across all four
> fields, but in thinking about it, I have come to the conclusion that
> there's no way it's remotely useful, as I won't have a specific date to
> use for the second 2/4 of the key field, and I don't think the joint index
> works that way.
>
> Is there an efficient way to handle this situation?  If so, what is it?
>
> I'm envisioning lots of getnexts and tracking the last @rn that had the
> highest value, then when the lookup has exhausted all records which match
> the two specific values, flip back to the record at the most appropriate
> @rn value.  Is that the only real way to skin this cat?
>
> mark->