Experiences of a FileMaker Pro Database Developer

Unix Timestamp Custom Function

I needed to create a custom function to convert a standard FileMaker timestamp to the Epic Unix Timestamp. We are beginning to develop in Redshift on AWS and need to pass data from FileMaker to a table in Redshift.

I created the custom function to automatically adjust to daylight savings time here in the US. The function would need to be adjusted for use outside the US. You will also need Howard Schlossberg’s isDayLightTime custom function.

UnixtimeStamp ( _date; _time; _UTC_offset )
//UnixtimeStamp ( _date; _time; _UTC_offset )
//Written by Mathew Greger, matt@mattgreger.com
//Converts FileMaker Date and Time to Unix Timestamp
//Automatically adjusts for Daylight Savings Time (US)
//Requires isDaylightTime custom function by Howard Schlossberg

//_date = standard FileMaker Date
//_time = standard FileMaker Time
//_UTC_offset = Current Timezone Offset from UTC

Let ([

//daylight savings time = 1, otherwise 0
_dst = isDaylightTime ( _date);

//convert time to GMT, must pass UTC offset
_gmt = _time + ((5 - _dst) * 3600)


//convert timestamp to number
GetAsNumber ( 
//must use GMT for time
Timestamp ( _date;  _gmt ) )
//convert number to Unix Timestamp (difference in second from 1/1/1970 to 1/1/0001)
- 62135596800


Post to Twitter

Usability: A Lesson From Evernote’s Penultimate 6 Blunder

Penultimate 5

Penultimate 5

As software/app developers it’s critical that we understand usability. We must learn who our users are and their behavior. Essentially, we build use cases and user personas. It’s our job to figure out what is working and what doesn’t work in their workflow. People are creatures of habit and they get used to work-arounds, double entry, etc., so our job also involves retraining once deployed. To me, the most enjoyable part of development is learning about the users and how they work and then understanding how you can create something to make their life better.

This week Evernote released version 6 of Penultimate, their handwriting-note-taking application for iPad. Some of us still like to take handwritten notes, but want the convenience of digital. I loved Penultimate. It was like a digital version of a Moleskine notebook. I could have a notebook for every client or every project. And, it was great for a personal journal too. The best thing, I could still doodle.

To me, what they have done with Penultimate 6 is to completely disregard who their user is. The biggest lesson I learned is to turn OFF auto-updates for apps. At the very least, if the upgrade is no longer intuitive (a mistake), make sure the help and user guides are updated. Here are a couple of snip-its from their forum posts:

Notebooks have pages – people look through pages to find what they want – bring that back.
The hand writing engine is a joke, its the nail in this “updates” coffin – makes it unusable.

In short, this version that is touted as having a “New UI for focused writing” makes it impossible to focus on writing because it gets in the way of that single activity — the core functionality of the app, mind you — at every turn…. Revert, re-think, re-do. Or just re-release the old version — under a “Penultimate Classic” name if need be — until you are ready to make this one work for its intended purpose.

The comments on the Penultimate forum and the Evernote blog post illustrate exactly what NOT to do when upgrading an existing app — ignore the user. This should be a lesson to all developers.


Thank goodness I had a backup of the previous version in iTunes! Two hours later, all was right again in the world of note taking.

Post to Twitter

Creating QR Codes with FileMaker and KAYWA API



I want to thank Kevin Murphy for submitting his FileMaker QR Code solution using the KAYWA API for creating QR Codes. With the fate of Google’s API for creating QR Codes unknown, it’s good to see alternative solutions. Kevin’s recent submission has spark me to look into creating a self contained QR Code generator within FileMaker. I hope to use the ScriptMaster plugin along with HTML5 to create QR Codes without needing to be connected to the Internet and use an API.

Kevin wrote:

We’re building an application that needs Web URL QR Codes generated in it, and I happened across your solution.  I noticed that you were concerned about the Google API going away, but looked through your code with some interest.  At the risk of telling you something you already know, I thought I would share what we eventually figured out for our solution.

I happened across kaywa.com, and they have a reasonable API for generating QR Codes.  It turns out that they have a direct URL for creating just a web URL QR Code – exactly what I was hoping to find.

We are developing in FMPA12, so while we grit our teeth through some of the growing pains, we benefit from several of the new functions.  One of the new script steps – Insert from URL – greatly simplifies the process of generating QR codes from URLs – as long as you have a simplified URL to draw them from.

I’ve attached a very quick sample file, if you’re into FMP12.

You can download Kevin’s sample file here:


About Kevin Murphy: Kevin is a typical FileMaker Pro developer that loves FileMaker because it just gets the job done. He is the IT Team Manager for Hancock Lumber Company in Casco, Maine.

Post to Twitter

Creating QR Codes with FileMaker


QR Code

This QR Code generator developed in FileMaker Pro is free and unlocked so it can be used in your own solution. The QR Code is still popular today, especially when used with smart phones to quickly launch a website for more information. The website could be anything from more information about the product you are looking at, the daily lunch specials at the restaurant you just passed or more information on a house for sale that you saw driving by.

Using Google’s Chart API for QR Codes, Custom Functions and the ScriptMaster plug-in by 360Works, I created the FileMaker Pro QR Code Creator. There are 2 options for displaying and creating the QR Code:

  1. Create QR Codes in Web Viewer
  2. Store a QR Code as a PNG file in a container field

Each option uses a different custom function. I was hoping to generate the QR Codes with a single CF, but the Web Viewer’s automatic encoding makes it basically impossible.

UPDATE 4/30/2011: I just updated the file so the plug-in function is now loaded within the custom function instead of having to call a separate script. All you need to do to make this work in your own solution is to install the ScriptMaker plug-in and to import my “QR_Code_Container” Custom Function from my example file below.

UPDATE 5/16/2011: Thanks to Daniel, he discovered that it’s required that my sample file is open before you import the Custom Function. If my sample file is not open when you import, the Custom Function will be commented out and missing the ScriptMaster GetURLAsContainerpart2 function.

UPDATE 1/21/2013: Google seems to be changing the API for QR Codes. I updated the link above, but it looks like QR Code generation may no longer be supported after 2015. The file still works as expected, but I am not sure how Google’s future changes may impact the function of the file. I will try to dig into this and if anyone else has further information, please let me know.

UPDATE 3/7/2013: Thanks to Bill, he helped me discover that I needed to clarify that not only is it required to open my sample file, but you MUST create a QR Code in my sample file first, before you import the Custom Functions. Creating a QR Code, or just changing one of the fields that is used to generate the code, causes the Custom Functions to load. If they do not evoke the Custom Function first, you will get  ( $url & $dim & $data & $encoding & $error & $margin ), instead of GetURLAsContainerpart2( $url & $dim & $data & $encoding & $error & $margin ).

UPDATE 3/16/2015: You can also try the QR Code Generator using the KAYWA API if you are having trouble with the Google API or want to see another solution. Check it out here.

You can download the fully opened, unlocked example file here:


If you have any comments, questions, suggestions or problems with the file, please post a comment. Although I have done the best I can to create a file that works, is bug free and generates a code that is expected, use the file at your own risk and there is no implied warranty protecting data loss or the accuracy of the code generated.

Post to Twitter


FileMaker Pro version 11 has new charting options. These are nicely integrated directly in the layout options. For simple charts, FileMaker’s built-in options are a great choice.

I needed something a bit more powerful and interactive. My solution was to use Highcharts, interactive JavaScript charts. Here’s an example of a chart created from one of my FileMaker databases using Highcharts: Highchart Example.

By clicking the legend, you can turn the graphs on and off. You can also drag and select to zoom in on specific areas of the chart.

In order to integrate with FileMaker,  use the substitute function and building pieces of the chart using calculations formatted as html snippets. Next, build a master chart with variables that when processed with the substitute function, the variables are replaced with the html snippets. Click here for an example of the master chart that I used. You will need to view the source in order to see the html.

I dump the master chart into a global text field and then process that field with the substitute function. Use a separate substitute for each variable.

Make sure to check out the demo gallery to see all the charting options available.

Post to Twitter