GetColumnOfList ( _list ; _column ; _delimiter )

extracts a column from a multi-column list

Be the first to rate this function Log in to vote

Otmar Kramis - Show more from this author
https://developer.balimusic.ch

Sample input:

GetColumnOfList ( "1,red,apple,steel¶2,green,pear,wood¶3,blue,orange,rubber¶4,yellow,peach,concrete" ; 3 ; "," )

Sample output:

"apple¶pear¶orange¶peach"

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

/**********************************************************
GetColumnOfList ( _list ; _column ; _delimiter )

Author: Otmar Kramis

Dependency: CustomList, Agnes Barouh:
https://www.briandunning.com/cf/868

extracts a column from a multi-column list

Params:
_list : the List
_column : the column to extract
_delimiter : the field delimiter of the list

If your list values contain the pipe "|", 
replace $delimiter = "|" with a single not used character

Samle call :

Let ( 
_list = "1,red,apple,steel¶2,green,pear,wood¶3,blue,orange,rubber¶4,yellow,peach,concrete"
; GetColumnOfList ( _list ; 3 ; "," ) 
)

Result : "apple¶pear¶orange¶peach"

***********************************************************/

Let ( [

$delimiter = "|"
; $list = Substitute ( _list ; _delimiter ; $delimiter )
; $column = _column
; $n = ValueCount ( $list )
; _function = "Let ( [ $value = GetValue( $list ; [n] ) & $delimiter ; $start = Position ( $value ; $delimiter ; 1 ; $column - 1 ) + 1 ]; Middle ( $value ; $start ; Position ( $value ; $delimiter ; 1 ; $column ) - $start ) )"

] ;

CustomList ( 1 ; $n ; _function )

)

Based on the genius CF CustomList of Agnes Barough.
Since FM12 we gather data with ExecuteSQL, getting lists. Unfortunately we can't query this lists again to extract i.e. just a single column of it, so here you go with a non recursive CF.

Comments

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

GetColumnOfList ( _list ; _column ; _delimiter )

Comments

Support this website.

Support this website.