Table Agnostic Scripting
Wednesday 2nd April, 2014
Today I was trying to debug two almost identical scripts - identical except that they referred to two different tables (albeit with the same structure.) What I really wanted to do was use one script, and have it figure out the fields as it went through.
SetFieldByName[ if( $tableName = "report" ; GetFieldName( report::id ) ; GetFieldName( dashboard::id ) ) ; $record_id ]
I was using the
GetFieldName() calls to make sure I don’t have to worry about changing the table schema too much (ha, ha), and the thought struck me: there was an awful lot of:
if( test ; GetFieldName( x ) ; GetFieldName( y ) )
and that generally what I wanted in each case was to set a similar field in both tables, e.g. the record id, or the start date, or the end date.
So instead, how about:
SetFieldByName[ $idField ; $record_id ]
Wouldn’t that be simpler?
Turns Out™ that in fact, yes it is! The trick is a simple variable assignment at the start of the script, that decides which table you’re talking about, and then uses a let statement to assign a bunch of local variables storing the names of the fields you want to use.
Case( $tableName = "report"; Let( [ $from = GetFieldName( report::startDate ); $until = GetFieldName( report::startDate ); ... ]; True); $tableName = "dashboard"; Let( [ $from = GetFieldName( dashboard::startDate ); $until = GetFieldName( dashboard::startDate ); ... ]; True); False ; // trap for $tableName unknown )
(Note the return value - you can easily test after the assignment to make sure that a table was identified.)
After that you can use, for example,
GetField($from) in place of the field name and the field value respectively.
Making this work for another table, is just a case (ahem) of extending the single assignment statement at the top of the script.