Array.SetCell ( array ; columnName ; rowName ; delimiter ; value )

Set a cell within a text array - useful for creating sparse CSV data for virtual list export.

Average rating: 4.6 (26 votes) Log in to vote

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

Sample input:

Array.SetCell ( ".A.B.C.D¶1.0.0.0.0¶2.0.0.0.0¶3.0.0.0.0" ; "B" ; "2" ; "." ; "X" )

Sample output:

".A.B.C.D¶1.0.0.0.0¶2.0.X.0.0¶3.0.0.0.0"

i.e.

.A.B.C.D
1.0.0.0.0
2.0.X.0.0
3.0.0.0.0

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

/*

Array.SetCell ( array ; columnName ; rowName ; delimiter ; value )

Example:
	Array.SetCell ( ".A.B.C.D¶1.0.0.0.0¶2.0.0.0.0¶3.0.0.0.0" ; "B" ; "2" ; "." ; "X" )

Result:
	".A.B.C.D¶1.0.0.0.0¶2.0.X.0.0¶3.0.0.0.0"

i.e.

	 .A.B.C.D
	1.0.0.0.0
	2.0.X.0.0
	3.0.0.0.0
	
*/

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 ;
		
		_insertRow = MiddleValues( array ; _rowNum ; 1 );


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

		_resultRow = Left( _insertRow ; from ) & value & Middle( _insertRow ; until ; 64000 )

	] ; 
	
	LeftValues( array ; _rowNum - 1 ) & _resultRow & MiddleValues( array ; _rowNum + 1 ; 64000 )

 )

The function allows you to add to an existing array, created with say Array.Create(). Because you can change the delimiter it makes export sparse CSV data easier; create an empty array; fetch the data from an SQL query that returns a list of colName,rowName,value tuples; iterate over the list using Array.SetCell to fill in the blanks in the empty array.

Edited to make parameter order match other functions.

Comments

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

Array.SetCell ( array ; columnName ; rowName ; delimiter ; value )

Comments

Support this website.

Support this website.