max function

[DBasine]

I am currently using the Max function to obtain the highest value out of related records. That value is compared with the value from a certain field in my main table.

 

But what I really want, is the first value from the related records that is higher than the value in my main table.

Not the highest (max) value of the related records.

 

Any suggestions?

[-Queue-]

Have you tried creating a look up field that uses the 'next higher value' if there is no exact match?

[DBasine]

I'm afraid I can't. I use the Max function on a related number field from a relationship between text fields. If I would take the first higher value from a relationship between text fields, it would take the first higher alphabetical value, while I want it to seek for the numbers.

And I can't make the relationship between the number fields, because I want it to look between a range of numbers within that 'text' relationship.

 

A lookup would not be advisable since it would have to be updated on a regular basis.

 

Thanks anyway! If you would come up with another solution, feel free to let me know...

[comment]

I believe Queue is on the right track here, although it needs a twist. The fact that your keys are text fields complicates the things slightly, but I think it can still work:

 

Let's consider a relationship based on a concatenated field=

TextKey & Right ( "000000000" & Number , 9 )

 

Now "lookup next higher" should fetch the first higher number that has the same text keyfield. Except when there is no such higher number - then it will cross to a record with the wrong text entry. But that is easy to catch with a similar lookup field that looks up the text. So, if looked up text local text, the fetched number is invalid and needs to be replaced with "no higher number found".

 

True, you need to update the lookup if the related data changes. But refreshing the lookup is not that difficult using the Relookup script step, and may be a small price to pay.

[DBasine]

thanks! I will look into this. This seems like a good solution; I will look into it later and let you know if it is solved.