Friday 3 March 2017

Writing maintainable SQL functions in FileMaker

At work recently, I had to troubleshoot why a particular set of Execute SQL functions was not returning the expected results.  This code was not written by me, so I had to analyse what the developer had done.  I don't know for sure, but I suspect the developer used one of the neat SQL Generators for FileMaker, such as SQLExplorer.  Anyway, this is the code.

cf_StripEmptyValues(

Substitute(ExecuteSQL ( 
"SELECT \"z_Record ID_n\", \"corroType\", \"Date\", \"Mailing Title\", \"Title\", \"Signatory\", \"Body Result\"  FROM VLCorrespondence WHERE \"zk_CommunityID_t\" = ? OR \"zk_CommunityID Child_t\" = ? OR \"zk_Family Code_t\" = ? ORDER BY \"Date\" DESC" ;  "|"; "|*|"; Student Current::Student ID; Student Current::Student ID; Student Current::Family Code ); ["¶"; "\n"]; ["|*|"; "¶"])

& "¶" &
Substitute( ExecuteSQL ( "SELECT \"z_RecordID_cn\", \"corroType\", \"Date Sent\", \"Surname Preferred Name\", \"eMail Subject\", \"eMailFromName\", \"eMail Body\"  FROM VLEmail WHERE \"zk_eMailToID_f\" = ? ORDER BY \"Date Sent\" DESC" ; "|"; "|*|"; Student Current::Student ID); ["¶"; "\n"]; ["|*|"; "¶"])

& "¶" &
Substitute(ExecuteSQL ( 
"SELECT \"zk_recordID_p\", \"corroType\", \"Date of Note\", \"Surname Preferred Name\", \"Note\", \"Category\", \"zi_CreationUser\"  FROM VLNotesAC WHERE \"zk_CommunityID_f\" = ? OR zk_FamilyCode_f = ? ORDER BY \"Date of Note\" DESC" ;  "|"; "|*|"; Student Current::Student ID; Student Current::Family Code); ["¶"; "\n"]; ["|*|"; "¶"])

& "¶" &
Substitute(ExecuteSQL ( 
"SELECT \"Ω_id\", \"corroType\", \"created_date\", \"mobile_numb\", \"message_string\" , \"Reply Text\", \"message_string\" FROM VLSMS WHERE \"Student ID\" = ? ORDER BY \"created_date\" DESC" ;  "|"; "|*|"; Student Current::Student ID); ["¶"; "\n"]; ["|*|"; "¶"])
)

Tersely packaged, and at first glance, not all that daunting.  Tearing this apart though, that's another matter, the nested bracketing and many semicolons can make it pretty easy to get lost in.  The idea here was that a series of SQL Queries would be run across a set of tables, one each for Correspondence database (letters, basically), Email database, Notes database, and SMS Messages database, loading them all into a global variable, and displaying the aggregated data in a virtual table.

While that function call above certainly did the job, it was not as "comprehendable" as I would like. I've been reviewing/maintaining/updating a lot of old FileMaker code lately (some of which goes back to FileMaker 4!) and I have cursed under my breath whenever I find it difficult to work out what the code was supposed to be doing. So writing easily comprehendible code has become one of my passions lately.

The first thing was that we are using the same column and row separators for each separate SQL Query, so how about using a let() function, and declare them once at the start?

Let([

fieldSeparator = "|" ;

rowSeparator = "|*|" ;

Next, let's break out the nesting a bit, and make the load easier on the next poor schmuck who has to try and comprehend the code sometime in the future. Firstly, comment on the area being queried, then put the query itself into a variable, then perform the query by inputting the variable into the ExecuteSQL() function, and lastly parse the results.  Oh yes, one other thing, and I cannot stress this enough: use variable names that describe their usage!

/* correspondence */

correspondenceQuery = "SELECT \"z_Record ID_n\", \"corroType\", \"Date\", \"Mailing Title\", \"Title\", \"Signatory\", \"Body Result\"  FROM VLCorrespondence WHERE \"zk_CommunityID_t\" = ? OR \"zk_CommunityID Child_t\" = ? OR \"zk_Family Code_t\" = ? ORDER BY \"Date\" DESC" ; 

correspondenceQueryResults = ExecuteSQL ( correspondenceQuery ; fieldSeparator ; rowSeparator ; Student Current::Student ID; Student Current::Student ID; Student Current::Family Code ) ;

correspondenceData = Substitute ( correspondenceQueryResults ; ["¶"; "\n"]; ["|*|"; "¶"] ) ;



Now each stage of the data gathering process is pretty clear.  Do all the other queries in a similar manner, and then put it all together:

/* now put them together */
assembledResults = correspondenceData & "¶" & emailData & "¶" & notesData & "¶" & smsData ;


/* now clean it up */
finalOutput = cf_cleanAndPlainText ( cf_StripEmptyValues ( assembledResults ) )

];

finalOutput

)

cf_cleanAndPlainText () and cf_StripEmptyValues () are custom functions created in the FileMaker Database for removing unwanted leading and trailing white spaces, and empty rows.  Yes I know about the Trim() function, but that's another essay.

Now, the amount of text in increased, but the individual steps followed to get the aggregate output are much easier to follow.  If you write something like this and come back to it 5 years later, you will be thanking your favourite deity that you went to the effort of laying it out this way. It really doesn't take much longer to write either!

Let([


fieldSeparator = "|" ;

rowSeparator = "|*|" ;



/* correspondence */

correspondenceQuery = "SELECT \"z_Record ID_n\", \"corroType\", \"Date\", \"Mailing Title\", \"Title\", \"Signatory\", \"Body Result\"  FROM VLCorrespondence WHERE \"zk_CommunityID_t\" = ? OR \"zk_CommunityID Child_t\" = ? OR \"zk_Family Code_t\" = ? ORDER BY \"Date\" DESC" ; 

correspondenceQueryResults = ExecuteSQL ( correspondenceQuery ; fieldSeparator ; rowSeparator ; Student Current::Student ID; Student Current::Student ID; Student Current::Family Code ) ;

correspondenceData = Substitute ( correspondenceQueryResults ; ["¶"; "\n"]; ["|*|"; "¶"] ) ;



/* email */

emailQuery = "SELECT \"z_RecordID_cn\", \"corroType\", \"Date Sent\", \"Surname Preferred Name\", \"eMail Subject\", \"eMailFromName\", \"eMail Body\"  FROM VLEmail WHERE \"zk_eMailToID_f\" = ? ORDER BY \"Date Sent\" DESC" ;

emailQueryResults = ExecuteSQL ( emailQuery ; fieldSeparator ; rowSeparator ; Student Current::Student ID ) ;

emailData = Substitute ( emailQueryResults ; ["¶"; "\n"]; ["|*|"; "¶"] ) ;



/* notes */

notesQuery = "SELECT \"zk_recordID_p\", \"corroType\", \"Date of Note\", \"Surname Preferred Name\", \"Note\", \"Category\", \"zi_CreationUser\"  FROM VLNotesAC WHERE \"zk_CommunityID_f\" = ? OR zk_FamilyCode_f = ? ORDER BY \"Date of Note\" DESC" ;

notesQueryResults = ExecuteSQL ( notesQuery ; fieldSeparator ; rowSeparator  ; Student Current::Student ID; Student Current::Family Code ) ;

notesData = Substitute ( notesQueryResults ; ["¶"; "\n"]; ["|*|"; "¶"] ) ;


/* SMS */

smsQuery = "SELECT \"Ω_id\", \"corroType\", \"created_date\", \"mobile_numb\", \"message_string\" , \"Reply Text\", \"message_string\" FROM VLSMS WHERE \"Student ID\" = ? ORDER BY \"created_date\" DESC" ;

smsQueryResults = ExecuteSQL ( smsQuery ; fieldSeparator ; rowSeparator ; Student Current::Student ID ) ;

smsData = Substitute ( smsQueryResults ; ["¶"; "\n"]; ["|*|"; "¶"] ) ;


/* now put them together */
assembledResults = correspondenceData & "¶" & emailData & "¶" & notesData & "¶" & smsData ;


/* now clean it up */
finalOutput = cf_cleanAndPlainText ( cf_StripEmptyValues ( assembledResults ) )

];

finalOutput

)