A lookup with no exact match

Classic Servoy
1

Hi Servoy,

FM offers the option that if a lookup does not match exactly, it can copy next lower or higher value. How to do the same with Servoy?

thanks!

2

Yes, good question! I was bumping into that too, yesterday.
And the possibillity to do a NOT relookup if the value does not match!.
Now the lookup field becomes empty.

3

Interesting thought. However, can you write a simple SQL statement that would work here? I really can’t think of one.

Since FMP is the database as well as the UI, they can just “look into” the internal index to see what the next closest value to the key is. In SQL, I’m not sure it’s possible.

Bob Cusick

4

SQl wise you could do the following:

select field 
from table 
where field like "abdcefg%"
and rownum <2
order by field asc

The query without the “and rownum <2” clause gives all the values of the field column that start with “abcdefg”, ordered ascendingly. By adding the “and rownum <2” clause, you limit the resultset to the first value.

I think the rownum function is a standard SQL function. At least in Oracle this works.

Paul