ISO FileMaker Magazine

When embarking on your journey with FileMaker, you encounter the wonderfully visual tool known as the Relationship Graph, which grants you the ability to visually comprehend the intricate interconnections within a complex solution. In fact, the relationships used to link table occurrences in FileMaker are fundamentally akin to those in SQL.

FileMaker essentially offers a visual representation of what would otherwise be a textual definition of a SQL statement. The key distinction lies in the fact that SQL operates within the realm of text and only executes when required. Conversely, the Relationship Graph in FileMaker is persistent and is continually assessed by FileMaker. Consequently, comprehending the implications of specific relationships in your solution becomes vital as your solution expands. Relationships that appear ostensibly simple, serving utility purposes within your user interface, might have more profound ramifications than you initially realize. Hence, it is imperative to comprehend and effectively employ all of your available tools.

One of these indispensable tools is FileMaker's full SQL implementation. In this video, I delve into the advanced aspects of using SQL within FileMaker. If you've constructed your FileMaker solution exclusively using native FileMaker features, you might unknowingly incur a big cost if you haven't invested time in learning FileMaker's SQL capabilities.

Your FileMaker database is obviously designed to store data. FileMaker also provides the tools for precisely extracting that data as needed. When and how you extract the data is based on your needs. Sometimes, you need the data for an external system or API. Maybe it's needed for an export or you need the data specifically for a given visualization of that data.

In a recent video, I showed some Sankey Diagrams and how cool they are as visualization tools. One of keys to presenting a great visualization is being able to extract the data you need to present. This data may also change as the user navigates through the chart or your user interface.

So, in short, we need to be familiar with all the various ways in which we can collect our data out of the database. Fortunately, there is a somewhat small collection of methods, but knowing how each of them work and when you might use one over the other is a matter of experience and practice. In this video, I cover the possible variations and you get to choose which method you want to use and when. The ultimate method is something you'll find towards the end of the video.

Desde la versión 12 de FileMaker tenemos la posibilidad de ejecutar una secuencia de consulta SQL dentro de nuestra propia base de datos. Esto nos permite obtener datos de otras tablas de la solución desde cualquier contexto y sin tener que crear, por tanto, relaciones adicionales en nuestro gráfico de relaciones.
Aprende con este vídeo lo esencial para ponerte a trabajar con la función ExecuteSQL.

While FileMaker's own Layout Mode provides excellent tools for formatting data for display and output, it isn't always the most convenient for certain types of output. A prime example is a pivot table. Or, the output of data which is normally columnar formatted into a horizontal format.

When attempting to do this with native FileMaker functionality, it quickly becomes a bit of a hassle, typically including more relationships than should be otherwise necessary.

There are solutions which can make use of repeating horizontal fields, but these typically have a hard limit and the width of the data does not dictate the largest column width. If you work creatively with HTML/CSS and the other available features within FileMaker, you can realize a more flexible solution for horizontal data display and output!

The new world of FileMaker development has been opened up due to the ExecuteSQL function. The days of many different extra table occurences should be behind us.

Yes, there will still be the occasional user interface related table occurrence which is still necessary. However, your advantages with using ExecuteSQL should dictate that it be leveraged to its full extent when possible - especially in an increasingly mobile computing world.

The real trick for some developers is advancing what they know and letting go of older knowledge. As an example, I'm periodically faced with FileMaker developers who remember their development days when working with FileMaker versions 3 through 6. I've seen FileMaker 10/11/12 solutions, new solutions in fact, which were developed with many more files than is necessary.

This same thing applies to ExecuteSQL. In order to take advantage of the newer technology (which, in this case, is quite old, but still valid, technology) you have to learn new things.

Hopefully, that's exactly what this video will do for you. If you've not yet taken the step to learning how SQL within FileMaker can benefit your own solution, then absorb as much as you can from this video!

I've also included some links on the article page to various SQL resources.