array.cellValueByColTitle ( array ; rowName ; column ; delimiter ; useTitle )

A function to return the cell value from an array by specifying the row name and column title

Average rating: 4.4 (36 votes) Log in to vote

Tim Anderson - Show more from this author
Tim Anderson Group

Sample input:

array.cellValueByColTitle ( array ; "row2" ; "length" ; "§";1 )

array.cellValueByColTitle ( array ; "row3" ; 1 ; "§";0 )
where array is
row1§40§200§23§160
row2§200§40§98§121
row3§500§32§12§90

Sample output:

40
500

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

/*

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 occurrance 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))
)

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 occurrance 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.

Comments

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

array.cellValueByColTitle ( array ; rowName ; column ; delimiter ; useTitle )

Comments

Support this website.

Support this website.