自定义函数: SplitLists18 ( CompoundList ; Delimiter )

2020年5月18日 • ExecuteSQL • Value List

函数功能描述

来源:

作者:

作者原述:

猫壹佰描述:

将数组值列表纵横颠倒, 输出新的数组值列表

适用于处理ExecuteSQL返回值列表的某些场景

测试文档下载:

调用范例

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

返回结果

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

函数内容

/*=================================
# 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

当前页面使用FileMaker生成发布, 更新于2021年3月22日0时4分7秒

← 较旧的文章较新的文章 →