Text Arrays in FileMaker

Friday 24th April, 2015

I recently needed to solve a layout problem. The client wanted a table of hours spent by staff members on particular tasks, by date and task number. Something like this:

You get the idea.

The problem was that both dates and tasks were fluid. One member of staff might work just 2 days a week, and have tasks T1, T2, and T3, whilst another might full-time expending their energies on T2, T4, and T4. It probably could be done using repeating fields, but the client didn’t want any empty columns or rows! So there was already a prospect of scripting overhead putting the values in the right repetitions.

The thought occurred that this would be easy to do manually on a typewriter! Therefore, why not use just plain, tabbed text?

Given some pre-calculated fields for the year and month of an entry, and the parameter stored in appropriate variables, getting a list of tasks and dates for each member of staff is a trivial SQL query. E.g.

// list of dates
ExecuteSQL(
    "SELECT entry_date
      FROM task_entries
      WHERE staff_id = ?
      AND entry_month = ?
      ORDER BY entry_date";
    "";
    "";
    $user_id;
    $month
)

// list of tasks
ExecuteSQL(
    "SELECT task_name
      FROM task_entries
      WHERE staff_id = ?
      AND entry_month = ?
      ORDER BY task_name";
    "";
    "";
    $user_id;
    $month
)

At which point… well, what next.

Let’s pretend we already have our text table set out:

    T1,  T2,  T3
D1,  0,   0,   0
D2,  0,   0,   0
D3,  0,   0,   0

Our table will be quite sparse - most staff members work on just one or two tasks per day. Can we at least get a list of the task durations we’ll need to add?

The list of durations is, again, trivial:

// list of durations
ExecuteSQL(
    "SELECT entry_date, task_name, duration
      FROM task_entries
      WHERE staff_id = ?
      AND entry_month = ?
      ORDER BY entry_date, task_name";
    "";
    "";
    $user_id;
    $month
)

Which for the table at the top would give us:

1/1/2015, T1, 3.5
2/1/2015, T2, 3.5
3/1/2015, T1, 1.5
3/1/2015, T2, 1
4/1/2015, T2, 1
4/1/2015, T3, 2.5

Then we’d just need to iterate over this list, and use a custom function to drop the duration into the correct cell in our table, something like:

SetCell( column, row, value )

Of course, there’s no point in making a non-generic function, so a better function signature would be:

SetCell( table, column, row, delimiter, value)

And of course, first we need to create our array. Here’s what I came up with.

/*

Array.Create ( columnNames ; rowNames ; delimiter ; nullValue)

Example:
	Array.Create ( "A∂B∂C∂D" ; "1∂2∂3" ; "." ; "0" )

Result:
	".A.B.C.D∂1.0.0.0.0∂2.0.0.0.0∂3.0.0.0.0"

i.e.

	 .A.B.C.D
	1.0.0.0.0
	2.0.0.0.0
	3.0.0.0.0

*/

Let(
	[
		colCount = ValueCount( columnNames );
		rowCount = ValueCount( rowNames );
		rowSlug = Substitute ( 10^colCount- 1 ; "9" ; delimiter &     nullValue )
	];
	List(
		delimiter & Substitute( columnNames ; ∂ ; delimiter );
		Substitute ( rowNames ; ∂ ; rowSlug & ∂ ) & rowSlug
	)
)

Actually, that’s the third attempt. The first was entirely recursive. Then I realised that empty row would be the same for each row, so I made it partially recursive. Then I realised that the same non-recursive replace trick for producing my default row would also work for producing the entire table. Doh!

Now we need to be able to set particular cells in the array.

At this point Google is your friend, and in particular briandunning.com. There was a nice little function by Tim Anderson on there for reading from text arrays - with a little bit of fiddling it could be made to write into arrays.

/*

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 )

 )

My function is a little less manoeuvrable than Tim’s, in that it demands named columns and rows.

Finally, we may need to pull values from our array.

 /*

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  )

)

We can now get the row and column headings, create an empty table, query the cells to fill in, and update the table accordingly.

We can then stick the table directly into a global variable, and put it on the layout in a suitable formatted text box, with the tabs set-up accordingly.