AddSortOrder ( values ; padWidth ; delimiter ; currentValue )

Add a numeric prefix to a lists of values, with leading 0's for easier sorting

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

Richard Dyce - Show more from this author
Dyce & Sons Ltd.
http://dyce.com

Sample input:

AddSortOrder ( "q¶w¶e¶r¶t¶y¶q1¶s¶r¶rt¶t¶j" ; "" ; " - " ; "")

Sample output:

01 - q¶02 - w¶03 - e¶04 - r¶05 - t¶06 - y¶07 - q1¶08 - s¶09 - r¶10 - rt¶11 - t¶12 - j¶

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

// AddSortOrder ( values ; padWidth ; delimiter ; currentValue )
// values - list of values to prepend
// padwith -  the number of characters to pad the prefix to
// currentValue - the value we're looking at now
// 
// The function recursively prepends the correctly padded item number for the value.
// e.g. AddSortOrder ( "a¶b¶c¶f¶e¶d¶g¶t¶a" ; 3 ; " - " ; "" )
// gives : "001 - a¶002 - b¶003 - c¶004 - f¶005 - e¶006 - d¶007 - g¶008 - t¶009 - a¶"

Let(
	[
		c = ValueCount( values ) ;
		p = If( padWidth = "" ; Length( c & "" ) ; padWidth ) ;
		n = If( currentValue = "" ; c ; currentValue ) ;
		prefix = Right( ( 10 ^ p ) + n ; p ) & delimiter
	];
	If(
		n = 0 ;
		values ;
		AddSortOrder( 
			List(
				LeftValues( values ; n - 1 ) & Prefix & GetValue( values ; n ) ; // values before the one we're looking at now prepended ti value we're looking at now
				MiddleValues( values ; n + 1 ; c ) // values after the one we're looking at now
			) ; 
			p ;
			delimiter ;
			n - 1
		)
	) 
)

Provides leading-zero numeric prefixing with a definable delimiter.

Comments

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

AddSortOrder ( values ; padWidth ; delimiter ; currentValue )

Comments

Support this website.

Support this website.