Every custom app we build is a complex DNA strand of tables, fields, layout object and scripts, security privilege sets, conditional formatting, data-formatting options, relationships, calculations, indirection sources, and other attributes. The web these pieces creates starts simple in the first stages of development and we may even be able to keep it all in our heads. But as the custom app grows in functionality, data-storage, user-interfaces, it is easy to lose track of some part of it. I might have created a field called “zz_AccountName_g” and used it throughout, but months later, I cannot remember where I used it or even if I ever used it in a script or on a layout or in an privilege set. The FileMaker Database Design Report helps us to find that which we forgot.

FileMaker Database Design Report (DDR)

The FileMaker Database Design Report is meant to expose the DNA of a custom app, clearly identifying the locations and uses of fields, calculations, layouts, themes & styles, scripts, and other pieces. After running a DDR and processing it in some way, I can find the exact places the field “zz_AccountName_g” are used (if any). This exposure is essential for a developer’s sanity, efficiency in tracking down issues, and lean & well-organized custom apps.

Possible Uses

The DDR gives us the ability to find any part of our custom app. People use the DDR to do any of the following:

  • Find out where the field “zz_AccountName_g” is used throughout the system: what layouts it is on, what scripts use it, and any calculations (field definitions, conditional formatting, hide-conditions, etc.) that rely on the value.
  • Discover unreferenced fields or scripts/layouts that aren’t called from anywhere.
  • Find errors. This includes commented-out script steps or calculations, etc.
  • Read a script outside of the script workspace
  • Find indirection sources
  • Review the themes used and local styles.
  • Check the accounts that use the “EditOnly” privilege set.

This list is very incomplete. So remember this: you can find any part of your custom app using the DDR.

Start at the beginning.

FileMaker Pro Advanced users have access to an additional menu: Tools

Here is where the Database Design Report is found.

The dialog that comes up is fairly simple.

In this dialog box you have:

  • Available Files: These are all the files you have currently open. Those files checked will combine into one DDR, so make sure there’s no extraneous ones lurking about. With a multi-file solution, however, you’ll want all those included in the DDR. Just make sure each file is open before running the DDR.
  • Include Fields from Tables: The top-right box allows us to deselect some tables we don’t want included in the DDR.  This might come in handy, but I’ve rarely done this. Of course it depends on the size of the file; if your goal is to see only where one field in one table is used, you can only select that table.
  • Include in Report: The middle box lists what to include in the report. Again, it seems worth it to leave all these checked except for rare circumstances.
  • Report Format: This choice describes the kind of output you want. There are two: HTML and XML. Both are valid and useful, but the XML is MUCH more useful than the HTML format.
  • File Handling: I rarely immediately open the file, especially if I’m using the XML format.

When I click on Create, FileMaker produces a Summary file and a file for each FileMaker file included in the report. Any use of these files (opening, importing) starts at the Summary file.

The HTML format

The HTML format looks like this.

It is an HTML page that includes a long barely-formatted table of all the components of the DDR. Each column contains a helpful link to take you to its details. When I click on “31” under the Tables header, I am taken to this:

Now I can navigate anywhere in the report, to fields, to scripts that use a certain field, to all the relationships in which a certain table is used. Anything.

Run an HTML DDR on one of your files and click around in there. Go ahead. I’ll wait.

The HTML report can be useful, but navigating through the HTML document can get unwieldy. I personally never use this report. Instead, I create my DDRs in the XML format.

XML DDR

We FileMaker developers are interested in completing a task in the fewest clicks, and that includes finding a field in the complex DDR. Database analysis tools such as Base Elements and Inspector, and Realtime Developer Intelligence tools such as FMPerception allow us to find a field or calculation or conditional formatting logic extremely quickly. And these tools use the XML DDR. So this is the format I recommend (and most all developers use).
After creating the DDR in this format, you can use the tools mentioned above and either import the Summary.xml  (Base Elements & Inspector) or just open the Summary.xml in FMPerception. Here’s what a DDR looks like in FMPerception.

FMPerception and the other tools mentioned provide much easier access to every last byte of your custom app. They provide searches, drill-downs, reporting functions, and other organization tools.

Organization & Frequency

FileMaker developers lean heavily on the DDR and the tools that help them read the DDRs. These folks run DDRs on a regular basis

Before Development begins:

If a developer takes over the development, she will run a DDR and bring into a tool before any work begins. She’ll review the DDR to see its current state, looking for errors, unused elements, and overall structure. The reporting tools help her see the basic current state.

During Development

I’ve known developers to run a DDR on a regular basis: every hour, every week, every day, at the start of their work day, or before a work sprint. This DDR reflects the current state and shows the changes made recently. Tech leads use the daily DDR to keep an eye on errors or large structure changes. Developers use it to look up where a certain field is used.

After Development

After the custom app has gone through a development cycle and then has been released, a DDR is run to capture its final state. Again, post-work analysis can be done with the DDR. This DDR becomes documentation on the current release. This has an advantage too of helping us to identify and changes made by the client after they started working with the file.

Organization & Frequency

Running DDRs and importing them into the analysis or intelligence tools will cause you think about organization of the files since each DDR overwrites the previous one if you let it. So think about how you will organize these files.
I typically set up a “DDR” folder in my overall project folder. Inside this DDR folder is a folder for each day the DDR is run.

This method keeps all the DDRs organized proper. You can, at anytime, find a previous DDR, open it up again and compare it to the current one.

Rely on the FileMaker Database Design Report

The FileMaker Database Design Report is an essential tool for all FileMaker Developers, and all FileMaker developers can use this tool to better understand their custom apps. As you start using it, you’ll find that you can’t live without it.

You, Ms. / Mr. FileMaker developer can be a JavaScript developer as well. You can learn JavaScript and use it in your daily work.

FileMaker folks are suited for JavaScript. Though we may have stumbled into the FileMaker platform, we have enough knowledge and conceptual understanding to program also in JavaScript. There are many reasons why we can learn and why we should learn JavaScript. Let’s examine them.

Can Learn

A simple & powerful language

JavaScript was designed to be a beginner’s language. Like the Dick & Jane books or Sesame Street, JS’s structure and language is simple. It uses normal words and and a straightforward syntax. I bet you could figure out what this JS function does:

function sum (){
var a = 3, b = 5;
return a + b;
};

(answer: this is a function called “sum” that assigns values to two variables and then returns the sum).

This simple language is appealing. That’s not to say the language is impotent in any way. It is incredibly powerful.  Complex powerful applications that are used by billions of people around the world, like QuickBooks Online, and Facebook are built with JavaScript.

FileMaker knowledge

Our FileMaker knowledge can help us to learn JavaScript faster. We already have an understanding of such things as variables, variable scope, functions ( scripts and FM functions), and parameters. We can leverage what we know about FileMaker to help us learn JavaScript better.

The VAST community

On the recent StackOverflow survey, 62% of respondents said JS was their favorite language. People devote their career to working in JavaScript, so there’s a plethora of resources out there from which to learn and ask questions.

I learned it

Though I’m still plugging away, I feel pretty confident in my JavaScript knowledge. I played around with the language a lot and built countless FileMaker and html filles and watch hundreds of hours of video, but I learned it. You can too.

There are many reasons we can learn JavaScript. Let’s take a look at why we should learn the language.

Should Learn

Native FileMaker

We should learn JavaScript because it is a native part of FileMaker. It is supported on every part of the platform: FileMaker Pro and FileMaker Go runs JS in a web viewer, and FileMaker Server 16 includes node.js ( a JavaScript framework) in the install. Even WebDirect can run JavaScript in a limited way.

Since it is part of the platform, it behooves us to spend time with it and learn it. We can use JS to solve some vexing UI and scripting problems we occasionally encounter in normal FileMaker development. If your client wants a portal to have columns that sort in whatever combination she wants, a JavaScript library can solve this problem in much less time than it takes to develop it using FileMaker buttons and scripts.

If we have our way, FileMaker functions one day will call JavaScript functions. Right now we can do that with some small workaround (and will show that very soon). GoDraw3 uses this FileMaker-calling-JS-functions idea (check it out and download the unlocked trial version).

‘Tis the future

JavaScript will be around a long time:

  • JavaScript ranks number 1 in a survey of favorite languages or languages in use.
  • JavaScript is a cross-platform language.
  • The JS community invests tons of time in creating libraries
  • JS runs both on the client (in the browser) and server-side (using node.js).

If you do not learn it, your competitor will

Some day, some client will come to you with a use case to solve using their FileMaker custom app. Without the multi-tool capabilities of JS in your belt, the problem cannot be solved easily, and your client will find someone that can solve it.

Moonlighting

I know you’re not looking, but knowing JS can open up other job possibilities. JS developers are few, and the jobs are abundant. It’s a developer’s market.

So what’s your excuse?

Answer: there is none. It is important for FileMaker developers to see beyond just scripts and layout objects and view what’s possible and the tools that make those possible. So learn it.

Okay, I thought of an excuse: timing / familiarity. Folks think they don’t have the time to learn something that looks scary. Learning FileMaker is a full-time job, they say. These folks do not recognize their brains are primed for this language. It’s scary to jump into something new.

Join me at DevCon & Learn JavaScript

At FileMaker DevCon 2018, I will do my best to help folks learn JavaScript concepts and some techniques, taking away the scary and unfamiliar nature of it. We will spend a half-day training session learning the syntax and JS concepts, and will spend lots of time coding. We’ll start with FileMaker and use our knowledge of that platform to learn JavaScript. My training session is on Monday, just before the full conference starts.

Learning objectives

We have specific learning objectives. You’ll walk away with a lot of knowledge:

By the end of the session, FMDWBAT*:

  • Write JavaScript apps using the FileMaker web viewer and a text field
  • Pass FileMaker data to a JavaScript function
  • Retrieve the result of a JavaScript function to FileMaker
  • Customize an integration of popular JavaScript libraries
  • Use FileMaker scripts to call JavaScript functions (passing data to and retrieving data from the JS function).

*FMDWBT = “FileMaker Developers will be able to” –a derivation of a phrase I wrote on my classroom chalkboard/ whiteboard / smartboard everyday that proceeded that day’s objectives.

Complete documentation

Because I’m a teacher, you will be given lots of materials with which to participate during the session and continue your study afterwards. Here’s what will be available during and after the training:

  • The similarities between FileMaker and JavaScript
  • JavaScript use cases of varying levels
  • A playground in which to try out our JS code.
  • Videos of yours truly demonstrating a JS app for your repeated viewing at your own pace.
  • Great differentiated help.
  • Extensions: tasks and learning materials continuing through the week. Who needs the pool when you can continue to solidly your knowledge of JS.

Welcome anyone of any level

Join me and learn JavaScript as painlessly as possible no matter where you’re at in your FileMaker development path.

I’m a teacher at heart, so I’m there to guide and redirect and support anyone. We will work together to bring JavaScript more into the community so that we can talk about it from the FileMaker perspective just as often in the community as we talk about SSL or ExecuteSQL.

Feel free to shoot me any questions about the training. I’m happy to answer them: jeremy@geistinteractive.com.

See you in just three months!

 

 

GoDraw3 is a FileMaker drawing tool. Since it is FileMaker (100% native) and since you are a FileMaker developer, you can take this tool and do anything you want with it.

Use it as it is

When you first open the trial version, you’re given a complete set of familiar drawing tools with which to draw on an image or create an image from scratch. A user can:

  • Place text on an image
  • Draw with a brush or a line
  • Change the color/size/stroke/opacity of an object
  • Add an image
  • Tag data to an object
  • Copy/paste an object
  • Delete / undo / redo actions
  • Group objects
  • Change the size of the drawing canvas
  • Select/rotate/scale objects
  • Zoom in and out

And much more. There are so many possibilities for the user to use this for FileMaker drawing.

That’s great.

What FileMaker developers do

But FileMaker developers (you and me) are interested in much more than drawing. We’re interested in hacking at any tool to make it work perfectly for our users and for the custom app. Well guess what, you Ms. FileMaker developer, can do that to GoDraw3. You can hack away at this tool until it barely resembles the example editor. Please. We want you to hack at it. We want you to make it your own, and we’ve set it up for that very purpose.

How is hacking this possible? Because GoDraw3 is pure FileMaker. There’s nothing else you need to worry about. Since you know FileMaker, you can do whatever you want with this. Here’s a list of possibilities (woefully incomplete):

  • Add tool tips to the buttons
  • Remove tools that your users don’t want
  • Rearrange the tools
  • Change the background color
  • Add a button that only draws a purple circle with 50% transparency and with a yellow stroke
  • Change the default text to be 100 pt using Arial
  • Tag data to an object, and save that data to FileMaker
  • Include Snippets (JSON objects) & Favorites (.png or .jpg files) for reuse
  • Add a button that places a red arrow onto the canvas.
  • Make different tools available for different users
  • Adjust the size of the canvas
  • Remove the color palette
  • Disable the scaling or rotating of an object
  • Create a visual data-entry only system

And much more.

My hack

Here’s my hack.

My Method

This really looks a lot different than the example file. Here’s what I did:

  • Removed most of the tools
  • Included only buttons that draw specific colored circles.
  • Cleared the document size (I overrode the default document size) so this could go on forever, and as the picture grows, so too does the size.
  • Added the “Circle” script to each button and adjusted the “Circle” script to collect the fill color from the parameter on each button. No longer does it set an empty $toolOptions variable (as the example does) but instead the script gets the script parameter as the options. The Red circle has this in the parameter:

Hack away

GoDraw3 is a FileMaker’s dream tool. It is perfectly fine as it is for drawing, but FileMaker hackers will take great pleasure in hacking the heck out of it.

Go ahead. Download it. Hack it. Do what you want with GoDraw3.

GoDraw3 contains the word “draw” in it, and it certainly is a drawing tool. But it is not just a drawing tool. It can be used as a FileMaker visual data-entry system.

At the Doc

Check out this example called “Patients.fmp12” which illustrates a possible use in a doctor’s office. A patient comes in and describes pain points on his body. The doctor uses this app to place icons on a human body diagram to where the patient tells her. She can actually use circles and mark up the diagram. That’s cool, but what’s even cooler is that each of these icons is tagged with data: the body part and the current state of the body part.

Marking up a human body diagram and tagging the objects

Marking up a human body diagram and tagging the objects

This poor patient gets to identify the broken parts of his body, and the doctor quickly and easily marks the diagram with red circles. Upon save, the data attached to each circle creates a record and is saved.

The data from the objects gets saved to a child table.

The data from the objects gets saved to a child table.

Further, when the patient returns and reports of progress, say his left shoulder is fixed, The doctor can update the data for the paint point mark.

Updating an object’s data in the image updates the data in the table.

 Using our FileMaker skills

The script that does this is fairly simple. I used the “Find All Tags”  that came with the exampleas a subscript with a few modifications. This parent script then does the actual parsing of the JSON and either creates a new record or edits the existing body part record.

Example script.

Example script.

 

Drawing as data entry

This is just one example. But think, for a moment of the possibilities. Any developer could easily create a FileMaker visual data-entry system that is primarily visual for any number of projects. Here’s a small list (Thanks to Kirk Rheinlander for some of these.):

  • Equipment locations in hospitals (one record per floor) containing dates of service, warranty information, point of contact.
  • Seating charts for a wedding or a classroom. Guests or students are in related records, and each person gets assigned a seat in the venue.
  • Floor plans: assigning names to each room in the plan or showing the availability of each room for overnight guests.
  • Marking up a piece of art’s damaged spots.
  • Building inspections

In all of these cases, a FileMaker visual data-entry system might be easier for the users. It is visual, but the data is added to or edited from a table in the custom app. That’s pretty powerful.

Taking it even further

GoDraw3 can do even more:

  1. Since the picture is a simple JSON object, not only could the pictures update/add records, but the record information could update the picture. In my example above, let’s say the doctor changes the status of “Left Shoulder” from “broken” to “Fixed” in the portal there. That update triggers a script that updates the JSON of the drawing object tagged with “Left Shoulder”. In this scenario, the picture would have to be opened and saved, but the action of adjusting the value in the table COULD update the picture.
  2. Snippets in GoDraw3 are drawn objects on the canvas saved as JSON. These snippets can contain tags. So a FileMaker developer could create pre-tagged objects and store them in the app. During the exam, then, the doctor could access these snippets and place these pre-tagged objects on the canvas as needed.

FileMaker visual data-entry: Nothing but FileMaker

GoDraw3 is a FileMaker drawing tool. However, since the tool is native FileMaker, we can use our FileMaker skills to create a FileMaker visual data-entry system. And we can use our clever brains to come up with even more possibilities with this tool. FileMaker’s uniqueness allows us to do anything we want with the tools given. GoDraw gives us an additional tool with which to make the best custom apps possible for our clients.

 

We are excited to see and hear of what awesome scenarios the community comes up with with GoDraw. If you’ve played with the demo and have thought of a cool scenario for visual-data entry, let us know in the comments below.

Learn more about GoDraw3 and download the free trial. Give it a spin and see its awesome power.

If you want, check out the sample file. Download it here.

Welcome to GoDraw3: The FileMaker Drawing Tool.

GoDraw3 is a powerful FileMaker drawing tool. Its native FileMaker features gives freedom to a user and flexibility to the developer of the custom app.

Freedom for the User

There is a lot of freedom for a user of GoDraw3 to draw images and store them in FileMaker.

Any user is able to:

  • Create a brand new image or draw on an existing picture.
  • Use drawing tools such as a pen or shape tool inside GODraw3’s drawing canvas.
  • Save the image in one of four possible formats: .png, .jpg, .pdf, or .svg format and use these formats for other purposes.
  • Open an image and re-edit the drawing.
  • Use standard shortcuts to speed up the workflow.

Flexibility for the Developer

GoDraw3 is all native FileMaker.

FileMaker Drawing: All Native

The drawing canvas. All FileMaker buttons, scripts, layouts and other objects

Even though the drawing part of tool is a JavaScript application running in a web viewer the developer has complete control over the implementation and customization of this app. Here are some of the reasons why:

  • The web viewer app is self-contained in a separate file, making it easy to integrate into an existing custom app.
  • The design of the toolbars buttons and other UI elements is all done in FileMaker, so you can completely change how it looks just using FileMaker Layout mode. Don’t want users to create Text, just delete the button. It’s up to you.
  • The buttons all run FileMaker scripts that eventually tell the GoDraw canvas what to do. Once you learn how those scripts work you can customize the heck out of what they do.  You can have buttons that only make blue circles, or Red Rectangle, or insert an image of your Han Solo action figure. You get to decide.
  • The image is stored three ways:
    • in the container in a format chosen by the user (.png, .jpg, .pdf)
    • in a text field as an .svg
    • in a text field as a JSON object
  • JSON is the perfect data format for storing the edited images. With FileMaker’s built in JSON functions you create and parse entire documents if you want.

Because of the native nature of GoDraw3, there are plenty of possibilities for customization. GoDraw3 is completely open a developer to do with it what she wishes.

The Best FileMaker Drawing Experience

GoDraw3 is the best way to draw in FileMaker, saving the image and then editing it later. We’re excited about the potential. We have a lot of thoughts on how it can be used, and we will be sharing those over the next few days. Stay tuned to our blog to learn:

  • Specific use cases for which GoDraw3 would be great.
  • How to fully customize this tool for your custom app.
  • The techniques built into the app.

Learn all about GoDraw3.

Download it for free to try it out. The demo comes fully functional, but does contain a watermark on the drawing. So give it a spin and see how easy it is to draw in FileMaker.

I’ve been thinking a lot about FileMaker security. The topic comes up at least two times a day on the community forums. Bets are made, files are cracked in those bets due to the lack of security, and the conversation continues. It is a big topic, and one, I think, doesn’t get as much attention as it should. My attempt to remedy the lack of attention problem is one where I develop a FileMaker security model on an app of mine.

Choose your path

Wise people have schooled me in this part of development: it really is a matter of how much FileMaker security you need. A file of your favorite cheeses might not need so much, but others, like the storing employee HR information, requires ultimate security.

FileMaker security can be thought of as a game of deciding who a person  is and what they can do.

It is kind of like a video game ( a time-sucking past-time for me). I need to level up my character with certain skills or spells or weapons and armor in order to progress through the game. If I don’t care about beating that big bad monster around the corner, I don’t need to attain the proper level or attributes to beat it.

My level needs

So I sat down to think through and plan out the security model for a custom app of mine. The simple file I put together was to test out a mathematical formula for creating a Sudoko puzzle. I wanted to give it out, but I learned it from a book on magic tricks (one of my oft-ignored hobbies). Fearful of my life if I broke the magician’s code and revealed the secret, I held onto the file.

 

I needed to level up my app’s security to prevent some Josh person from breaking in and discovering how the formula works. Here’s what I decided on:

The user should be able to do

  • Log in with a username and password
  • View layouts and fields
  • Run scripts
  • Enter data into fields
  • Print!

The user should not

  • Have access to seeing a script run in debugger.
  • Have access to viewing a script in the script workspace (to see how the formula is run or to see how I wrote the script)
  • Edit the layout.
  • Open manage database.
  • Break into it from another file.
  • Export the data–I don’t want them to see how my fields are set up and get a clue on the scripting.

That’s top-level security right there!

Leveling up FileMaker Security

I tackled these level-up needs as follows (in the File / Manage Security menu):

Set up a new privilege set

I created a privilege set from scratch just to walk through the controls in this area. You can see the settings here.

FileMaker Security custom privilege set

My custom privilege set: User

A newly-created privilege set has absolutely no access granted. All boxes are not checked, and all the Data Access and Design drop-downs say “none”. That fact is something to remember, and it is a quite opposite of creating a new file, where that action gives full access to everything. So I had to go through each line to make sure I had the correct settings.

For the Records Access, I chose this:

FileMaker Security: Records Access Settings

Records Access Settings

Like the general settings, these start with “no” as the choice. I had to update each one as necessary.

For a moment, let’s review the choices for these settings. You have three: Yes, No, Limited. The first two are easy but the third one requires some thought. We’ll explore this in a later post, but it is a place to set a calculation to determine the access. For example, I could say anyone who’s first name is “Josh” has no access to the records in a table.

I’ve set up a new privilege set. Now I have to work on the accounts and login.

Set up the account and login

I created an account called “user” with the password “sudoku_’user’* and gave it the privilege set created above.

Next, I set up the Admin account to have a password (I won’t tell you what it is).

Finally I removed the ‘auto-login’ feature found in the File Options of the File menu.

*Note: I realize the password above is not very secure, but since many folks will download this file, I didn’t want to have to give a unique account and password to each person 🙂

Pause and Assess

So far my set up has been pretty good. I’ve taken care of both the ‘who is the person logging in’ though the account creation and ‘what she can do’ by setting up the limited privilege set. But now I have to remove “God Mode”.

God Mode – get in from the side

Another step in my leveling up is to remove what I call “God Mode.” In video games, the concept of “God mode” is the ability to get in and do whatever you want using a cheat code. In FileMaker we don’t have a cheat code, but we do have a sort of God mode. I can create a new file, call it “GodMode.fmp12” and I can set up an external reference to the Sudokou.fmp12 file if I can get to that file, whether it is on a machine or on a server.

In a separate file, I can create access to another file

In a separate file, I can create access to my ‘secure’ file.

 

With that reference created, I can get in by adding a table occurrence of Sudokou.fmp12 in GodMode.fmp12. Now I have access to fields, tables. GodMode.fmp12 has access to the scripts, the very scripts, in my case, that perform the ‘magic’ of creating the puzzle. I can write a script to perform “Create Sudoku” in the Sudoku file, debug it and see how it works.

Access to my Sudoku scripts using “GodMode”

God mode is powerful.

Disabling God Mode

Preventing God from accessing my Sudoku file is easy, but it is a setting a bit buried away in the FileMaker security settings. It is “File Access”.

One little checkbox removes God Mode.

 

This simply says that GodMode.fmp12 needs full access privileges of Sudoku.fmp12 to access the file.

When GodMode.fmp12 tries to access Sudoku.fmp12, this message pops up:

God Mode privilege is challenged

Failing to enter the Full Access account and password of Sudoku_Security.fmp12 will not allow GodMode to have access to the file. No access means no access to fields or scripts.

Optional: Remove Full Access

A hacker can do a lot of damage if they have Full Access username and password. So we can get rid of it. In FileMaker Pro Advanced, in the Tools / Developer Utilities, there’s an option to remove admin access permanently. That means that once stripped, the file cannot be edited as it previously could. It’s worth doing, but not always necessary. If you keep tight control over your password and don’t write it down on a post-it note and pose for a picture next to the note, you should be fine.

Other Security Measures

A final step in leveling up the security of your app is: Encryption at Rest, or EAR as we developers call it. This feature, again found in the Developer Utilities, actually encrypts the physical file.

EAR requires a user to enter a password to decrypt the file before it can be opened.

Without encryption, a hacker could open the file in a text editor and gain some knowledge possibly about the file. It might be worth trying it out.

My file isn’t encrypted, but when I opened it in a text editor, I couldn’t make heads or tails of it.

 

Security is such an important part of FileMaker development. We’re working with real data and valuable data (okay, even a database of cheeses can be valuable to someone ). So it behooves us to find the right level of security for each and every system. For my file, available below, I had to make sure that you couldn’t get into it, so I took proper steps to ensure that. We should do that for each of our files. WE should level up our file to the best security for our particular needs.

We’ve got something special up our sleeves. It’s an awesome new version of a great product, and it features some great technology. . .

We’re pretty proud of it. Stay tuned!

In a recent post on Charting, I used a FileMaker summary table as my data source. I clarified in that post my summary table stores a summary of the sales for a particular product for a particular month of a particular year. The use of a summary table in a custom app is worth exploring. I think it is a “Like a Boss” level technique, something you should consider that isn’t always part of the regular discussion about FileMaker.

Summarizing a FileMaker Summary Table

A FileMaker Summary Table

The summary data we will chart.

A summary table is simply a storage area for a summary of a closed set of data. In my example, the closed set is a month’s worth of total sales for each product. It is set up pretty simply. It is a narrow table containing:

  • A field for each attribute that defines a unique combination. My table contained: Year, Month, Products.
  • All the summary-type fields you need. My table only included Sales. But I could also include such fields as Trend or “Date_MostSales”.

Advantages

A summary table in our custom apps makes a lot of sense for the following reasons:

Unchanging data

It is unlikely that a particular time period’s, say June of 2012, data will change, so why not store it? Of course last month’s data might change a bit as final sales are tallied, but changes of data can be solved through business rules (all sales finish on the last day of the month) or through scripting that updates the created summary record.

No Calculated or Summary fields

I don’t need to build a report layout, add a summary field, then find June, 2012 Apple sales and examine the summary field. I have one record to look at in the summary table.

Static and Stored

The data is stored. The total sales for Apples for June of 2012 is stored and doesn’t need any additional calculated fields or summary fields to access the total.

Less Records

By definition a summary record is an encapsulation of many other records. If there were 14,000 sales across the products Apples, Peaches, and Bananas, in June of 2012, I can reduce those to three records. One for Apples, June 2012. Another for Peaches, June, 2012. Finally one for Bananas in June of 2012. Any additional finding (all of 2012, for example) can be done on this summary and sorted and charted or reported on much faster.

Initial Scripting

In order for this to work we have to set up a scripting process to run at regular intervals to do the summarization. Business rules would determine when and how often (weekly, monthly, yearly) the process would run. It would be a simple matter, then to find the data to be summarized, create the summary data and set it to a new record in the summary table. I’d create a JSON object to hold all the data I need to summarize and then create the records in one shot.

Something like:

{
"month":"February",
"year":2018,
"products":[
{
"type":"Apples",
"totalSales":43
},
{
"type":"Pears",
"totalSales":24
},
{
"type":"Bananas",
"totalSales":0
},
{
"type":"Peaches",
"totalSales":23
}
]
}

Since we’re creating many records that summarizes many records in this process, we have to consider whether or not the process should be transaction. I would recommend a transactional approach: allow this summary to be created for all product types or none of them.

Using Summary Data

The summary data table can be used in many ways.

  1. Charting the total sales for a particular product over time using the ‘related records’ data source.
  2. Performing a query for some chart or virtual list or other use case on this table will be much faster. I have fewer records to summarize in the query
  3. Printable reports. I can make a simple report from this table without the need for many levels of sub summary parts and summary fields.
  4. Create an entire dashboard of charts and lists.

These are just a few.

Updating the FileMaker Summary Table

The whole point of a FileMaker summary table is to store a summary of a set of data. As I said above, it is unlikely the data will change. But if business rules allow it to happen, then we would need to build a process to update the changed information. In the above summary, I’ve captured the total sales for February 2018. This process happened at 12:00 AM on March 1.  But on March 3, five pears were returned for some reason. Therefore, it is important to update the record in the summary table.

This is not that difficult to think about and to solve. Here are some thoughts I have about it.

  • The script that processes the return could detect that this sale happened in a month that was previously summarized, go to that summary record, and update it appropriately.
  • Each evening a process could find records modified in a time period past the current one and update their corresponding summary records

There’s really not a whole lot to this idea, but it is something of which to consider.

A FileMaker Summary Table is a useful technique and should be considered for data-intensive custom apps. If your use case requires summarizing a lot of data, this technique is a good and simple idea. There’s some considerations to ponder, but none of them are out of our skill sets.

I’ve written some about Charting, and I’ve written about JSON. I realized I like working with these tools. It recently dawned on me, thanks to a conversation with Todd, that I could combine the two together into “FileMaker JSON Charting”.

So, I laid in the floor of my loft while the dogs happily chewed on bones or toys and worked through it. I tried to think of how JSON could work with a chart.

It took a bit, but I started with the idea that a chart needs a list of values for the x- and y-axis. And a chart needs its two lists to match up line by line. Read the previous posts for more detail.

I realized JSON is the PERFECT structure to keep the lists intact. I can create an array of JSON objects with the attributes:  “month”, and “sales”.

{
"lineChart" :[
{
"month" : "January",
"sales" : 50
},
{
"month" : "February",
"sales" : 3
},
{
"month" : "March",
"sales" : 0
},
…
]}

If I could somehow pull the values out of each item in the array, I could easily create a list and put it in the correct axis. It turns, out we have a custom function that will help with this:

JSON.GetValuesAtPath(array, path). found in our JSONAdditions.fmp12 file.

This function “returns a list containing only the values at that path”.

So I could do:

Let (
_array = JSONGetElement ( zSystem::JSON ; ".lineChart" );

JSON.GetValuesAtPath( _array ; "sales")
)

and get “50¶3¶0” in return.

Look. It’s a list. It is easily stuffed into the y-axis. For the x-axis  I can use the same calculation as above, but take out “sales” and put in “month”. Easy easy easy.

Expansion

It occurred to me during the JSON object creation process that I could add another attribute and use the new attribute in another chart:

{
"lineChart" :[
{
"dollars" : 415,
"month" : "January",
"sales" : 50
},
{
"dollars" : 22.8,
"month" : "February",
"sales" : 3
},
{
"dollars" : 0,
"month" : "March",
"sales" : 0
},
…
]}

So chart 1 works with month and sales attributes. And Chart 2 works with “month” and “dollars” attributes. Two charts for the price of one JSON object, AND they stay in sync.

Two charts for the price of one JSON object

Two charts for the price of one JSON object

That’s very cool. In fact, as I drifted off to sleep, I remembered there are JavaScript libraries that do this same thing. They are designed to pull from different attributes of the same JSON object.

So my main task became gathering the data from the fields into the array and, as you see above, placing it in a field.

Generating the Data

I won’t go into a lot of detail about the method I used to generate the data. You can see it in the download. Here are the highlights:

  1. Find the records with the matching year and product.
  2. Gather the JSON data in some format. I’m looping through the records and creating objects which will go into an array, but there are other ways.

    Collecting data into an array

    Collecting data into an array

  3. Update the existing JSON object in the correct path. In my case: “.lineChart”.

Ever Expanding

I can expand the idea of FileMaker JSON Charting. A JSON object is useful for holding the data necessary to fill out a chart. A JSON object can have many parts to it. The line and column chart above run off of the “lineChart” nested array. I realized I could have another nested array for another chart: “pieChart”. In fact, it occurred to me one JSON object could run an entire dashboard, including a virtual list! Woo!

An entire dashboard powered by one JSON object. Complete FileMaker JSON Charting

An entire dashboard powered by one JSON object.

This dashboard runs off of one JSON object. The JSON object contains a nested array for “pieChart”, and one for “lineChart”.

The JSON object

The JSON object

The Year field at top left determines  the data to be shown, so the JSON object is re-rendered each time. And the additional choices at top further refine the data displayed.

Updating the Data

My scripting gathers the data and then overwrites what’s already in the JSON object with new data. So if a user chooses 2016 and “Peaches” whatever is in the “lineChart” key will be replaced with new data. This is useful. I don’t wipe out other data that is powering other charts.

FileMaker JSON Charting Further Considerations

I woke up in the morning and, as often happens, an idea popped into my head. I could set up my JSON object to collect data for the entire range of years and products and refine the calculation for each axis in the charts to drill down to the path for the year and the product the user chose above. No re-capturing of the data.

But why would I do all this? I could very easily create many table occurrences and filters and such to display the same data. I think that answers my question. These charts are completely context-free. the JSON is stored in a field (or could be in a global variable) and available no matter where the objects happen to be.

Again, JavaScript libraries do this: ‘reduce’ and filter from a huge JSON object to what is needed for a particular chart.

I’m sure there’s some more that can be done here. I’ll continue to play with it and see what else I come up with, but if you’ve got any ideas, let me know in the comments below.

Download the Demo

We are taking a look at the FileMaker charting object, specifically how to collect the data for a chart. We started the discussion in the previous post. I’ll repeat what I said there:

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.

You can download the demo (below) and review the video.

We previously looked at the Current Found Set data source, which is a very valid and useful source of data. In this post, we’ll look at the remaining two data sources: Related Records and Current Record (delimited list). Let’s start with Related Records.

Recall our data set.

The summary data we wil chart.

The summary data we will chart.

Related Records

FileMaker Charting Data Source: Related Records

Data Source: Related Records

There’s not a whole of discussion around this; it is pretty straight forward.

I could easily set up a chart on a record in the products table and easily display that product’s sales to tell any story we wish: sales per month of a particular year (as defined by the relationship) or all of the data for every year, or for any month (as defined by the relationship). Think of this as a portal with bars or lines or slices.

A graph with related data shown.

A graph with related data shown.

There is not too much to this. Notice the Data Source Tab in the picture above. I have the sort order of related records unsorted. That’s okay. I chose to sort the records in the relationship. But it can be done here instead. And if you allow the sort to happen in the FileMaker charting object (like we would do a filter) you can use summary fields instead, just as we can do with the Current Found Set Data Source.

There’s a lot of flexibility with this kind of data source as well. If you need a chart to show related data, throw one of these on a detail record, point it at some related records, and you’re good to go.


And now we get to the fun one:

Current Record (delimited data)

FileMaker Charting Data Source: Current Record (Delimited Data)

Data Source: Current Record (Delimited Data)

It isn’t obvious from this data source, but this source is the most flexible to do FileMaker charting. For the third time, I’ll restate: All we need to do is a pass a return-delimited list into the x- and y- axis.

Using this data source, we get full control over what is passed in.

This chart has two fields from which the data comes. I can set up a chart like this pretty easily.

Full Control over the data passed into the axes.

Full Control over the data passed into the axes.

But if we stop and think about it, we really only need the list of data for each axis. Rather than putting it in a field, we can put it in the calc dialog of the axes themselves.

Heck. We don’t even need a hardcoded function in there. We can use a variable: $$xaxis in there and, using a script, set that variable to be a return-delimited list of data.

Collecting the Data

The FileMaker charting object needs a list of data, so we need to create a list. We can do that in many ways.

List ()

Our go-to list-generating function, but it contains some issues. The list function returns nonblank values, and it doesn’t ‘skip’ a row for the blank one. If we collect a list of data from two fields in a table, the two lists are not synced up. I would only use this if I was sure that every list I made had each and every record accounted for in the result.

ExecuteSQL()

Developers love using this to gather a list of records since it can be called from anywhere. I’ve seen a chart using this function in a dashboard set up–a layout with multiple charts on it, with each chart’s axes using this function to collect data.

ExecuteSQL will return a blank value. But it has its limits. You have no control over when it is called, so if five charts use ExecuteSQL, that’s around ten calculations happening at once. If any of the records upon which the query is running are open, a documented performance drain will occur.

ListOf Summary field

This is useful but, like the list function, this skips blank records in a field. Like List(), I’d use this only when I had a record for every point of data across the two axes.

Looping through records

Using HyperList 2.0 or another method, I write a script to gather the data from the found and sorted set, setting a variable in a loop.

However you collect the data and form it into a list, you just to make sure you have all the values in the two lists (x and y axis lists) correspond exactly. It would be disastrous if the lists got off–our data would be unreliable and therefore worthless.  So be careful in your gathering of the data.

One more thought, just a bit off topic

In the examples for this and the previous post, I used a table called “SummaryData”. It is a table with one record per product / month / year combination that summarizes the total sales across all invoices. At the end of the month, I run a script that totals the sales for each product for that month and stores it in this table. The data is stored and static. For months with zero sales, there is still a record (zero is a total), so I do not have that problem.

I prefer this method for chart displays. Since charts do often display summary data, it is logical to gather the data from a table where the data is stored.

We’ll explore this idea of a summary table in a future post. I just wanted to bring it up now.

FileMaker Charting Now and Next steps

Go forth and use the FileMaker Charting object to create good-looking visualizations of your data. FileMaker charting might be confounding at first glance, but it can be easily understood given a few practice runs. This charting method will suffice for much of your graphing needs. One of the leaders of the FileMaker world that uses the FieMaker charting object in all his data reporting tools. They’re extremely adequate.

However, since Native FileMaker is almost limitless, we do have the option of exploring using a JavaScript library to construct a graph. This option gives us so much more options in customizing the graph: colors, fonts, sizes, widths of lines, space between columns, etc.. In the near future we will explore these possibilities.

Download the Demo