List_SortColumn error on calculated attribute in aggregate

List_SortColumn error on calculated attribute in aggregate


Hi there,

I have an aggregate with a calculated attribute to left pad a text column so I can do a numeric like sort. The formula is

SyntaxEditor Code Snippet

Substr("0000000000", 0, 10 - Length(Reference.Code)) + Reference.Code

The attribute is called PaddedCode. In the RichWidgets\List_SortColumn I set the value to 

"[PaddedCode]". It works fine for a couple of times in the page, the text based code column sorts nicely on numeric value. After leaving the page and coming back through the menu to the page, an error is raised: "Invalid syntax in 'Order By' parameter."

How come the order by works fine a couple of times, and later the error is raised? I cannot see the sql statement that raises the error at runtime, so I am at a loss.



Hi Wilko, 

List_sort function keeps sorting expression into session variable. Please check your application session, It should not get expired until you want to.


Hi Wilko,

Do you have by any chance in the preparation of that screen two or more aggregates? Look into those aggregates and check that you are not using the same expression in the dynamic sort (the same Id of the TableRecords).



Sachin hi,

Thanks for the reply, as far is I can see we do not use any specific session variable for the sort, only for the search bar and search drop downs. So I am not sure what to check.

Jose also thanks. We have 6 aggregates in the preparation, 2 of which use the List_SortColumn_GetOrderBy in the sorting. The others don't use sorting or have fixed sorts. The 2 aggregates using the List_SortColumn_GetOrderBy each have different widget ID's in the List_SortColumn_GetOrderBy call

  • (List_SortColumn_GetOrderBy(ReferenceTable.Id,"")
  • List_SortColumn_GetOrderBy(ExtensionTable.Id, "{Extension}.[Text]")

The error occurs on the first table (ReferenceTable)

So that should be fine, right? If so, what else could be the cause? If I change screens and come back without having sorted first all seems well, but once I sorted the ReferenceTable on the paddedcode, go away and come back, the error is raised.



Hello wilko,

The sort system (from Richwidgets) uses a session variable to store the actual sorted column.

About the error, could you do a small test? Remove the [ ] from the string of the sort column. Use only PaddedCode. Publish a d before testing, guarantee you are in a new session (logoff and login).

I use calculated fields in order all the time, but never with the [ ]. There is no need. It is just a shot in the dark, but I'm not seeing anything different from what I do in your approach.


Eduardo Jauch

Hi Wilko,

Do this to troubleshoot the error:

  1. Go through the procedure to get the error (sort, go away, come back)
  2. Add a breakpoint in the Preparation
  3. Start the Debugger
  4. Refresh the screen (this will stop execution)
  5. Go step by step in Service Studio until you see which aggregate is giving the error
  6. Try to see what is wrong with that aggregate (if it's the aggregate)

If you do not see anything wrong:

  1. Add two variables of type Text to your screen
  2. Add, at the start of your Preparation, an Assign Statement with two assignments (do copy and paste of what you have in the Dynamic Sort of each Aggregate). Something like:
    1. Var1 = List_SortColumn_GetOrderBy(ReferenceTable.Id,"")
    2. Var2 = List_SortColumn_GetOrderBy(ExtensionTable.Id, "{Extension}.[Text]")
  3. Go through the previous procedure of troubleshooting checking the values of Var1 and Var2.

Post here what were your findings.

Good luck. :)



Hi wilko,

On your table record,for every column, please check if you have given attributes names properly which matches with entity.

If you have changes attribute name after scaffolding, this error will raise.

Thanks and Regards,

Suraj Borade