Sort expression problem QV 8.5

[#1]

Hi could someone please help?

I have a listbox which contains a list of Consultant names from a field called Consultants, e.g.

DR A RASHID

MR C TRUEMAN

DR B MEHTI

DR Z BIGELOW

what I would like to do is sort this list on the surname so it would appear as

DR Z BIGELOW

DR B MEHTI

DR A RASHID

MR C TRUEMAN

I have tried to add a sort expression like so

=Mid([Consultant],FindOneOf([Consultant], ' ', 2))

so this will find the surname after the second space in the string.  If I create a new list box with this expression in as the field it displays as I would expect and is sortable

BIGELOW

MEHTI

RASHID

TRUEMAN

However this does not work if I use this to sort expression, I have tried all the different combinations of checkboxes to no avail

I have tried to make a new field in the load script like so

Mid([Consultant],FindOneOf([Consultant], ' ', 2)) as [Cons_Surname],

The field is added and is available but I still cannot get it to sort correctly.

I need to show the full string in the list box but for it to be sorted on the surname portion of the string

Any ideas?

Thanks a lot

Stephen

[danielrozental]

Try

aggr(Mid([Consultant],FindOneOf([Consultant], ' ', 2)),[Consultant])

[#3]

Thanks for the reply

That doesnt work unfortunately, I get the same results

Any other ideas?

Thank you

Stephen

[#4 · Accepted solution]

After more headaches this morning I have come up with a workaround to this problem

The Consultant field in its raw form contains

DR A RASHIDSR0152

MR C TRUEMANSR0153

DR B MEHTISR0154

DR Z BIGELOWSR0152

I now have an expression on the Consultant field in the list box

=Mid([Consultant],FindOneOf([Consultant], ' ', 2),Len(Mid([Consultant],FindOneOf([Consultant], ' ', 2)))-Len(Right([Consultant], 6)))

& ' ' & Left([Consultant], FindOneOf([Consultant], ' ', 2))

This identifies the surname portion of the consultant name first,

strips out the SR0xxx number, then adds on the Prefix and initials.

I then sort on State/Text Ascending

This works fine

like so

BIGELOW DR Z 

MEHTI DR B

RASHID DR A

TRUEMAN MR C

It would be nice to understand why the original solution didnt work though

Thank you

Stephen

[jedgson]

Stephen,

From my own experience it seems that the sort by expression is looking to sort by some numeric value returned by expression.

You could therefore use hash128() around your original expression

Jay

[#6]

Thanks Jay I will bear that in mind for future reference

Stephen