Lors de la sortie de FileMaker 14, nous avions déjà publié un article sur ce sujet, car nous avions un début de solution à l’éternel problème des fins de ligne (LF, CR, CRLF) dans les fichiers exportés par FileMaker.

Mais FileMaker 16 met un terme à cette éternité : le problème est désormais résolu de manière propre.

Voici donc une petite vidéo qui explore la fonction TextEncode

[Version française]

In this blog post, I will share a technique that Laurent Spielmann (@laurent_at_joes) and I developed together and that greatly simplifies imports. It uses ODBC import.

Have you never been bothered by inconsistent database structures between a data source and your own application?

Have you never cursed the SQL developer who uses as a unicity key a concatenation of several columns?

Have you never felt anxiety before renaming a field because it could break an import order?

Have you never lost your temper in front of a progress bar during a sync operation on a ESS table?

If you answered no to all of these, I’m jealous. This article is for the rest of us. 🙂

Some technical background for a start

FileMaker has had a great friend for a long time: ODBC.

ODBC is implemented in 3 very different manners in FileMaker. Let’s avoid confusion:

1. FileMaker can be defined as an ODBC data source. It is not our topic today but it’s interesting because a FileMaker source interrogated via ODBC will act as any other database. An ODBC driver is provided with FileMaker Pro and FileMaker Server.
2. FileMaker can access ODBC data in the context of ESS (External SQL Source). The purpose is here to interact with external data as if they were FileMaker data, through table occurrences, layouts, scripts…). Since FileMaker 9, we used to be able to play with mysql, Oracle and SQL Server. Since the release of FileMaker 15, Postgresql and DB2 have joined the game. Note that on Mac, a third party driver is required (Actual Technologies). Since the release of ESS in FileMaker 9, developers tend to use it for all interactions with SQL sources. And that is a very bad idea. I admit that the fact that you can interact with external data just as if they were FileMaker data is great, but the cost in terms of performance is huge. ESS performance is well below ODBC potential.
3. Finally, and that is often forgotten -and therefore our today topic— the capability to communicate with a SQL source by script.

What script steps are we talking about?

Two script steps allow interaction with ODBC sources:

1. READ: Import records.
2. WRITE: Execute SQL (I’m not talking about the ExecuteSQL calculation function, that can only read (SELECT), but of the Execute SQL script step, for good)

Note that it is not possible, at least to my knowledge, to read data from an external source without writing to a FileMaker table (import). One could expect that Execute SQL would return the result of a SELECT statement to a variable, but it doesn’t.
Tip: Execute SQL can also modify the structure of a database. (CREATE, DROP, ALTER…). Come back to this tip after reading this article. Could give you ideas.

Driver ? DSN ? what’s this?

The main reason why these features are somehow neglected is that they require a DSN (Data Source Name) to be installed at the operating system level, and sometimes, depending on the database, a specific driver.

A DSN is needed for the operating system to give applications access to an ODBC data source. ODBC is a standard that allows different databases to communicate using SQL. (Open DataBase Connectivity).

On the Mac, you have to setup a DSN using ODBC Manager, that used to be installed by default in /Applications/Utilities/ before Apple decided it was too good for you. Fortunately, unlike MagSafe connector and other great hardware and software features removed by Apple, ODBC Manager can still be downloaded here.

While it is true that setting up a DSN on each client machine is complex, it’s a snap to install it once on the server.

As a matter of fact, we’ve been able since FileMaker Server 9 to schedule scripts on server, and since FileMaker 13 to perform script on server from the client.

What’s more, and while Import Records [ ODBC data ] has always been server compatible, Execute SQL has become server compatible only with FileMaker Server 15. Enough to re-think some stuff!

To sum up: you can now set up a DSN only on the server and benefit from it on all clients, including Pro, Go, WebDirect and CWP.

However, the developer (that’s you) will also need to access the SQL sources in order to write scripts (namely to configure import orders). To address this you’ll simply have to set up the same DSN on your computer, paying attention to using exactly the same name. So you will be able to edit your scripts and the server can perform them.

We’re done with the technical setup. Now let’s focus on what’s interesting.

The plot

Here is a short summary of the situation we had to address. We have to import a massive amount of data stored in mysql.
Performance is a key here, and this excludes ESS immediately. By the way, we had really no reason to consider ESS: this technology is good at one thing only: present and manipulate external data as if they were FileMaker data. Nothing else. It’s really NOT designed to import or run synchronization operations.
So we go for ODBC imports, but refinements are to come later.

First challenge: unicity and performance

So here we are with the following script configuration:

As you can see, the Import records script steps is talking to a DSN with a query that we define as a variable for the sake of readability.

But among the numerous imports we have to make, some are of ‘update’ type (the third option checked in the import order dialog)

The problem is that the unicity criteria does not fit in a single column. To decide wether a record matches and must be updated, 3 columns had to be used as matching keys.

FileMaker allows this, but with dramatically poor performance. Since the number of records was huge, this simply wasn’t an option.

Imagine the query:

SELECT a, b, c, d FROM myTable

But although we want to import these 4 columns into 4 FileMaker fields: A, B, C, and D, we must update records if a, b and c are matching A, B, and C.

Let’s define the import order like:

but we know that performance won’t be acceptable.

One way —I’m serious here— would be to ask the SQL developer to update the view and add a column that would concatenate the 3 others. In our situation it would have been a reasonable option, but very often you have simply no control over the source structure.

Let’s see if we couldn’t have mysql do the work for us…

First, let’s create a FileMaker calculation field that will be a unique key on the destination side.

K is a calculated field such as

A & B & C

Now, let’s update the query like:

SELECT CONCAT (a, b, c) AS K, a, b, c, d FROM myTable

Some explanations:

• a 5th column is created on the fly (concatenation of a, b, and c).
• we move before the others (optional, that’s just to show them who’s the boss.)
• it’s renamed K, just because it looks nice (but you’ll see, that will give us ideas. Beauty IS important.)

By selecting the Matching names options, we end up with this:

and that is way faster!

Second challenge: naming

Wait! that’s interesting, isn’t it? OK, we solved the performance issue, but while doing so we really took control over the left side of the import order dialog (source).
And one of the great issues we face with import records in FileMaker is it’s fragility. The only way to maintain an import order, even if you create or remove fields is to choose Matching names, but then you’re exposed to the consequences of renaming.

As we just saw, we can control the name of the left side columns. Those who already used XSLT to import XML data already knew, but it’s worth mentioning.

In the above example, I simplified the column names in a, b, c, d and the field names in A, B, C, D, but as you probably expect the real world names were a bit more complex.

Imagine that the original request would be:

SELECT name_first, name_last, jobTitle, date_of_birth FROM PEOPLE

and that the target field names would be

firstName, surName, occupation, dob

we can write:

SELECT name_first AS firstName, name_last AS surName, jobTitle as occupation, date_of_birth as dob FROM PEOPLE

and with the concatenation:

SELECT CONCAT (name_first, name_last, jobTitle) AS K, name_first AS firstName, name_last AS surName, jobTitle as occupation, date_of_birth as dob FROM PEOPLE

Fantastic! we can now use the Matching names option!

Still we knew that the SQL developer might want to rename some columns, or even release new views to replace the old ones after some weeks (this is a real case, not fantasy)
So we wanted to build a system that would resist a change on the source side. Not that the change would be entirely automated, but we wanted to be able to switch to a new source in minutes, without coding. That’s where our work began.

The nice little trick

Wouldn’t it be nice if each FileMaker table was able to generate it’s own import query?

We saw that the left side of the import order could be managed on the fly. It is therefore up to the right side (the database structure) to contain the information.

One spot seemed nice for this: field comments.

Let’s create a small syntax that will:

• declare the left side column name. We’ll use a tag, “SOURCENAME:” followed by the source column name.
• allow to modify this name easily: we’ll simply have to change the column name that follows the tag.
• to comment out. If // is found before SOURCENAME:, the tag is ignored
• not interfere with other information you’d like to place in the comments. As you can see on the image, you can combine a source name and some other comments.

Then we need to create a custom function. (the code is available in this text file)

It might look a little complex at first glance but:

• a great part of the work is done by 2 other functions written by Agnès Barouh, CustomList and FilterList, that we renamed list.custom and list.filter. As a side note, Agnès now develops the Ti’Sac, that we sincerely recommend (it’s not simple politeness here, it’s a amazingly clever, unique, patented purse). Christmas is coming, so you should definitely take a look here.
• if it wasn’t a bit complex, you’d be disappointed.

In fact, this function code doesn’t matter. If for the above mentioned table we evaluate the following expression:

sql.query.import.map ( "" ; "contacts AS C" )

the empty parameter indicates “current table”. One can also write:

sql.query.import.map ( "people" ; "contacts AS C" )

the result is:

SELECT "C"."CIE" AS "company", "C"."familyname" AS "name" FROM "contacts" AS "C"

which is exactly the query we need to pass to the script step Import records to have consistent source and destination names.

Here is the same image again:

• Field ‘company’ will receive data from column ‘CIE’
• Field ‘excluded’ won’t receive data
• Field ‘inactive’ won’t either
• Field ‘name’ will receive ‘familyname’

And conflicts with SQL reserved words are avoided using quotes.

The second parameter, “contacts AS C”, could have been “contacts”, but the function supports table aliases. This will allow importing from joints in the future (currently not supported by the function)

Finally, this second parameter is optional, so you can inject SQL functions in the query:

sql.query.import.map ( "" ; "" )

returns:

SELECT "CIE" AS "company", "familyname" AS name

So if you need to do more complex things you can:

sql.query.import.map ( "" ; "" ) & ", CONCAT ( column1, column2 ) AS myField FROM contacts"

As you can see, this techniques opens up a lot of possibilities. Whether you’re importing from an external source or from the FileMaker database itself.

If you combine this with the fact that an import script step is able to create a new table, that Execute SQL is able to delete this table (DROP), that you can now duplicate a record set without being limited because a table cannot import to itself, etc, etc… the potential is huge!

Do not hesitate to leave a comment here, and now it’s up to you to explore!

Intro

Dans nos solutions FileMaker, en général du moins, nous ne soucions pas particulièrement de la taille des données textuelles, en revanche, nous accordons un intérêt certain pour la taille des données “multimédia” à intégrer dans les bases de données au sein des rubriques de type conteneur ou même directement placées sur les modèles.

Les fichiers Pdf que nous pouvons générer à partir de FileMaker occupent une place importante dans cette problématique liée à la taille des données, notamment sur les solutions “métier”, où l’envoi et la sauvegarde des documents administratifs, commerciaux, contractuels, légaux, etc., ont une importance capitale.

Aujourd’hui, nous disposons de nombreuses techniques et solutions, plus ou moins efficaces, pour traiter les fichiers Pdf générés par FileMaker afin de réduire leur taille : plugins, lignes de commande, applications tierces et services en ligne. Malheureusement, la plupart de ces solutions sont souvent assez délicates à intégrer dans un flux de travail quand d’autres s’avèrent tout simplement complexes à implémenter dans nos projets.

Dans cet article je vous propose d’étudier ensemble, par des simples tests empiriques, quels sont les éléments de nos modèles qui, à coup sûr, influencent la taille des fichiers Pdf générés par FileMaker et, par conséquent, quelles solutions adopter pour les optimiser au mieux de manière totalement native, sans recourir à une quelconque technologie annexe.

L’étude exposée ici concerne plutôt les documents administratifs et de gestion, tels que les devis, factures, bons de commande, bons de livraisons, notes de frais, contrats, licences, condition générales, etc., c’est-à-dire, tous les documents où le texte occupe une place prépondérante par rapport aux éventuelles images existantes (généralement des logos ou pictos).

Tests et Solutions

Pour commencer, je vous invite à télécharger le fichier « Optimisation-Pdf.fmp12 » qui se trouve à la fin de cet article. Il s’agit tout simplement d’une base FileMaker issue des ses solutions de démarrage (version 14), à savoir, la solution “Facturation”, qui dispose d’un modèle de facture que nous allons utiliser et modifier pour réaliser tous nos tests.

Tout ce que nous avons à faire, donc, c’est de travailler nos modèles d’impression, ou concevoir des modèles spécialement dédiés aux Pdf, de manière à les simplifier et à les alléger de tout ce qui pourrait impacter la taille des Pdf générés. Trois aspects sont particulièrement à considérer (par ordre croissant d’importance) :

1. Les images
2. La composition (la mise en page des modèles)
3. Les textes

1. Les images

Et oui, ça peut paraître bizarre (voire paradoxal), mais les images ce sont les éléments qui, finalement, posent le moins de problèmes lors de la création des fichiers Pdf avec FileMaker.

Ceci tout simplement parce que, hélas, FileMaker ne dispose d’aucune fonctionnalité native pour travailler directement sur les images, aussi bien lorsqu’elles sont intégrées dans des rubriques de type conteneur que lorsqu’elles sont placées sur les modèles.

Même si, selon mes observations, il semblerait que le moteur de fabrication des Pdf utilisé par FileMaker tente d’optimiser les images, les résultats dépendent évidemment de la nature des images déjà présentes, il convient donc de les traiter en amont, avec des logiciels dédiés, afin d’optimiser leurs poids avec différentes manipulations, comme par exemple :

• en les redimensionnant aux dimensions exactes requises sur les modèles ;
• en supprimant les espaces, marges et bords inutiles ;
• en réduisant le nombre de couleurs ;
• en les passant en niveaux de gris si la couleur n’est pas nécessaire ;
• en adoptant les bons formats de fichiers compatibles FileMaker : Jpeg, Png, Gif, etc. ;
• en utilisant les taux de compression qui offrent le meilleur compromis poids/qualité ;
• etc.

Pour illustrer cette optimisation, vous trouverez dans le fichier de test disponible à la fin de l’article, une icône en guise de logo placée dans les modèles d’impression. Le poids du fichier original de cette image, en couleurs, était de pratiquement 3 Ko (2 837 octets), après une optimisation dans un logiciel de traitement d’images, l’icône intégrée (en niveaux de gris) pesait moins de 1 Ko (879 octets).

Au final, ce qu’il faut retenir, c’est qu’une fois les images intégrées dans FileMaker, on ne pourra plus rien faire sur place, de manière native, pour réduire leurs poids respectifs afin d’optimiser nos Pdf, ce travail doit donc être fait en amont.

2. La composition

L’idée est d’optimiser la composition des nos modèles d’impression ou dédiés aux Pdf, avec comme but d’obtenir des mises en pages les plus simples possibles, qui nous permettront d’économiser encore quelques dizaines ou centaines d’octets.

Alors, disons-le tout de suite, ne vous attendez pas à réaliser des gains spectaculaires entre une composition “normale” et une mise en page optimisée, mais aucune économie étant à négliger, autant adopter les bonnes pratiques dès le début de chaque projet.

Évidemment, si vous avez déjà une multitude de modèles déjà réalisés dans vos solutions, la simplification de leur mise en page vous demandera de nombreuses heures de travail pour une optimisation assez minime de vos Pdf au final, je ne vous conseille donc pas de vous lancer dans de telles manipulations si peu rentables.

En revanche, pour tout nouveau projet, vous pouvez suivre les recommendations suivantes (sans ordre particulier d’importance) afin de soigner la mise en page de vos modèles d’impression et ainsi optimiser vos Pdf :

• préférez les rubriques de fusion aux simples rubriques lorsque c’est possible ;
• évitez autant que possible de multiplier les objets, simplifiez vos compositions ;
• évitez également les éléments superposés quand ce n’est pas nécessaire ;
• évitez les effets sur les objets quand ce n’est pas utile : ombres, contours pointillés, bords arrondis, dégradés, transparences, etc.
• tentez de privilégier les attributs de paragraphes pour gérer les alignements et espacements des textes dans les blocs ;
• lorsqu’il faut matérialiser des tableaux avec des colonnes de données, tentez d’utiliser la technique des tabulations pour séparer les données ;
• évitez les couleurs quand ce n’est pas nécessaire, préférez l’utilisation des niveaux de gris pour gérer la mise en valeurs des objets et des textes.

Pour avoir une idée de ce qu’on obtient avec ce type d’optimisation, je vous invite à afficher côte-à-côte, en mode modèle bien-sûr, les modèles “Imprimer/envoyer facturation” et “Pdf-4-Mono-Test” du fichier de test présent à la fin de cet article.

Encore une fois, j’insiste, les gains de poids obtenus avec ces mises en pages simplifiées sont très modestes et ne justifient nullement de reprendre tous vos modèles existants, utilisez plutôt ces techniques pour vos nouvelles réalisations.

3. Les textes

Voilà le chapitre le plus intéressant car c’est en manipulant les textes que nous allons obtenir les optimisations les plus importantes sur nos Pdf.

De nombreux aspects sont à considérer, comme le nombre de polices des caractères utilisés, les choix des ces polices des caractères, la présence des enrichissements typographiques (gras, italique, etc.) et le nombre de caractères différents affichés (ce qui est difficilement maîtrisable). Le principe d’optimisation est très simple (par ordre croissant d’importance) :

– moins il y a de caractères différents affichés,
– moins il y a d’enrichissements typographiques appliqués,
– moins il y a de polices de caractères utilisées et mieux elles sont choisies,
= plus l’optimisation sera importante et donc la taille du Pdf réduite.

Les conseils à suivre sont donc évidents :

• limiter autant que possible la quantité de caractère différents et l’utilisation des caractères hors alphabet classique (pas toujours possible, mais à tenir en compte au besoin) ;
• éviter les enrichissements typographiques quand ils ne sont pas vraiment nécessaires : gras, italique, souligné, petites majuscules, etc., préférez jouer avec la taille des textes ;
• éviter de multiplier les polices des caractères, une seule police de caractères peut souvent suffire à obtenir une mise en page claire et lisible ;
• bien choisir sa police des caractères : suivant la police utilisée, la taille du Pdf peut varier quasiment du simple au triple (imaginez avec plusieurs polices).

Concernant l’impact que peut avoir l’utilisation des caractères différents, voici un simple petit test, que vous pouvez retrouver dans l’archive “Pdf-Caracteres.zip” à la fin de l’article, composé de 5 fichiers Pdf dont voici le détail :

• Fichier “_vide.pdf” : Pdf vide (aucun texte, aucune police de caractères) = 5 Ko (5 110 octets) ;
• Fichier “a.pdf” : juste une lettre “a” avec la police “Arial” = 42 Ko (42 422 octets) ;
• Fichier “aaaaaa_26.pdf” : 26 lettres “a” avec la police “Arial” = 42 Ko (42 454 octets) ;
• Fichier “alphabet_26.pdf” : alphabet de 26 caractères minuscules avec la police “Arial” = 52 Ko (51 586 octets) ;
• Fichier “alphabet_ascii.pdf” : alphabet ASCII de 224 caractères avec la police “Arial” = 119 Ko (118 610 octets) ;

Ce test nous montre clairement que, pour chaque police de caractères utilisée, le nombre de caractères différents présents dans le modèle influence la taille finale des Pdf. Malheureusement, mis à part une bonne information auprès des utilisateurs, il n’y a pas vraiment de technique efficace pour limiter le nombre de caractères différents sans risquer de dénaturer les données.

Dans le fichier FileMaker de tests, vous trouverez, dans la zone de navigation en haut du modèle “Détails facturation”, un petit bouton-popover “PDF” qui vous permettra de générer des fichiers Pdf à partir de différents modèles, tous issus du modèle d’impression original, mais qui ont été modifiés afin de pouvoir visualiser la taille des Pdf produits en changeant un ou plusieurs aspects de cette optimisation des textes.

Deux options possibles :

1. Exemples : permet de générer des Pdf d’exemples, cela produit 7 fichiers Pdf pour illustrer l’impact important qu’ont les polices des caractères et le formatage des textes sur la taille des Pdf ;
2. Polices : permet de générer des Pdf selon une police de caractères choisie, cela produit 3 fichiers Pdf pour comparer leurs tailles respectives avec les Pdf analogues d’une autre police de caractères.

Le choix des polices de caractères de ces tests est volontairement limité à celles qui sont les plus utilisées et compatibles avec les plate-formes Mac et Pc. D’autre part, vous trouverez dans ce panneau popover un petit bouton d’aide “?” vous donnant accès à une description du nommage choisi pour les fichiers Pdf générés et leurs signification.

Pour que vous puissiez avoir une idée de la taille des différents Pdf générés par l’option “Polices”, voici un tableau comparatif qui présente assez bien les polices des caractères les plus légères à privilégier dans vos réalisations :

1. Avec = modèle PDF avec des textes qui ont des enrichissements typographiques (gras ou italique).
2. Sans = modèle PDF avec des textes qui n’ont aucun enrichissement typographiques (gras ou italique).
3. Test = modèle PDF de test avec composition simplifiée et textes sans aucun enrichissement typographiques.

Nous pouvons voir dans ce tableau que la police de caractères “Arial” est la plus “encombrante” dans tous les cas, alors que certaines polices, suivant l’application des formatages ou pas, produisent des Pdf extrêment légers avec des gains très intéressants, il convient donc de bien les choisir dans nos modèles pour une meilleure optimisation de nos Pdf.

Attention tout-de-même, ces valeurs sont rélatives aux polices présentes dans chaque système d’exploitation, dès lors, un modèle réalisé avec la police “Arial”, par exemple, peut produire des Pdf de tailles très différentes d’un ordinateur à un autre, il est donc important d’effectuer des tests lors du développement mais aussi, surtout, chez l’utilisateur final.

Conclusion

Nous avons vu que la taille des Pdf est directement liée aux contenus des nos modèles d’impression et à leur formatage, alors, bien qu’il soit difficile de tout maîtriser, si nous observons les trois préceptes suivants, nous allons pouvoir obtenir nativement des fichiers Pdf de taille très réduite, ce qui n’empêche nullement, au besoin, d’opérer d’autres optimisations avec des technologies annexes :

• Optimiser les images avant de les intégrer dans nos solutions ;
• Simplifier au maximum la mise en page de nos modèles ;
• Utiliser le minimum de polices de caractères et appliquer le minimum de formatages (idéalement : une seule police de caractères et aucun formatage typographique).

Voici le fichier FileMaker de tests à télécharger :  Optimisation-Pdf.fmp12

Voici l’archive avec les tests des caractères :  Pdf-Caracteres.zip

Merci pour votre lecture, toutes remarques, commentaires et questions sont les bienvenues.