GetColumn ( text ; columnNumber ; columnDelimiter )
Returns a list of values from the requested column in text.
Average rating: 4.3 (44 votes) Log in to vote
|
Michael Horak - Show more from this author
*COMMENT Visual Realisation |
Sample input:
GetColumn (
"1|2|3¶a|b|c¶x|y|z" ;
2 ;
"|"
)
"1|2|3¶a|b|c¶x|y|z" ;
2 ;
"|"
)
Sample output:
2
b
y
b
y
Function definition: (Copy & paste into FileMaker's Edit Custom Function window)
Returns a carriage return-delimited list of values from the requested column in a tabular text array.
Comments
 |
Henry, Breda Feb 7, 2016 |
|
| Ran into an issue with the 2008 version above, it caused Filemaker to use many GB's memory within 30 seconds and becoming unresponsive when getting columns from a (large) set of data. Found modified version which solved this entirely on the following page, from Mike_Mitchell https://community.filemaker.com/message/171033#171033 |
||
 |
Malcolm Aug 10, 2022 |
|
| Does anyone know of the solution to the above comment? I'm also trying to use this with large sets of data. The above link is not archived on WayBack and search doesn't pull up anything. | ||
|
Michael Horak, *COMMENT Visual Realisation Aug 10, 2022 |
|
| I am guessing it's here: https://community.claris.com/en/s/question/0D50H00006h8xIYSAY/parsing-columns-out-of-a-matrix If you have a large set of data, you probably don't want to use a recursive custom function at all. Even with a tail-recursive rewrite, the maximum number of recursions will be 50,000. |
||
|
Malcolm Aug 10, 2022 |
|
| Great, thanks - posted below for posterity. Thanks for heads up about recursion limits. ----- Here's a custom function I use for that purpose: -------------------------------------------------------------------------------------------------------------------- /* GetColumn function Author *COMMENT Visual Realisation Format cfGetColumn ( text ; columnNumber ; columnDelimiter ; result ) Parameters text - any text expression or text field columnNumber - any numeric expression or field containing a number columnDelimiter - any text expression or text field result - initially empty; used for tail recursion Data type returned text Description Returns a carriage return-delimited list of values from the requested column in a tabular text array. July 7, 2008 Modified February, 2014 to use tail recursion Mike Mitchell, Net Caster Solutions www.netcastersolutions.com */ Let ( [ row = GetValue ( text ; 1 ) ; cell = MiddleValues ( Substitute ( row ; columnDelimiter ; ¶ ) ; columnNumber ; 1 ) ; cell = Substitute ( cell ; ¶ ; "" ) ; countRows = ValueCount ( text ) ] ; Case ( countRows > 0 ; cfGetColumn ( RightValues ( text ; countRows - 1 ) ; columnNumber ; columnDelimiter ; List ( result ; cell )) ; result ) ) -------------------------------------------------------------------------------------------------------------------- HTH Mike |
||
|
Malcolm Aug 10, 2022 |
|
| Great, thanks - posted below for posterity. Thanks for heads up about recursion limits. ----- Here's a custom function I use for that purpose: -------------------------------------------------------------------------------------------------------------------- /* GetColumn function Author *COMMENT Visual Realisation Format cfGetColumn ( text ; columnNumber ; columnDelimiter ; result ) Parameters text - any text expression or text field columnNumber - any numeric expression or field containing a number columnDelimiter - any text expression or text field result - initially empty; used for tail recursion Data type returned text Description Returns a carriage return-delimited list of values from the requested column in a tabular text array. July 7, 2008 Modified February, 2014 to use tail recursion Mike Mitchell, Net Caster Solutions www.netcastersolutions.com */ Let ( [ row = GetValue ( text ; 1 ) ; cell = MiddleValues ( Substitute ( row ; columnDelimiter ; ¶ ) ; columnNumber ; 1 ) ; cell = Substitute ( cell ; ¶ ; "" ) ; countRows = ValueCount ( text ) ] ; Case ( countRows > 0 ; cfGetColumn ( RightValues ( text ; countRows - 1 ) ; columnNumber ; columnDelimiter ; List ( result ; cell )) ; result ) ) -------------------------------------------------------------------------------------------------------------------- HTH Mike |
||
Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.