Door 1 What is SQL?

Today we start with the first door of this year's MBS Advent calendar. This year, we have come up with a themed Advent calendar for you. Over the next 24 doors, you will learn how to work with SQL within a FileMaker database using MBS functions.

So let's start today with the question: What is SQL in general, and what can I use it for?

SQL stands for Structured Query Language and refers to a language that can be used to query, modify, create, and delete data in a relational database. SQL can be used to work with a wide variety of databases, including MySQL, MariaDB, and PostgreSQL. SQL can also be used in FileMaker. On the one hand, it allows you to access other databases (e.g., with the Execute SQL script step or with the SQL component in MBS), thus providing a connection between FileMaker and the external database and making communication at all possible. But you can also use SQL internally in FileMaker, for example, to make queries to the database. To do this, you can use either FileMaker's own ExecuteSQL function or the MBS functions from the FMSQL component. We would like to focus on this part in this Advent calendar.

Even though FileMaker already offers many built-in options for searching, evaluating, and linking data, SQL can be an extremely flexible addition to a FileMaker database. SQL allows you to access data in FileMaker directly and independently of the layout. This means that you can retrieve information spread across multiple tables without having to create additional relationships in the relationship graph or layouts. You can define complex queries that combine, filter, or group data from different tables, for example. For example, you can use a single SQL query to determine how many orders a customer has placed in the current year—something that would often only be possible in FileMaker itself with multiple scripts, loops, or auxiliary relationships.

When working with FileMaker's own function, we can send SELECT queries to the database, which we will examine in more detail in the next section. SELECT queries allow you to retrieve information, similar to a search query. However, instead of a selection of records, you receive text that separates the individual pieces of information with predefined delimiters. The MBS plugin has a total of 65 functions in the FMSQL component to provide you with additional support when working with SQL, so that you can, for example, create, update, or delete records and handle errors even easily. In the next 23 days, I would like to dive into this topic with you so that you can then take full advantage of all the possibilities SQL has to offer.