array.cellValueByColNum ( array ; rowName ; column ; delimiter )

A function to return the column value from an array by specifying the row name.

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

Tim Anderson - Show more from this author
Tim Anderson Group

Sample input:

array.value ( array ; "jelly" ; 2 ; "§" )
where array is
row1§40§200
row2§200§40
jelly§very wobbly§raspberry

Sample output:

"very wobbly"

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

/*

Name:
array.cellValueByColNum

History:
Created by Tim Anderson, Tim Anderson Group
www.timanderson.co.uk
Creation Date: 5 January 2009
Modified: 6 January 2009 - now treats the row names as column 0 and name change to take account of enhanced version array.cellValueByColTitle

 
Purpose:   
A function to return the column value from an array by specifying the row name. Row names must be unique or only the first occurrance will be returned.

Parameters:   
array
rowName
column
delimiter
 
Example: 
array.cellValueByColNum ( array ; "jelly" ; 2 ; "§" ) gives "very wobbly" (no quotes) where array is
row1§40§200
row2§200§40
jelly§very wobbly§raspberry
 

Requires Other Custom Functions:
None

Other Notes:
Have included the option to specify the delimiter for most flexible use

Options:
None

*/ 
Let([_rname="¶" & rowName & "§";
     _notHere=PatternCount(array;_rname)=0;
    _string=Right("¶" & array;Length(array)-Position("¶" & array;_rname;1;1));
    _row=GetValue(_string;1)];
Choose(_notHere;GetValue(Substitute(_row;delimiter;"¶");column+1);"")
)

Purpose:
A function to return the column value from an array by specifying the row name. Row names must be unique or only the first occurrance will be returned.

Parameters:
array
rowName
column
delimiter

Example:
array.value ( array ; "jelly" ; 2 ; "§" ) gives "very wobbly" (no quotes) where array is
row1§40§200
row2§200§40
jelly§very wobbly§raspberry

Comments

		Matt Lygo, UK Oct 5, 2011
A minor change is needed on the first line of the 'code' to take into account the option to specify a delimiter: Let([_rname="Â¶" & rowName & "Â§"; should be changed to Let([_rname="Â¶" & rowName & delimiter;

		Tim Anderson, Tim Anderson Group Oct 5, 2011
Thanks for that Matt, as you will have guessed I initially used the Â§ as a delimiter before deciding that it should be user definable

		Matt Lygo, UK Oct 11, 2011
One more minor change is necessary I think... Currently if the 'rowName' supplied is the first row of the array the function returns an empty result. E.G in the example above, array.value (array ; "row1"; 2; "Â§") gives nothing when you would expect to receive "200". The solution appears to be to either supply the array with a carriage return at the beginning, or to amend the second line of the custom function as follows: Original - _notHere=PatternCount(array;_rname)=0; Changed to - _notHere=PatternCount("Â¶" & array;_rname)=0; That seems to work for me, but I haven't tested thoroughly...

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

array.cellValueByColNum ( array ; rowName ; column ; delimiter )

Comments

Support this website.

Support this website.