complimentary lookup criterion question

[Mike Schwartz]

> >>> 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->
>
       How about building your own key, something that you can easily work
with, and storing it in a real field?  For example, start with the customer
name and then add the Julian date, followed by a counter indicating the
number of seconds.  Or possibly put the number of seconds since the Epoch
into the field.

      You could add other fields to the key as necessary in order to make it
unique and to do what you need as far as the getnext's and so forth.

Mike Schwartz