List display not matching order added

[jyoung]

I am having a really bizarre problem that I cannot seem to track down where the data is sorted correctly (as it comes out of the server module), the data gets added into a list in the correct order, yet the list displays data that is out of order.

This is related to this question (http://vlforum.lansa.com.au/viewtopic.php?f=3&t=1371) where my "main instance list" does not contain the list itself but coordinates smaller more specific lists based on a different report selection. Everything works great, except this one thing.

Here is a pic of the list. The data is actually sorted by "Date Last Billed DESC". But you can see the list is not in order.

trace.PNG (58.05 KiB) Viewed 39598 times

The query that generates the data (in SQL Server shows the data is sorted properly and 20160807 should be the at the top of the list, XCOFID and XCOCN make up part of the "Number" and id in the avListManager)

query.PNG (13.08 KiB) Viewed 39598 times

Even the trace output shows that it is the first item getting added to the list

report list.PNG (24.32 KiB) Viewed 39598 times

Yet the list is out of order.

The sorting is handled on the server side via the query, all I am doing is picking up the ColumnClicked event on the list, setting the sort directory and field and passing that back up to the server module. All other sorts are displaying correctly.

Code: Select all

evtroutine handling(#LastBilledColumn.ColumnClick) handled(#handled) origin(#origin)
#handled := True

#wk_SortDirection := #LastBilledColumn.SortDirection
#wk_SortBy := 'LAST_BILLED'

#ReportManager.SignalSort( #wk_SortBy #wk_SortDirection )
endroutine

Then in the filter

Code: Select all

evtroutine handling(#ReportManager.Sort) sortby(#sortBy) sortdirection(#sortDirection)
#CMGApplicationManager.avFrameworkManager.avRecordTraceAValue component(#COM_OWNER) event("Handling Sort") avalue(#sortBy) avalue2(#sortDirection)

#wk_PageNumber := 1
#wk_SortBy := #sortBy
#wk_SortDirection := #sortDirection

#COM_OWNER.Search
endroutine

Code: Select all

mthroutine name(Search)
#CMGApplicationManager.avFrameworkManager.avClearMessages requester(#COM_OWNER) inmainform(False)

if (#COM_OWNER.IsValid = False)
#CMGApplicationManager.avFrameworkManager.avShowMessages requester(#COM_OWNER) formainform(False)
return
endif

#ReportManager.SignalReportLoading

* save our search fields
inz_list named(#SearchFields)

#CMGApplicationManager.avFrameworkManager.avRecordTrace component(#COM_OWNER) event("Executing Request")

#ListItems.RemoveAll

#Get.ExecuteAsync officenumber(#CLOFID) formalname(#CLOFN) countrycode(#wk_CountryCode) pagenumber(#wk_PageNumber) pagesize(#wk_PageSize) sortby(#wk_SortBy) sortdirection(#wk_SortDirection) resultlist(#ListItems) recordcount(#wk_Count) status(#wk_Status)
endroutine

Code: Select all

evtroutine handling(#Get.Completed)

#CMGApplicationManager.avFrameworkManager.avRecordTraceAValue component(#COM_OWNER) event("Get Request Completed") avalue("Record Count: " + #ListItems.ItemCount.AsString)

#CMGApplicationManager.avFrameworkManager.avReceiveSystemMessageQueue requester(#COM_OWNER) intomainform(False)

#avListManager.ClearList

for each(#item) in(#ListItems)
#ListManager.AddToList( #avListManager #item )
endfor

#wk_PageCount := (#wk_Count + #wk_PageSize - 1) / #wk_PageSize

#CMGApplicationManager.avFrameworkManager.avRecordTraceNValue component(#COM_OWNER) event("Page Count") nvalue(#wk_PageCount)

* restore search fields
get_entry number(1) from_list(#SearchFields)

* Go back to display free state
#ReportManager.SignalReportLoaded( #wk_PageCount #wk_PageNumber #wk_Count )

endroutine

When debugging the list, it has zero items then has the one item after it gets added to the list, but I cannot see what that item is once its added.

Is there a way to debug and/or view the items in a list?

At this point I am just grasping in the air trying to find something out of place.

Since the data is correct, the trace output is correct I have no idea why the list display is not.
All other sorts are working correctly and they all use the same search method and completed handler.

[jyoung]

So I had a random thought while trying to figure this out and thought "No Way!", but it turned out to work.

I don't know if this is a bug or by design, would love some feedback.

Added another hidden column that represented the date as a number. Then set the the "SortAsColumn" on the LastBilledColumn to the hidden date (number) column.

Now the list is defined as

Code: Select all

define_com class(#PRIM_LIST) name(#ReportList) displayposition(1) left(0) parent(#COM_OWNER) tabposition(1) top(0) height(270) width(732) columnlines(False) rowlines(False)
define_com class(#PRIM_LIST.String) name(#NumberColumn) columnwidth(207) displayposition(1) parent(#ReportList) source(#STD_TEXTS) columncaption('Number') columnunits(Proportion) columncaptiontype(Caption) sortonclick(True)
define_com class(#PRIM_LIST.String) name(#ClientColumn) columnwidth(598) displayposition(2) parent(#ReportList) source(#CLOFN) columncaption('Client') columnunits(Proportion) columncaptiontype(Caption) sortonclick(True)
define_com class(#PRIM_LIST.String) name(#OfficeColumn) columnwidth(598) displayposition(3) parent(#ReportList) source(#OFOFN) columncaption('Office') columncaptiontype(Caption) columnunits(Proportion) sortonclick(True)
define_com class(#PRIM_LIST.String) name(#StateColumn) columnwidth(119) displayposition(4) parent(#ReportList) source(#OFSC) columncaption('Office State') columncaptiontype(Caption) columnunits(Proportion) sortonclick(True)
define_com class(#PRIM_LIST.String) name(#LastBilledColumn) columnwidth(136) displayposition(5) parent(#ReportList) source(#wk_DateString) columncaption('Date Last Billed') columncaptiontype(Caption) columnunits(Proportion) sortonclick(True) sortascolumn(#ColumnWK_DATE1)
define_com class(#PRIM_LIST.String) name(#ColumnAverageDTP) columnwidth(203) displayposition(6) parent(#ReportList) source(#STD_TEXT) columncaption('Average Days To Pay') columncaptiontype(Caption) columnunits(Proportion)
define_com class(#PRIM_LIST.String) name(#ColumnSTD_BOOL1) columnwidth(114) displayposition(7) parent(#ReportList) source(#STD_BOOL) columncaption('Has Notes') columncaptiontype(Caption) columnunits(Proportion)
define_com class(#PRIM_LIST.Number) name(#ColumnWK_DATE1) columnwidth(0) displayposition(8) increment(1) parent(#ReportList) source(#wk_Date) wrap(False) columnvisible(False) visible(False)

Where "LastBilledColumn" is sorted on "Column_wkDate1"

Then I just set the wk_Date

Code: Select all

#wk_Date := #item.DateLastBilled

if ((#item.DateLastBilled = 19000101) *Or (#item.DateLastBilled = 0))
#wk_DateString := "-"
else
#wk_DateString := #item.DateLastBilled.AsDate( CCYYMMDD ).AsDisplayString( MMsDDsCCYY )
endif

And all of a sudden the list displays correctly!

report list correct.PNG (28.4 KiB) Viewed 39596 times

Is this indented behavior? If so can someone explain what is going on?

Edit

Come to find out the same thing can happen if the hidden field has a zero or null in it.
The is displays a different order then what the data is in. I can only assume that the list is sorting itself by some mechanic.

This becomes really a problem when dealing with NULLs. It seems like some RDBMS make nulls come first and others make them come last when they are involved in an order by situation. So we you have to resort to something like this on the server side. https://stackoverflow.com/questions/149 ... -ascending

However, the LIST is still sorting itself. Is there a way to stop it?

The list should display the data I tell it to, in the ORDER I tell it to.

[Stewart Marshall]

Hi Joe

The order of the entries in the underlying data list will always be as the entries were added; in your case, the order they are returned from the server. The order of items in the visual list depends entirely on the SortPosition of the columns. If you look at your first picture it shows the data column in descending order, and sure enough the numbers go 08....05.....02.

This is entirely correct because the columns are numbers and strings and 8312014 is definitely bigger than 8242014. Had the column been a date, it would have sorted as a date.

To get round such problems, we created the SortAs feature which you've used in precisely the way it was intended. When data doesn't sort the way we want it to, we can add a hidden column to get the visual order we want.

Regards

[jyoung]

Hi Stewart,

Thanks for the feedback and after some struggling I think I am understanding what is happening. I was thinking about this over the weekend and recalled the "SortOnClick" property.

I have the SortOnClick property checked as I discovered that SortDirection property only changes (when the column header is clicked) when the "SortOnClick" property is checked, i.e. from Ascending -> Descending and vice versa with the nice little arrow indicator graphic.

Your comment on the SortPostion gave me the missing clue. What I failed to understand was that it was also toggling the SortPosition. Unchecking the SortOnClick would prevent the list from sorting, but would also mean that I would have to manage the direction property myself and possible the arrow image (although I don't know if the image can be changed).

As always thanks for being the voice of reason and helping.

Joe

[atostaine]

We've never had to handle the sort direction arrow. Pretty sure that's automatic.

We use sort position so we can display the list how we want it, but we almost always allow sort on click for all columns. AFAIK, once the user clicks on a column to sort, all of the sort positions are set to 0.

Art