In version 11.4, if you have NULL values in any field and then do an ORDER BY on that field, any records that contain NULL values will be removed from the Current Selection. This method is a work around for that behavior. It will return the records to the top of the selection. See notes in the method for places to add other features like inverting the sort or placing the nulls at the bottom of the selection.

 

  `***********************************************************************

  ` Method Name: FND_sort_w_nulls

  ` Created by: Walt Nelson (Seattle)

  ` Date created: 06/15/09, 15:30:47

  `

  ` Description:

  `  Workaround for sort bug in v11.4 that removes records from selection 

  `when sorting on a field that contains null values

  ` 

  ` Returns: 

C_LONGINT($0;$records_in_selection;$element_i)

  ` Required Parameters: 

C_POINTER($1;$sort_field_ptr;$current_table_ptr)

  ` Optional Parameters: --none--

  ` Other Variables:

ARRAY LONGINT($record_numbers_of_nulls_ai;0)

ARRAY LONGINT($record_numbers_ai;0)

C_TEXT($SQL_query_string_t)

 

  ` 

  `***********************************************************************

 

$sort_field_ptr:=$1

 

$current_table_ptr:=Table(Table($sort_field_ptr))

 

CREATE SET($current_table_ptr->;"$current_set")

  ` find the records with nulls in order by field

  `Is field value Null (aField) -> Boolean

  `Generally, if you want to be able to use NULL values in your 4D database, it is recommended 

  `to exclusively use the SQL language of 4D.

  `SELECT Name, Weight, Color 

  `FROM PRODUCTS

  `WHERE Weight<15.00 OR Color IS NULL

 

 

$SQL_query_string_t:=Field name($sort_field_ptr)+" IS NULL"

 

QUERY BY SQL($current_table_ptr->;$SQL_query_string_t)

 

 

If (Records in selection($current_table_ptr->)>0)

SELECTION TO ARRAY($current_table_ptr->;$record_numbers_of_nulls_ai)  ` get the record numbers

USE SET("$current_set")

ORDER BY($sort_field_ptr->)  ` sort ascending - could add optional parameter for sort order

SELECTION TO ARRAY($current_table_ptr->;$record_numbers_ai)  ` get the record numbers

 ` add back the null records to the bottom of the selection?

If (False)

For ($element_i;1;Size of array($record_numbers_of_nulls_ai))

APPEND TO ARRAY($record_numbers_ai;$record_numbers_of_nulls_ai{$element_i})

End for 

End if   ` false

 ` add back the null records to the top of the selection?

For ($element_i;1;Size of array($record_numbers_ai))

APPEND TO ARRAY($record_numbers_of_nulls_ai;$record_numbers_ai{$element_i})

End for 

COPY ARRAY($record_numbers_of_nulls_ai;$record_numbers_ai)

CREATE SELECTION FROM ARRAY($current_table_ptr->;$record_numbers_ai;"sorted_selection")

USE NAMED SELECTION("sorted_selection")

 ` for testing

SELECTION TO ARRAY($sort_field_ptr->;$prices_r)

Else   ` we have no nulls

ORDER BY($sort_field_ptr->)  ` sort ascending - could add optional parameter for sort order

 ` nothing else to do

End if 

 

 

$records_in_selection:=Records in selection($current_table_ptr->)

 

$0:=$records_in_selection

 

` *** End of Method ****