C#, SelfServicing, 2.6.

Hi,

I want to sort on a substring of a field. It's a customernumber which always had a 2 letter prefix indicating the type of client, but I want to sort on the numbers after that only. So a sort of SubString(2) on the column.

So: AA001 BB002 CC003 AA004 AA005

Should be sorted as shown above, and not as:

AA001 AA004 AA005 BB002 CC003

I understand it's something with Field Expressions, but since i've never used that before some pointers or links to the correct threads would be great.

Thanks.

You should change your signature to reflect v.2.6

First thing to think of is how you can do this using SQL code, please post the SQL here.

Then you may find the following links useful: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15167 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15671 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14463 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14331

The SQL code could be something like:

Select [UID], [DESC] From SomeTable Order By Substring([Desc], 5, 100);

Solved!

This is the code:

First I used 'Number' as the first parameter of substringParameters, this didn't work. Also DebtorFields.Number.Name was not correct. The only correct form is to use it a the "IEntityField" representation.

                    // Below based on http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14331
                    // Create custom field
                    object[] substringParameters = { DebtorFields.Number, 3, 20 };
                    DbFunctionCall substringCall = new DbFunctionCall("SUBSTRING", substringParameters);
                    EntityField sortField = new EntityField("NumberOnly", substringCall);

                    SortClause numberSortClause = new SortClause(sortField, null, SortOperator.Descending);
                    numberSortClause.EmitAliasForExpressionAggregateField = false;

                    SortExpression sort = new SortExpression(numberSortClause);
                    //object maxNumber = debtors.GetScalar(DebtorFieldIndex.DebtorId, null, AggregateFunction.Max, filter);                 
                    debtors.GetMulti(null, 1, sort);