FileMaker Pro Custom Functions

/* 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)) )
 Then copy & paste into FileMaker Advanced's Edit Custom Function window.