Suneido sorting troubles…

[gmauro]

Hi, I have a little sort problem in Suneido…
Let’s suppose we have a table “atest” created in this way:

create atest(field1) key(field1)

Now let’s suppose that in the table atest there exist these records:

“ART”, “air”, “PICTURE”, “car”, “pen”, “XYLOPHONE”

A query request like “atest sort field1” would generate an output like the following:

ART
PICTURE
XYLOPHONE
air
car
pen

But this is not the natural ordering that a human being would expect! Instead I would like that ‘a’ < 'A' was true, but this is not the case, being that the standard ordering is based on values from an ASCII table, and in a ASCII table 'a' (which is 97) is greater than 'A' (which is 65). There is a way to change this behaviour so that “atest sort field1” would make the following oredered result? air
car
pen
ART
PICTURE
XYLOPHONE

Moreover, in some applications I would like to have the following ordering:

air
ART
car
pen
PICTURE
XYLOPHONE

I have managed to partially resolve this problem with a query like: “atest extend upperField1 = Upper(field1) sort upperField1”, where Upper(s) is a function that returns “s.Upper()”. Anyway, the problem is that in the lookup window of an IdControl (a control that I use very often) the sort ordering is always based on the key field.
Of course, you can create a Field definition like:

Field_string
{
Prompt: ‘Field1’
Control: (Id query: ‘atest extend u=Upper(field1)’
field: ‘field1’
nameField: ‘field1’
columns: (u, field1)
keys: (u)
)
}

But in this way it is very ugly to see the duplicated column ‘u’ (an upper-case version of the real key field1) in the lookup window, and there could be problems in selecting rows that before where not duplicated, but after the forced “upper-casing”, they could be…
I think it would be very useful to have the possibility to change the sort ordering at “query” level, so that all the problems would be resolved without tricks… Something like:

atest lower-case-first-sort field1

to have the first behaviour I have described, or:

atest natural-sort field1

to have the second behaviour I have described.

What do you think?

[j_ferguson]

I agree, those sort options could be useful.

Currently the only option is to make a rule that modifies the value so it sorts the way you want, as you’ve already discovered.

If performance is an issue, you may want to consider saving the rule (creating a field for it in the table schema) and putting an index on it so Suneido does not have to create a temporary index for the sort.

[gmauro]

Thankyou for your answer. Unfortunately, the definition of a new field with duplicated upper-case versions of the original field will waste space in the database, and I think could be problems with IdControl because this requires a key field for the searching and sorting column…

[amckinlay]

Previously I started to implement “lower” indexes that would convert values to lower case before inserting into the index, but this did not work out.

I think it would be better to allow creating an index (or key) on a un-stored rule field (capitalized when created).

One of the problems with this is if you change the definition of the rule you would need to manually rebuild the index.

I think this would do what you want. It would avoid storing the data twice (but would still use space for the index).

You can specify the columns to IdControl so the user does not see the “extra” ones.

[Author]

Posts