Using the Data Viewer for Testing Data and Writing Functions

What is it?

The Data Viewer is a feature of FileMaker Advanced that gives you under-the-hood access to your data. You can certainly program in FileMaker Pro and not use FileMaker Advanced, but you will be missing certain—I must say critical—tools that, essentially can make your life a lot easier. We’ve all been there, learning FileMaker, getting excited about putting fields on layouts and seeing our data grow. But learning to use the tools that are at our fingertips is equally exciting.

As a developer, you want to be precise and use the right functions for the right calculation. FileMaker has a lot of functions that can be interpreted in different ways. Additionally, there are functions that sound similar, especially to a new developer.

The Data Viewer can be a great tool to test your pudding before serving it at the dinner table. In this article we’ll take a look at some things you can do with the Data Viewer. You can test your data while debugging a script, you can write and test calculations, and you can even set variables to use later. Data can be sorted by the column headers, if you hover over with your mouse you can expand the data that is in a variable, you can even retype and change the data in your variable and you can double-click on it to see it in its entirety

Testing Data

The first thing I think the Data Viewer is useful for is checking the results of your script steps when you’re debugging a script. Anyone who’s ever written a script knows that you should debug your script before running it live. Until you debug it—and see what it actually does—you cannot be sure it will achieve the desired result.

So run your script often—and always one more time—in the Debugger and have the Data Viewer open every time you debug a script. As you step through your script the Data Viewer updates the data so you can see the result of your script line by line. If you want to test with different data, you can modify the data in any of the variables and continue running your script. In addition to the variables, the Data Viewer will show you all the fields that are being touched by the script, with an indicator in the left column for what fields are used by the script step you are on in the script debugger.

Writing Functions

When writing calculations or creating custom functions, you should test them, as well. It’s so much easier to write them in the Data Viewer than in the Specify Calculation window or a text editor, because you can test with live data.

Imagine you have to get data tested but you don’t have the data yet in records. I usually make some string up, just like Lorem Ipsum is used on a web page to see how formatting and spacing will work.

Below is an example of how you can use the Data Viewer to your advantage when writing a function:

Timestamp ( GetAsDate ( Middle (Table::DateTime ; 6 ; 2 ) & "/" & Middle (Table::DateTime ; 9 ; 2 ) & "/" & Left (Table::DateTime ; 4 ) ) ; GetAsTime ( RightWords (Table::DateTime ; 1 ) ) )

Any calculation written as one long string is hard to decipher. When playing in the Data Viewer I recommend formatting your calculation with some white space around the lines so it becomes more legible and so you can read it easier yourself:

Timestamp (
GetAsDate ( Middle ( Table::DateTime ; 6 ; 2 ) & "/" &
Middle (Table::DateTime ; 9 ; 2 ) & "/" &
Left (Table::DateTime ; 4 ) ) ;
GetAsTime ( RightWords (Table::DateTime ; 1 )
)
)

Now when you create something like this, it’s best to put it in a Let statement and give it some test value right there and then in the Data Viewer. So now our calculation would look something like this:

Let (
[
dateTime = Table::DateTime
];
Timestamp (
GetAsDate ( Middle (dateTime; 6 ; 2 ) & "/" &
Middle (dateTime; 9 ; 2 ) & "/" &
Left (dateTime; 4 ) ) ;
GetAsTime ( RightWords (dateTime; 1 )
)
)
)

Notice how I swapped out the ‘Table::DateTime’ field with the variable ‘dateTime’ I defined in the Let statement. The advantage of this is that you have to define your data once, and FileMaker will just use it from there every time it needs is, removing some strain from the calculation engine.

The next step is to give it some test value:

Let (
[
dateTime = "1970-01-01 00:00:00 UTC"
];
Timestamp (
GetAsDate ( Middle (dateTime; 6 ; 2 ) & "/" &
Middle (dateTime; 9 ; 2 ) & "/" &
Left (dateTime; 4 ) ) ;
GetAsTime ( RightWords (dateTime; 1 )
)
)
)

Add this to your Data Viewer’s Watch section as a new function and see how it will generate you a result. You can test with different timestamps. Imagine how this can be even more useful when you’re writing more complex calculations.

Working with ExecuteSQL

One incredibly good tool we have in FileMaker—since version 12—is the ExecuteSQL function. It lets you query data in a Table Occurrence in FileMaker without having to put things in context; therefore, it can work from anywhere. Some of these ExecuteSQL Calculations can be quite cumbersome, so it’s best to test them in the Data Viewer.

The Sql.debug ( _executeSQL ) custom function from my Belgian friend, Andries Heylen, makes your life even easier by getting meaningful SQL responses to your malformed queries. Back in the day, when I still smoked, we shared a cigarette break at DevCon and he told me about his accidental discovery. By the way, if you’re going to DevCon for the first time or if you’ve never been and are considering attending, I highly recommend you read my colleague, Jeremy Brown’s post about DevCon: FileMaker DevCon: A First-Timer’s Guide to Beating the House.

So let’s say you have put this SQL code together:

"SELECT SUM(a."Qty_proj")
FROM "Projection _Entries" a
WHERE a."Item_code" = ? AND a."Monthcode" = ?"

And after you run it in the Data Viewer it gives you a question mark back, which tells you that you have a syntax error in your query. Instead of modifying it again and again and hoping next time it will not serve you with a question mark, you can wrap your code in the Sql.debug function and see what SQL actually complains about:

Sql.debug (
"SELECT SUM(a."Qty_proj")
FROM "Projection _Entries" a
WHERE a."Item_code" = ? AND a."Monthcode" = ?"
)

This will just show you what FileMaker WILL execute once you’ve hit “Evaluate Now”. If you want to see the magic, you’ll have to hit “Monitor”, though – which is a quirk of what makes the sql.debug custom function work. So now, it’s loud and clear that I am referencing the wrong table. This whole approach is crucial to me, because it’s so easy to miss a comma or misspell a table name. I recommend copying and pasting as much as you can from the FileMaker fields, so you can minimize human error.