SortList ( )

Function stats

Average user rating
46
260
9999
Support
FileMaker 8.0 +
Date posted
10 July 2010
Last updated
28 February 2014
Version
Recursive function
Yes

Author Info
 Agnès

17 functions

Average Rating 4.6

author_avatar



 

Function overview

Prototype

SortList  ( ValueList;   Sort;   Type )


Parameters

ValueList  ValueList List for sort - ValueCount ( ValueList ) ≤ 100000


Sort  Empty or


Type  Empty or


Description

Tags:  value list sort  

// SortList ( ValueList ; Sort ; Type ) v2.0
// Carrefull : Parameter is : ValueList ; Sort ; Type and not ValueList ; Type ; Sort
// If you change the name "SortList", also change in the variable $sl_Exe
/*
No dependance. Recursive CustomFunction .....

To sort a list
-> ValueList List for sort - ValueCount ( ValueList ) ≤ 100000
-> Sort : Empty or "Asc" or "Des" [Empty = "Asc"]
-> Type : Empty or "Text" or "Number" or "Date" or "Time"or "TimeStamp" [Empty = "Text"]

* Note :
Keep empty value and double value
When you can use ValueListItem(), it's better for timing
< 2000 : 0 to 2 sec but 5000 number : between 12 and 15 secondes

Examples

Sample input

SortList ( "B¶D¶C¶¶A¶C" ; "Asc" ; "Text" )


Sample output

"¶¶A¶B¶C¶C¶D"

 

Function code

// SortList ( ValueList ; Sort ; Type ) v2.5
// Carrefull : Parameter is : ValueList ; Sort ; Type and not ValueList ; Type ; Sort
// If you change the name "SortList", also change in the variable $sl_Exe
/*
No dependance. Recursive CustomFunction .....

To sort a list
-> ValueList List for sort - ValueCount ( ValueList ) ≤ 100000
-> Sort : Empty or "Asc" or "Des" [Empty = "Asc"]
-> Type : Empty or "Text" or "Number" or "Date" or "Time"or "TimeStamp" [Empty = "Text"]

* Note :
Keep empty value and double value
When you can use ValueListItem(), it's better for timing
< 2000 : 0 to 2 sec but 5000 number : between 12 and 15 secondes
*/
//---------------------------------- Example
// SortList ( "B¶D¶C¶¶A¶C" ; "Asc" ; "Text" ) --> Result = "¶¶A¶B¶C¶C¶D"
//---------------------------------------------------------*/
// Agnès Barouh - August 2009 - Substitute( filemaker§tictac.fr; §; @ )
// WebSite : http://www.tictac.fr/CoinFileMaker/Page.html
//---------------------------------------------------------*/
/* UPDATE */ // Updated code February 28 2014 - for adapte to FileMakerGo, FileMakerServer and FileMaker IWP

Case (
not IsEmpty ( $$sl_V ) ;

Let ([
Size = ValueCount ( ValueList )
];
Case (
Size ≤ 2 ; Replace ( $$sl_Res ; Position ( $$sl_Res ; ¶ ; 1 ; $$sl_n ) + 1 ; 0 ; $$sl_V & ¶ ) & Let ( $$sl_V = "" ; "" ) ;
Let( [
A = Ceiling ( Size / 2 ) ;
V = GetValue ( ValueList ; A ) ;
Compare = Choose ( Type ; V ; GetAsNumber ( V ) ; GetAsDate ( V ) ; GetAsTime ( V ) ; GetAsTimestamp ( V ) ) ;
Test = Case ( Sort = "<=" ; $$sl_G <= Compare ; $$sl_G >= Compare ) ;
NewList = Choose ( Test ; Let ( $$sl_n = $$sl_n + A - 1 ; MiddleValues ( ValueList ; A ; Size )) ; LeftValues ( ValueList ; A ))
];
SortList ( NewList ; Sort ; Type )
)
));

Let ([

$sl_List = ValueList ;
$$sl_Res = GetValue ( $sl_List ; 1 ) ;
$sl_Get = "GetAs" & Case ( IsEmpty ( Type ) ; "Text" ; Type ) ;
$sl_T = Case ( Type = "Text" or IsEmpty ( Type ) ; 0 ; Type = "Number" ; 1 ; Type = "Date" ; 2 ; Type = "Time" ; 3 ; Type = "TimeStamp" ; 4 ) ;
$sl_A = Case ( Sort = "Des" ; ">=" ; Sort = "Asc" or IsEmpty ( Sort ) ; "<=" ) ;
$sl_D = Case ( Sort = "Des" ; "<=" ; ">=" ) ;

Start = 2 ; End = ValueCount ( $sl_List ) ; Diff = End - Start + 1;
End = Case ( Diff > 100000 or IsEmpty ( End ); "Error"; End );
$sl_C = Start - 1; $null = "\"\"";

V = Case ( Left ( Get ( ApplicationVersion ) ; 2 ) = "Go" ; 1 ; Left ( Get ( ApplicationVersion ) ; 2 ) = "Se" ; 2 ; Left ( Get ( ApplicationVersion ) ; 2 ) = "We" ; 3 ; 0 ) ;
PL = Choose ( V ; 1700 ; 400 ; 300 ; 900 ) ;
Calc = Case ( Diff ≥ ( PL - 10 ) ; ( PL / 10 ) ; Floor ( Diff / 10 ) + 1 );
First = Substitute ( ( 10 ^ Calc ) - 1; 9; "__________" ) ;
X = Floor ( Diff / PL );

$sl_Exe = Substitute ( First ; "_" ;
"Let ([
$sl_C = $sl_C + 1 ; $$sl_n = 1 ;
$$sl_V = GetValue ( $sl_List ; $sl_C );
$$sl_G = " & $sl_Get & " ( $$sl_V );
$$sl_Res = Case( $$sl_G " & $sl_A & $sl_Get & " ( GetValue ( $$sl_Res ; 1 ) ) ; $$sl_V &\¶& $$sl_Res ;
$$sl_G " & $sl_D & $sl_Get & " ( GetValue ( $$sl_Res ;$sl_C-1)) ; $$sl_Res &\¶& $$sl_V ;
SORTLIST ($$sl_Res ; $sl_A ; $sl_T))];\"\")&\¶&¶" ); // NOTA : If you change the name of the CF, "SortList", Change here too.

Final = Case ( X > 0; Substitute ( ( 10 ^ X ) - 1; 9; "Evaluate ( $sl_Exe & $null ) & " ) ) &
"Evaluate( LeftValues ( $sl_Exe ; " & Diff - ( X * PL ) & " ) & $null ) & " & $null
];
Case (
IsEmpty ( ValueList ) ; "" ;
End = "Error" ; "Error : Check ValueList or Start and End" ;
IsEmpty ( $sl_T ) or IsEmpty ( $sl_A ) ; "Error : Check Sort and Type" ;
Substitute ( "#^#" & Evaluate ( Final ) & "#^#"; [ "¶"; "¶#^#" ];[ "#^#¶"; "" ];[ "¶#^#"; "¶" ];[ "¶#^#"; "" ];[ "#^#"; "" ]) & $$sl_Res
)
)
& Let ([ $sl_Exe = "" ; $sl_C = "" ; $sl_List = "" ; $$sl_Res = "" ; $$sl_G = "" ; $$sl_n = "" ; $$sl_V = "" ]; "" )
)

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/260

    Prototype: SortList( ValueList; Sort; Type )
    Function Author: Agnès (http://www.fmfunctions.com/mid/46)
    Last updated: 28 February 2014
    Version: 1.5

*/
// ===================================

 

Comments

dnhdennis
05 February 2013



I am getting a strange result from this function for one particular list of values.
Being sorted by number:
SortList ( $PageLIst ; "Asc" ; "Number" )

The original list in this case is already in numerical order:
1 - C3 PEST_GRP3
2 - C3 PEST_GRP3
3 - M2+Plate Count, Micro_FC
4 - M2+Plate Count, Micro_FC
5 - C3 PEST_GRP3, M9, HM2
6 - M2+Plate Count, Micro_FC
7 - M2+Plate Count, Micro_FC
8 - M2+Plate Count, Micro_FC
9 - M2+Plate Count, Micro_FC

But result of the function is coming out as:
3 - M2+Plate Count, Micro_FC
4 - M2+Plate Count, Micro_FC
6 - M2+Plate Count, Micro_FC
7 - M2+Plate Count, Micro_FC
8 - M2+Plate Count, Micro_FC
9 - M2+Plate Count, Micro_FC
1 - C3 PEST_GRP3
2 - C3 PEST_GRP3
5 - C3 PEST_GRP3, M9, HM2

Any ideas why it is doing some sort of weird alphabetical order instead of numerical?
 Bug report
Agnès
05 February 2013



Hello,

I do not think we can speak of bug.
Or ValuesListItems() was the same. ( if you put your list in field number, ValuesListItems() is the same result )

For SortList(), your values ​​are numbers. ( for example, 1 - C3 PEST_GRP3 = 133 )
You need a function that sorts only the first number found.

I do not know if I'll have time to watch another calculation. I'll try !
     Feature request
Agnès
06 February 2013



Hello

in fact, I was already makes this calc, but I do not take time to verify a lot.
Please, said to me if they are "bug".

In your case, the calc is :
SortListTag ( $PageLIst ; "Asc" ; "Number" ; "-" ) ( - or space )

//--------------- Function SortListPart
// SortListPart ( ValueList ; Sort ; Type ; TagSort ) v1.0
// Carrefull : Parameter is : ValueList ; Sort ; Type ; TagSort and not ValueList ; Type ; Sort ; TagSort
// If you change the name "SortListPart", also change in the variable $sl_Exe

/*
No dependance. Recursive CustomFunction .....

To sort a list
-> ValueList List for sort - ValueCount ( ValueList ) ≤ 100000
-> Sort : Empty or "Asc" or "Des" [Empty = "Asc"]
-> Type : Empty or "Text" or "Number" or "Date" or "Time"or "TimeStamp" [Empty = "Text"]
-> TagSort : "|" for example if your data is "125|lalalala12" and you want to sort only on "125" but If "|" is not found in one of the value,
result is an error message : "Error tag TagSort"

* Note :
Keep empty value and double value
When you can use ValueListItem(), it's better for timing
< 2000 : 0 to 2 sec but 5000 number : between 12 and 15 secondes
*/
//---------------------------------- Example
// SortList ( "B¶D¶C¶¶A¶C" ; "Asc" ; "Text" ) --> Result = "¶¶A¶B¶C¶C¶D"
//---------------------------------------------------------*/
// Agnès Barouh - August 2009 - Substitute( filemaker§tictac.fr; §; @ )
// WebSite : http://www.tictac.fr/CoinFileMaker/Page.html
//---------------------------------------------------------*/

Case (
not IsEmpty ( $$sl_V ) ;

Let ([
Size = ValueCount ( ValueList )
];
Case (
Size ≤ 2 ; Replace ( $$sl_Res ; Position ( $$sl_Res ; ¶ ; 1 ; $$sl_n ) + 1 ; 0 ; $$sl_V & ¶ ) & Let ( $$sl_V = "" ; "" ) ;
Let( [
A = Ceiling ( Size / 2 ) ;
V = Left ( GetValue ( ValueList ; A ) ; Case ( $$sl_LSn = "pos" ; "m" & Position ( GetValue ( ValueList ; A ) ; $$sl_Pos ; 1 ; 1 ) - 1 ; $$sl_LSn ) ) ;
Compare = Choose ( Type ; V ; GetAsNumber ( V ) ; GetAsDate ( V ) ; GetAsTime ( V ) ; GetAsTimestamp ( V ) ) ;
Test = Case ( Sort = "<=" ; $$sl_G <= Compare ; $$sl_G >= Compare ) ;
NewList = Choose ( Test ; Let ( $$sl_n = $$sl_n + A - 1 ; MiddleValues ( ValueList ; A ; Size )) ; LeftValues ( ValueList ; A ))
];
SortListPart ( NewList ; Sort ; Type ; TagSort )
)
));

Let ([

$$sl_Pos = TagSort ;
$$sl_LSn = Case ( IsEmpty ( TagSort ) ; 10000000 ; GetAsNumber ( TagSort ) > 0 ; TagSort ; "Pos" ) ;

$sl_List = ValueList ;
Count = Case ( $$sl_LSn = "Pos" ; ValueCount ( ValueList ) = PatternCount ( ( Filter ( ValueList ; TagSort & "¶" ) & ¶ ) ; TagSort & "¶" ) ; 1 ) ;

$$sl_Res = GetValue ( $sl_List ; 1 ) ;
$sl_Get = "GetAs" & Case ( IsEmpty ( Type ) ; "Text" ; Type ) ;
$sl_T = Case ( Type = "Text" or IsEmpty ( Type ) ; 0 ; Type = "Number" ; 1 ; Type = "Date" ; 2 ; Type = "Time" ; 3 ; Type = "TimeStamp" ; 4 ) ;
$sl_A = Case ( Sort = "Des" ; ">=" ; Sort = "Asc" or IsEmpty ( Sort ) ; "<=" ) ;
$sl_D = Case ( Sort = "Des" ; "<=" ; ">=" ) ;

Start = 2 ; End = ValueCount ( $sl_List ) ; Diff = End - Start + 1;
End = Case ( Diff > 100000 or IsEmpty ( End ); "Error"; End );
$sl_C = Start - 1; $null = "\"\"";
Calc = Case ( Diff >= 1600; 170 ; Floor ( Diff / 10 ) + 1 ) ;
First = Substitute ( ( 10 ^ Calc ) - 1; 9; "__________" ) ;
X = Floor ( Diff / 1700 );

$sl_Exe = Substitute ( First ; "_" ;
"Let ([
$sl_C = $sl_C + 1 ; $$sl_n = 1 ;
$$sl_V = GetValue ( $sl_List ; $sl_C );
$sl_LS = Case ( $$sl_LSn = \"pos\" ; Position ( $$sl_V ; $$sl_Pos ; 1 ; 1 ) - 1 ; $$sl_LSn ) ;
$$sl_G = " & $sl_Get & " ( Left ( $$sl_V ; $sl_LS ) ) ;
$$sl_Res = Case( $$sl_G " & $sl_A & $sl_Get & " ( left ( GetValue ( $$sl_Res ; 1 ) ;
Case ( $$sl_LSn = \"pos\" ; Position ( GetValue ( $$sl_Res ; 1 ) ; $$sl_Pos ; 1 ; 1 ) - 1 ; $$sl_LSn ) ) ) ; $$sl_V & \¶& $$sl_Res ;
$$sl_G " & $sl_D & $sl_Get & " ( left ( GetValue ( $$sl_Res ;$sl_C-1) ;
Case ( $$sl_LSn = \"pos\" ; Position ( GetValue ( $$sl_Res ;$sl_C-1) ; $$sl_Pos ; 1 ; 1 ) - 1 ; $$sl_LSn ) )) ; $$sl_Res &\¶ & $$sl_V ;
SORTLISTPART ( $$sl_Res ; $sl_A ; $sl_T ; $$sl_Pos ))]; \"\" )&\¶&¶" ); // NOTA : If you change the name of the CF, "SortListPart", Change here too.

Final = Case ( X > 0; Substitute ( ( 10 ^ X ) - 1; 9; "Evaluate ( $sl_Exe & $null ) & " ) ) &
"Evaluate( LeftValues ( $sl_Exe ; " & Diff - ( X * 1700 ) & " ) & $null ) & " & $null
];
Case (
IsEmpty ( ValueList ) ; "" ; Count = 0 ; "Error tag TagSort" ;
End = "Error" ; "Error : Check ValueList or Start and End" ;
IsEmpty ( $sl_T ) or IsEmpty ( $sl_A ) ; "Error : Check Sort and Type" ;
Substitute ( "#^#" & Evaluate ( Final ) & "#^#"; [ "¶"; "¶#^#" ];[ "#^#¶"; "" ];[ "¶#^#"; "¶" ];[ "¶#^#"; "" ];[ "#^#"; "" ]) & $$sl_Res
)
)
& Let ([ $sl_Exe = "" ; $sl_C = "" ; $sl_List = "" ; $$sl_Res = "" ; $$sl_G = "" ; $$sl_Pos = "" ; $$sl_LSn = ""; $$sl_n = "" ; $$sl_V = "" ]; "" )
)

//--------------- End Function SortListPart
(Edited by Agnès on 06/02/13 )
  General comment
chris
28 February 2014



used this cf to sort after loading ... 1000 records values with
customListe(start;end; "T1::f1 & Char(9) & T2::f2") values works fine on fmp 12 :)
transfer to iPad, doing the same in fmgo12 returns "? " value -> only one element :(
seem there is a bug for that use (starting around 300 records after testing)
replacement.. SortArray ( valueList ; sortDirection ; dataType ) on briandunning page works fine!
 Bug report
Agnès
28 February 2014



Hello,
Did you have the last version CustomList() v4_8 ?
Is sortList or CustomList who do not give the good result ?

Agnès
  General comment
Agnès
28 February 2014



in case, I have updated the code of SortList().
Evaluate() has not the same behavior on Go, Server & IWP
If you could retest
thank you

Agnès
  General comment
chris
01 March 2014



Hi, Custumn List works fine, bug is SortList() with fmGo 12
I have an example prepared if you lke 706 elements, result is cut off to 306
after sorting it on FMGo, all strings are like ... str1 & Char(9) & str2
can send you an excel sheet for testing!
regards, chris
 Bug report
Agnès
13 March 2014



Hi,

sorry, I did not notice your message !
it interests me of course.
Have you also tested with the updated code?

you can try changing the variable PL with this?
Choose PL = (V, 1700, 300, 300, 900);
Yes I don't have any problems, you can send me an eMail filemaker[at]tictac[point]fr
thank you

Agnès
(Edited by Agnès on 14/03/14 )
  General comment

 

 

 

 

 

Top Tags

Text Parsing  (33)
List  (31)
Date  (28)
XML  (26)
Format  (23)
Sql  (22)
Dev  (20)
Debug  (17)
Interface  (15)
Layout  (15)
Text  (14)
Variables  (12)
Layout Objects  (11)
Filter  (11)
Design  (10)
Array  (7)