Newest functions Upload a function Leave a tip My account

There are 2,522 functions in this library.

SortList ( ValueList ; Sort ; Type )

To sort a list,

  Average rating: 4.3 (75 votes) Log in to vote

Agnes Barouh - Show more from this author Tic Tac http://www.tictac.fr/CoinFileMaker/Page.html

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

// 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 = "" ]; "" ) ) Click to copy

-----------------------------------------***
Edit February 28 2014 :
/* UPDATE */ // Updated code February 28 2014 - for adapte to FileMakerGo, FileMakerServer and FileMaker IWP
//Evaluate() has not the same behavior on Go, Server & IWP

// 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

-----------------------------------------

 

Comments

		Jerry Salem, Comcast May 13, 2011
This function works for me and got me out of an interesting hole. Thanks! Jerry

		Jonathan A, Westfield, NJ Aug 16, 2012
Excellent function. As with Jerry, got me out of a hole! Thanks for sharing.

		Scott Zeigen MD, Richboro PA Apr 20, 2014
Fantastic!

		Ibrahim Bittar, León, México Jul 20, 2014
THANK YOU VERY MUCH!!!. I'm using this function to perform the O(n log n) algorithm so I can detect if there are scheduling conflicts and works like a charm.

		Denis, dsomar@gmail.com Aug 28, 2014
Great function - works without issue and no other functions needed. Can't ask for more than that!

		Jim Hamilton, Alabama Feb 20, 2015
I cannot get the function to accept my value_list. I have tried to enter the values like this SortList ("time1_second¶time4_second¶time7_second¶time9_second¶time12_second¶time14_second¶time17_second¶time21_second¶time23_second¶time25_second¶time2_second¶time3_second¶time5_second¶time6_second¶time8_second¶time10_second¶time11_second¶time13_second¶time15_second¶time16_second¶time18_second¶time19_second¶time20_second¶time22_second¶time24_second¶time26_second¶time27_second¶time28_second¶time29_second¶time30_second¶time31_second¶time32_second¶time33_second¶time34_second¶time35_second¶time36_second¶time37_second¶time38_second¶time39_second¶time40_second"; "Asc" ; "Number" ) But this treats the time variables as literal "time1_seconds" in stead of reading in as the time "1234543". When I get rid of the quotation marks, it tells me the syntax is incorrect or that I have too many parameters. Any ideas?

		Agnès, Paris Feb 20, 2015
Hi, I think "40_second&quo t;" to your end calc is an error Copy Paste Your Calc return a list, sorted : "time1_second time2_second time3_second (etc.)" it's normal, SortList do not change the list, type Number is only a precision for result sort you want If you want only number, 123456etc. you add GetAsNumber ( ResultSortList ) If you want 1¶2¶3¶4¶5¶6¶etc you add Filter ( ResultSortList ; "1234567890¶" ) I do not know if I really understood.... what is time1_second, a field or only text ? Agnès PS : Thanks for use it and all comment "Discuss" !

		Rob Woof, Sydney Oct 12, 2015
Hi Jim, Looking at your comment, I'm guessing that "time1second" is the name of a field, and that all the other time... are field names as well. As a result, you are wanting to list all of the values in those fields, then sort the list of time... values. If this is the case, your function call should look like: SortList ( List ( time1_second ; time4_second ; time7_second ; time9_second ; time12_second ; time14_second ; time17_second ; time21_second ; time23_second ; time25_second ; time2_second ; time3_second ; time5_second ; time6_second ; time8_second ; time10_second ; time11_second ; time13_second ; time15_second ; time16_second ; time18_second ; time19_second ; time20_second ; time22_second ; time24_second ; time26_second ; time27_second ; time28_second ; time29_second ; time30_second ; time31_second ; time32_second ; time33_second ; time34_second ; time35_second ; time36_second ; time37_second ; time38_second ; time39_second ; time40_second ) ; "Asc" ; "Number" ) This uses the List function to take the field *values* into a return-separated list. Your comment has the field *names* in a return-separated list as the input to the SortList function. HTH Rob

		Eric, SJSU Oct 6, 2016
Great Function Agnes What about sorting Num and Text. Example : having a list of 1¶2¶3¶3B¶4¶6¶5¶3A¶5A¶ is returning : 1¶2¶3B¶3A¶3¶4¶5A¶5¶6 Would be nice to have : 1¶2¶3¶3A¶3B¶4¶5¶5A¶6 Just a thought Eric SCHMIDT

		Agnes Barouh, Tic Tac Oct 6, 2016
Hi Eric, I hope that Let ([ L = "1¶2¶15¶25¶10¶3¶3B¶4¶6¶5¶3A¶5A¶"; r = SortList ( L ; "Asc" ; "Text" ) ]; SortList ( L ; "Asc" ; "Number" ) ) was your solution (?) Regards Agnès

		Agnes Barouh, Tic Tac Oct 6, 2016
We can't edit... it was : Let ([ L = "1¶2¶15¶25¶10¶3¶3B¶4¶6¶5¶3A¶5A¶"; r = SortList ( L ; "Asc" ; "Text" ) ]; SortList ( R ; "Asc" ; "Number" ) // and not L here ) but is not ok :) sorry

		Agnes Barouh, Tic Tac Oct 6, 2016
Pardon :) Perhaps this one are ok : Let ([ L = "1¶2¶15¶5C¶35¶5B¶25¶10¶3¶3B¶4¶6¶5¶3A¶5A¶"; R = SortList ( L ; "Des" ; "text" ) ]; sortList ( R; "Asc" ; "number" ) ) but test and test ( I do not want to go to the code of SortList ; )

		Eric, SJSU Oct 6, 2016
Pas de Quoi :) Yep, nice workaround, .... it works. Understand why you don't want to dive into the code again. I'll keep it aside, ... for Holliday homework :)

		Mustafa Ates, Istanbul/Turkey May 18, 2017
This function works on v14 but does not on v15. For example : sortList ($$Alan ; "" ; "Number" ) result : $50247 Could you please assist me ?

		Agnès, France May 18, 2017
Hi, Could you send me your file by eMail ? Substitute( filemaker§tictac.fr; §; @ ) Thanks Agnès

		Jason M Apr 14, 2022
This is now built into Filemaker: https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help/sortvalues.html

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: