Function overview
Prototype
array.cellValueByColTitle (
array; rowName; column; delimiter; useTitle )
Parameters
array
rowName
column
delimiter
useTitle
Description
Tags:
Text Parsing Cell Array
A function to return the cell value from an array by specifying the row name and column title. Row names must be unique or only the first occurrence will be returned.
A more complex version of array.cellValueByColNum, this gives you the option to specify column Titles (headers) rather than by number. However you can also specify the column number (the row name is zero) as long as you set useTitle to 0.
Examples
Sample input
array.cellValueByColTitle ( array ; "row2" ; "length" ; "§";1 )
array.cellValueByColTitle ( array ; "row3" ; 1 ; "§";0 )
where array is
§width§length§height§weight
row1§40§200§23§160
row2§200§40§98§121
row3§500§32§12§90
Sample output
40
500
Function code
/*
Name:
array.cellValueByColTitle
History:
Created by Tim Anderson, Tim Anderson Group
www.timanderson.co.uk
Creation Date: 5 January 2009
Purpose:
A function to return the cell value from an array by specifying the row name and column title. Row names must be unique or only the first occurrence will be returned.
Parameters:
array
rowName
column
delimiter
useTitle
Example:
array.cellValueByColTitle ( array ; "row2" ; "length" ; "§";1 ) gives 40
array.cellValueByColTitle ( array ; "row3" ; 1 ; "§";0 ) gives 500
(no quotes) where array is
§width§length§height§weight
row1§40§200§23§160
row2§200§40§98§121
row3§500§32§12§90
Requires Other Custom Functions:
None
Other Notes:
A more complex version of array.cellValueByColNum, this gives you the option to specify column Titles (headers) rather than by number. However you can also specify the column number (the row name is zero) as long as you set useTitle to 0.
Options:
None
*/
Let([
_headers=Case(useTitle=1;GetValue(array;1);"");
_colTitlePosition=Position(_headers & delimiter;delimiter & column & delimiter;1;1);
_array=Case(useTitle=1;RightValues(array;ValueCount(array)-1);array);
_colNum=Case(useTitle=1; PatternCount(Left(_headers;_colTitlePosition);delimiter)+1;column);
_rname="¶" & rowName & "§";
_string=Right("¶" & _array;Length(_array)-Position("¶" & _array;_rname;1;1)+1);
_row=GetValue(_string;1)];
Case(useTitle=1 and _colTitlePosition=0;"ERROR, Column Title not found"; GetValue(Substitute(_row;delimiter;"¶");_colNum))
)
// ===================================
/*
This function is published on FileMaker Custom Functions
to check for updates and provide feedback and bug reports
please visit http://www.fmfunctions.com/fid/174
Prototype: array.cellValueByColTitle( array; rowName; column; delimiter; useTitle )
Function Author: TimA (http://www.fmfunctions.com/mid/102)
Last updated: 06 January 2009
Version: 1
*/
// ===================================
/*__LITBR____LITBR__Name:__LITBR__array.cellValueByColTitle__LITBR____LITBR__History:__LITBR__Created by Tim Anderson, Tim Anderson Group__LITBR__www.timanderson.co.uk__LITBR__Creation Date: 5 January 2009__LITBR____LITBR__ __LITBR__Purpose: __LITBR__A function to return the cell value from an array by specifying the row name and column title. Row names must be unique or only the first occurrence will be returned.__LITBR____LITBR__Parameters: __LITBR__array__LITBR__rowName__LITBR__column__LITBR__delimiter__LITBR__useTitle__LITBR__ __LITBR__Example: __LITBR__array.cellValueByColTitle ( array ; "row2" ; "length" ; "§";1 ) gives 40__LITBR__array.cellValueByColTitle ( array ; "row3" ; 1 ; "§";0 ) gives 500__LITBR__ (no quotes) where array is__LITBR____LITBR__§width§length§height§weight__LITBR__row1§40§200§23§160__LITBR__row2§200§40§98§121__LITBR__row3§500§32§12§90__LITBR____LITBR__ __LITBR____LITBR__Requires Other Custom Functions:__LITBR__None__LITBR____LITBR__Other Notes:__LITBR__A more complex version of array.cellValueByColNum, this gives you the option to specify column Titles (headers) rather than by number. However you can also specify the column number (the row name is zero) as long as you set useTitle to 0. __LITBR____LITBR__Options:__LITBR__None__LITBR____LITBR__*/ __LITBR__Let([__LITBR__ _headers=Case(useTitle=1;GetValue(array;1);"");__LITBR__ _colTitlePosition=Position(_headers & delimiter;delimiter & column & delimiter;1;1);__LITBR__ _array=Case(useTitle=1;RightValues(array;ValueCount(array)-1);array);__LITBR__ _colNum=Case(useTitle=1; PatternCount(Left(_headers;_colTitlePosition);delimiter)+1;column);__LITBR__ _rname="¶" & rowName & "§";__LITBR__ _string=Right("¶" & _array;Length(_array)-Position("¶" & _array;_rname;1;1)+1);__LITBR__ _row=GetValue(_string;1)];__LITBR____LITBR__Case(useTitle=1 and _colTitlePosition=0;"ERROR, Column Title not found"; GetValue(Substitute(_row;delimiter;"¶");_colNum))__LITBR__)__LITBR____LITBR__// ===================================__LITBR__/*__LITBR____LITBR__ This function is published on FileMaker Custom Functions__LITBR__ to check for updates and provide feedback and bug reports__LITBR__ please visit http://www.fmfunctions.com/fid/174__LITBR____LITBR__ Prototype: array.cellValueByColTitle( array; rowName; column; delimiter; useTitle )__LITBR__ Function Author: TimA (http://www.fmfunctions.com/mid/102)__LITBR__ Last updated: 06 January 2009__LITBR__ Version: 1__LITBR____LITBR__*/__LITBR__// ===================================