array.cellValueByColTitle ( )
Function stats
Average user rating
Support
FileMaker 8.0 +
Date posted
06 January 2009
Version
1
Recursive function
No
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
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
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
*/
// ===================================
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
*/
// ===================================
