• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • How do we get a datawindow column sort to treat nulls as a low value?

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved How do we get a datawindow column sort to treat nulls as a low value?

  1. How-to
0
Votes
Undo
  1. Glenn Barber
    Glenn Barber
  2. PowerBuilder
  3. Tuesday, 28 June 2022 01:30 AM UTC

We have a number of applications which present a data grid which allows the user to click on column headers to sort data (using the pfc service).

One issue is that nulls always sort to the top in datawindows - which can be really aggrevating when there is a lot of null data.

We dont want to fill all the date with dummy dates or empty strings.  

Is there a way to tell a datawindow to sort nulls to the top or bottom always?

Ideally have these many decades it would be great if Appeon put a "Sort Nulls As" feature in the column definition.  Maybe its there and I am unaware.

As I am using the pfc  and it essentially grabs the column name from the headername (extracting the -t) creating a setSort

I did some experimenting in the debugger and it looks like the Set Sort function may accept datawindow functions - so instead of

of_setSort ("mydate a")  

I can do

of_setSort ("If(IsNull(mydate, 1900-01-01, mydate) 

I never so this is an example in the documentation - is this supported?

 

  1. DataWindow
PowerBuilder 2019 with Windows Server 2019
PB2021 Multi-target workspace one target crashes I...
Responses (3)
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 28 June 2022 05:22 AM UTC
  2. PowerBuilder
  3. # 1

Hi Glen,

I don't know a DW setting for sorting NULL values.

What you can do with PFC: Create a invisible computed field with the IF function (e.g. with name c_mydate). Also change the name of the header to match the computed fields name (c_mydate_t). So the column header search will sort by the computed field.

But keep in mind that PFC uses the header text also for other functionality (e.g. display header text instead of column names in messages). So it may be that you also need an additional unvisible text for the column.

Regards,

René

Comment
There are no comments made yet.
Brad Mettee
Brad Mettee Accepted Answer Pending Moderation
  1. Tuesday, 28 June 2022 16:25 PM UTC
  2. PowerBuilder
  3. # 2

Yes, it will work.

of_setSort ("If(IsNull(mydate), date('1900-01-01'), mydate)")

Not sure if there's a way to embed a date directly in a datawindow, so the date function is necessary to get the proper datatype into the IF statement.

 

Comment
Glenn Barber
  1. Glenn Barber
  2. Tuesday, 28 June 2022 16:56 PM UTC
You will note that the code I provided of_setSort ("If(IsNull(mydate, 1900-01-01, mydate) seems to work without supplying the string to date function.

I would need a similar function for strings, numbers and fields supported by a dropdown.



  1. Helpful
René Ullrich
  1. René Ullrich
  2. Wednesday, 29 June 2022 05:45 AM UTC
Yes, you can use a date in format yyyy-mm-dd in Powerbuilder source and datawindows without any conversion.

For string values you could use: If(IsNull(mystring), '', mystring)

For numeric values you could use: If(IsNull(mynumber), 0, mynumber)

It depends on where the NULL values should be in the order. If you have negative and positive values it could be a problem to have NULLs as zero in the middle?
  1. Helpful
There are no comments made yet.
Glenn Barber
Glenn Barber Accepted Answer Pending Moderation
  1. Tuesday, 28 June 2022 16:32 PM UTC
  2. PowerBuilder
  3. # 3

I started out creating the computed field then redirecting column sort by naming the column so that it points to the hidden computed field.

As you say there are other side effects with manipulations on column naming when using the pfc.  Also it can nearly double the amount of data being retrieved (which is a problem for our app).

This is how I eventually dicovered that the setsort seems to work with dw expressions rather than just column names - but I dont know if that is a supported behavior and I'd like to find out before I spend too much time on this.

I am working on the pfc sort feature so that can identify the data type and modify the sort syntax to handle nulls.  

if anyone has already done this in some version of the pfc please let me know.

 

Comment
Load more comments
Glenn Barber
  1. Glenn Barber
  2. Thursday, 30 June 2022 16:30 PM UTC
The above doesnt work but this seems to work (must change interpretation of null depending on direction

IF Trim(is_sortorder) = 'A' THEN

ls_sortstring = "If(IsNull(" + is_sortcolumn + "), 1, 0)" + is_sortorder + ", " + is_sortcolumn + is_sortorder

ELSE

ls_sortstring = "If(IsNull(" + is_sortcolumn + "), 0, 1)" + is_sortorder + ", " + is_sortcolumn + is_sortorder

END IF

  1. Helpful 2
René Ullrich
  1. René Ullrich
  2. Friday, 1 July 2022 04:43 AM UTC
If you want to have NULLs at the end also with inverted sort direction it is simple: You don't have to change the first sort expression.

ls_sortstring = "If(IsNull(" + is_sortcolumn + "), 1, 0) A, " + is_sortcolumn + is_sortorder

  1. Helpful
Glenn Barber
  1. Glenn Barber
  2. Friday, 1 July 2022 16:38 PM UTC
Very clever - forcing the Ascending on the is null 0 or 1 value - streamlines the syntax. Either works.

Thanks for the idea on the combining columns.



Of course this only solves the header click via the PFC - not the other sorting features.



However, I would still hope that Appeon could add a functionality that would assert Sort Nulls to botton rather than to the top. People who use grids expect them to sort as exce does.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.