text Find ">" gives wrong result

[tomp]

I'm trying to find all records with a text field value greater than ('>') some other value.

With the Default Language for sorting and indexing set to 'English', the result is returning records '>='.

The specific script steps that return the wrong set of values are

enter find mode []

set field("resources::name";">"" & get(scriptparameter) & """

perform find []

where scriptparameter=Boat/Motor 15HP

The result includes records where name=Boat/Motor 15HP, not just those that are '>'.

When I change the Default Language for sorting and indexing to 'Unicode', it's still wrong. with language 'unicode', it excludes the value 'Boat/Motor 15HP' but does NOT include 'Boat/Motor 20HP' which sorts '>' in either language.

What's wrong?

TIA for any help.

[Vaughan]

I'd say you're better off having another field somewhere that provides the sort order you're after.

It sounds to me like you really want to sort by the motor power.

[Søren Dyhr]

1NF says "One fact per field" not that we here have raised normalization issues here though, but you should split the facts into individual fields in proper types numberfields for numerical values etc.

In order to split it into a field that behaves as expected, make yourself a calcfield ...

Filter ( aField ; "1234567890" )

...with resulting type as number and let the scriptparamter place >15 in it when making the request.

--sd

[tomp]

That would work if all values of the field had the two pieces. A sampling of other values in the 'name' field are:

Dock

Pontoon

TV/VCR

Are you telling me there is no way to do a '>' search/find and get the correct result when a text field has an embedded blank??

Thanks

[comment]

IMHO there is no 'correct result' in this type of search. Strictly speaking, the comparative operators are not text operators. The fact that two text strings can be compared using > in a calculation seems to me more like a lucky bug than an intended result. AFAIK this is not a documented feature. As it turns out, it does not extend to finds.

[Søren Dyhr]

That would work if all values of the field had the two pieces.

You are abel to stack the seaches in an AND'ed way

Enter Find Mode [ ]

Set Field [ Untitled::aField; LeftValues ( Get ( ScriptParameter ) ; 1 ) ]

Perform Find [ ]

Enter Find Mode [ ]

Set Field [ Untitled::measure; RightValues ( Get ( ScriptParameter ) ; 1 ) ]

Constrain Found Set [ ]

...which require that the two parts of the parameter is pilcrow delimited!

--sd

[tomp]

Since in general, my users could have 2, 3, or more 'pieces' in the field (it depends on what they offer to their customers in their business), it looks like I'll have to find another approach to the problem. What I'm really looking for is the next value for the field. What I tried to do was find > current value, sort by name, and take the new value from the 1st record. I see my choices now as:

1) I could require no embedded blanks (separate pieces with '-' or other delimiter. Validate new entries into the field and return an error if I find an embedded blank. Using '>" value "' seems to work if the 'value' is a single word, or as I used to do it:

2) find all records, sort by name, 'go to next record' until I get to the first name > current value (sorts do seem to work) but this isn't very efficient when there are a large number of records, or......

3) : is there any way to get the 'next value' from a Value List. If I had a value list for the name field (which I do), is there any way to 'go to' a value and then grab the next one :?

Thanks again.

[Lee Smith]

Hi Tom,

enter find mode []

set field("resources::name";">"" & get(scriptparameter) & """

perform find []

where scriptparameter=Boat/Motor 15HP

I'm not sure if I'm reading this right, but there appears to be a typo between what you have in the field and your set field.

Your field contains Boat"[color:"red"]/"Motor and yet your [color:"blue"]set field is looking for ">[color:"red"]"", or am I not understanding something here, which is quite possible.

Lee

[comment]

The third option seems most sensible. Try the following calculation:

Let ( [

list = ValueListItems ( Get(FileName) ; "--YourValueListNameHere--") ;

this = --thePreviousValueHere-- ;

pos = Position (

[tomp]

This is fantastic! Slick as a whistle. Exactly what I need. This is a corner of FM I had not explored.

Thank you 'Old Hand'!

tomp

[Søren Dyhr]

But wasn't it the task LookupNext( ...higher are supposed to handle??

--sd

[comment]

You need straight lookup to fail, before it will lookup next. Maybe you could use something like value & "!", to tip it over slightly. But then it would depend on the field's language. And the chosen character would have to be reserved. Too unpredictable, IMHO.

[Søren Dyhr]

What about the operators < and > in the relations def, behind the lookup???

--sd

[comment]

Why don't you try this, and if you can find any logic in the received result...

Even before lookup: a relationship based on text > text seems to give unpredictable results even in the portal.

Anyway - even if it worked, in a field > field relationship, ALL the lesser values are related. What then is "next" value?

BTW: what is the "next" value in a multi-criteria relationship?

[Søren Dyhr]

Why don't you try this, and if you can find any logic in the received result...

I could, examine the template - my thought was; nothing goes on without the index!!! But LookUpNext is indeed tough to comprehend.

--sd

RelIDX.zip

[comment]

Yes, that works and it is simpler than mine. Just to clarify: this has nothing to do with lookup. The relationship matches all records that are higher. Sorting the realtionship makes the lowest record the first one in the relationship. The lowest record among the higher is the next record. Well done!