Array.GetCell ( array ; columnName ; rowName ; delimiter )

Fetch the value from the named column and row in a text array.

Average rating: 4.7 (27 votes) Log in to vote

Richard Dyce - Show more from this author
Dyce & Sons Ltd.
http://dyce.com

Sample input:

Array.GetCell ( ".A.B.C.D¶1.0.0.0.0¶2.0.X.0.0¶3.0.0.0.0" ; "B" ; "2" ; "." )

Sample output:

"X"

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

/*

Array.GetCell ( array ; columnName ; rowName ; delimiter )

Example:
	Array.GetCell ( ".A.B.C.D¶1.0.0.0.0¶2.0.X.0.0¶3.0.0.0.0" ; "B" ; "2" ; "." )

Result:
	"X"
	
*/


Let ( 
	[
		// strip headers from array
		_array  = RightValues ( array ; ValueCount ( array ) -1 ) ; 

		// calculate column number		
		_headers = GetValue ( array ; 1 ) ; 
		_colTitlePosition = Position ( _headers & delimiter ; delimiter & columnName & delimiter ; 1 ; 1 ) ; 
		_colNum = PatternCount ( Left ( _headers ; _colTitlePosition ) ; delimiter ) +1 ; 
		
		// calculate row number
		_rowNamePosition = Position ( ¶ & _array ; "¶" & rowName & delimiter ; 1 ; 1 ) ;
		_rowNum = PatternCount ( Left ( ¶ & _array ; _rowNamePosition ) ; ¶ ) +1 ;
		
		_row = MiddleValues( array ; _rowNum ; 1 );


		from = Position ( _row ; delimiter ; 1 ; _colNum - 1 ) ;
		until = If( PatternCount( _row ; delimiter ) < _colNum ; Length( _row ) ; Position ( _row ; delimiter ; from+1 ; 1 ) ) 
	] ; 
 	
	Middle( _row ; from + 1 ; until - from  )

 )

For use with the Array.SetCell(), Array.Create(), and other array functions on this site.

Comments

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

Array.GetCell ( array ; columnName ; rowName ; delimiter )

Comments

Support this website.

Support this website.