ListColumnValue ( Value ; NumberOfColumn ; ColumnSeparator ; RowSeparator )

Get return-separated value of specified column in Value. Like List function for multi-column array.

Average rating: 4.7 (21 votes) Log in to vote

Koji Takeuchi - Show more from this author
TonicNote, Inc.
https://tonicnote.com

Sample input:

ListColumnValue ( "George Harrison¶John Lennon¶Paul McCartney¶Ringo Starr" ; 2 ; " " ; ¶ )

Sample output:

"Harrison¶Lennon¶McCartney¶Starr"

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

/* ------------------------------------------------------------------------
ListColumnValue ( Value ; NumberOfColumn ; ColumnSeparator ; RowSeparator )
2012.09.22, Koji Takeuchi <takeuchi@nemoux.com>


description:
	Get return-separated value of specified column in Value.
	Like List function for multi-column array.
ex1.
	$$Array = "George Harrison¶John Lennon¶Paul McCartney¶Ringo Starr"

	ListColumnValue ( $$Array ; 2 ; " " ; ¶ )
	= "Harrison¶Lennon¶McCartney¶Starr"
ex2.
	$$Array = "George,Harrison,Lead Guitar,Gretch¶John,Lennon,Rhythm Guitar,Rickenbacker¶Paul,McCartney,Bass,Hofner¶Ringo,Starr,Drums,Ladwig"

	ListColumnValue ( $$Array ; 3 ; "," ; ¶ )
	= "Lead Guitar¶Rhythm Guitar¶Bass¶Drums"
ex3.
	$$Array = "George,Harrison,Lead Guitar,Gretch¶John,Lennon,Rhythm Guitar,Rickenbacker¶Paul,McCartney,Bass,Hofner¶Ringo,Starr,Drums,Ladwig"

	ListColumnValue ( $$Array ; 3 ; "" ; "" )
	= "Lead Guitar¶Rhythm Guitar¶Bass¶Drums"
	// If you don't specify ColumnSeparator or RowSeparator, "," and "¶" are used by default.
------------------------------------------------------------------------ */

Case (
not IsEmpty ( Value ) and
GetAsNumber ( NumberOfColumn ) > 0 ;

Let (
[
sep_col =
	Case (
		not IsEmpty ( ColumnSeparator ) ;
		ColumnSeparator ;
		","
	) ;
sep_row =
	Case (
		not IsEmpty ( RowSeparator ) ;
		RowSeparator ;
		"¶"
	) ;
return_separated =
	Case (
		sep_row ≠ "¶" ;
		Substitute (
			Value ;
			[ ¶ ; "<<return_for_separated_value>>" ] ;
			[ sep_row ; ¶ ]
		) ;
		Value
	) ;
current_row =
	GetValue ( return_separated ; 1 ) ;
current_row_return_separated =
	Substitute (
		current_row ;
		sep_col ;
		¶
	) ;
current_value =
	GetValue ( current_row_return_separated ; NumberOfColumn ) ;
rest_return_separated =
	RightValues ( return_separated ; ValueCount ( return_separated ) - 1 ) ;
rest_value =
	Case (
		sep_row ≠ "¶" ;
		Substitute (
			rest_return_separated ;
			[ ¶ ; sep_row ] ;
			[ "<<return_for_separated_value>>" ; ¶ ]
		) ;
		rest_return_separated
	)
] ;

current_value &
Case (
not IsEmpty ( rest_value ) ;
¶ & ListColumnValue ( rest_value ; NumberOfColumn ; ColumnSeparator ; sep_row )
)

) // Let
) // Case

ListColumnValue ( Value ; NumberOfColumn ; ColumnSeparator ; RowSeparator )
2012.09.22, Koji Takeuchi

description:
Get return-separated value of specified column in Value.
Like List function for multi-column array.
ex1.
$$Array = "George Harrison¶John Lennon¶Paul McCartney¶Ringo Starr"

ListColumnValue ( $$Array ; 2 ; " " ; ¶ )
= "Harrison¶Lennon¶McCartney¶Starr"
ex2.
$$Array = "George,Harrison,Lead Guitar,Gretch¶John,Lennon,Rhythm Guitar,Rickenbacker¶Paul,McCartney,Bass,Hofner¶Ringo,Starr,Drums,Ladwig"

ListColumnValue ( $$Array ; 3 ; "," ; ¶ )
= "Lead Guitar¶Rhythm Guitar¶Bass¶Drums"
ex3.
$$Array = "George,Harrison,Lead Guitar,Gretch¶John,Lennon,Rhythm Guitar,Rickenbacker¶Paul,McCartney,Bass,Hofner¶Ringo,Starr,Drums,Ladwig"

ListColumnValue ( $$Array ; 3 ; "" ; "" )
= "Lead Guitar¶Rhythm Guitar¶Bass¶Drums"
// If you don't specify ColumnSeparator or RowSeparator, "," and "¶" are used by default.

Comments

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

ListColumnValue ( Value ; NumberOfColumn ; ColumnSeparator ; RowSeparator )

Comments

Support this website.

Support this website.