Gathering Data for FileMaker Charting, Part 1

Every day it seems, on the FileMaker Community, someone asks about how to work with FileMaker Charting. This question usually comes when I’m walking a dog, so I have to stand there on the corner and try to answer on my phone while my pup does his thing.

Setting up a chart using the chart object can be quite confounding until the day it isn’t. And once it is easy to understand you’ll never forget how to use it. It’s like riding a bike or something else that you never forget. While this might not be a ‘boss-level’ technique, it is still worth getting to know.

So we are going to focus our attention on the chart object so that you can create a chart in relatively short amount of time. In this post, we’ll take a look at the foundation of the chart object: the data and how to gather it. In the past, there’s been some great documentation about it. But I’d like to zero in on the data gathering only. The rest you can read in the white paper.You can download the demo (below) and review the video.

Understand the FileMaker Charting Object

FileMaker chart objects will render a returned-delimited list of data in a visual way. That’s all you need to know. If you can pass a list to the x-axis and a matching one to the y-Axis (as applicable), then you can display data on a chart. It is really that simple.

These are valid for the FileMaker Charting object.

These are valid for the chart.

There are some caveats:

  • The data in the x- and y-axis must match exactly. If you have four colors in a list in the x-axis, you must have four values in the y-axis with corresponding values.
    You can use an empty value but it must be in the correct spot in the list. I would recommend actually using a 0 instead of an empty value.

Here’s an example:

A chart of some data. What do you see missing?

Your list must match exactly:

X-axis: “Jan¶Feb¶Mar¶Apr”
Y-axis: “7¶4¶¶9”

Or this:

X-axis: “Jan¶Feb¶Mar¶Apr”
Y-axis: “7¶4¶0¶9″

These are correct because there are four values in the list. Each value in the y-axis corresponds to a value in the x-axis.

This would be incorrect:

X-axis: “Jan¶Feb¶Mar¶Apr”
Y-axis: “7¶4¶9”

There is a missing value, the sales amount for March. In this case, the 9 would apply to March and April would be missing a value.

I must clarify that most of the charts in the object require an x- and y-axis, but not all. The pie chart requires “Category Labels” and “Slice Data”, but they’re basically the same thing.

Making a list/Checking it Twice

Now we just have to collect the data into a list, and that is done in a myriad of ways. Let’s take a look at them by first reviewing the three data sources of the FileMaker Charting object. In the Data Source tab, there are three options we can use.  Let’s examine each one.

The Data Sources

The Data Sources of FileMaker Charting

And let’s examine it within the context of a table called SummaryData.

The summary data we wil chart.

The summary data we will chart.

This table holds the total sales for different product types ( Apples, Bananas, Grapes, Peaches, Pears) for each month of a year (2010-2017). We’ll use this data as much as we can throughout the examples.

Current Found Set

Individual Record Data

Current Found Set: Individual Records

Current Found Set: Individual Records

It is often the case that we want to build a chart to display some data from the found set. For example, if we wish to display the total sales for a particular fruit for each month, we can use this data source option.
This option (as shown above) will display on the chart all of the values of of a field for the x-axis and y-axis in the sort order of the records.

A chart displaying the records in the found set

A chart displaying the records in the found set

This chart shows the list view of the found set. I’ve placed the chart in the header part of the layout, but I don’t have to have the fields on the layout. This could be a simple form view with just the chart and buttons / fields with global storage for scripted finds.

This current found set option will respect the sort of the found set. If I change the sort from a monthNumber field (not displayed) to the Month field, the chart would update once the sort is finished.

And to address an earlier point, if one of these summary records had zero sales for a month, I would fill that sales field with a big fat 0 so it the value ‘shows up’ on the chart and the remaining values line up with their respective months.

Summarized Groups of Records

Current Found Set: Summarized Records

Current Found Set: Summarized Records

We can also visualize the data as we would do on a subsummary report.
Here’s a traditional subsummary report. This one has the body part removed and is just showing the sub summary sections.

A typical Sub Summary set up

A typical Sub Summary set up

Within each sub summary part, I can place a chart. The chart summarizes the data in the group just below it, exactly as the sub summary part does.

Here’s the chart in the header, and is set up to include the Year in the x-axis, and a summary field (total of sales) in the y-axis. (the picture below shows a graphical bug in the chart set up in High Sierra. Arg!)

A chart in the header

A chart in the header

In the Year sub summary part, another graph is placed, and it summarizes the each type’s sales for that year.

A chart placed in the sub summary part

A chart placed in the sub summary part

The current found set data source is a flexible way to pass a return-delimited list of data to the chart object. The options allow us to choose what to send to the chart: fields in actual values or summary information, as a sub-summary report shows. You can get a lot of mileage with this option, so consider it carefully.

How to Collect the Data

Since this chart displays the current found set, we simply have to find and sort the records for our liking. That’s it.

In the next post, we’ll look at the other two data sources of the FileMaker Charting object: Related Records and Current Record (Delimited Data).


Download the Demo

Like a Boss: FileMaker JSON Parsing with Generator

We all know that one of the biggest updates to FileMaker was the inclusion of native FileMaker JSON parsing. And now that we have that, we can, with much less effort, parse JSON we receive from an API into fields. And we can reduce the effort, even more, using our Generator file, a free tool that ‘generates FileMaker code’.

When I first joined Geist Interactive, I had little experience with Generator. I’d only opened the file a few times but didn’t understand its use. I closed it and went back to hand-writing the code I’d need to parse a JSON object using FileMaker 16’s native JSON functions.

FileMaker JSON Parsing: The Catalyst

Someone in the FileMaker Community asked the question about how to do FileMaker JSON Parsing and so I decided to give this tool a try on a Saturday night. Turns out Generator is a pretty great tool for working with JSON among other things.

The primary question the author of the post had was about drilling down into a JSON object, getting to the 5th element in an array, which itself is in an object, which is another object. That’s a very distinct and long path. Well, Generator can parse JSON easily. Here’s how.

  • It can read a JSON object, even a complex one, and create a map of the entire object.
  • Once the map has been created, Generator can link the map up to a file and table (your custom app) and actually create the fields needed for each mapped key in the JSON.
  • And once the field has been created, Generator can then create the script steps needed to 1) pull the mapped keys’ values out of the object; and 2) set the mapped fields with the correct data.

Reread that last point. Generator will literally do 80% of the work for you. It will remove all the tedious steps we would have to do without this tool. Would you like to save 80% of your time developing this use case? I thought so.

Let’s take a look at each step in picture form. You can also watch the video where we walk through the process step-by-step.

By the way, you need to have the latest version of the Base Elements plugin installed before proceeding.

Generator creates a map of the object

In my experiment, I used‘s API to gather the 10-day forecast of the city in which I live, Denver. I received that response after manually constructing the URL and pasting that into my browser. Here’s a sample of the response.

  "response": {
  "features": {
  "forecast10day": 1
 "txt_forecast": {
 "date":"12:04 PM MST",
 "forecastday": [
 "fcttext":"Partly cloudy. High 66F. Winds NW at 5 to 10 mph.",
 "fcttext_metric":"A mix of clouds and sun. High 18C. Winds WNW at 10 to 15 km/h.",

To start the process to parse the JSON, I took the response and copied it to my clipboard, then moved over to Generator and pressed “New Map”. The tool took the JSON object in my clipboard and created this map.

FileMaker JSON Parsing - JSON Field Map

The map created by my JSON object

This object had a few nested arrays. Generator detected those, and it allowed me to make a map for each nest. That was pretty cool.

FileMaker JSON Parsing - The nested array's Map

The nested array’s Map

Generator Maps the JSON path to fields in my file

The goal was to parse the JSON and get the data from some or all of the key/value pairs into fields, and Generator provides the tools to achieve this goal.

To start, I had to choose a file and table into which these fields will be placed. Generator can map these JSON paths to any open file. I chose my “ForecastFile” and the tables for the forecast and the details.

Choose file

Choosing the open file and a table in that file

Now that the map is created and the link to an open file and table is set, Generator will actually create fields in the linked file for each JSON path row you set. It will do any of the following:

  1. Create fields for ALL the JSON Path rows, using the JSON path key as the field names
  2. Create fields for the JSON path rows you choose, ignoring the others, using the JSON path key as the field names
  3. Create a specific custom-named field for a JSON Path row.

Here’s a map I created for the details portion of the object and the fields.

FileMaker JSON Parsing - Mapped Fields

Mapped Fields

These actions are pretty amazing. Until I started using Generator, I didn’t know a FileMaker script could create fields in a table. I mean, how cool is that? I opened my Manage Databases in my Forecast File and there were the fields in the table!

Generator Creates the script steps to parse and set the fields

This next step can be very tedious. First variables are created using JSONGetElement() to find the exact value of a JSON path. Then Set Field script steps have to be created to set the fields with the values from the variables. This is a mind-numbing task fraught with possible errors.

Generator does the tedious work for you. It will literally generate script steps to do both of these things. One simple button created the following for me:

FileMaker JSON Parsing - Created Script Steps

Generator created these script steps

FileMaker JSON Parsing: The remaining 20%

Generator takes care of much of the work of FileMaker JSON parsing and setting fields. But there’s always some final tasks to take care of. In my example above, the script turned out to be a subscript, and I had to pass the script the JSON object as a script parameter. Since the details records where children of a parent record, I also had to write script steps that grabbed the parent ID and set it to the foreign key field. Finally, in my example I had to set up a loop that stepped through all of the array elements of the daily forecast data.

But these final things are unique to my custom app. Though I might like it, Generator can’t do ALL the work for me. I still have to put a few thoughts into it.

A Time-Saving Tool

Generator is an essential tool for FileMaker developers. It works very easily to create the FileMaker JSON Parsing code and that is just scratching the surface of this tool’s capability. We are always looking for ways to save time, and Generator does that.

I am a convert. Even though I became a decent FileMaker developer by hand coding, I will use this tool all the time to parse JSON, to create the fields, and create script steps, as we saw above. I encourage you to give it a try: test out the free tool, use my demo and video as a place to start. You’ll be impressed as I was.

Download the Demo

Native FileMaker: More than Meets the Eye

I’ve only been around the FileMaker world since FileMaker 9, where I stumbled into it at the school where I was a teacher. So I’m relatively new, though have got a respectable number of versions under my belt. In that time, I’ve seen native FileMaker grow. It has evolved from a database tool to a platform upon which we can develop and access highly-complex custom apps.

Each release of FileMaker brings more features, more tools we have inside the application to develop our own custom apps. And we are grateful for it. We can now use JSON natively. We’ve been able to connect to SQL systems for many versions, and we have cool layout transitions for iOS. We have been able to create custom web pages with CWP technology. With native FileMaker, we can reach out to a web service and retrieve data. The list of native FileMaker possibilities has grown.

But there have been questions about what constitutes native FileMaker. I am guessing because the platform is much different than in FileMaker .fp3, .fp5, of .fp7 days, people worry that they have to work with stuff that’s not native FileMaker.

So let’s discuss what constitutes native FileMaker.

What is Native FileMaker?

Native FileMaker is simply what you can do with FileMaker that requires no additional install of other apps or tools. Additionally, native FileMaker is anything we can do inside of the platform using the tools, objects, functions, script steps, and schema to solve a use case from our client.

Native FileMaker, by the above definition, then includes the following:
  • Using the JSON syntax to collect data into an object
  • Using the web viewer to build a list view or complex chart using JavaScript
  • Connecting to a weather api to get the latest weather forecast
  • Using a virtual list of data from many different tables into one table
  • Creating a web page using Custom Web Publishing that shows data from a FileMaker custom app.
  • Creating a SQL query using ExecuteSQL which collects data from an unrelated table in our app.

And much, much more . . .

I have presented these to folks and have gotten back the challenge that these are not native FileMaker.  I respect the thoughts of those folks and so I spent some time thinking about how their thoughts differ than mine. And I think it is because there’s a concept of “Idiomatic FileMaker”.

Idiomatic FileMaker

Idiomatic FileMaker includes, it seems, that which we think of first when we think of FileMaker: Scripts, fields, schema, layouts, rectangles, text boxes, etc. That’s what we first learned about as we began our journey. This definition of FileMaker is all well and good but it severely limits our ability to do much with the application. Idiomatic FileMaker hasn’t changed a whole lot since FileMaker 7, and if we stay thinking of FileMaker in these terms, we risk being left behind in the custom-app development world.

Expanding Native FileMaker

But “native FileMaker” has changed. The engineers at the Wedge (the nickname given to FileMaker’s headquarters–a wedge-shaped building) have added so much more to the platform. There is almost no limit to what can be done. (I once pointed out we can’t do 3D drawings in FileMaker. I was corrected. ) And we are glad they have done so. We are glad we can now natively parse JSON with no plugin. It is awesome we can connect to web services and get back data and easily work with it. It is amazing that we can create a list of records using data from all over our custom app without having to build lots of relationships or calculated fields while de-normalizing data. Instead, we can create a virtual list which is native FileMaker, but may not be idiomatic.

Keep Moving Forward

We should take advantage of native FileMaker for our clients. We should leverage everything the platform has as our clients require it. Let’s say our client wants a list view that has columns that sort and a filtering mechanism and even expandable rows, we should seriously consider using a web viewer integration of a DataGrid. If our client wants to be able to send items up to, we have to explore data APIs and Insert from URL. If the client wants to see the weather forecast in their app, we should turn to parsing JSON from a web service that FileMaker has reached out to for the latest information. With open arms, we should embrace the virtually-limitless capability rather than shunting much of it to the side.

The rest of the software development world has already embraced technologies that expand their app. We, as FileMaker developers, designing the best possible custom app for our clients or ourselves, should do the same. FileMaker is not a small-time system anymore (not sure it ever has been small-time). Let’s use native FileMaker to its fullest.

Geist Interactive at FileMaker DevCon 2018


We love FileMaker DevCon. We get to hang with our peeps, and talk about our favorite thing, the awesome power of the FileMaker Platform. That’s why we are very excited to announce that Geist Interactive is a Diamond Sponsor of the 2018 FileMaker DevCon in Dallas, Tx  Aug 6 – 9, 2018. Our team will be there in force, delivering presentations, hanging out in our booth, or leveling up our skills. We hope to see you there.

Training and Sessions

This year three of the team members will be presenting in sessions throughout the week. Here is a list of the topics we bring to DevCon. And be sure to check out the full schedule.

Todd Geist

 Modular FileMaker 2.0

Modular FileMaker is a guideline for writing re-usable code that can be shared between developers or different projects. The web-site was launched more than 4 years ago, and has proved to be very popular. There are over 60 modules published on the site to date. Times have changed. JSON and Card Windows add some of the much needed native features we lacked back in 2013. It’s time for an update. We’ll look at the new updated guidelines and examine several sample modules. We’ll also introduce everyone to how they can contribute to this important free community resource.

Microsoft Office Addons Integration

FileMaker Server and Microsoft Office are a powerful combination. Not only does FileMaker Server OAuth support Active Directory, the FileMaker Data API, provides a way to connect custom MS Office Addons directly to FileMaker data. MS Office Addons are built with HTML and JavaScript, and are embedded directly inside the MS Office suite. In this session, we’ll construct and Outlook Addon that works on Mac, Windows, Web, iOS, and Android, and connects to a FileMaker database to allow viewing and editing data related to the currently displayed email.

Lance Brandenberg

Testable FileMaker Custom Apps

With the release of FileMaker 16 and native JSON functions, it’s easier than ever to create testable FileMaker apps. We will walk through a simple example of what testing is, and how it can improve your solutions. Most developers have had situations where they need to alter some existing logic, and are concerned that they might alter something with unforeseen consequences. Wouldn’t it be nice if you could ensure that your changes didn’t break existing code? Welcome to FileMaker Testing.

Jeremy Brown

JavaScript for FileMaker Developers

JavaScript, the easy-to-read and most widely used language is the only programming language that is natively supported in all parts of the FileMaker platform. The language brings more functionality and speed to our custom apps. JavaScript is very much worth learning. In this course, we will learn JavaScript starting with our vast knowledge of FileMaker. We will focus on scope, variables, and functions, connecting those concepts to what we know already. By the end of the morning, you’ll have many small apps and have a better understanding of JavaScript. Jeremy loves this stuff and is excited to share about it.

This session is a half-day training session that is an additional cost.

FileMaker DevCon

Geist Interactive Team at our San Diego Office

Geist Interactive Booth

In the exhibit hall, we will have a large booth where team members will gladly say hi, offer advice, and talk about our services and products. The larger-than-life Dave Ramsey, author of FMPerception and DamageDetectoR will give entertaining and informative impromptu talks about the FileMaker platform and FMPerception.

FileMaker DevCon Out and About

The entire team will be there for the entire week, so find one of us and say hi. We’ll have the “Geist Interactive” swag, so we will be easy to identify. We want to meet everyone and talk FileMaker! Come to our booth or find us around the exhibit hall or in the sessions or by the pool, and we’ll happily chat about the platform we love.

Like a Boss: Let us Discuss the FileMaker Let Function

In this series, “Learn FileMaker Like A Boss“, we’re exploring those techniques, mindsets, and tools that make us a better developer. I’m starting the series with topics based on my own discoveries during my FileMaker formative years. In this post, we will take a look at the FileMaker Let function.

I consider the day I started using the FileMaker Let function a huge milestone in my development. Its use opened new development doors for me and made me a better developer. I produced cleaner code: better organized and easier to read. Though a small, innocent-looking function, Let is a powerful technique for developers. So let’s discuss it.

FileMaker Let: A Multi-Purpose Function

The Let function has a lot of uses. It allows you to:

  • Store a value into a variable (be it a function, a field’s value, or something hard-coded in)
  • Break complex calculations into smaller steps
  • Assign multiple variables to values for this function, for the entire script, or for the file in general.
  • Return the result of a calculation using the variables declared inside.

These purposes give us a chance to make code that is cleaner and allows us to debug a complex calculation easier.

Let’s Start at the Very Beginning

Here’s an example of a Let statement.

A simple but complete FileMaker Let format.

A simple but complete Let format.

The top of the function, in between the square brackets [] is the declaration area, and the section after is the calculation area. Whatever is declared and calculated in the declaration area can be in turn calculated and returned. In this example, I’m declaring the variables _a and _b and assigning them to 1 and 2 respectively. In the calculated area I’m adding these two together, and my result is returned.

The Syntax

The syntax is important and there are some rules around it:

  • You don’t need the [] unless you plan on assigning multiple variables. I always place the brackets in the statement to be consistent. I’ve a shortcut key to which I’ve assigned this structure, and the brackets are always there.
  • The last variable declared in the declaration area cannot have a semi-colon after it. Notice in the picture, the variable _end has no semi colon. This actually kept tripping me up until I decided to always end my declaration variable with an _end variable.
  • Something needs to be in the calculation area, even if it is “” or a 1.

Let functions can be used anywhere there is a calculation dialog. And they are useful everywhere. They can be simple, like shown above, or very complex.

In fact, the more complex a calculation needs to be, the more useful a let function will prove in two ways:

  • A variable can be a smaller part of a calculation
  • A variable can get the value from a field or function that is meant used many times throughout.

Consider this example

A company has an invoice system. It contains three tables (for simplicity sake): Parts, Invoices and Invoice line items. As invoice lines are entered, some parts may have a discount on them. So that needs to get applied to the total cost.

But sometimes a part has an extra sale going on. The business logic says we should apply the larger of the two discounts to the total cost.

Here are the fields:

Sample fields

Sample fields

Without a let statement, the calculation to determine the total cost would be something like:

A typical complex calculation. Correct results. Bad formatting

A typical complex calculation. Correct results. Bad formatting.

It’s a mess and hard to read. It produces the correct result ($37.50), but is worthless to my future self. I don’t know what’s happening here. Note. There is another way to do this calculation. For this post, I chose an extreme calculation.

Here’s the same calculation using the FileMaker Let function:

A well-formatted FileMaker Let statement

A well-formatted Let statement

Nicer looking, huh?

Let’s look at the individual parts of the function.

In the first part of the Let declaration area, I’m creating some variables and assigning them values from the current record and will use these throughout the rest of the statement. I’m doing this simply because “_q” is shorter and easier to read than “InvoiceLineItems::Quantity”. As I put together the calculation, I can use “_q” anywhere the value from that field is needed. I have 5 variables that each have a value from a field.

The next part of the Let declaration area performs simple math using the variables above. In this example, I am first finding _discToUse: which discount is more will be used. That’s simple. Then I find the total cost sans any discount in _totalCost. Next, I’m figuring out the discount’s multiplier. Finally I’m finding the _result.

Notice each step here is a simple calculation, and the next one relies on some variables above. This makes the let statement invaluable. Since each calculation part is in a variable, I can view each part (or many parts) by returning those variables when using the data viewer.

All the work is done in the cascading variables, and so it leaves only one simple calculation to perform, the Round (), in the calculated area.

This is much more simple to read. Sure it’s longer, but it is easy to read. And that’s a great help to my future self.


Each value and small calculation is stored in a variable. As I debug it in the Data Viewer, I can return one or all of the variables listed above with a simple extra statement. I might do this:

Returning the variables' values to ensure all is good.

Returning the variables’ values to ensure all is good.

I’ve commented out the final result, the Round function, and instead returned a List of my small calculations variables. I can now see how they are calculated.

One time is a Charm

Look at this calculation, which returns my correct age.

A calculation with many Get(CurrentDate) functions

A calculation with many Get(CurrentDate) functions

Notice anything? (Look at the highlighted text) Besides the lack of formatting, the Get(CurrentDate) function is used ten times. That means each time FileMaker comes to that function it has to evaluate it. It basically is calculating the current date ten times. That is unnecessary.

Instead we’d use the Let function to save the current date in a variable. _curr = Get(CurrentDate). That’s one time and now we can use the variable throughout the rest of the calculation. It might save milliseconds, but it still saves time.

Other Thoughts


When putting this together for a script or for a calculated field, I always use the data viewer to generate the calculation. That way I can test every part of it before committing it to the script or field calculation.

How Low Will You Go?

You can get very far into the weeds in using the FileMaker Let function; each calculation can be broken down further and further. There’s no problem with that. But you can decide at any time to combine calculations into one variable declaration. It is up to you, but remember to think about your future self. What would he or she like you to do now?

What’s in a Name?

There’s nothing special about the variables that I chose except for two things:

  1. I always start mine with an underscore “_”. It’s just a convention I use. Some folks start with “~” and some do not include a prefix.
  2. The names are important. Since one of the points of the let statement is to use “_q” instead of “InvoiceLineItems::Quantity” in multiple places, I want to make sure the variable means something. I might, in this example, change “_q” to “_quan” to remind myself it is the value in the Quantity field.

Doing More

You can also assign a local variable ($var) or a global variable ($$var, which is actually global to this file only) in the Let statement’s declaration area. This is used for highly specialized reasons, ones that you’re not likely to come across very often. In my daily development I never do this. When I’m debugging a script, I might want to temporarily change the value of a local variable in order to see different logic performed.

The FileMaker Let Function

The FileMaker Let function is powerful and is an essential tool for all FileMaker developers. The function is essential for making complex calculations easy to debug in the now and to review in the future.

Like a Boss: Exploring all of the FileMaker Data Viewer

In this series, “Use FileMaker Like A Boss“, we’re exploring those techniques, mindsets, and tools that make us a better developer. I’m starting the series with topics based on my own discoveries during my FileMaker formative years. In this post, we will take a look at the FileMaker Data Viewer.

As a developer who stumbled into FileMaker, I always had a copy of FileMaker Pro Advanced, but the tools that were included largely went unused. The data viewer, when I finally found it, proved to be something that I can’t live without. The amount of information presented there was amazing. As I look back, I’m surprised I could do anything in FileMaker without the data viewer.

The FileMaker Data Viewer Up Close

The FileMaker Data Viewer has quite a bit of functionality packed into the small window. It is a place to view values of variables and fields and expressions. When I need to test a function out or work on my ExecuteSQL() syntax, I use the data viewer. I’d hazard to say it’s the most commonly used window in our tools. It certainly is for me.

There are two tabs: Current and Watch. The current tab, as it says right there, shows the variables and fields being used in the current script. Seems simple enough, but there’s a lot of power in this statement. We’ll get into those in a bit.

The Watch tab is a place to “Monitor the values of fields, variables and expressions.” Again, simple on the surface, but this space allows for some great complex work.

What’s happening now

The Current tab shows the value of all fields and declared variables in the current script. Here you can see all the fields that you need for this script and their value if applicable. Such usefulness!

The Current Tab of the FileMaker Data Viewer

The Current Tab of the FileMaker Data Viewer


FileMaker Data Viewer Facts

There’s much going on in the Current tab:

  • Fields that are not part of the current context of the script (the underlying Table Occurrence of the current layout) have a value of “<Unrelated Table>”. That’s okay.
  • If you’re using the script step Set Field by Name, you will not see that field in the list.
  • The default sort is the Names column, but you can sort by others as needed.
    • Variables are included as they’re declared, so these may be out of order until you specifically click on the Names column.
  • Fields are always at the top, and variables are sorted to the bottom with global variables above local variables.
  • A field or a variable can be added to the Watch tab via the button with that name. We’ll take a look at this in a bit.
  • Global variables are shown here as well.

More About Variables

Variables are shown in the Current tab, whether they are the local kind ($var is available only for the current script) or the global kind ($$var is available for the entire file). These are listed below the fields in the data viewer sort order. A variable gets added to the Current Tab only after a script step in the current script declares it. If the script debugger enters a subscript, the local variables for the parent script are no longer available. They will disappear from the data viewer list until the debugger returns to the parent script. This is important. If I declare $Id in the parent script, it is NOT automatically available to a subscript. It would be available if I passed its value to the subscript and then re-declared it there.

Why the FileMaker Data Viewer is Useful

Many developers set their scripts up to perform actions on a blank layout. This layout contains no fields or objects save maybe a “Processing this record.” piece of text. Since there are no fields on this layout, so I need to use the data viewer to view the fields and their values. If, for example, I create a new record on a blank layout, after each Set Field [] script step, I want to make sure the fields in the underlying table are getting filled as I would expect. I don’t have to look at the layout itself. Instead, I keep my eye on the Data Viewer Current tab.

Another useful feature is the ability to change the value of a variable, be it a local one (as the script is running) or a global one (any time). Let’s say the script declares $y to have a value of 275. That’s nice, but as I debug, I want to test another value. So, after it has been declared, I can click on the variable in the data viewer and change its value. After clicking “OK”, the value gets saved back to the variable, and that value will be used in the rest of the script.

The value of a declared variable can be updated in the data viewer.

The value of a declared variable can be updated in the data viewer.

Final Thoughts About the Current Tab

  • Fields are automatically sorted since they all begin with their table name.
  • Variables are also sorted by name by type. However, in a long list of fields and variables, it can get tricky to find a certain variable. So adopt a naming convention that will put like variables next to each other, something like $idProperty and $idCorporation, etc. Some folks use dot notation: $id.Property and $id.Corporation.

The Watch Tab

This tab is probably the most used by FileMaker developers.

The Watch Tab of the FileMaker Data Viewer

The Watch Tab of the FileMaker Data Viewer

There’s a lot one can do here. You can:

  • View developer-friendly data about the file (#1 in the picture above).
  • Test out a function or a long calculation there (#2 in the picture above)
  • Watch specific field or variable values in the currently-running script (#3 in in the picture above)
  • Add variables and values that effect the logic of the script
  • Change variable values that result in the logic of the script changing

Let’s examine each one in turn.

Test out a function

As I work to develop a script or calculated field, I can test out what I think will be the syntax in the data viewer’s Watch tab. I can add a new calculation with the + button and work away. If I’m in the correct context, if I’m on a layout that contains the table and the fields I am working with, those fields and related ones) are available to the data viewer. So I can work something out.

Watch a specific field or value values

As a script runs, the data viewer Current tab fills up with fields and variables. I can move one ore more of them over to the Watch tab and hold them there to view them separately. Once a field or variable is in the watch tab, it will stay there until you remove it (or FileMaker crashes).

Add Variables

I can even add a variable to this tab and use it in the currently-running script. Here’s a scenario: A script is supposed to pick up an optional script parameter with the value of “Contacts”. However, I run the script from the Script Workspace. The $param variable doesn’t get the script parameter, so I can manually set it:

Let (
 $param = “Contacts”;

This variable will remain “Contacts” throughout the script, just as I need it to. The 1 in this calculated section allows the Let function to work correctly. It is only the variable’s value I care about.

Change a Variable’s Value

Similar to adding a variable, I can change the value of the variable in the calling script. This is a bit tricky, but it does work. Here’s how: As a script runs, the script sets the value of $param to “Students” which came from the button’s optional script parameter. But I want to test the logic of the “Contacts” section, so I can use the above function to change the $param from “Students” to “Contacts”. As long as I press the button “Refresh Values” this value will override what was initially set in the script.

Be careful with this. What you have set here will override the script’s declaration even if you didn’t intend for it to happen. It is best to use this feature sparingly and to remove the variable as soon as possible. This issue also happens when you declare a variable, as described above. So be careful!

A final few words about the Watch Tab

  • The Watch Tab’s values are lost when FileMaker has a hard crash. The tab will be blank when reopening FileMaker.
  • The watch tab is not file specific, so you’ll see many fields or functions that have nothing to do with what you’re working on right now. As long as you don’t mind it, keep them. Like a sock drawer or closet, it is always good to clear them out one in awhile.

The FileMaker Data Viewer is essential to a developer’s process. I can’t recall the last time I didn’t have it open. I’ve grown in my use of this tool in the past few years. I strongly recommend you take time and explore all it has to offer.

Like a Boss: Using the Script Debugger to its Full Potential

In this series, “Learn FileMaker Like A Boss“, we’re exploring those techniques, mindsets, and tools that make us a better developer. I’m starting the series with topics based on my own discoveries during my formative FileMaker years. In this post, we will take a look at the FileMaker Script Debugger.

The FileMaker Script Debugger (found in the Tools menu of FileMaker Pro Advanced ) is a developer’s essential tool for testing a script to see if it works as described in the use case. We can watch a script step-by-step as it runs. We can control it precisely, running through the whole script or just parts of it as we test it out.

The FileMaker Script Debugger

The FileMaker Script Debugger contains a lot of useful tools

The many buttons and parts to this debugger are shown in the picture, and FileMaker’s help has a complete description of each.

Narrow Our View

What we are going to do is focus on using the buttons labeled as 4, 5, 6, and 7. Though FileMaker’s help talks about these, I’m going to put it into practical terms how these can be used. So as I go thru this description, refer back to this diagram.

The focus of this blog post

A set of buttons that are often underused.

All You Can Do

Once the FileMaker Script Debugger is activated and holds the script in its window, you have very fine control over the progress of the script.
We can:
  • Perform each step in sequence
  • Perform many steps at once
  • Perform an entire subscript without having to view that script’s steps
  • Skip over a certain part of the script
  • Back up

Let’s discuss each one of these in detail. The video that accompanies this post will show this clearly as well.

Perform each step in sequence

Button 5, called “Step into” performs each executable step in sequence. Clicking that button (or its shortcut F6) performs the step the Active indicator is on ( label 10) and pauses at the next step. If this button is clicked on a subscript, that script’s steps will display in the debugger and the active indicator will be at the first executable step in this subscript. This button is very handy to watch every step in every script being called. I tend to use this one more.

Perform many steps at once

Button 6, called “Step Out” simply performs all the steps in the current script until it reaches a breakpoint (label 11) or the end of the script. This could be in the parent script or in a subscript. If in a subscript and there are no breakpoints, the action will return to the next line of the parent script

Perform an entire subscript without having to view the steps

Button 4 is one I fail to use often, but am using it more. “Step Over” does roughly the same as button 5: it performs each script step in sequence, and it performs an entire subscript up to a break-point. Clicking this button, we will not see the subscript’s steps, up until a break-point in the subscript.

Skip over a certain part of the script or Back up

This action and the next one are done by the same button, button 7, called “Set Next Step”. This is an amazing command that I always forget about. A former colleague, Agnes Riley, reminded me of it. This button allows you to pick the next step to be performed. You highlight the step you wish to go to and press this button. The active indicator will go that step but will not perform it.

How it is used

With this button, you can do the following:
  1. Go back to the beginning of the script.
  2. Skip an entire section of the script
  3. Go into some logic that is not true in the normal course of the script.

This action requires some thought and care. In FileMaker, context is king, so a script step needs the right context to be able to perform the expected action. And it might not make sense to skip an entire part of the script, but if it can be done safely, then this button helps with that. But this is extremely useful to test and retest a part of the script. I’m finding this more and more useful.

The Script Debugger In the Real World

In a recent demo, I wrote a script to show an explanation card window depending on the script parameter passed into it. It is a simple script, but it can illustrate my purposes well.
A script to debug

Real-life Uses

Backup and Start Over

I start to test this script, and so I press button 5, “Step Into” from the Script Workspace. I notice that the debugger isn’t going into any of the If statements, and realize it is because I forgot to run this from the button which passes in the parameter. Rather than stopping the script, I can add the $param variable in the data viewer (see next post), setting the value to “part1” and then go back to line 11. I highlight that line and then press “Set Next Step” (button 7). This takes the active indicator back to line 11 and allows me to run through and finish the script.

Go inside

Next, I finish testing the logic inside the IF statement at line 15. I get to line 27 and, before I stop this script, I test line 19. Even though $param = “part1” I can use the “Set Next Step” button to go inside the lF statement at line 18. So I highlight line 19 and press “Set Next Step”.

Skip a Subscript

Again I run the script , debugging it from the button with the “Solution” parameter so that line 21 will be true. It opens the card window on line 22. I next press “Step Over” (button 4) and the subscript is fully performed. That is useful because I don’t need to test that subscript.

Test A Subscript

I decide to test the subscript on line 23. I debug the script with the “solution” as the parameter and then, on line 23, press “Step Into”. This opens up the subscript in the debugger (I can see the names of the two scripts in the call stack, label 13). I view this subscript and decide it doesn’t need to be stepped thru line by line, so I press “Step Out” (button 6). This fully-performs this subscript and takes me back to the parent script.

 Using FileMaker Script Debugger Completely

I personally am using these buttons of the FileMaker Script Debugger more and more every day. I find them extremely valuable in testing. Their functions give me complete control over a script’s run during testing. I can’t imagine testing a complex script with out the power of the FileMaker Script Debugger.

Learn FileMaker Like a Boss

Many of us FileMaker developers stumbled into the platform. We discovered it one day on our computer or were given a copy of it. We found FileMaker to be useful to rapidly develop a custom app for our personal or business needs, and so we invested time into learning it. Our evenings and weekends were spent on such places as,, the FileMaker Community, and other places where we could begin to understand how to use the program.

Along the way we picked up techniques that may or may not be the best technique for a given situation. We also may not have become aware of all the tools a developer has to better develop, test, and analyze.

If that is you, stay tuned. In this series of posts, we will explore what it means to learn FileMaker like a boss as we develop the techniques and flesh out mindsets we all should have.

My Stumbling Story . . .

I am a former teacher, teaching for 15 years, in grades 2 through 7. About year 11, I stumbled into FileMaker in a school that used the application simply to print paychecks for students. Though the administrator was the only one to use it, I became fascinated with the app and begged to help out with the development. I did the usual things described above and developed a pretty good system for my school: for the teachers, administrators, and secretarial staff. It expanded to three schools and 100 people using it.

A few years into my development, I stumbled upon parts of the platform that I had never used. I started to learn FileMaker more like a boss, and now those tools and concepts are essential to daily work. I look back on the very clear events and wonder how did I go so long without using, for example, the Script Debugger (I really did spend about 2 years NOT using that thing)

The years have paid off, and I feel like gained my footing in my development. I’ve worked hard, have struggled with learning techniques, and have learned from the community and some of the best (and possibly award-winning) developers. Everyday I regain more of my footing.

Let’s Learn FileMaker Like a Boss

  • learn great techniques quicker
  • understand the essential concepts of FileMaker
  • use all the tools available for the entire platform
Learn FileMaker

These goals are here because I discovered these items ‘late’ in my development learning. We are going to explore how to become a FileMaker Boss! in this series. We’ll start with three items I discovered for the first time well into my development career. We will discuss such things as the Script Debugger, the Data Viewer, the Developer tools and other tools.  Finally, we will explore such concepts as relationships and transactions. We will describe techniques such as related value lists or data visualization. And all the while, we will be a FileMaker Boss!

The Topics

We will cover many topics, and those will be listed here. Check back for a new topic. The first three topics come directly from my own learning back in my beginner days. Those three are:

We will look at these in the next blog posts.


See yah soon as we Learn FileMaker Like a Boss.

FileMaker Drag and Drop: Handling Script Triggers and Validation


In a typical day, a FileMaker user is interested in storing information in a custom app and then reporting out on that data in many different ways. A record, at the very heart of FileMaker, accomplishes this need. Here, data is entered and stored, and from this object, information is retrieved. One way data is entered is through FileMaker Drag and Drop, and therein lies a problem.

The Life Cycle

A record has a life cycle. It is created, edited, and committed to the file. Sometimes it is deleted. This cycle happens over and over. A record may go through many edits and commits in its life.

FileMaker drag and drop

A record’s life cycle

A primary job of FileMaker developers is to control that record life cycle. We set up strict controls about when a record can be created, edited, and deleted. We build logic to ensure a record has the correct type of information, and we work to ensure that the record is committed with integrity.

Like a vampire or a walker, however, there’s a user experience piece of FileMaker that is completely outside this life cycle and disrupts how a record is manipulated and then committed. That is the Drag and Drop.

FileMaker Drag and Drop: the Gotchas

FileMaker drag and drop is a great feature for fast data entry. This feature is commonly used for container fields and PDFs or pictures, but it can also be used to drag text data to a field. And that action is the disruption. When dragging data from another source into FileMaker, a user is almost completely subverting the record’s carefully structured life cycle, and thus the unexpected can happen.

Here’s a possible scenario: The user drags a text block from a website or email. She wants to save the text of an article or the current price of an item. The user simply highlights the text on the web page and clicks on it. Holding the mouse down, she drags the text over to the open empty and inactive field on the layout. Finally, she lets the mouse go and the text fills the field.

To the user, all is good, but under the hood, all sorts of unexpected things are happening:

The record is never opened. Or is it?

Normally when I type in a field, the record opens. FileMaker holds this record open for all edits. This doesn’t happen in a FileMaker drag and drop scenario. The data viewer, when watching the function Get(RecordOpenState), does not update from zero. Practically it may have opened, but the problem is that we cannot get to the open state of the record.

The record is never committed.

Well, I take that back a bit. Yes. It is committed because the data is stored in the field and record. Like the open state, we cannot get to the moment before the record is committed.

The modification count goes up

The result of the function Get ( RecordModificationCount) in the data viewer, increments by one each time.

Object script triggers do not run at all or properly

Mislav Kos and Wim Decorte at Soliant Consulting have a great rundown of all the script triggers and when they happen. The script trigger sequence, however it breaks down in a drag/drop scenario:

  • onObjectModify does run, but it runs after the record has already been committed (or the record has been closed).
  • The rest of the triggers do not run at all, no matter if they were meant to be a pre- or a post- trigger.

These facts present many problems for the developer. We are not able to control the life cycle of the record. We don’t get to capture when a record is open or before it is committed. Our lack of control over these means we cannot rely on script triggers to check field data for validation or for anything else we might do.

The Solutions

We can’t allow users to enter data without going through the proper business logic. So I can think of two solutions: either disallow drag and drop or highly control when a user can enter data into a record by any means they wish.

Disallow it

The first method is easier said than done. FileMaker Drag and Drop a preference in the app on each user’s machine. It is NOT specific to the file. So a developer or IT person would have to turn this off for every user. And of course, it can be turned on again in the preferences menu by a savvy user.

The Drag & Drop functionality can be turned off via the app preferences.

It would be awful to have to update this for each user

Disable it

The second method holds more promise: As a developer I might want to control when a user can enter data. I would have a separate data-entry layout and scripting to open a record and then check to make sure the record can be locked. When finished, the user would click a button that runs a script that checks various validations and allows the record to be committed or causes it to be reverted. Controlling the life cycle of the record means I could allow drag and drop.

Disallow it (Again)

Going back to method one, there is a way to disallow FileMaker Drag and Drop for all fields in a table without resorting to visiting each machine to turn off the app preference. The technique consists of a nondescript little field (text or number type) in the table where we want to prevent inadvertent drag and drop. I named my field “zz_ValidationField” so it sorts to the bottom of the field list. I’ve learned this from wise people: sort fields that do not contain data to the bottom.

On this field, I added validation in this manner:

  • The “Allow user to override during data entry” is unchecked.
  • The Validation can happen either “Always” or “Only during data entry”.
A validation of the field

Data Validation for my zz_ValidationField

  • Validation by calculation is used. Inside this is the function Get(RecordOpenState).
Validate using Get ( RecordOpenState)

The field’s calculation

  • Uncheck the “Validate only if field has been modified” checkbox at the bottom left.


This setup simply prevents drag and drop from happening when the record is not previously open.  This field’s validation will fail if the record is not open ( Get ( RecordOpenState) = 0 ) and thus will not let the data be saved. The custom message doesn’t come up since the record wasn’t open in the first place.

Conversely, if the user opens the record  (Get (RecordOpenState) > 0 ) via scripting or clicking in any field and then drags and drops, the validation will allow the drag-drop to happen as normal.

As FileMaker developers, we have to control every aspect of our custom app so that we can ensure data integrity. The FileMaker Drag and Drop experience, dragging text blocks or PDFs gets around our control unless we take an extra step to ensure that users don’t get carte blanche in adding data to the file.

Download Demo File

FMPerception Version 16.07 Released


FMPerception, the Fastest FileMaker Database Analysis Tool is even faster now. Version 16.0.7 brings some performance improvements to a couple of key areas as well as some much requested small enhancements. Take it for a spin and find our why FMPerception changes your game.


  • The Script Call Chain Diagram and the Layout Object Queries are faster, especially on large multifile systems.
  • Export, email and save your Report Cards and Script Call Chain diagrams. This was one of the most requested features on the list.
  • Global Variables now respects case. This means you can use FMPerception to find $$Vars that have different cases and clean them up.

See below for the full list of changes.

All Changes

Mac and Windows Changes:

  • Performance increases:
    • Script Call Chain diagrams are now much faster to display, especially in large, multi-file systems.
    • Layout Objects (flat) is now faster to display, especially in large, multi-file systems.
    • Huge thanks to Mark Jockin and his team for access to their DDR, which made wonderful test data for illustrating the problem.
  • Added a menu option to export the contents of the results web view. Now you can export, email and save your Report Cards and Script Call Chain diagrams. A couple of notes:
    • While tooltips and other mouse-over effects still work, clicking on elements won’t drill into them anymore. Those hyperlinks require that the URL be clicked from inside FMPerception
    • Because of the embedded engine that FMPerception uses, these exports will likely view best from Safari on macOS and Edge on Windows. If you are using other platforms or other browsers, your mileage may vary. Let me know if you have any issues.
    • In the short term, this functionality will allow you to use your browser to print or PDF the documents until such time as FMPerception supports printing natively.
    • There’s a known bug in the macOS implementation of the Script Call Chain diagram. If you open the exported HTML document in Safari and then expand the browser window, the boundaries of the diagram will not also expand. Short term solution: refresh the page once you’ve resized the browser window.
    • This one was requested so many times, I’ve lost track of who asked first. Sorry.
  • The Global Variables list now respects case.
    • Previously, all global variables in the global variables list were displayed in all lowercase.
    • Now, FMPerception groups all global variables ignoring case (as FileMaker does internally), but then displays the Global Variable by its most common capitalization pattern.
    • If the global variable is referenced using variations in case, the ‘Case Variants’ column will display all variants for that global variable (including, in parentheses, the number of times that variant was used). All variants will be displayed in order from most common to least common.
    • A line was added to the report card to indicate how many global variables have been references using inconsistent case.
    • This one also was requested so many times, I’ve lost track of who asked first.
  • Numerous typos were fixed in the Report Card, including a couple of 1/x math errors and a division error that caused the display of potentially very large percentages.

Huge thanks to Chiyoko Yoshida for the following:

  • Table Occurrences based off of Tables which have been deleted (and Layouts based upon those TOs) will provide better documentation in the detail sidebar about what elements are missing and why there are blanks in the results table.

Huge thanks to John Renfrew for the following:

  • When the contents of a script are displaying in the details sidebar, the formatting of blank script comment lines was subtly adjusted to make them more consistent with actual functional lines, making them easier to parse.

Huge thanks to Todd Geist for the following:

  • The JSON constant “functions” that specify node/property type were appearing as plugin functions. They now appear properly as FileMaker Pro standard functions

Mac-Only Changes:

  • The FMP ID column for table occurrences no longer uses a comma separator.
  • There were spots in the report card where the HTML used to format the text became visible. This has been fixed.

And finally, my apologies: The new Diff tool has taken quite a bit longer than anticipated. It’s still on it’s way.