[vc_row bg_color=””][vc_column][vc_column_text]After spending two days writing a calculation function, I figured I wasn’t a day short, and that sharing the story behind writing this function could be interesting for other developers—not so much for the function itself, but to capture the mindset and the general ideas that help approach a complex function.

Moreover, since Claris has honored me with a spot at the Engage conference next March in Austin, with a topic as unusual and pretentious as “how I, Fabrice Nordmann, tackle a problem to solve with the FileMaker platform,” I need to start seriously reflecting to understand my own process in order to explain it. As readers, you’re serving as my test audience, for which I thank you.[/vc_column_text][vc_column_text]

First, let’s explain what the function does.

The function is called json2sql and enables the formulation of an SQL query for FileMaker using a JSON parameter.

Indeed, the JSON writing function, JSONSetElement, became quite enjoyable to use with FileMaker 21, and you quickly get used to it, while writing SQL remains cumbersome—especially if you want to write it well, without dependency on field names, so that the query doesn’t break if a field is renamed.

The idea, therefore, is to pass a JSON parameter to this function, have it translate the parameter into an SQL query, and execute it (that was the initial idea, but things got much more complicated afterward, as you’ll see).

We already had a function that we use frequently, sql.match (_requestedField ; _matchField ; _match), which allows retrieving values from a column (_requestedField) in records matching a criterion in a query of this type.

SELECT _requestedField WHERE _matchField = _match

It allows us to handle a large portion of the queries we need when coding in FileMaker.

But, of course, it’s very limited. If you want multiple columns, functions, multiple comparisons (WHERE)… it’s not enough.

We also have a set of functions that help us write SQL cleanly, like **sql.table**, **sql.col**, **sql.in.clause**… but nothing as cool as JSON.

Plus, there were dirty dishes in the sink, so it was clearly the perfect moment to come up with a new function to write.

So here we go!

First, what should be the “signature” of this function? What name? What parameters?

I like to keep things simple, so:

json2sql ( _json )

Exactly, JSON is already designed to structure information, so I’ll include everything in the JSON—it looks nice. (Spoiler: it won’t end up that way.)[/vc_column_text][vc_column_text]So, I’m thinking about the structure of the JSON, keeping in mind that the function ultimately needs to call the native function:

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

If you’re not familiar with the ExecuteSQL function, let’s recap: sqlQuery is a SELECT-only SQL query. You can use ? placeholders instead of comparison values, which FileMaker will replace with items from the argument list at the end of the function, in order. The advantage of this approach is that FileMaker handles data types for you (converts dates to SQL format, adds quotes around text but not numbers, manages decimal separators, etc.).

For example, if I write:

ExecuteSQL ( "SELECT primaryKey FROM invoices WHERE clientName > ? AND invoiceDate = ? AND totalAmount > ?" ; "" ; "" ; "L" ; Date ( 6 ; 1 ; 2024 ) ; round ( 100/3 ;2 ) )

Then, FileMaker will interpret it automatically and execute the following query:

SELECT primaryKey FROM invoices WHERE clientName > 'L' AND invoiceDate = '2024-06-01' AND totalAmount > 33.33

(adds single quotes, converts the date, and uses a period as the decimal separator, even if my file uses a comma, since SQL interprets the period).

That’s our quick refresher on ExecuteSQL; now, let’s get back to writing the function.

We’ll have a JSON structured using a function like:[/vc_column_text][vc_column_text]

JSONSetElement ( ""
   ; [ "query" ; <a complex JSON object I will think about later> ; JSONObject ]
   ; [ "fieldSeparator" ; "" ; JSONString ]
   ; [ "rawSeparator" ; "" ; JSONString ]
   ; [ "arguments" ; <a JSON array, since arguments must be ordered> ; JSONArray ]
)

[/vc_column_text][/vc_column][/vc_row][vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]Note that I wish to use these notations

• "{table}"."{column}" instead of simply {column}

• "{table}" instead of {table}

This approach helps avoid issues with SQL reserved names and oddly named fields, like those with spaces. It’s essential, in my opinion, to adopt this mindset: everyone should be able to use this function in any FileMaker file. I make no assumptions that fields will be named “correctly” or that the decimal separator will match mine, or that the language will be French (even though I personally use the English version of FileMaker). So, always think “ubiquity”: the function should work everywhere, and if it can’t, that limitation should be deliberate, acknowledged, and documented.[/vc_column_text][/vc_column][/vc_row][vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]The “algorithmic” structure of the query is therefore:

"SELECT" & [ loop over an ordered list or array of column names ] & " FROM " & [ table, which is information I can extract from the same list if I fully qualify field references with the table::field notation ]

followed by an optional section (criteria aren’t mandatory):

& " WHERE " & [ loop over an array of criteria composed of a logical operator (except for the first one), a column, a comparison operator, and a value ]

[/vc_column_text][vc_column_text]I finally dive into writing a JSON prototype for the query parameter.

So I open the Data Viewer and…[/vc_column_text][vc_column_text]

JSONSetElement ( ""
   ; [ "query.columns[+]" ; GetFieldName ( invoice::invoiceNumber ) ; JSONString ]
   ; [ "query.columns[+]" ; GetFieldName ( invoice::invoiceDate ) ; JSONString ]
   ; [ "query.columns[+]" ; GetFieldName ( invoice::amount ) ; JSONString ]
)

[/vc_column_text][vc_column_text]Here’s the first array—the list of column names (this is indeed the name, not the value, so the type is always String).

Except… I realise I forgot about functions. I know I don’t want to handle them just yet, but my structure needs to allow for future expansion. I’m also keeping in mind that if I ever want to manage joins, I’ll need a bit more information about the columns, but that’s for later. Starting over:[/vc_column_text][vc_column_text]

JSONSetElement ( ""
   ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceNumber ) ; JSONString ]
   ; [ "query.columns[:].function" ; "Sum" ; JSONString ]
   ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceDate ) ; JSONString ]
   ; [ "query.columns[+].name" ; GetFieldName ( invoice::amount ) ; JSONString ]
)

[/vc_column_text][vc_column_text]Good, now I have an array representing the list of columns, and I need to do something with it.

First and foremost, I need an environment for this. So, I write in my Data Viewer:[/vc_column_text][vc_column_text]

Let ([
   _json = JSONSetElement ( ""
         ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceNumber ) ; JSONString ]
         ; [ "query.columns[:].function" ; "Sum" ; JSONString ]
         ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceDate ) ; JSONString ]
         ; [ "query.columns[+].name" ; GetFieldName ( invoice::amount ) ; JSONString ]
      );
   _query = ""
];
   _query
)

[/vc_column_text][vc_column_text]

[/vc_column_text][vc_column_text]

"SELECT " & While ([
        j = _json ;
	          c = ValueCount ( JSONListKeys ( j ; "columns" )) ;
	          i = 0 ;
	          r = "" ;
	          v = "" ;
	          to = "" ;
	        toq = "" 
	     ];
	        i < c ;
	     [
		        v = Substitute ( JSONGetElement ( j ; "columns[" & i & "].name" ) ; "::" ; ¶ ) ;
		        function = JSONGetElement ( j ; "columns[" & i & "].function" ) ;
		        // as we are selecting from 1 table only, let's extract the table occurrence name only during the first iteration
		        to = Case ( i = 0 ; GetValue ( v ; 1 ) ; to ) ;
		        // quoted version, to avoid issue with reserved words or blanks
		        toq = Case ( i = 0 ; Quote ( to ) ; toq ) ;
		        column = GetValue ( v ; 2 ) ;
		        colq = Quote ( column ) ; // quoted version
		        // append to the list
		        r = List ( r ; Case ( IsEmpty ( function ) ; colq ; function & "(" & colq & ")" )) ;
		        i = i+1
	     ];
		        Substitute ( r ; ¶ ; ", " ) & " FROM " & to 
   )
)

[/vc_column_text][vc_column_text]First, rest assured, I didn’t write this all in one go. But I can’t dissect each step, or you’d get bored, and the dishes in the sink would start to smell…

A few things to note:

• I didn’t handle functions right away.
• I follow several conventions for my While loops, which make the process faster:
  • The iterator is always i (except in nested loops, where it’s j).
  • I start with c (the number of iterations) and initialize i (the iterator), r (the result), and usually v (the processed value).
  • The condition is almost always i < c.
  • Incrementing the iterator (i = i+1) is in the first parameter of the logical part or last when working on a JSON array (zero-based).
  • I only added comments later.

You might also wonder why I have to and toq, column and colq, instead of just the q (quoted) versions. You’re right; at this stage, it’s not necessary. I left them here to make comparison with future versions easier (saving you some work!).[/vc_column_text][vc_column_text]

WHERE clause

I started with the criteria.

I began writing the array, but at that point, I needed some data—or at least a table and fields for testing.

I asked ChatGPT to provide me with a sample dataset in CSV format, making sure it included various data types (text, including at least one column with multi-line text, numbers, and dates) and duplicates (to enable grouping). I also ensured that some column names included spaces or accented characters. I dragged the CSV file onto the FileMaker icon, which converted it into an .fmp12 file, and voilà, I was ready to work “for real.” Well, almost. I first needed to convert the date data from the YYYY-MM-DD format to FileMaker’s format, DD/MM/YYYY in my file. That should have clued me in, but… you’ll see.

Then, back to the Data Viewer.[/vc_column_text][vc_column_text]

JSONSetElement ( "" 
   ; [ "criteria[+].column" ; GetFieldName ( invocies::total ) ; JSONString ]
   ; [ "criteria[:].operator" ; "<" ; JSONString ]
   ; [ "criteria[:].value" ; 100.23 ; JSONNumber ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::invoiceNumber ) ; JSONString ]
   ; [ "criteria[:].operator" ; "LIKE" ; JSONString ]
   ; [ "criteria[:].logicalOperator" ; "OR" ; JSONString ]
   ; [ "criteria[:].value" ; "NC%" ; JSONString ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::clientVatNumber ) ; JSONString ]
   ; [ "criteria[:].operator" ; "=" ; JSONString ]
   ; [ "criteria[:].value" ; "*" ; JSONString ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::taxTotal ) ; JSONString ]
   ; [ "criteria[:].operator" ; "=" ; JSONString ]
   ; [ "criteria[:].value" ; "=" ; JSONString ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::date ) ; JSONString ]
   ; [ "criteria[:].operator" ; ">" ; JSONString ]
   ; [ "criteria[:].value" ; Date ( 12 ; 1 ; 2015 ) ; JSONString ]
)

[/vc_column_text][vc_column_text]As you can see, with the new [+] and [:] notation in version 21, it’s incredibly easy to write an array naturally. With [+], I start a new row, and with [:], I add a “column” (“attribute”) while staying in the same row.

This allows me to write in a very natural way, and new ideas come to mind as I go. For example, I thought of using common, practical search operators in FileMaker, such as “=” (empty) and “*” (not empty). So, I included these cases in the prototype, planning to map them to IS NULL and IS NOT NULL in SQL. What’s important here is that I hadn’t considered this before starting, but as I worked, I realised it would be manageable and decided to integrate it right into the first version.

First pitfall

On the other hand, while writing this prototype, I realize something I hadn’t thought of (I should have, but I must face the truth, even if it disappoints my mother: I am imperfect).
Note: For my grandmother, whom I mentioned earlier (article in French), it’s all black or white; she has a very Boolean way of thinking.

And so, the issue is that when it comes to date comparison, I realize that if I pass the arguments as ?, as I initially wanted, so they can be substituted with values (see above on the ExecuteSQL syntax), the type will inevitably be lost through JSON, as JSON lacks a date type. As a result, FileMaker won’t be able to pass the correct data type, seeing only text or a number.

This adds to what I already had in mind: to pass a variable number of parameters to a function, as indicated by {}(optional) and … (variable number) in the function’s signature:

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

[/vc_column_text][vc_column_text]This mishap led me to reconsider the signature I had chosen, with a single JSON parameter.

Although I could have continued in this direction, I realized that including fieldSeparator and rowSeparator parameters in the JSON wouldn’t highlight the difference from ExecuteSQL or the way arguments are passed. I decided to revise this approach and move to the following signature:

json2sql ( _jsonQuery ; _fieldSeparator ; _rowSeparator )

which resembles the signature of the native function more closely:

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

and it lightens the documentation that the user (developer) will need to read to use the function.

This, too, is part of the “mindset” I adopt when writing a custom function: I aim to respect the conventions of the FileMaker platform and to be as consistent as possible. I’m not giving up the underscores before the parameters, though; firstly, they remind the user that this is a custom function, and secondly, it’s a convention I find quite important in my code. But let’s not get into naming conventions…[/vc_column_text][vc_column_text]I then add to my function the part that handles the criteria (the WHERE clause):[/vc_column_text][vc_column_text]

<the first part of the function here>
& Case ( not IsEmpty ( JSONListKeys ( j ; "criteria" )) ; 
   " WHERE " &
   While ([
      i = 0 ;
      c = ValueCount ( JSONListKeys ( j ; "criteria" )) ;
      v = "" ;
      r = ""
   ];
      i < c ;
   [
      f = GetValue ( Substitute ( JSONGetElement ( j ; "criteria[" & i & "].column" ) ; "::" ; ¶ ) ; 2 ) ;
      o = JSONGetElement ( j ; "criteria[" & i & "].operator" ) ;
      o = Case ( IsEmpty ( o ) ; "=" ; o ) ; // default comparison operator is =
      lo = JSONGetElement ( j ; "criteria[" & i & "].logicalOperator" ) ; 
      lo = Case ( IsEmpty ( lo ) ; "AND" ; lo ) ; // default logical operator is AND
      value = JSONGetElement ( j ; "criteria[" & i & "].value" ) ; 
      type = JSONGetElementType (  j ; "criteria[" & i & "].value" ) ;
      // this ugly things converts the data to JSON then parses it as a string to handle decimal separators and single quotes.
      v = Case ( type = JSONNumber or type = JSONBoolean ; Substitute ( JSONSetElement ( "" ; "v" ; value ; JSONNumber ) ; [ "{\"v\":" ; "" ] ; [ "}" ; "" ] ) ; "'" & value & "'" ) ;
      r = r & Case ( i ; " " & lo & " " ) // the logical operator is omitted for the first criterion
          & Quote ( f ) & " " & Case ( o = "=" and ( IsEmpty ( value ) or value = "=" ) ; "IS NULL" ; o = "=" and value = "*" ; "IS NOT NULL" ;  o & " " &  v ) ;
      i = i+1 
   ] ;
      r
   )
)

[/vc_column_text][vc_column_text]A few comments:

• This part is conditional on finding a criteria array in the JSON. I could have included the ” WHERE ” directly in the While result, but I find this approach more readable. It’s immediately clear that it’s the WHERE clause of the query.
• For the comparison operator (o), if it’s empty, I default to “=”. Considering default values helps make the function easier to use.
• For the logical operator, the default is AND.
• As anticipated, handling * and = is very straightforward (see the definition of r).

[/vc_column_text][vc_column_text]I’ll briefly cover the other clauses: GROUP BY, ORDER BY, OFFSET, and FETCH FIRST. They’re fairly straightforward in the function’s code.

Just note that I intentionally excluded OFFSET by percentage, as it introduced ambiguity in the JSON parameter—should I send a number (5) or a text (“5%”)? Also, I’ll admit that I’ve never used the percentage offset, so I didn’t want to add unnecessary complexity, at least not in the first version.[/vc_column_text][vc_column_text]

// GROUP BY
& Case ( not IsEmpty ( JSONListKeys ( j ; "group" )) ; " GROUP BY " &

   While ([
      i = 0 ;
      c = ValueCount ( JSONListKeys ( j ; "group" )) ;
      f = "" ;
      r = ""
   ];
      i < c ;
   [
      f = GetValue ( Substitute ( JSONGetElement ( j ; "group[" & i & "].column" ) ; "::" ; ¶ ) ; 2 ) ;
      r = List ( r ; Quote ( f )) ;
      i = i+1 
   ] ;
      Substitute ( r ; ¶ ; ", " )
   )
)

// ORDER BY
& Case ( not IsEmpty ( JSONListKeys ( j ; "sort" )) ; " ORDER BY " &
   While ([
      i = 0 ;
      c = ValueCount ( JSONListKeys ( j ; "sort" )) ;
      d = "" ;
      r = ""
   ];
      i < c ;
   [
      f = GetValue ( Substitute ( JSONGetElement ( j ; "sort[" & i & "].column" ) ; "::" ; ¶ ) ; 2 ) ;
      d = JSONGetElement ( j ; "sort[" & i & "].dir" ) ; 
      d = Case ( IsEmpty ( d ) ; "A" ; d ) ;
      r = List ( r ; Quote ( f ) & Case ( Left ( d ; 1 ) = "d" ; " DESC" ; " ASC" )) ;
      i = i+1 
   ] ;
      Substitute ( r ; ¶ ; ", " )
   )
)

// OFFSET and FETCH FIRST
& Case ( JSONGetElement ( j ; "offset" ) ; " OFFSET " & JSONGetElement ( j ; "offset" ) & " ROWS" )
& Case ( JSONGetElement ( j ; "limit" ) ; " FETCH FIRST " & JSONGetElement ( j ; "limit" ) & " ROWS ONLY" )

[/vc_column_text][vc_column_text]

SQL execution, and it’s done!

And so, here I am with a function that creates a perfect SQL query from a JSON parameter. All that was left was to execute the SQL and return the result.

Nothing could be simpler.

A few tests. It works like a charm! The queries work perfectly—I get the correct results, with or without functions, with or without criteria…

And then… I had an idea.

And that’s when things started to go sideways…

…but I’ll tell you all about it in part two.[/vc_column_text][vc_column_text]

Conclusions of Part One

• Get into a specific mindset. Here, I know I’m working on a generic function, so it must be capable of operating in any context and be easy for any developer to use. When I create a function for a client project, I prefix the function name with the project name, and at the end of the project, I review these functions to see if any should be made generic and added to our toolkit. But if some qualify, I NEVER do so without reviewing the code to ensure it’s consistent with our other functions, and that it accounts for cases we didn’t need in this specific project.
• Respect the FileMaker platform whenever possible and write functions in a way that makes them naturally usable within that context.
• Thinking about handling default values prevents forcing the user (the developer) to specify all parameters.
• Plan, but not too much. I have an idea of what the function needs to do, and I defer some possibilities right away (like joins in this case), but on the other hand, I don’t close myself off to ideas that may come up along the way (such as handling the comparison operators = and * in this example). As you’ll see in the second part, having an idea midway through cost me a lot of time, but on the other hand, the result was worth the effort.
• Work with data. Start with concrete examples and move towards abstraction. This approach is far more efficient than building an entire mental structure, only to confront it with reality later and realize it doesn’t work.
• Think about data types, and keep in mind that when looping (While), multi-line texts are a data type in themselves.
• Have conventions (like the While loop structure here, among others) and stick to them.

[/vc_column_text][/vc_column][/vc_row]

[vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]As we saw in this detailed review of what’s new in FileMaker 2023, one of the two flagship features, along with Perform Script on Server With Callback, is what Claris calls “Audit Log.”

There is so much to know about this feature that we preferred to dedicate a full post to it.[/vc_column_text][ish_headline tag_size=”h2″]Not an audit log[/ish_headline][vc_column_text]First of all, let’s agree that even if Claris’ marketing didn’t resist the temptation to call it that, this feature is not a real audit log, and this for several reasons.

• First of all for a simple reason: this feature does not log anything. It does allow a developer to build an audit log on top of it.
• Then an audit log, to be worthy of its name, must meet certain requirements of inviolability (it must not be possible to make a modification without it being logged, it must not be possible to modify an entry in the log…). Even if we will see that this is the best FileMaker could offer, the new functionality does not meet these criteria.

Does this mean that you should pass by without looking? Not at all! The new feature is very exciting, but as Camus said, “to name things wrongly is to expose oneself to criticism” (or something like that). And exposing yourself to criticism from an external auditor who was sold an “audit log” that wasn’t an audit log… that’s a situation I’d rather avoid.

So no, the audit log is not an audit log. So be it. What should we call it then?[/vc_column_text][ish_headline tag_size=”h2″]Window transactions[/ish_headline][vc_column_text]This is the “technical” name of the feature. This is the name under which it is found in FileMaker Pro, and it seems to me to be much closer to reality.

Window transactions show up as a script trigger in the file options, and will apply to all transactions.

Once enabled, every transaction (or almost every transaction) will trigger the script.

What is a transaction ? A transaction is not particularly what Claris called a transaction in version 19.6.
A transaction is simply the act of creating, modifying, deleting a set of records (including a single record) and committing. It already existed before 19.6, even if 19.6 allowed to write transactions more easily, or to make some actions “transactional” when it was not possible before.
Note that the modification of a global field, which is not strictly speaking a data modification, does not cause a window transaction, (except of course if the modification of a global field leads to a modification of another field, which is standard, by means of an calculated result auto-entry).

In short, you have understood what a data transaction is. During a transaction you can :

• create new records
• modify records
• delete records

Window transactions are the closest thing FileMaker has to a data transaction, or more precisely to an event that takes place on a data transaction.

But these are window transactions. So you still need a window, even a virtual one.

In other words, direct interactions with the data layer are excluded from these transactions:

• Data API (unless it executes a script)
• OData (unless it runs a script)
• PHP/XML (unless running a script)
• ODBC
• Truncate table script step
• A file is defined as an external data source of an interface file in which window transactions are enabled (active trigger), but data is modified from another file.

That’s why even if a log system was provided, this feature would not allow a real “audit log”.[/vc_column_text][ish_headline tag_size=”h2″]How window transactions work[/ish_headline][vc_column_text]As we have already mentioned, the configuration interface can be found in the file options (File Menu)

On peut sélectionner le script qui sera activé quand on valide une transaction, et indiquer facultativement le nom hard-codé d’une rubrique. Comme pour les autres déclencheurs au niveau du fichier, seul un script du fichier en cours peut être sélectionné. Dans ce cas ci, c’est un peu dommage, mais on comprend bien la raison.

Quand une transaction est validée, le script sera déclenché et recevra automatiquement un paramètre en JSON tel que :[/vc_column_text][/vc_column][/vc_row][vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]

{
   "fileName":
   {
       "tableName":
       [
             [ //(for each record):
                  modification type ("New", "Modified" ; "Deleted"),
                  record ID,
                  "optional parameter"
             ];
             [ 
                  modification type ("New", "Modified" ; "Deleted"),
                  record ID,
                  "optional parameter"
             ]
        ]
   }
}

[/vc_column_text][vc_column_text]As you spotted at first glance because you are now JSON experts, the modified records are elements of an array, with 3 pieces of information so you need to know the order: the type of modification (New, Modified, Deleted), the record ID, and the optional parameter, which will be present but empty if you didn’t set it.

The other thing you immediately noticed is that there is no trace of modified data or field names.[/vc_column_text][ish_headline tag_size=”h3″]Optional parameter[/ish_headline][vc_column_text]The content of the optional parameter comes from the field that you can choose in the file options.

If you did not specify a field name and there is a field named OnWindowTransaction, this field will be taken into account instead.

So at the time of the transaction commit, for each record created, modified, or deleted (in this last case the field is evaluated before deletion), the field -which can of course be calculated- is evaluated and its content passed as an optional parameter for this record.

JSON

Note that if the content of the field is a JSON, it is not rendered as a string (“stringified”). To be very precise, in order not to waste too much time validating each JSON, which would greatly slow down transactions with many records, FileMaker considers that if the content of the field starts and ends with { and } or [ and ], it is a valid JSON.[/vc_column_text][ish_headline tag_size=”h3″]Mass transactions[/ish_headline][vc_column_text]There are nine ways to edit multiple records in one operation with FileMaker.

Some of them are perfectly handled by the window transactions, others are not.

Let’s eliminate the ones that are not handled first, but keep them in mind if we really want to talk about audit logging:

• External requests directly on the data layer (Data API, OData, PHP/XML, ODBC). In the case of script execution (and therefore the existence of a window), window transactions will take place.
• Truncate table script step.
• Modification of the data schema (you can delete a table, create a calculated field or modify a calculation formula, validate the modification, then change the type of field to make it a standard field, and the data has been modified without a window transaction.
• Let’s add a variant of this: file creation by conversion. If, for example, you drag an Excel file onto the FileMaker icon, you will obtain a file with a table and data, even before you have been able to configure any triggers or scripts.

Handled methods are :

Non-transactional methods :

• Imports (Import records, Importer folder, Importer from ODBC source) can create or modify data (or both at the same time).
• Delete all records (or Delete found records if you have made a selection beforehand)
• Replace field contents and Relookup Field Contents, for they are in fact the exact same thing.

Transactional methods:

• Transactions through relationships, as they have existed since FileMaker became relational (FileMaker 3, released in 1996): you can modify the main record (the one displayed) as well as create, modify and delete related records (deletion requires a portal object).
• FileMaker 19.6 transactions, which are the same thing except that :
  • they can be done while changing context (no need for a relationship anymore)
  • they can include other types of mass transactions (import, delete all records, replace field contents)

Importance of using “19.6” script transactions for bulk transactions

For transactional methods, it is quite obvious: a transaction will cause one and only one window transaction (all created, modified and deleted records will be in the script parameter)

As for the other three methods (import, delete all, replace), it is very advantageous to also include them in a transaction.

Indeed, as opposed to our FM AuditLog Pro (which will obviously be updated very soon to take advantage of this new feature) which managed to combine the different real transactions into one logical transaction, the window transactions exactly reflect the internal workings of FileMaker.

For example, when you delete all the records, FileMaker deletes them 100 by 100. When you import, it depends on the format of the file (in increments of 25 for the csv format, but in alternating increments of 25 and 1000 for the .mer format (a csv with the column headers), in increments of 500 for Replace field content…
In short, that’s all I had discovered while developing FM AuditLog Pro, but this “internal popote” is surely not interesting for the user and we would have preferred that an import or a deletion be summarized in one transaction. But this is not the case, the OnWindowTransaction script will be triggered as many times as there are internal transactions. It is therefore very important to encapsulate these operations in a script transaction.

Tip : if the user himself can perform an import, a deletion of the found set, or a field contents replacement, we recommend to use the custom menus to replace these commands, with associated scripts such as:

Open transaction
   Import records
Commit transaction

There remains the marginal case where the user is already in the case of a transaction with a paused script. If we want to avoid error 3 for nested transactions (an error that is not a problem but it is less pretty and we like it to be pretty), then we can write :

If [ Get ( TransactionOpenState )]
   Import records
Else
   Open transaction
      Import records
   Commit transaction
End if

Data API, OData, XML

As far as these modes of interaction with FileMaker are concerned, there is the possibility of giving preference to scripts. In order to guarantee the validity of an audit log (in the strict sense), it can be ensured in the security settings that these modes of interaction can only modify records if a script is running.

not isEmtpy ( Get ( ScriptName ))

Unfortunately, this comes at the cost of reduced performance.

We can really regret that the script step Execute FileMaker Data API has read-only access. It would be so much easier to convert the API calls into scripts…[/vc_column_text][ish_headline tag_size=”h2″]Drag and drop and Replace Field Contents[/ish_headline][vc_column_text]Since the beginning, two events have been distinguishable in the way of modifying data: drag and drop and Replace Field Contents.

Indeed, these two events have the particularity of being able to operate on records not previously opened, to modify the records, and to keep them “closed”, without triggering an OnRecordCommit event.

Well, that’s great news: window transactions can now capture these events. If the active record is not open when you start a replace action or drag content onto a field, then a window transaction will be triggered after the event.

In terms of data integrity, this is a big improvement![/vc_column_text][ish_headline tag_size=”h2″]Caveats[/ish_headline][vc_column_text]One of the difficulties encountered when setting up a script to log transactions is to disable the script after recording the transaction.

Remember, all transactions taking place in a file window trigger the script.

Workaround: write at the beginning of the script an exit condition like

If [ Get ( LayoutName ) = <theLayoutOnWhichILogTransactions>
   Exit Script
End If
The rest of the script

An annoying bug

The version released today has a very annoying bug. We hope that it will be fixed in a future update.

When running the onWindowTransaction script, the Close Window is simply ignored. It does not return an error but has no effect. – update: this is fixed in 20.1.2, released on Jun-7.[/vc_column_text][ish_headline tag_size=”h2″]An audit log… why?[/ish_headline][vc_column_text]If you’ve read this far, you’ll be rewarded, because beyond the technical aspects, the big question is: “what to do with this new feature?”

As you will see, the potential is enormous .

Examples of use:

• Of course, keep track of the changes. This makes sense, but it should not be forgotten
• Updating related records, or even “views”. It’s an eternal challenge with FileMaker -without table triggers (OnUpdate)- to update related records. For example, if I update the amount of a payment, I want the corresponding invoice to be updated, as well as the customer record (to know the balance). From now on, I can definitely detect that a record in the PAYMENTS table has been created/modified/deleted and update the corresponding records.
• It is easy to imagine view tables that replace list views and that synthesize the information that the user needs to see, without calculation and without links, in order to optimize the scrolling or sorting speed.)
  For example, a record in the CUSTOMERS table, which is linked to n invoices, n orders, n payments… can have its counterpart in the CUSTOMERS_VIEW table, with standard (non-calculated) fields that allow for very fast lists. It is indeed relatively easy to develop a logic that causes such a record to be refreshed as soon as a payment, an order or an invoice is modified in a transaction.
• With a tool like FM AuditLog Pro 3.0 in preparation, it will not only be possible to roll-back transactions as with FM AuditLog Pro 2.0, but it will also be possible to re-execute transactions on another server, for example in a scenario where users work on several continents, each on their own server, but where changes need to be rolled-back to a central server.

So, I hope that these few hints have helped you see the potential of this new feature and that you enjoyed this article enough to share it on the social networks.[/vc_column_text][/vc_column][/vc_row]

[vc_row bg_color=””][vc_column][vc_column_text]With the release of FileMaker 19, it becomes easier than ever (it was already largely possible before – I had the opportunity to show the “#hash trick” in a few conferences 😉 ) to make FileMaker and Javascript communicate. Notably by executing Javascript code in a Web Viewer thanks to the script step Perform Javascript in Web Viewer (FileMaker asking the Web Viewer to execute a Javascript), and to the FileMaker.PerformScript Javascript class (“FileMaker script name”, “Optional parameter” ), Javascript calling a FileMaker script.

However, in our opinion, the latter lacks some possibilities. We have mentioned here the impossibility to resume a FileMaker script that would be paused, but there is also the absence of possibility to differentiate the called script (callback) according to success or failure. This article develops this last point and provides a solution.

Synchronous programming

No library is necessary, but I had to write my own class that extends a bit FileMaker.PerformScript to FileMaker.PerformScript ( scriptName, parameter, successCallback, errorCallback ).

A small example to make a bidirectional interaction between FileMaker and Web Viewer. It is a system of callback and/or promise, a way that Javascript often calls something from the outside and depending on whether everything went well (success) or badly (error), you let something happen in your Javascript application.

In fact, it all boils down to this: “FileMaker, call this script. If it went well do this, if it went badly do that”.

A concrete example with calendar integration: if the user changes the location of an event, he must call FileMaker and save it in the database. If this was NOT successful (for example, a commit error), the transaction must be canceled and the event returned to its original location so that the user sees that it was not saved. This is called asynchronous programming, and it is something that you must absolutely understand if you want to continue with Javascript.

So in the calendar, here’s what really happens:

1. The action in Javascript is called, with immediate actions: what to do if it goes well, what to do if it goes bad.
   Internally, these actions are recorded, and Javascript calls FileMaker.
   The Javascript code isn’t “running” anymore, there is no more pause, we have actually reached the end of the code.
   FileMaker does its job and decides if it wants to tell the Web Viewer if it went well or badly.
   The Web Viewer executes the pieces of code (saved in step 1).

In code :[/vc_column_text][vc_column_text]

#BUSINESS LOGIC
#add as much logic as you want, for instance try to write to a folder

#DEMO: a custom dialog
Show Custom Dialog [ Title: "Choice"; Message: "Was the record saved?"; Default Button: “Yes”, Commit: “No”; Button 2: “No”, Commit: “No” ]

If [ Get ( LastMessageChoice ) = 1 ]
    #we pass success to the callback
    Set Variable [ $result; Value:JSONSetElement ( "" ;
[ "id" ; JSONGetElement ( Get ( ScriptParameter ) ; "id" ) ; JSONString ]; [ "success" ; Get ( LastMessageChoice ) = 1 ; JSONString ]
)]
Else If [ Get ( LastMessageChoice ) = 2 ]
    #we pass error to the callback
    Set Variable [ $result; Value:JSONSetElement ( "" ;
[ "id" ; JSONGetElement ( Get ( ScriptParameter ) ; "id" ) ; JSONString ]; [ "error" ; Get ( LastMessageChoice ) = 2 ; JSONString ]
)]
End If

#RETURN TO WEB VIEWER
Perform JavaScript in Web Viewer [ Object Name: "wv"; Function Name: "fmcallback"; Parameter 1: $result ]

[/vc_column_text][vc_column_text]As you’ll see in the example, I’ve put a library in there. It took a lot of sweat and tears to make it all work. I would like to explain how it all works, but it boils down to this:

I’ve expanded the FileMaker.performScript a little bit so that you can now include a success callback and an error callback. I record it and then I call the FileMaker script. I adjust the parameter a bit, because I add another “id” and then the parameter as passed.
Think of it as a kind of “phone line that FileMaker can remember”. When FileMaker calls the web viewer again with this id, the web viewer knows what action it is.

In fact, the Web Viewer tells FileMaker, “Hello, I’m action 23 and I’d like you to save this record. Afterwards, FileMaker tells the Web Viewer that action 23 went very well!

And here is an example, play with it and if there are any questions, feel free to ask them below! It’s a rather difficult concept, but once you’ve mastered asynchronous programming, you’ll want to do the same in FileMaker 🙂

Promise

Oh yeah, for the diehards: it also works with promise (on Mac anyway, on Windows not sure), so you can do that too :[/vc_column_text][vc_column_text]

fm.performScript ( "getData", "" )
    .then ( (data) => {
       //data is script result of getData
       data.sort();
       fm.performScript ( "storeData", data);
     })
    .then ( ( data ) => {
       //data is script result of storeData
       //do things here
    })
    .catch ( ( error ) => {
        //this is the error of getData or storeData, cool isn't it? :)
        alert(error);
 
    })

[/vc_column_text][/vc_column][/vc_row][vc_row bg_color=””][vc_column width=”1/1″][ish_button el_text=”Download demo” url=”url:https%3A%2F%2Fwww.1-more-thing.com%2Fwp-content%2Fuploads%2F2020%2F06%2F1MT_javascriptCallBack.zip|title:Download%20demo||” size=”big” align=”center” color=”color7″ text_color=”color4″][/vc_column][/vc_row]