Door 24 Insert or Update

Welcome to the last door of the Advent calendar. Today, we would like to take a look at a function in MBS that is particularly popular with users: FM.InsertOrUpdateRecord. This function means that we don't always have to decide whether a data record needs to be inserted if it doesn't already exist, or whether it already exists and just needs to be updated. The function makes this decision for you. It checks whether the entry already exists in the database. If so, it updates the data record; otherwise, it creates a new data record.

This is what the structure of the function looks like:

MBS( "FM.InsertOrUpdateRecord"; FileName; TableName; IDField; IDValue; FieldName...; FieldValue... )  

	Door 23 Dictionary

Welcome to door 23. We have already seen that we can turn an SQL result into JSON, XML, a matrix, CSV, or text. Now I would like to introduce you to another data structure: dictionaries.

These dictionaries are key-value pairs. Instead of addressing values via positions or sequences, you access the desired data directly via a meaningful key. This allows related information to be stored in a clear and structured manner. In addition, dictionaries are very fast at querying data, making them ideal for intermediate results. Today, we want to look at how we can fill such a dictionary with data using SQL. To do this, we have the functions Dictionary.AddSQLRecord and Dictionary.AddSQLRecords. Both functions are very similar in structure. First, both are passed the references to the dictionary. This is followed by a list of the names for the individual keys to be entered into the dictionary. This is followed by the SQL query we use to select the data for our dictionary. Next, we specify the file on which this SQL query is to be executed. If necessary, you can also pass any number of parameters to the function. We do not need these in our example. If both functions have the same parameter input and a similar description, what is the difference between the two? The Dictionary.AddSQLRecord function takes only the first entry from the SQL result set and stores it in the dictionary. Dictionary.AddSQLRecords, on the other hand, takes all records from the SQL result and enters the data into the dictionary.

	Door 22 Table statistics

Welcome to door 22. Today's door will be another short one, but no less importantly. Because today I would like to talk to you about the FM.TableStatistics function. This function provides you with table statistics for your tables.

In this function, we first specify the file name and then the table name. Our call then looks like this:

Set Variable [ $Statistics ; Value: MBS( "FM.TableStatistics"; Get(FileName); "Movie" ) ]
Show Custom Dialog [ "Table statistics from the tabl…" ; $Statistics ]

	Door 21 XML

Welcome to door 21. We have already seen in other doors that we can output the results of SQL queries not only as text but also as a matrix or JSON. Today, I would like to introduce you to another return format: XML.

Like JSON, XML is format that is often used for exchange between applications. Its form is reminiscent of HTML, because tags are also used here to structure the data. In XML, the data is structured, hierarchical, and clearly described.

If we want to output an SQL result as XML, we use the FM.SQL.XMLRecords function. First, we use FM.SQL.Execute to determine the data set that we want to output as XML. Only then we can output the data set with FM.SQL.XMLRecords. Here, we first specify the SQL reference in the function. This is followed by the root node name and the name of the tag for each individual record. Then we list the individual field names as a list. This completes the parameters that we must specify in any case.

	Door 20 Copying records to a new table with SQL

Welcome to door 20 of this advent calendar. We already saw in door 7 how to copy a part of a table. There, we read each field individually and then wrote it to a new table. But there is an easier way to do this. With the FM.InsertRecordQuery function. This function can transfer certain entries from one table to another.

MBS( "FM.InsertRecordQuery"; InsertFileName; InsertTableName; FieldNames; QueryFileName; 
   SQL Statement { ; Params... } )  

First, we specify the file name and table name of the table into which the data is to be inserted. This is followed by a list of the field names into which the data is to be written. Then comes the file name of the file on which the SQL query is to run, followed by the SQL query itself. If parameters are used in the SQL query, you can specify them afterwards.

	Door 19 CSV and TSV

Welcome to door 19. In previous doors, we have already seen some alternatives to pure text output. Today, I would like to show you another one, which is output as CSV.

A CSV (Comma-Separated Values) is a simple, text-based file format for storing tabular data. Each row corresponds to a data record, and the individual values are separated by a delimiter (usually a comma or semicolon). It is easily readable by both - humans and machines. We now want to output our data from the database as CSV.

The FM.SQL.CSV function is available for this purpose. In the parameters, we first define our SQL query. That's all we need for the function, but like many functions, we can also optionally specify a range in the SQL result we want to get back, allowing us to specify the first and last rows. In the same way, we can now limit the columns by specifying the first and last columns. If we want to deviate from the line break as the line separator and the semicolon as the column separator, we can also define this in the parameters. Last but not least, we can specify a flag that determines whether our values should all be returned as text. If we want this, we write 1 in the flags.

	Door 18 Update

Welcome to door 18 of our Advent calendar. Today, I would like to show you how to update data records.

The plugin offers two types of functions for this purpose. On the one hand, we have functions that update a single data record, and on the other hand, we have functions that update several data records at once. Let's start with the single records. Here we have the functions FM.UpdateRecord, FM.UpdateRecord2, FM.UpdateRecord3, FM.UpdateRecord4, and FM.UpdateRecord5. We'll come back to why there are so many update functions in a moment.

	Door 17 Compare tables

Welcome to today's door. Today, I would like to introduce a function that allows you to compare changes in tables. The FM.CompareTables function is available for this purpose. It compares two tables for their fields and records and checks whether data has been changed, added, or deleted. This function has several parameters, so let's take a look at them together.

MBS( "FM.CompareTables"; FileName1; TableName1; IDField1; FileName2; TableName2; IDField2 { ; Options } )  

	Door 16 Delete

Today we want to take a look at how to delete records. As already mentioned in door 3, we can also delete records using the MBS SQL functions. Today you will learn how this works.

For deleting, we have the functions FM.DeleteRecord and FM.DeleteRecords in the MBS FileMaker Plugin, which I would like to introduce to you. As the name suggests, the FM.DeleteRecord function is well suited for deleting single records from the database. This function works with unique fields. In the function, we first define the file in which we find the record to be deleted in the parameters, then the table name, and finally the field information that identifies the data record. We specify the field name in which we find the unique ID and then the unique ID itself of the record to be deleted. This is the information you must provide. You can also specify the data record with additional unique IDs. It is important that the fields that help us identify the data record not only contain unique values, but that this is also reflected in the database structure in the validation. If we set the name as a field in the Door7 table, even though the values are unique, an error will occur because it is not declared as unique in the database. So, if we want to delete the movie Gramlins from our database in the Door7 table, we use the PrimaryKey field and the primary key corresponding to the movie for identification.

	Door 15 Information about tables and base tables

Today, I would like to introduce you to a few functions that do not directly affect SQL queries, but can still be very useful. In order to perform SQL queries, we need to know the database structure of our database. Which tables do we have and what are the names of the fields in the corresponding tables? I would now like to find this out together with you. Our plugins have included the relevant functions for years.

	Door 14 SQL and JSON

We have already established that we can output the results of our SQL query not only as text, but also as a matrix, for example. In today's door, I would like to show you another way of representing the results of your query. Today, we want to receive our results as a JSON return. JSON (JavaScript Object Notation) is an easy-to-understand, text-based data format that is used to store and exchange information in a structured way. It consists of simple key-value pairs and lists is both easy to read for humans and easy to process for computers. JSON is often used to transfer data between applications, web services, or databases.

Three different functions are available for working with SQL in FileMaker. Let's start with the FM.SQL.JSONRecords function. This function can output the data we determine in an SQL query as JSON. To do this, we first specify the reference to our SQL query in the function and then specify the names for the keys in the JSON. In our case, we kept the same names as the field names in the database. That's all it takes to get a result. In this example, we limited the SQL query to the fields Name, Director, Launch, and Generated_Sales.

	Door 13 The last SQL

In Door 8, we learned about error handling and were able to use the FM.ExecuteSQL.LastError and FM.ExecuteSQL.LastErrorMessage functions to read the error code and error description provided by the last SQL query with FileMaker. We can display such an error in a dialog box, for example. Often, you may not even know which SQL query was involved. But how can we find out? The FM.ExecuteSQL.LastSQL function helps us with this. It provides you with the last SQL command as run by the plugin.

	Door 12 UNION

Today, I would like to explain the term UNION in SQL in detail. With UNION, we can combine results from multiple tables. To demonstrate this, we will create another table, because in a film, there are not only leading roles, as we have in the Rols2 table, but also supporting roles. These also have a role name and an actor, and then we assign the film to them as well.

We now want to combine these two tables in the result. We want to know which actors appeared in the individual films, regardless of whether they played a leading or supporting role. To do this, we formulate our UNION statement. This consists of two SELECT-FROM parts that are then connected to each other. In the first SELECT FROM part, we want the fields Name, Actor, and Movie from the Rols2 table. In the second part, we want the fields Name, Actor, and Movie from the table Supporting_roles. We now combine these two queries by writing the term UNION between them. Since we now get all actors back, this can become quite confusing. For this reason, we want to sort the whole thing according to the individual films. The query then looks like this:

SELECT Name, Actor, Movie 
FROM Rols2 
UNION 
SELECT Name, Actor, Movie 
FROM Supporting_roles 
ORDER BY Movie

	Door 11 Adding records

Today, we want to take a look at how we can use SQL to insert new records into our table. This feature is incredibly cool because you can insert a record into your FM table without having to switch layouts. Personally, this is my favorite use of SQL in FileMaker databases. Let's take a closer look at the appropriate SQL command:

INSERT INTO table_name (field1, field2, field3, ...)

VALUES (value1, value2, value3, …)

	Door 10 Minimum and Maximum

Welcome to Door 10. Today, I would like to talk to you about the FM.SQL.Max and FM.SQL.Min functions. As their names suggest, these two functions determine the lowest or highest value within a result set. As we did yesterday, we first send an SQL query to the database using the FM.SQL.Execute function. We can then apply FM.SQL.Max and FM.SQL.Min. In our example, we want to determine the values of the highest and lowest generated sales. Our script then looks like this:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Generated_Sales FROM Movie" ) ]
Set Variable [ $max ; Value: MBS("FM.SQL.Max"; $SQLRes; 0; ""; "") ]
Set Variable [ $min ; Value: MBS("FM.SQL.Min"; $SQLRes; 0; ""; "") ]
Show Custom Dialog [ "Minimum and Maximum generated …" ;
   "Min: " & $min & "¶Max: " & $max ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

	Door 9 Totals and averages

Today, I would like to introduce two functions that you can use to evaluate the data in your results even further: FM.SQL.Sum and FM.SQL.Avg. The FM.SQL.Sum function calculates the total from a column in our results. Let's try this out right away and calculate the total number of screenings for all films. To do this, we have the following code:

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Generated_Sales FROM Movie" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Sum"; $SQLRes; 0; ""; "") ]
Show Custom Dialog [ "Sum of generated sales" ; $r ]

	Door 8 Error handling

In Door 2, we saw that FileMaker itself has a function called ExecuteSQLe that can output error messages when errors occur in the process. If we have an error in the SQL query with the MBS functions, then instead of a result or a reference number, we receive an error message from the function that went wrong.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName);
   "SELECT Nme, Launch FROM Movie WHERE Director=? OR Launch=? AND NOT Director=?";
   "-" ;  "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ]

	Door 7 Precise entries and their types

Welcome to door 7. We still have some homework from yesterday to discuss. The task was to solve this query with a cross join:

SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor 
FROM Movies2 
JOIN Rols2 ON Movies2.PrimaryKey = Rols2.Movie

Such a cross join may look like this:

# CROSS JOIN WITH WHERE
Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Rols2.Movie, Movies2.Name, Movies2.Director, Movies2.Launch, Rols2.Actor 
   FROM Rols2 CROSS JOIN Movies2 WHERE Rols2.Movie=Movies2.PrimaryKey" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes; ""; ""; ""; ""; "¶"; "-") ]
Show Custom Dialog [ "Result" ; $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

	Door 6 Joins

Today, I would like to introduce you to an important topic in SQL: Joins.

In FileMaker, we have relationships between the tables we work on, allowing us to relate data records to each other. We do not have these direct, fixed connections between two tables in SQL. Here, we have to create the relationship in the query, and we do that with Joins. There are different types of Joins, which we will now learn about using small examples so that we can use them later in your actual database. To do this, we will use a small section of our Movies table, which contains 5 movies for the example. We will simplify the primary key as a sequential number, as this is of course different in real life.

	Door 5 Count your field and rows

Yesterday, we looked at the FM.SQL.Execute and FM.SQL.Text functions. In the parameters of the FM.SQL.Text function, we saw that we can limit our result set during output. But how large is our result set actually?

Let's find out together today. We have two functions that are essential for this: FM.SQL.RowCount and FM.SQL.FieldCount. With these two functions, we can count the rows or columns of our result.

	Door 4 FM.SQL.Execute and life in the matrix

Yesterday, we learned about an SQL function from MBS. Just like FileMaker's own function, it returned text that we could then work with. This time, we want to take a look at the FM.SQL.Execute function. Instead of returning text output, this function creates a reference. A reference is a reference to the working memory in which the result of the function is temporarily stored. This allows you to use the result returned by the SQL query with other cool MBS functions, which you will learn about later in this calendar. The advantage of having a reference instead of text is that the text does not have to be reloaded into the working memory every time we want to work with the result. The result with the reference is simply stored in the working memory, which saves us a lot of time when dealing with large amounts of data. This also gives us the option of running through the result (in another door) or selecting a different output type. We will output the result as text as well as a matrix. The FM.SQL.Text function is available for output as text. Let's take a look at such a query on our database. Here, we want to search for all movies made before the year 2000. In the FM.SQL.Execute function, we first specify the database on which we want to perform our operations.If we leave this parameter blank, we do not limit the query to one database. Instead, we keep the option open for multiple databases. In our case, it is clear which one we want, and we specify it. Then the SQL statement follows. The result of this function, our reference, is then stored in our variable $SQLRes. We then use this reference as the first parameter in our next function, FM.SQL.Text. Then we can determine the range of data to be displayed. We have parameters for the first and last row and the first and last column. For now it is sufficient to leave the parameters empty, because then the entire result set will be displayed. Two further parameters then stand for the row separator and field separator.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName); 
   "SELECT Name, Launch FROM Movie WHERE Launch<2000" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.Text"; $SQLRes;"";"";"";"";"¶¶";"| - |") ]

	Door 3 FM.ExecuteFileSQL

Today is the third day. Yesterday, we learned what we can do with FileMaker's internal functions. Today, we want to look at an MBS function that is very similar in structure: the FM.ExecuteFileSQL function.

Let's take a look at our query for all movies directed by Chris Columbus, plus movies released in 2000 that were not directed by Greg Beeman. We remember that this was the query:

SELECT Name, Launch 
FROM Movie 
WHERE Director = 'Chris Columbus' OR Launch=2000 AND NOT Director='Greg Beeman'

We now want to execute this query in the MBS function FM.ExecuteFileSQL.

	Door 2 SQL with FileMaker's own functions

Before we take a look at what MBS can do with SQL, let's take a look at FileMaker's own functions and get started with the SQL language. In today's example, we are working with a table listing Christmas movies. It is called Movie and has the following fields: Name, Director, Year and Generated_Sales. The first queries will be run on this table in a moment.

	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?