SplitLists18 ( CompoundList ; Delimiter )

Takes an array and moves rows to columns and columns to rows; breaks a typical EQSL result into columns

Be the first to rate this function Log in to vote

Bill Thurmes - Show more from this author
MDCA
http://www.miyotadca.com

Sample input:

SplitLists18 ( "5,8,3¶9,7,12¶A,B,C¶R,S,T" ; "," )

Sample output:

"5,9,A,R,¶8,7,B,S,¶3,12,C,T,"

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

/*=================================
# SplitLists18 ( CompoundList ; Delimiter )
# Splits a compound list into individual lists (any number of lists)
# Specifically, splits an array of columns (delimited by Delimiter) and rows (delimited by ¶) into rows and columns,
# 	mapping columns to rows and rows to columns,
# 	so you can manipulate the resultant individual columns
# Or, for those used to FileMaker nomenclature, splits an array of data from fields and records,
#	with Delimiter between fields and ¶ between records, into an array of results from 
#	each field (separated by Delimiter), with ¶ between the fields' results
# Or, another way to say it: start with an array, and each element of the array is moved 
#	from location n,m to location m,n; transposing array
# Similar to GetColumn CF, except that it returns all columns, with each column's values delimited
#	by Delimiter, and the columns separated by ¶
#
# Dependancies: Requires FMP 18 (that's what the 18 in the CF name means)
#
# Parameters:
# CompoundList is a ¶-delimited value list, with sub-values delimited by Delimiter
# Delimiter can be any character string permitted as a field delimiter by ESQL (except ¶)
# 	Default delimiter is comma
# Result is individual sublists, delimited by Delimiter
#
# Example: SplitLists18 ( "5,8,3¶9,7,12¶A,B,C¶R,S,T" ; "," )
# 	Result: "5,9,A,R,¶8,7,B,S,¶3,12,C,T,"
# Typical data requiring SplitLists18 might be an ESQL query with multiple fields separated by Delimiter;
# 	Result is fields split out into separate lists (delimited by Delimiter), separated by ¶
#	Likely use: get many fields from many records via ESQL query; 
# 		split into lists of info from field1, field2, etc, using SplitLists18;
# 		assign those lists to variables as value lists by Substituting ¶ for Delimiter
# Note that running this consecutively on a list [SplitLists18 ( SplitLists18 ( $List ; "," ) ; "," )]
#	results in original list (with a final Delmiter at the end of each row), 
#	so you can run this, do your manipulation, then run again to get a 
#	compound list with formatting similar to the original list.
# Example: SplitLists18 ( SplitLists18 ( "1|2|3|4|5¶1|4|9|16|25¶A|B|C|D|E" ; "|" ) ; "|" )
#	Result: "1|2|3|4|5|¶1|4|9|16|25|¶A|B|C|D|E|"
# 
# Written by Bill Thurmes, 2019-10-17
===================================*/

Let ( ~n1 = ValueCount ( CompoundList ) ;
	Case ( ~n1 > 0 ;
		While ( [ `n = Substitute ( GetValue ( CompoundList ; 1 ) ; Delimiter ; ¶ ) ;
			$~cfn = If ( ValueCount ( `n ) > GetAsNumber ( $~cfn ) ; ValueCount ( `n ) ; $~cfn ) ; 
				//$~cfn to ensure that we know how many variables to zero out at end
			`i = 0 ] ; //end initial
			GetAsNumber (`i) <= ValueCount ( `n ) ; //condition
			[ `i = `i + 1 ;
			`v = GetValue ( `n ; `i ) ;
			$~cf[`i] = $~cf[`i] & Case ( IsEmpty ( `v ) ; "" ; `v & ¶ ) ] ;//logic
			SplitLists18 ( RightValues ( CompoundList ; ValueCount ( CompoundList ) - 1 ) ; Delimiter )//result
		) ;
		
		/*case no values left*/
		/*start by assembling the result*/
		While ( [ `i = 0 ;
				~result = "" ] ;//initial
			GetAsNumber (`i) <= GetAsNumber ( $~cfn );//condition
			[ `i = `i + 1 ;
			`v = Substitute ( $~cf[`i] ; ¶ ; Delimiter ) ;
			~result = ~result & Case ( IsEmpty ( `v ) ; "" ; `v & ¶ ) ] ; //logic portion for result
			//in result of While, zero out $~cf[i]
			Let ( [ $~cfn = 
					While ( `i = 0 ;
						GetAsNumber ( `i ) <= GetAsNumber ( $~cfn ) ;
						[ `i = `i + 1 ;
						$~cf[`i] = "" ] ;
						""
						) ] ; //end zeroing While
				~result ) //end Let
			) //end result While
	)//end Case
)//end Let

Splits a compound list into individual lists (any number of lists), transposing the array
Specifically, splits an array of columns (delimited by Delimiter) and rows (delimited by ¶) into rows and column, mapping columns to rows and rows to columns, so you can manipulate the resultant individual columns. You can then re-apply the CF to get back to your original (but manipulated) rows and columns.
Works with FMP 18 or later
This is *MUCH* faster than running an equivalent subroutine, but monopolizes the system while running. Often, a separate ESQL query for each column will be even faster.

Comments

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

SplitLists18 ( CompoundList ; Delimiter )

Comments

Support this website.

Support this website.