[vc_row bg_color=””][vc_column][vc_column_text]Après avoir passé deux jours sur l’écriture d’une fonction de calcul, je me suis dit que je n’étais plus à un jour près et que raconter l’histoire de cette écriture pourrait être intéressant pour d’autres développeurs, non pas tellement pour cette fonction en elle-même, mais pour capter l’état d’esprit et les idées générales qui permettent d’aborder une fonction complexe.

En plus, Claris m’ayant fait l’honneur de me programmer à la conférence Engage en mars prochain à Austin, avec un sujet aussi peu banal et prétentieux que “comment, moi, Fabrice Nordmann, j’aborde une problématique à résoudre avec la plateforme FileMaker”, il faut que je commence sérieusement à faire de l’introspection pour comprendre moi-même comment je fonctionne afin de l’expliquer. En tant que lecteur vous me servez donc de cobaye, ce dont je vous remercie.[/vc_column_text][vc_column_text]

Tout d’abord, expliquons ce que fait la fonction.

La fonction s’appelle json2sql et permet de formuler une requête SQL pour FileMaker en utilisant un paramètre JSON.

En effet, la fonction d’écriture de JSON, JSONSetElement, est devenue franchement agréable à utiliser avec la version 21 de FileMaker, et on prend vite l’habitude, alors qu’écrire du SQL est vraiment pénible, surtout si on veut l’écrire bien, sans dépendance au nom des champs, afin que la requête ne s’arrête pas de fonctionner si on renomme une rubrique.

L’idée est donc de passer un paramètre JSON à cette fonction, qu’elle le traduise en requête SQL et l’exécute (ça c’est l’idée avec laquelle j’ai commencé, mais ça s’est bien compliqué ensuite, vous allez voir).

Nous avions déjà une fonction que nous utilisons beaucoup, sql.match ( _requestedField ; _matchField ; _match ), qui permet de retrouver les valeurs d’une colonne (_requestedField) dans les enregistrements correspondant à un critère dans une requête du type.

SELECT _requestedField WHERE _matchField = _match

Elle nous permet, de réaliser une grosse proportion des requêtes dont nous avons besoin quand on code en FileMaker.

Mais évidemment, c’est très limité. Si on veut plusieurs colonnes, des fonctions, plusieurs comparaisons (WHERE)… ça ne suffit pas.

Nous avons aussi un ensemble de fonctions qui nous aident à écrire proprement du SQL, comme sql.table, sql.col, sql.in.clause… mais rien d’aussi cool que le JSON.

Et puis aussi, il y avait de la vaisselle sale dans l’évier, donc vraiment c’était le moment de trouver une fonction à écrire.

Donc c’est parti, “yapuka”

D’abord, quelle doit être “l’empreinte” de cette fonction ? Quel nom ? quels paramètres ?

J’aime faire simple, alors :

json2sql ( _json )

Ben oui, JSON, c’est déjà fait pour structurer l’information, donc je vais tout indiquer dans le JSON, c’est joli. (Spoiler : ça ne finira pas comme ça).[/vc_column_text][vc_column_text]Je réfléchis donc à la structure du JSON, en ayant bien en tête que la fonction doit, in fine, appeler la fonction native

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

Si vous n’êtes pas familier avec la fonction ExecuteSQL, rappelons que sqlQuery est une requête SQL de type SELECT uniquement, que l’on peut utiliser des ? en lieu et place des valeurs de comparaison et que ces ? vont être remplacés par FileMaker par des éléments dans la liste d’arguments passés en fin de fonction, dans l’ordre. L’avantage de cela est que FileMaker gère tout seul les types de données (transforme les dates au format SQL, ajoute les guillemets autour des textes mais pas des nombres, gère le séparateur décimal…)

Donc par exemple si j’écris :

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

Alors FileMaker va comprendre tout seul et exécuter la requête:

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

(ajout des guillemets simples, transformation de la date, et utilisation du point comme séparateur décimal, même si mon fichier utilise la virgule, car SQL comprend le point).

Voilà pour ce rappel concernant ExecutesSQL, revenons maintenant à l’écriture de la fonction.

On aura donc un JSON écrit avec une fonction du type:[/vc_column_text][vc_column_text]

JSONSetElement ( ""
   ; [ "query" ; <un objet JSON compliqué auquel je réfléchirai dans le détail après> ; JSONObject ]
   ; [ "fieldSeparator" ; "" ; JSONString ]
   ; [ "rawSeparator" ; "" ; JSONString ]
   ; [ "arguments" ; <un array JSON puisque les arguments doivent être passés dans l'ordre> ; JSONArray ]
)

[/vc_column_text][/vc_column][/vc_row][vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]Notons aussi que je souhaite écrire

• "{table}"."{column}" et nom simplement {column}

• "{table}" et non simplement {table}

Ceci afin d’éviter les problèmes de noms réservés de SQL ainsi que les nommages bizarre des champs avec par exemple une espace. Il est très important selon moi d’être dans cet état d’esprit : tout le monde doit pouvoir utiliser cette fonction, dans n’importe quel fichier FileMaker. Je ne prends donc ni pour acquis que les rubriques seront nommées “correctement” ou que le séparateur décimal d’un fichier sera forcément le mien, ou la langue le français (même si j’utilise la version anglaise de FileMaker). Donc toujours penser “ubiquité” : la fonction doit marcher partout, et si elle ne le peut pas, cela doit être conscient, assumé et documenté.[/vc_column_text][/vc_column][/vc_row][vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]La structure “algorithmique” de la requête est donc :

"SELECT" & [ boucle sur une liste ordonnée de noms de colonnes ] & " FROM " & [ table, qui est une information que je peux extraire de la même liste si je qualifie pleinement les références aux rubriques avec la notation table::rubrique ]

Suivi d’une partie facultative (on n’est pas obligé d’avoir des critères) :

& " WHERE " & [ boucle sur un tableau de critères composés d'un opérateur logique (sauf pour le premier), d'une colonne, d'un opérateur de comparaison, et d'une valeur ]

[/vc_column_text][vc_column_text]Je me lance donc enfin dans l’écriture d’un prototype de JSON pour le paramètre query.

J’ouvre donc le DataViewer et…[/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]Voici donc un premier tableau, celui du nom des colonnes (il s’agit bien du nom et non de la valeur, c’est pourquoi le type est toujours String).

Sauf que… je réalise que j’ai oublié les fonctions. Je sais que je ne veux pas les gérer tout de suite, mais il faut que ma structure permette l’évolution. J’ai aussi en tête que si un jour je veux gérer les jointures, il va me falloir un peu plus d’information sur les colonnes, mais ça, c’est pour plus tard. Je reprends :[/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]Bien, maintenant j’ai un tableau qui représente la liste des colonnes, je dois maintenant en faire quelque chose.

Avant tout, il me faut un environnement pour ça. J’écris donc dans mon Visualiseur de données :[/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]Je peux maintenant attaquer le paramètre query, et observer le résultat.

Je ne vous remets pas tout le contenu du Visualiseur de données, juste le paramètre _query de la fonction Let (Definir en français)[/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]D’abord, rassurez-vous, je n’ai pas écrit ça d’un seul coup, mais je ne peux pas décortiquer chaque étape, sans quoi vous allez vous ennuyer et la vaisselle dans l’évier va commencer à sentir…

Notez tout de même que :

• je ne me suis pas occupé des fonctions tout de suite
• j’utilise pour mes fonctions While (TantQue) un certain nombre de conventions qui me font aller assez vite :
  • l’itérateur est toujours i (sauf dans le cas d’une boucle imbriquée (While dans While, auquel cas c’est j)
  • je commence par c (le nombre d’itérations), et par initialiser i (l’itérateur), r (le résultat), et en général v (la valeur traitée)
  • la condition est presque toujours i < c
  • l’incrémentation de l’itérateur (i = i+1) intervient en premier paramètre de la partie logique, ou en dernier quand on travaille sur un array JSON (0-based)
• je n’ai ajouté les commentaires que plus tard

Vous pourriez aussi vous demander pourquoi avoir to et toq, column et colq, et pas seulement la version q (entre guillemets). Vous avez raison, à ce stade ça n’est pas nécessaire. Je les ai laissés ici pour que la comparaison avec les versions suivantes soient facilitées (je vous mâche le travail !)[/vc_column_text][vc_column_text]

Clause WHERE

Je peux donc passer aux critères.

Je commence à écrire le tableau (array), sauf que là, il me faut quelques données, ou du moins, à ce stade, une table et des rubriques.

Je demande à ChatGPT de me fournir un échantillon de données en csv, en faisant bien attention d’avoir différents types (texte (dont au moins une colonne contenant des textes multi-lignes), nombre, date) et des doublons (pour pouvoir faire des regroupements). Je m’assure aussi d’avoir des colonnes dont les noms comportent des espaces ou des caractères accentués. Je glisse mon CSV sur l’icone de FileMaker qui le convertit en fichier .fmp12 et hop, on peut travailler “pour de vrai”. En fait, pas tout à fait encore. Je dois d’abord convertir les données de type date qui sont au format YYYY-MM-DD au format FileMaker, qui dans mon fichier est DD/MM/YYYY. Cela aurait dû me mettre la puce à l’oreille, mais… vous allez voir.

Retour au Visualiseur de données.[/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]Comme vous le voyez, avec cette nouvelle notation [+] et [:] de la version 21, il est vraiment très aisé d’écrire un array de manière naturelle. Avec [+], je commence une nouvelle rangée, avec [:] j’ajoute une “colonne” (“attribut”) en restant dans la même rangée.

Je peux donc écrire presque naturellement, et donc au cours de l’écriture me viennent des idées. Par exemple l’idée d’utiliser des opérateurs de recherche classiques et bien pratiques en FileMaker tel que “=” (vide) et “*” (non vide). Je prévois donc ces cas dans le prototype en pensant que je les traiterais pour des opérateurs IS NULL et IS NOT NULL en SQL. L’important ici est que je n’y avais pas pensé avant de commencer, et que je juge au fil de l’eau que ce sera assez facile à gérer et que donc je vais l’intégrer dès la première version.

Premier écueil

En revanche, en écrivant ce prototype je me rends compte d’une chose à laquelle je n’avais pas pensé (j’aurais dû, mais je dois bien me rendre à l’évidence, au risque de décevoir ma mère : je suis imparfait)
Note: pour ma grand mère, dont je vous parlais ici, c’est tout noir ou tout blanc, elle est très booléenne.

Et donc, ce problème, c’est qu’en arrivant à la comparaison de date, je me rends compte que si je passe comme je le voulais les arguments sous forme de ? pour qu’ils soient substitués pas les valeurs (voir plus haut sur la syntaxe de ExecuteSQL), le type sera forcément perdu par un passage par JSON, JSON étant dépourvu de type date, et donc FileMaker sera incapable de passer le bon type de donnée (il n’y verra que du texte ou du nombre).

Cela s’ajoute à ce que j’avais déjà en tête, à savoir que pour passer un nombre variable de paramètres à une fonction (ce qu’indiquent les {} (facultatif) et les … (nombre variable) dans l’empreinte de la fonction

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

Et ça, je sais que ça va m’obliger à utiliser l’indirection et la fonction Evaluate… et je n’ai pas très envie à ce stade, en particulier quand on est dans un contexte où on joue avec l’indépendance des noms des champs, avec des guillemets, des conversions de type… Bref, le truc pénible qui va compliquer l’écriture de cette fonction, sa maintenance, et éventuellement sa qualité. On verra plus tard qu’on y arrivera, à l’indirection (Evaluate), mais pour d’autres raisons.

Donc je laisse tomber l’idée de passer les arguments avec des ?, et je dois donc insérer directement les valeurs correctement dans le JSON à l’aide d’une fonction que j’utilisais déjà pour écrire les requêtes SQL : sql.getAsSqlData ( _data ; _type ). C’est vrai pour les données de types date, heure et horodatage. Les nombres sont bien gérés par JSON.

D’ailleurs, en écrivant cet article, je me rends compte que le nom même de la fonction json2sql n’est pas forcément le bon. Dans notre bibliothèque de fonction, le premier mot nous permet de regrouper alphabétiquement les fonctions selon ce qu’elles traitent. Nous avons une ribambelle de text.<quelque chose>, de json.<quelque chose>… or ici, il me semble que le sujet est plus le SQL que le JSON. Donc je devrais probablement renommer la fonction sql.fromJson… je vais devoir encore réfléchir un peu à ça.[/vc_column_text][vc_column_text]Cette mésaventure me pousse à reconsidérer l’empreinte que j’avais choisie, avec un simple paramètre en JSON.

Sans que ce soit évident (je pourrais très bien continuer dans cette voie, je me dis que le fait de passer les paramètres fieldSeparator et rowSeparator dans le JSON ne mettra pas en valeur la différence avec ExecuteSQL et la manière de passer les arguments. Je décide de revenir sur cette décision et de passer à l’empreinte suivante :

json2sql ( _jsonQuery ; _fieldSeparator ; _rowSeparator )

qui ressemble plus à l’empreinte de la fonction native :

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

et cela allège un peu la documentation que l’utilisateur (développeur) devra lire pour utiliser la fonction.

Cela aussi, ça fait partie de “l’état d’esprit” dans lequel je me mets pour écrire une fonction personnalisée : j’essaie de respecter les usages de la plateforme FileMaker, et d’être aussi cohérent que possible. Je ne renonce pas pour autant aux _ (underscores) devant les paramètres : d’abord ils rappellent qu’il s’agit d’une fonction personnalisée, et ensuite c’est une convention que je trouve assez importante dans mon code, mais on ne va pas discuter conventions de nommage…[/vc_column_text][vc_column_text]J’ajoute donc à ma fonction la partie qui s’occupe des critères (clause WHERE) :[/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]Quelques commentaires :

• cette partie est conditionnée au fait qu’on trouve bien un tableau (array) “criteria” dans le JSON, j’aurais pu inclure le ” WHERE ” dans le résultat du While, mais je trouve que c’est plus lisible ainsi. On voit tout de suite qu’il s’agit de la clause WHERE de la requête.
• pour l’opérateur de comparaison (o), on voit que s’il est vide, j’utilise “=”, valeur par défaut. Penser à des valeurs par défaut permet de faciliter l’usage de la fonction.
• pour l’opérateur logique, c’est AND par défaut
• comme prévu, le traitement de * et de = est très simple (voir la définition de r)

[/vc_column_text][vc_column_text]Je passe rapidement sur les autres clauses GROUP BY, ORDER BY, OFFSET et FETCH FIRST. Elles sont assez facilement compréhensibles dans le code de la fonction.

Notons simplement que j’ai volontairement exclus le OFFSET par pourcentage, parce que ça introduisait une ambiguité au niveau du paramètre JSON : dois-je envoyer un nombre (5) ou un texte (“5%”). Et puis je vous avoue que je n’ai jamais utilisé ce pourcentage, donc pas envie d’ajouter de la complexité pour rien, en tout cas pas dans la première 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]

Exécution du SQL et c’est fini !

Et donc me voilà avec une fonction qui crée un parfaite requête SQL à partir d’un parmètre JSON. Je n’avais plus qu’à exécuter le SQL et retourner le résultat.

Rien de plus simple.

Quelques tests. Ça marche du feu de Dieu ! (pour les plaintes en blasphèmes, adressez-vous au guichet d’à côté). Les requêtes fonctionnent, j’obtiens bien les bons résultats, avec ou sans fonctions, avec ou sans critères…

Et puis… j’ai eu une idée.

Et c’est donc là que les ennuis ont commencé…

… mais je vous les raconterai dans une seconde partie.[/vc_column_text][vc_column_text]

Conclusions de la première partie

• se mettre dans un certain état d’esprit. Ici je sais que je travaille sur une fonction générique, elle doit donc être capable de fonctionner dans n’importe quel contexte, et être facilement utilisable pas n’importe quel développeur. Quand je crée une fonction dans le cadre d’un projet client, je préfixe le nom de la fonction du nom du projet, et en fin de projet, je parcours ces fonctions pour voir si certaines mériteraient d’être rendues génériques et d’entrer dans notre boîte à outil. Mais si certaines se qualifient, ce n’est JAMAIS sans une revue du code pour qu’il soit présenté de manière cohérente avec nos autres fonctions, qu’il prévoie bien des cas dont nous n’avions pas besoin dans ce projet spécifique…
• dans la mesure du possible, respecter la plateforme FileMaker et écrire les fonctions d’une manière qui les rendra utilisables assez naturellement dans ce cadre.
• penser à gérer des valeurs par défaut pour ne pas contraindre l’utilisateur (le développeur) à renseigner tous les paramètres.
• planifier, mais pas trop. J’ai une idée de ce que dois faire la fonction, je diffère d’emblée certaines possibilités (ici les jointures par exemple), mais d’un autre côté je ne m’interdis pas d’avoir des idées en cours de route (le traitement des opérateurs de comparaison = et * dans cet exemple). Comme vous le verrez dans la seconde partie, avoir eu une idée en cours de route m’a coûté très cher en temps, mais d’un autre côté le résultat méritait cette peine.
• travailler avec des données. Partir du concret et aller vers l’abstrait. Beaucoup plus efficace que de monter toute une construction mentale et d’ensuite la confronter à la réalité et se rendre compte que rien ne va.
• penser aux types de données, et garder en tête que quand on va boucler (While), les textes multi-lignes sont en soi un type de données.
• avoir des conventions (ici la structure de la boucle While, entre autres) et s’y tenir.

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

[vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]Comme nous l’avons vu dans cette revue détaillée des nouveautés de FileMaker 2023, une des deux fonctionnalités phares, avec l’exécution de script sur serveur avec call-back, est ce que Claris appelle “Audit Log”

Il y a tellement de choses à savoir sur cette fonctionnalité que nous avons préféré lui consacrer un billet complet.[/vc_column_text][ish_headline tag_size=”h2″]Pas un audit log[/ish_headline][vc_column_text]Tout d’abord mettons-nous d’accord, même si le marketing de Claris n’a pas résisté à la tentation de l’appeler ainsi, cette fonctionnalité n’est pas un réel audit log, et ce pour plusieurs raison.

• D’abord pour une raison assez simple : cette fonctionnalité ne logue rien du tout. Elle permet éventuellement à un développeur de bâtir son audit log dessus.
• Ensuite un audit log, pour être digne de se nom, doit répondre à certains impératif d’inviolabilité (on ne doit pas pouvoir faire une modification sans qu’elle soit loguée, on ne doit pas pouvoir modifier une entrée dans le log…). Même si on verra que c’est ce que FileMaker pouvait proposer de mieux, la nouvelle fonctionnalité ne répond pas à ces critères.

Est-ce à dire qu’il faut passer son chemin sans regarder ? Pas du tout ! La nouvelle fonctionnalité est enthousiasmante, mais comme disait Camus, “mal nommer les choses c’est s’exposer à la critique”  (ou un truc comme ça). Et s’exposer à la critique d’un auditeur externe à qui on aurait vendu un “audit log” qui n’en serait pas un… voilà bien une situation que je préférerais éviter.

Donc non, l’audit log n’est pas un audit log. Soit. Comment l’appeler alors ?[/vc_column_text][ish_headline tag_size=”h2″]Transactions de fenêtre[/ish_headline][vc_column_text]C’est le nom “technique” de la fonctionnalité. C’est sous ce nom qu’on la retrouve dans FileMaker Pro, et ça me semble être déjà beaucoup plus proche de la réalité.

Enfin, c’est ainsi qu’on la retrouve dans FileMaker Pro… en anglais (OnWindowTransaction), car le déclencheur a été étrangement traduit par SurOperationFenetre en français, mais à moins que l’on parle de finance ou de compatibilité, c’est bien de transaction qu’il s’agit ici. Je réalise en écrivant que les transactions de la version 19.6 sont également, partiellement traduites en “opérations”, mais pas le nom de la fonction Obtenir ( EtatTransactionOuvert ).

Les transactions de fenêtres apparaissent sous la forme d’un déclencheur de script dans les options de fichier, et vont s’appliquer à toutes les transactions.

Une fois activé, chaque transaction (ou presque) déclenchera le script.

Qu’est-ce qu’une transaction ? La question se pose car quand on nomme mal les choses… et depuis 30 ans, il faut dire que nommer les choses bizarrement est une spécialité de FileMaker (essayez de parler de rubriques à des pros de la base de données, ou de modèles à des designers, et vous verrez ce que je veux dire). Donc une transaction n’est pas particulièrement ce que Claris a appelé transaction dans la version 19.6.
Une transaction, c’est simplement le fait de créer, modifier, supprimer un ensemble d’enregistrements (y compris un seul enregistrement) et de valider. Ça existait déjà avant la 19.6, même si la 19.6 a permis d’écrire des transactions plus facilement, ou de rendre certaines actions “transactionnelles” alors que ça n’était pas possible préalablement.
Notons que la modification d’une rubrique globale, qui n’est pas a proprement parler une modification de données, ne provoque pas de transaction de fenêtre, (sauf bien sûr si la modification d’une rubrique globale entraîne une modification d’une autre rubrique, standard elle, par le truchement d’une auto-entrée en résultat de calcul).

Bref, vous avez compris ce qu’est une transaction de données. Lors d’une transaction on peut :

• créer des enregistrements
• modifier des enregistrements
• supprimer des enregistrements

Les transactions de fenêtre sont en FileMaker ce qui se rapproche le plus d’une transaction de données, ou plus exactement d’un événement qui a lieu sur transaction de données.

Mais ce sont des transactions… de fenêtre. Encore faut il donc avoir une fenêtre, même virtuelle.

Autrement dit, sont exclus de ces transactions les interactions directes avec la couche data :

• Data API (sauf si elle exécute un script)
• OData (sauf si elle exécute un script)
• PHP/XML (sauf s’ils exécutent un script)
• ODBC
• Instruction de script Tronquer la table
• Un fichier est défini comme une source de données externes d’un fichier d’interface dans lequel les transactions de fenêtres sont activées (déclencheur actif), mais les données sont modifiées depuis un autre fichier.

Voilà pourquoi même si un système de log était fourni, cette fonctionnalité ne permettrait pas d’avoir un réel “audit log”.[/vc_column_text][ish_headline tag_size=”h2″]Description des transactions de fenêtre[/ish_headline][vc_column_text]Nous l’avons déjà évoqué, l’interface de configuration se trouve dans les options de fichier.

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]

{
   "nomDuFichier":
   {
       "nomDeLaTable":
       [
             [ //(pour chaque enregistrement):
                  type de modification ("New", "Modified" ; "Deleted"),
                  ID de l'enregistrement,
                  "paramètre facultatif"
             ];
             [ 
                  type de modification ("New", "Modified" ; "Deleted"),
                  ID de l'enregistrement,
                  "paramètre facultatif"
             ]
        ]
   }
}

[/vc_column_text][vc_column_text]Comme vous l’avez repéré du premier coup d’œil car vous êtes désormais des experts en JSON, les enregistrements modifiés sont les éléments d’un tableau (array), avec 3 informations dont il faut donc connaître l’ordre : le type de modification (New, Modified, Deleted), l’ID de l’enregistrement, et le paramètre facultatif, qui sera présent mais vide si vous ne l’avez pas défini.

L’autre chose que vous avez immédiatement repérée est qu’il n’y a pas de trace de données ou de noms de rubriques modifiées.[/vc_column_text][ish_headline tag_size=”h3″]Le paramètre facultatif[/ish_headline][vc_column_text]Le contenu du paramètre facultatif provient de la fameuse rubrique que vous avez pu choisir dans les options du fichier.

Si vous n’avez pas indiqué de nom de rubrique et qu’il existe une rubrique du nom de OnWindowTransaction (un truc que vous n’auriez pas inventé si on ne vous l’avait pas dit… un peu comme la chanson de la Sorcière du placard aux balais), cette rubrique sera prise en compte.

Donc au moment de la validation de la transaction, pour chaque enregistrement créé, modifié, ou supprimé (dans ce dernier cas la rubrique est évaluée avant la suppression), la rubrique -qui peut bien sûr être calculée- est évaluée et son contenu transmis comme paramètre facultatif pour cet enregistrement.

JSON

Notons que si le contenu de la rubrique est un JSON, ce JSON n’est pas rendu en string (“stringifié”). Pour être très précis, afin de ne pas perdre trop de temps à valider chaque JSON, ce qui ralentirait fortement les transactions avec beaucoup d’enregistrements, FileMaker considère que si le contenu commence et finit par { et } ou [ et ], il s’agit d’un JSON valide.[/vc_column_text][ish_headline tag_size=”h3″]Transactions massives[/ish_headline][vc_column_text]Il existe neuf manières de modifier plusieurs enregistrements en une opération avec FileMaker.

Certaines d’entre elles sont parfaitement gérées par les transactions de fenêtres, d’autres non.

Eliminons d’abord celles qui ne sont pas gérées, mais gardons-les en tête si nous voulons vraiment parler d’audit log :

• requêtes externes directement sur la couche données (Data API, OData, PHP/XML, ODBC). Dans le cas d’exécution de script (et donc de l’existence d’une fenêtre), les transactions de fenêtre auront lieu.
• action de script Tronquer la table.
• modification du schéma de données (on peut supprimer une table, créer une rubrique calculée ou modifier une formule de calcul, valider la modification, puis changer le type de rubrique pour en faire une rubrique standard, et les données ont été modifiées sans qu’il y ait de transaction de fenêtre.
• ajoutons une variante de cette dernière : la création de fichier par conversion. Si par exemple vous glissez un fichier Excel sur l’icône de FileMaker, vous obtiendrez un fichier avec déjà une table et des données, avant même d’avoir pu configurer le moindre déclencheur ou le moindre script.

Les méthodes gérées sont donc :

Les méthodes non transactionnelles :

• Les importations (Importer enregistrements, Importer un dossier, Importer depuis une source ODBC) sont capables de créer ou modifier des données, ou les deux à la fois.
• Supprimer tous les enregistrements (ou Supprimer les enregistrements trouvés, selon qu’on a fait une sélection préalablement ou non)
• Remplacer le contenu d’une rubrique

Les méthodes transactionnelles :

• Les transactions par lien, telles qu’elles existent depuis que FileMaker est relationnel (FileMaker 3, sorti en 1996) : on peut modifier l’enregistrement principal (celui affiché) ainsi que créer, modifier, supprimer des enregistrements liés -la suppression nécessitant une table externe.
• Les transactions de FileMaker 19.6, qui sont la même chose si ce n’est que :
  • elles peuvent être faites en changeant de contexte (on n’a donc plus besoin de lien)
  • elles peuvent inclure les autres types de transactions massive (importation, suppression de l’ensemble trouvé, remplacer)

Importance de l’utilisation des transactions de script “de la 19.6” pour les transactions massives

Pour les méthodes transactionnelles, c’est assez évident : une transaction occasionnera une et une seule transaction de fenêtre (tous les enregistrements créés, modifiés et supprimés se retrouveront  donc dans le paramètre du script)

Quant aux trois autres méthodes (import, supprimer tous, remplacer), il est très avantageux de les inclure également dans une transaction.

En effet, contrairement à notre FM AuditLog Pro (qui sera bien évidemment mis à jour très prochainement pour tirer parti de cette nouvelle fonctionnalité) qui parvenait à réunir les différentes transactions réelles en une seule transaction logique, les transactions de fenêtres reflètent exactement le fonctionnement interne de FileMaker.

Par exemple, quand on supprime tous les enregistrements, FileMaker les supprime 100 par 100. Quand on importe, cela dépend du format du fichier (par tranches de 25 pour le format csv, mais par tranches alternées de 25 et de 1000 pour le format mer (un csv avec les titres de colonnes), par tranches de 500 pour Remplacer contenu rubrique…
Bref, c’est tout ce que j’avais découvert en développant FM AuditLog Pro, mais cette “popote interne” n’intéresse sûrement pas l’utilisateur et on aurait préféré qu’un import ou une suppression soit résumé en une transaction. Or ça n’est pas le cas, le script SurOperationFenetre sera déclenché autant de fois qu’il y a de transactions internes. Il est donc très important d’encapsuler ces opérations dans une transaction de script.

Conseil : si l’utilisateur peut lui-même procéder à un import, à une suppression de l’ensemble trouvé, ou à un remplacement de rubrique, nous recommandons d’utiliser les menus personnalisés pour remplacer ces commandes, avec des scripts associés tels que :

Ouvrir une opération
   Importer enregistrements
Valider l'opération

Reste le cas marginal où l’utilisateur est déjà dans le cas d’une transaction (opération) avec un script en pause. Si on veut éviter l’erreur 3 pour des transactions imbriquées (erreur qui ne pose pas de problème mais c’est moins joli et on aime bien que ce soit joli), alors on peut écrire :

Si [ Obtenir ( EtatOperationOuvert )]
   Importer enregistrements
Sinon
   Ouvrir une opération
      Importer enregistrements
   Valider l'opération
Fin de si

Data API, OData, XML

Pour ce qui est de ces modes d’interaction avec FileMaker, il existe la possibilité de privilégier les scripts. Afin de garantir la validité d’un audit log (au sens strict), on pourra, dans les réglages de sécurité, s’assurer que ces modes d’interaction ne peuvent modifier des enregistrements que si un script est en cours d’exécution.

not EstVide ( Obtenir ( NomScript ))

Mais cela se fait malheureusement au prix d’une baisse des performances.

On peut vraiment regretter que le pas de script Exécuter FileMaker Data API soit limité à la lecture. Il serait tellement plus simple de convertir les appels à l’API en scripts…[/vc_column_text][ish_headline tag_size=”h2″]Drag and drop (glisser/déposer) et Remplacer[/ish_headline][vc_column_text]Depuis toujours, deux événements se distinguent dans la manière de modifier des données : le drag and drop et Remplacer contenu de rubrique.

En effet, ces deux événements ont la particularité de pouvoir opérer sur des enregistrements non ouverts préalablement, de modifier les enregistrements, et de les garder “fermés”, sans déclencher d’événement SurValidationEnregistrement donc.

Dans le cas du glisser/déposer, cela va même encore plus loin puisque l’on peut carrément modifier un enregistrement alors qu’il est ouvert par un autre utilisateur !

Et bien c’est une excellente nouvelle : les transactions de fenêtre permettent désormais de capter ces événements. Si l’enregistrement actif n’est pas ouvert quand on entame une action de remplacement ou que l’on glisse un contenu sur une rubrique, alors une transaction de fenêtre sera déclenchée après l’événement.

En terme d’intégrité des données, c’est un gros progrès ![/vc_column_text][ish_headline tag_size=”h2″]Difficultés[/ish_headline][vc_column_text]Une des difficultés que l’on rencontrera en mettant en place un script pour logguer ses transactions, c’est d’inhiber le script après avoir enregistré la transaction.

Souvenons-nous, toutes les transactions ayant lieu dans une fenêtre du fichier déclenchent le script.

Parade : écrire en début de script une condition de sortie comme

Si [ Obtenir ( NomModèle ) = <leModèleSurLequelJeLogueLesTransactions>
   Fin de Script
Fin de Si
Suite du script

Un bug ennuyeux

La version qui sort aujourd’hui comporte un bug très ennuyeux. Gageons qu’il sera vite réglé lors d’une prochaine mise à jour.

Lors de l’exécution du script SurOperationFenetre, l’action de script Fermer Fenêtre est tout simplement ignorée. Elle ne retourne pas d’erreur mais est sans effet.

C’est très embêtant car comme souvent, quand on ne veut pas perdre le contexte de l’utilisateur (onglets sélectionnés, position de défilement de la fenêtre et des tables externes…) un script classique serait de la forme :

Nouvelle fenêtre
Activer modèle [ <modèleOùJeLogueLesTransactions> ]
Nouvel enregistrement
Définir rubrique [ rubrique ; Obtenir ( ParamètreScript )]
Fermer Fenêtre

Or la dernière action ne sera pas prise en compte ! C’est embêtant.

En attendant la correction, deux parades à cela :

• dès le départ, transmettre le paramètre à un script exécuté sur serveur, mais cela présente deux inconvénients majeurs :
  • si l’on n’est pas sur serveur (c’est encore le cas dans de nombreuses situations, notamment pour des applications asynchrones sur iPhone ou iPad)
  • sur des grosses transactions, la taille limite du paramètre pourrait être atteinte (un million de caractères)
• utiliser un déclencheur SurTemporisation. Le script prend donc la forme :

Nouvelle fenêtre
Activer modèle [ <modèleOùJeLogueLesTransactions> ]
Nouvel enregistrement
Définir rubrique [ rubrique ; Obtenir ( ParamètreScript )]
Installer un script sur temporisation [ script: Fermer fenêtre; Intervalle : 0,00001 ]
Fin de script

Et bien sûr le script Fermer fenêtre… ferme la fenêtre ! (Bravo si vous aviez deviné ! 😉 )[/vc_column_text][ish_headline tag_size=”h2″]Un audit log… pour quoi faire ?[/ish_headline][vc_column_text]Si vous avez lu jusqu’ici, vous allez être récompensé(e), car au-delà des aspects techniques, la grande question est : “que faire de cette nouvelle fonctionnalité ?”

Or vous allez le voir, le potentiel est énorme -“juste énorme !”, comme disent ceux qui sont nés après la sortie de FileMaker 7.

Exemples d’utilisation :

• Bien sûr, garder la trace des modifications. Cela tombe sous le sens mais il ne faut pas l’oublier
• Mettre à jour des enregistrements liés, voir des “vues”. C’est un éternel challenge avec FileMaker -dépourvu de déclencheurs de tables (OnUpdate)- de mettre à jour des enregistrements liés. Par exemple si je mets à jour le montant d’un paiement je veux que la facture correspondante soit mise à jour, ainsi que la fiche client (pour connaître son solde). Dorénavant, je peux capter à coup sûr qu’un enregistrement de la table PAIEMENTS a été créé/modifié/supprimé et mettre à jour les enregistrements correspondants.
  On peut facilement envisager des tables de vues qui remplacent les vues liste et qui synthétisent l’information que l’utilisateur doit voir, sans calcul et sans lien, afin d’optimiser la vitesse de défilement ou de tri).
  Par exemple un enregistrement de la table CLIENTS, qui est liée à n factures, n commandes, n paiements… peut avoir son pendant dans la table CLIENTS_VUE, avec des rubriques standard (non calculées) qui permettent d’afficher des listes très rapides. Il est en effet relativement facile de développer une logique qui provoque le rafraichissement d’un tel enregistrement dès qu’un paiement, une commande ou une facture est modifié dans une transaction.
• “Réplication”. avec un outil comme FM AuditLog Pro 3.0 en préparation, il sera possible non seulement de faire du Roll-back comme avec FM AuditLog Pro 2.0, mais on pourra ré-exécuter les transactions sur un autre serveur, par exemple dans un scénario ou des utilisateurs travaillent sur plusieurs continents, chacun sur son serveur, mais où les modifications doivent être reportées sur un serveur central.

Voilà, j’espère que ces quelques pistes vous auront permis d’envisager le potentiel de cette nouvelle fonctionnalité et que vous avez apprécié cet article au point de le partager sur les réseaux.[/vc_column_text][/vc_column][/vc_row]

Dans le cadre du développement d’une application de gestion de cession de droits pour un grand groupe d’édition, notre client nous a demandé d’ajouter un module de calcul de droits disponibles. Son objectif était de savoir rapidement quels droits de diffusion étaient disponibles à la vente et dans quelles conditions.

À première vue, cela ne paraissait pas très compliqué : les droits acquis (qu’un ou plusieurs producteurs ont cédés à notre client) et donc disponibles à la vente, sont enregistrés dans les mandats, et les droits cédés aux diffuseurs sont enregistrés dans les contrats.
Autrement dit les mandats représentent les entrées de stock et les contrats les sorties.

Droits des mandats – droits des contrats = droits disponibles ! Voilà, il n’y a plus qu’à écrire un script et on pourra aller boire une bière 🍺. Oui, mais… ce n’est pas aussi simple qu’il y paraît…

Le challenge

Les droits enregistrés dans les mandats peuvent inclure plusieurs modes de diffusion. Chaque mode peut être acquis en exclusivité (c’est-à-dire que notre client est le seul à disposer de ce droit de vente), en non-exclusivité (auquel cas notre client ne pourra pas le vendre en exclusivité), ou en « Holdback » (qui correspond à une interdiction de vente soit totale, soit partielle, par exemple une interdiction de vendre le droit de diffusion du programme à un diffuseur donné). Chaque mode de diffusion acquis l’est pendant une période donnée, sur un ou plusieurs territoire(s) donné(s), les territoires pouvant eux-mêmes être une combinaison de régions ou de pays, et dans une ou plusieurs langues.

Par exemple, les droits acquis de mandats peuvent être de la forme :

Programmes : La soupe aux choux et L’aile ou la cuisse
Modes : Pay TV (Option : Exclusivité) et VOD (Option : Non-exclusivité)
Territoires : Pays de l’UE, Pays du Commonwealth (sans l’Inde ni le Bangladesh) et avec le Canada
Langues : français, anglais, allemand, espagnol, italien
Dates : du 01/01/2020 au 31/12/2023

Le script devrait donc commencer par linéariser (créer des lignes ou enregistrements pour représenter toutes les combinaisons possibles) l’ensemble des droits acquis pour avoir une liste de disponibilités. Dans notre exemple la liste est la suivante :

• La soupe aux choux en Pay TV en exclusivité, en France et en Français du 01/01/2020 au 31/12/2023
• La soupe aux choux en Pay TV en exclusivité, en France et en Anglais du 01/01/2020 au 31/12/2023
• etc

On ne vous liste pas les 1660 lignes de cet exemple simple (2 programmes x 2 modes x 83 pays x 5 langues)

À ce stade, cela pourrait rester raisonnable, même si nous doutions déjà de la capacité de FileMaker à gérer efficacement ce calcul pour des dizaines de mandats pouvant contenir des dizaines de programmes acquis avec chacun différentes d’options de territoires et langues sur des périodes différentes. De plus, notre client nous indique alors qu’il est possible de forcer des options sur les pays ou les langues : il est donc possible d’acquérir la diffusion d’un programme en Pay TV en exclusivité dans les Pays de l’UE et en allemand, mais pas en Allemagne (si cela est déjà détenu par un concurrent en non-exclusivité), donc on va forcer la non-exclusivité sur l’Allemagne uniquement. Vous suivez toujours ?

Si on reprend l’exemple précédent :

Programmes : La soupe aux choux et L’aile ou la cuisse
Modes : Pay TV (Option : Exclusivité) et VOD (Option : Non-exclusivité)
Territoires : Pays de l’UE (Forcer la non-exclusivité sur l’Allemagne), Pays du Commonwealth (sans l’Inde ni le Bangladesh) et avec le Canada
Langues : français, anglais, allemand, espagnol, italien
Lorsqu’on linéarise les droits acquis pour l’Allemagne, dans tous les cas, il faut forcer la Non-exclusivité. Cela n’aura pas d’impact sur le mode VOD mais changera l’option pour le mode Pay TV et ceci dans toutes les langues.

Ce n’est pas terminé 🤯.

Les options forcées peuvent l’être sur les régions (Pays de l’UE par exemple), un pays ou une langue, il a donc fallu définir des règles de priorité d’application des options pour chaque cas possible et notamment lorsque des options contradictoires sont définies dans une combinaison.

Dans un catalogue d’environ 1000 programmes avec en moyenne 100 acquisitions et 500 cessions par an sur des périodes différentes couvrant plusieurs années, le nombre de lignes de droits acquis et cédés linéarisées possible dans une recherche est astronomique et notre perspective de boire une bière après la rédaction de ce script s’éloignait de plus en plus. 😕

De plus, une fois l’ensemble des règles de linéarisation des droits acquis et cédés, nous avons dû déterminer les règles de soustraction (ce qui fut finalement la partie la plus simple). Les droits cédés sont soustraits de la liste des droits acquis pour obtenir les droits disponibles, avec quelques subtilités tout de même : un droit cédé en exclusivité n’est plus disponible à la vente, un droit cédé en non-exclusivité reste disponible à la vente en non-exclusivité, un droit en holdback total n’est pas disponible à la vente et un droit en holdback partiel est indiqué à l’utilisateur.

La solution technique

Finalement, après des heures de réflexions collectives avec notre client pour comprendre le fonctionnement des droits acquis et cédés, et la manière dont il souhaitait que les droits soient linéarisés et la disponibilité calculée, il devenait évident que FileMaker, avec sa mémoire sur disque, ne pourrait pas gérer ce calcul dans un temps raisonnable pour les utilisateurs. Il nous fallait trouver un outil travaillant en mémoire vive. Nous avons décidé de tirer parti de l’intégration de FileMaker avec Javascript, ce dernier étant bien plus efficace pour ce type de traitements 💡.

Dans l’application de gestion des cessions de droits, FileMaker dispose des droits acquis et cédés, regroupés dans une table qui contient toutes les informations sur les options (type d’option, date de début de de fin d’application, lien vers les blocs de droits cédés ou acquis, lien vers le ou les programmes, pays, langues, modes…).

Lorsque que l’utilisateur saisit une recherche, il doit au minimum rechercher les disponibilités à une date donnée et peut ajouter des critères de recherche sur un programme, un pays, une langue… FileMaker va chercher les droits acquis et cédés répondant aux critères de recherche. La liste des droits acquis et cédés trouvée est extraite par FileMaker Data API, le résultat est injecté dans le code d’un web viewer sous forme de json. Le scripts JS réalise tout le travail de linéarisation en appliquant les règles de priorité de chaque option ainsi que la soustraction. Il renvoie à FileMaker via FileMaker.PerformScriptWithOption le résultat  contenant la liste des droits disponibles. FileMaker transforme ensuite ce résultat en enregistrements dans la base de données et n’a plus qu’à présenter les disponibilités à l’utilisateur dans une table de droits disponibles.

Les cas courants demandent seulement quelques secondes d’exécution. Les cas les plus complexes quelques dizaines de secondes. 💪

Une fois les scripts FileMaker et JS rédigés, tous les nœuds au cerveau dénoués, une validation du fonctionnement de ce moteur de recherche de droits disponibles et la rédaction d’une documentation utilisateur, nous avons finalement bien mérité une bière 🍻 !

Leslie Audigane & Karl Vossen

[vc_row bg_color=””][vc_column][vc_column_text]Avec la sortie de FileMaker 19, il devient plus aisé que jamais (c’était déjà très largement possible avant – j’ai eu l’occasion de montrer le “#hash trick” dans quelques conférences 😉 ) de faire communiquer FileMaker et Javascript. Notamment en exécutant du code Javascript dans un Web Viewer grâce au pas de script Exécuter Javascript dans un Web Viewer (FileMaker demande au Web Viewer d’exécuter un Javascript), et à la classe Javascript FileMaker.PerformScript( “Nom du script FileMaker”, “Paramètre facultatif” );

Toutefois, il manque à nos yeux quelques possibilités à cette dernière. Nous avons évoqué ici l’impossibilité de reprendre un éventuel script FileMaker qui serait en pause, mais il y a aussi l’absence de possibilité de différencier le script appelé (callback) en fonction du succès ou de l’échec. Cet article développe ce dernier point et apporte une solution.

La programmation asynchrone

Aucune bibliothèque (library) n’est nécessaire, mais j’ai dû écrire ma propre classe qui étend un peu FileMaker.PerformScript à FileMaker.PerformScript ( scriptName, parameter, successCallback, errorCallback ).

Un petit exemple pour faire une interaction bidirectionnelle entre FileMaker et Web Viewer. Il s’agit d’un système de callback et/ou de promesse, une façon dont le Javascript appelle souvent quelque chose de l’extérieur et selon que tout s’est bien passé (succès) ou mal (erreur), vous laissez quelque chose se produire dans votre application Javascript.

En fait, tout se résume donc à cela : “FileMaker, appelle ce script. Si tout s’est bien passé fais ceci, si tout s’est mal passé fais cela”.

Un exemple concret avec une intégration de calendrier : si l’utilisateur change le lieu d’un événement, il doit appeler FileMaker et l’enregistrer dans la base de données. Si cela n’a PAS réussi (par exemple, erreur d’écriture dans l’enregistrement), il faut annuler la transaction et remettre l’événement à son emplacement d’origine pour que l’utilisateur voie qu’il n’a pas été enregistré. C’est ce qu’on appelle la programmation asynchrone, et c’est quelque chose qu’il faut absolument comprendre si vous voulez continuer avec Javascript.

Donc, en dans le calendrier, voici ce qui se passe réellement :

1. L’action en Javascript est appelée, avec immédiatement les actions : que faire si ça va bien, que faire si ça va mal.
2. En interne, ces actions sont enregistrées, et Javascript appelle FileMaker.
3. Le code Javascript ne “tourne” plus, il n’y a plus de pause, nous avons effectivement atteint la fin du code.
4. FileMaker fait son travail et décide s’il veut dire au Web Viewer si cela s’est bien ou mal passé.
5. Le Web Viewer exécute les morceaux de code (enregistrés à l’étape 1).

En 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]Comme vous le verrez dans l’exemple, j’y ai mis une bibliothèque. Il m’a fallu beaucoup de sueur et de larmes pour que tout cela fonctionne. J’aimerais vous expliquer comment tout cela fonctionne, mais cela se résume à cela :

J’ai un peu élargi le FileMaker.performScript de sorte que vous pouvez maintenant inclure un callback de réussite et un callback d’erreur. Je l’enregistre, puis j’appelle le script FileMaker. J’ajuste un peu le paramètre, parce que j’ajoute un autre “id” et ensuite le paramètre tel que passé.
Voyez cela comme une sorte de “ligne téléphonique sur laquelle le FileMaker peut se rappeler”. Lorsque FileMaker appelle à nouveau le Web Viewer avec cette identification, le Web Viewer sait de quelle action il s’agit.

En fait, le Web Viewer dit à FileMaker : “Bonjour, je suis l’action 23 et je voudrais que tu conserve cet enregistrement”. Après, FileMaker dit au Web Viewer que l’action 23 s’est très bien passée !

Et voici un exemple, jouez avec et s’il y a des questions, n’hésitez pas à les poser ci-dessous ! C’est un concept assez difficile, mais une fois que vous avez maîtrisé la programmation asynchrone, vous désirerez en faire de même dans FileMaker 🙂

Promesses (promise)

Oh oui, pour les irréductibles : cela fonctionne aussi avec les promesses (sur Mac en tout cas, sur Windows pas sûr), donc vous pouvez faire ça aussi :[/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=”Télécharger l’exemple” url=”url:https%3A%2F%2Fwww.1-more-thing.com%2Fwp-content%2Fuploads%2F2020%2F06%2F1MT_javascriptCallBack.zip|title:T%C3%A9l%C3%A9charger%20l’exemple||” size=”big” align=”center” color=”color7″ text_color=”color4″][/vc_column][/vc_row]

One of the most important features of FileMaker 17 is the “current found set portal”. It makes it easy and fast to display the list of found records on the layout (Master/Detail).

Not only does it display them, but it allows to browse records with a single click, without having to write a script or add a button.

Besides, it reflects the sort order, which means you can now design a real Master/Detail view.

In this demo file, I’ll show you a few tips, among which:

• how to use the new Get ( ActiveRecordNumber ) function to highlight the current record in the portal
• use the found set portal to browse records, and a simple button bar to act as a sortable column header

But also, on a second layout called Detail / Master, you will see what happens if we inverse the perspective and use the current found set portal as the main item of the layout body. We can suddenly use ‘list views’ on a form layout. Benefits:

• avoid the annoying “No record message” of FileMaker list view when user clicks on the body
• edit the record in a form without leaving the list view (right pane)
• add an action / navigation bar on the whole list height (left pane)
• you can now adjust the window and control its height

There is a missing feature though: no sub summary layout parts.

Enjoy!

[vc_row color=”color7″ text_color=”color4″ global_atts=”yes” style=”padding-x: 5px; padding-top: 25px; align: center; ” bg_color=””][vc_column width=”1/4″][ish_icon icon=”icon-download” align=”center”][/vc_column][vc_column width=”3/4″][vc_column_text global_atts=”yes” css_class=”btn btn-primary”]Download a sample file here. 1MT_MasterDetail.zip

Please share if you liked this post.[/vc_column_text][/vc_column][/vc_row][vc_row bg_color=””][vc_column width=”1/1″][vc_row_inner][vc_column_inner width=”1/1″][/vc_column_inner][/vc_row_inner][/vc_column][/vc_row]

Notre approche en tant que développeurs d’applications sur mesure est d’apporter des solutions évolutives à nos clients.
Leurs besoins progressent, et ils souhaitent adapter rapidement leur outil à la réalité de leur métier et de leur structure.

Mises à part quelques petites adaptations mineures qui peuvent être “reportées” en direct dans le fichier de production, la plupart du temps, pour pouvoir mettre en oeuvre de nouvelles fonctionnalités, nous devions nous lancer dans une aventure parfois fastidieuse : le versioning.

Ceci est bientôt de l’histoire ancienne

Souvenez-vous de ce à quoi pouvait ressembler un versioning :

Table par table, nous importions les données dans la nouvelle version.
En préparation de cet import, il avait fallu régler les correspondances de rubriques entre la table source de production et la table destination du nouveau fichier. L’interface que FileMaker propose pour régler l’import est sans doute le plus indigeste des interfaces de la plateforme : difficulté de positionner les rubriques qui “glissent” de haut en bas, contrôle de correspondance uniquement visuel… cette étape de configuration peut s’avérer gourmande en temps de développement (dans cet article de juin 2010 sur la mise à jour des données, nous revenons sur la méthodologie que nous adoptions dans pareille situation).

Après avoir réglé la séquence d’import, nous devions penser à mettre à jour les éventuels compteurs basés sur des numéros de série.

D’autres couches de l’application contiennent également des données qui avaient pu évoluer et être modifiées par les utilisateurs au cours de leur usage du fichier à mettre à jour :

• la sécurité, dans laquelle des comptes ont pu être créés, modifiés, supprimés;
• les listes de valeurs personnalisées dont certaines ont pu être adaptées par des utilisateurs disposants des droits pour ce faire.

Vue la complexité et la lenteur du processus de versioning, nous options souvent pour procéder à ces manoeuvres la nuit, le week-end… prévoyant selon le nombre d’enregistrements à transférer de nombreuses heures où avancerait lentement la barre de progression de la fenêtre d’import.

Parfois nous n’avions pas le choix, suite à une corruption de fichier, nous devions repartir d’une clone sain et injecter les données de production, en espérant ne pas interrompre trop longtemps le travail en cours des utilisateurs bloqués.

Un gain de temps considérable

Avec la version 17 de FileMaker, ceci va changer radicalement ! Mais c’est en dehors de l’application qu’il faut aller chercher cette excitante nouveauté. En accompagnement de FileMaker 17 (dans le package de la Developer Subscription), nous trouvons un petit outil léger en ligne de commande…léger mais d’une puissance telle qu’il va nous permettre de revoir fondamentalement le rythme de mise à jour de nos applications.
(On peut s’interroger sur la raison d’une attente si longue dans l’histoire de la plateforme pour bénéficier d’un tel outil – Fabrice se souvient que ses demandes à FileMaker pour un système du genre remontent à plus de 10 ans, et les développeurs étaient nombreux à appuyer dans ce sens.)

Le DMT, data migration tool, est donc un outil en ligne de commande qui permet d’injecter en un temps record les couches de données d’un fichier source vers un clone de la même base, et ce avec un tout petit minimum de préparation de notre part.

Comme pour le iOS SDK, cet outil vient de le “package” qui accompagne la Developer Subscription (89€HT/an). (Un package qui devient encore plus attractif avec le nouveau modèle de licence…allez jeter un oeil)

Comment utiliser le DMT

(Attention, veuillez prendre connaissance de l’article relatif à un bug important de la version actuelle de l’outil) 

Le principe est simple :

1. Réalisez un clone de la base de données de destination. Précision importante : un clone est une copie du fichier, dénuée des parties données (enregistrements et données locales). Un clone est un clone tant que le fichier n’est pas ouvert. Dès son ouverture, FileMaker y écrit des paramètres locaux (langue, format de date,…) et il perd dès lors son caractère de clone.
2. en ligne de commande, appeler l’outil FMDataMigration, identifier la source, le clone et la destination

FMDataMigration -src_path <path> -clone_path <path> [<other options>]

Et c’est réglé ! … un journal affiche la progression, les résultats et erreurs éventuelles.

Un cas vécu : le temps de versionning d’un fichier comportant environ 40 tables et des millions d’enregistrements est passé de 3h00 à 10 minutes !!  Et c’est évidement sans compter le temps gagné à ne pas devoir paramétrer la séquence d’importation des données pour chacune des tables.

Au final, nous récupérons non seulement les enregistrements, mais aussi les comptes utilisateurs et les modifications dans les listes de valeurs personnalisées, ainsi que les polices de caractères intégrées au fichier.

Ce qui est transféré

(en fonction des options cfr infra) :

• les comptes utilisateurs
• les enregistrements
• les listes de valeurs personnalisées
• le numéro de série suivant des rubriques entrée automatique numéro de série
• les index
• les ID d’enregistrements
• les paramètres régionaux (formats de date, langue par défaut…)

Paramètres de la ligne de commande

(en gras les paramètres obligatoires)

-src_path :  le chemin du fichier source
-src_account : un compte accès intégral dans le fichier source (par défaut = Admin)
-src_pwd : le mot de passe du compte (par défaut = vide)
-src_key : l'éventuelle clé d'encryption du fichier source

-clone_path :  le chemin du fichier clone
-clone_account : un compte accès intégral dans le fichier clone (par défaut = Admin)
-clone_pwd : le mot de passe du compte (par défaut = vide)
-clone_key : l'éventuelle clé d'encryption du fichier clone

-target_path : le chemin du fichier de destination (par défaut = chemin de la source avec ajout de 'migrated' )
-ignore_valuelists : par défaut le DMT va recopier les valeurs des listes de valeurs personnalisées du fichier source. Ajoutez cette option pour ne garder que les valeurs du fichier clone
-ignore_accounts : par défaut le DMT va recopier les comptes du fichier source ainsi que la clé d'encryption. Ajoutez cette option pour ne garder que les comptes du fichier clone
-ignore_fonts : par défaut le DMT va recopier les polices du fichier source. Ajoutez cette option pour ne garder que les polices du fichier clone

-v : (verbose) mode avec log de progression qui décrit étape par étape ce que le DMT est en train de faire
-q : (quiet) mode silencieux
-force : permet d'écraser un fichier de destination déjà existant

Quelques remarques et points d’attention:

Comment se fait la correspondance ?

Pour la correspondance des tables et rubriques, FM Data Migration Tool procède d’abord par nom et ID identique, puis s’il ne trouve pas par nom identique, puis enfin par ID interne correspondant. Cela signifie par exemple que si vous renommez la rubrique RUBOld en RUBNew et puis que vous créez une rubrique nommée RUBOld, les données de la source contenue dans la rubrique RUBOld seront transférées dans la nouvelle rubrique RUBOld.

Des conteneurs

L’outil est prévu pour que le fichier résultat soit ensuite placé au même endroit qu’à l’origine. Ce point doit être pris en considération dans le cas de stockage externe de conteneurs : l’outil ne gère pas de déplacement ou de conversion de ces fichiers.

Clone tu resteras

Petit rappel : un  clone reste un clone tant qu’il n’a pas été ouvert !

Réparation du code ?

Le DMT n’est pas un outil de réparation. Si on a dans la source une donnée corrompue, elle le restera après versioning.

Attention plug-in

Lors du versioning, le DMT “réévalue” les valeurs des rubriques calculs stockées. Ceci peut poser un gros problème si ces calculs font appel à des fonctions issues de plug-in. Le DMT n’ayant pas accès aux plug-in, les résultats de ces calculs sera un ? dans tous les enregistrements. Il faudra par la suite évaluer à nouveau ces calculs en entrant dans la définition de la base de données et en éditant la formule de calcul (y ajouter un espace suffit).

Sécurité : login et mot de passe

Dans les instructions en ligne de commande, il faut passer en clair un login et mot de passe du fichier clone et du fichier source. Ce login et mot de passe peut être soit celui d’un compte disposant de l’accès intégral au fichier…dans ce cas, vous exposez aux regards indiscrets et laissez dans l’historique du terminal un mot de passe critique. Soit vous pouvez mentionner un compte et mot de passe qui est associé à un jeu de privilège qui dispose d’un privilège étendu dont le nom commence par “fmmigration”. Le nom du privilège étendu doit être rigoureusement le même dans le fichier source et dans le clone (sensible à la casse).  Le jeu de privilège ne doit disposer d’aucun droit particulier dans la base de données (ni lecture des données, ni accès aux modèles, scripts,…). C’est sans aucun doute la manière la plus sûre de gérer cet aspect sécurité lors de l’usage du DMT.
Dans l’exemple ci-dessous. Nous avons créé un compte “mig” dont le mot de passe est “123456”; ce compte est associé au jeu de privilège “Mig” qui n’a aucun droit de lecture, d’accès au modèle, scripts,..dans la base. Ce jeu de privilège dispose d’un privilège étendu dont le nom commence par fmmigration : “fmmigrationTest”.

La commande devient la suivante :

 ./FMDataMigration -src_path Old.fmp12 -src_account mig -src_pwd 123456  -clone_path New.fmp12 -clone_account mig -clone_pwd 123456

Pour disposer de l’outil Data Migration Tool

Comme pour le iOS SDK, cet outil vient de le “package” qui accompagne la Developer Subscription (89€HT/an). 

Enjoy !