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.

Something like:

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, $from and 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.