Do you take pictures with your iPhone?
Then you should check out iTookThisToday.

Downloads

Paste Image Into Container Field
3/25/2008 - Someone in a very weird circumstance recently contact me about needing to paste images into container field records. He was on a Mac, so AppleScript was an option. I was able to offer the following proof of concept that used AppleScript to drive the application interface via System Events which is sneaky way to script things that are not normally accessible. Whether you are pasting images or trying to automate iCal, this might be of interest to you.
[download] [comments] [back to top]

Text Overflow Warning for FileMaker Pro 9
12/13/2007 - Have you ever needed to to know if the text that a user has entered in a field is overflowing the field boundaries? I know I have. Typically, this is important when printing is involved and sliding is not an option. I whipped up the following file in an attempt to address the issue. BTW - FileMaker 9 is required to open the file because resizing has been applied to layout objects. Please let me know what you think, or if you have a better solution. P.S. Thanks to Alan Kirtlink and Greg Lane at Skeleton Key for putting me on the right track.
[download] [comments] [back to top]

Top X Percent Function in FileMaker
7/26/2007 - I recently received the following request:

Is there a Filemaker equivalent to the MS Access/Excel xlTop10Percent function? I am interested in utilizing this capability to constrain a Find Request to the top 10% values. By the way, I need to process just over 41 million records.

There isn't a built in way in FMP to filter records as you've described, but there are ways to do it. You could attack this problem with a combination of custom functions, calculations, and summary fields, but considering the number of records involved I would recommend a scripted method similar to what you'll find in the following file.
[download] [comments] [back to top]

Finding Records with AppleScript
7/26/2007 - I recently received the following request:

I desperately need to understand how to applescript filemaker to do an exact Find. When I use show every record whose cell "cellname" is "the search string" it returns not an exact match but more like a Google match. I need it to return all and only the records with an exact match.

I really like using AppleScript, but using it to do finds in FileMaker is rough. Rather than try to talk my desperate friend into looking for a native FileMaker solution to the problem, I sent along the following example files.
[download] [comments] [back to top]

Exporting Named Files
7/25/2007 - I recently received the following request:

I have to create a number of text files sequentially (in a single run) from a database, each having its own name. On the Mac, I simply export each file with a standard name, and then tell AppleScript to rename it with the proper name. How does one do this in the PC world? Meanwhile I have discovered the DDE Execute script step, which apparently should do on the PC what AppleScript does on the Mac. Unfortunately, DDE is already old technology and I have difficulty finding out how to use it to just rename a file.

Fortunately this can be done easily with the local script variables that became available in FileMaker 8. You just store the path to the output file in a variable and use that variable as the file reference. A quick check for Mac or PC makes it cross-platform as well.
[download] [comments] [back to top]

Window Focus During Script Execution
06/26/2007 - If you are like me, you were very happy to see the New Window functionality added to FileMaker as of version 7. Sure it's great for users, but even better for developers. At first blush, the New Window feature seemed to give the developer a scratch area to do scripted work without altering the user's found set. In short order, some savvy developer even discovered that you could create these scratch windows off-screen, which minimized the flashing that can come with new window creation.

Unfortunately, there is a dark secret lurking. If your script takes even a second or two to execute in the active off-screen window, the user may get confused and click on the inactive on-screen window. Horrifyingly, if they do so, focus is transferred to the onscreen window and whatever action was supposed to take place in your safe little off-screen sandbox now continues executing in the wrong window. As far as I know, there is no way to prevent this. Download this sample file for a demo.
[download] [comments] [back to top]

FileMaker to iCal as To Dos
05/07/2007 - I recently received this request: "Do you have a script where I can take information from my database and export it to ical in the to do list? What I would like is to send a range of information to the To Do list."

This sounded like a fun challenge, so I modified the FileMaker to iCal example file to send FileMaker data to a calendar as To Dos rather than Events. As an added bonus, the To Dos are created with email reminders that are sent 24 hours before the due date.
[download] [comments] [back to top]

FileMaker.php TextMate Bundle
04/27/2007 - If you are a developer who does a lot of coding and works on a Mac, do yourself a favor and download trial copy of TextMate right now. Seriously, it's out of control. I do a lot of PHP coding and I don't know how I ever worked without TextMate (sorry, BBEdit!).

There are a ton of amazing features in TextMate, but my favorite is that it comes with bundles of code snippets that dramatically speed your development. Even better, you can make your own, which is what I did to assist my FileMaker.php work. All you have to do to install it in your version of TextMate is download this archive, unzip it, and double-click the resulting FileMaker.tmbundle package.
[download] [comments] [back to top]

Multi-date Calendar Picker
04/18/2007 - FileMaker's drop-down calendar is very convenient for many simple data entry situations, but what if you want to allow the user to select multiple dates? Or what if you need to block out certain dates? I find that these situations come up with enough regularity that it was worth my time to put together a self-contained "user prompt" file that I can drop into a solution and have instant access to this sort of functionality.

This file currently has two main functions: an advanced calendar picker as described above, and a record selection popup that works like a drop down menu on steroids. I intend to update this file periodically, so check back for the latest and greatest.
[download] [comments] [back to top]

FileMaker 8.5 to Address Book
1/20/2007 - I recently received an email from someone who wanted to use AppleScript to move contact records from FileMaker to Mac's Address Book application. His situation was complicated by the fact that the email and phone records were stored in separate tables, related to the person table.

I whipped up a simple solution based on Apple's sample Address Book scripts. It does not send over the email and phone types. You can figure that out on your own, for extra credit ;)
[download] [comments] [back to top]

Most Recent Prior Activity
12/17/2006 - Here's the request that this solution addresses: Given a table of People who have 0 or more related Activities, AND given a date value defined arbitrarily by the user at runtime, build a portal that displays the single most recent prior related Activity for each person. The behavior of the interface should be such that the portal data updates without the user clicking a button after entering a new date value.

See the copious (and opinionated) notes in the relationship graph for details on how it works – and why you should talk the customer out of certain requests. ;)
[download] [comments] [back to top]

Hotel Price Rate Fluctiations
12/2/2006 - I recently received the following email: "I'm making an invoice system for a friend's hotel. There are 3 "seasons", each with a different price per person per night. I need to be able to specify a start and end date (of the client's stay) and have the database calculate how many nights where spent in each season, if the stay were to span more than one."

This is actually a pretty common problem. Namely, calculating the intersection of two or more date ranges. What you need to do is break all the ranges down to a greatest common denominator (GCD) and relate through the GCD to navigate between the ranges. In the case of date ranges, days are usually an acceptable level of granularity.
[download] [comments] [back to top]

Manual FileMaker File Backups
10/26/2006 - This file was created to demonstrate how to set up a manual backup script. Users are allowed to backup manually by running the Backup File script in the Scripts menu or by clicking the Backup button. The file is also backed up when the file is closed. Backup files will be saved to the directory where the FileMaker application is installed.

Naturally, this is something that would be better accomplished with FileMaker Server, but in the event that FMS is not an option, or the solution is a single user application, this could be of help.
[download] [comments] [back to top]

Get Values from FileMaker with AppleScript
9/7/2006 - I got an interesting question today regarding accessing the data in a repeating field with AppleScript, which revealed to me that I had not done much work with that sort of thing. Much to my delight, exploring this topic led to me to some interesting discoveries.

This file has nine AppleScripts that grab data from FileMaker fields, repeating and otherwise. See the embedded AppleScript comments for more info. Thanks to PC of Pittsburgh, PA for the inquiry - and the iTunes gift certificate!
[download] [comments] [back to top]

DEVCON 2006: User Defined Data Model Examples
8/20/2006 - One of the files that I presented at DevCon 2006 was a proprietary customer file that I could not make available to the public. Unfortunately, that is the file everyone wanted :)

To address people's curiosity, I have done what I can to create a rough version that incorporates the most important features.
[download] [comments] [back to top]

DEVCON 2006: Why Data Modeling Doesn't Work
8/16/2006 - I presented a session at the 2006 FileMaker Developer's Conference entitled "Why Data Modeling Does't Work (And What To Do About It). Quite a few attendees requested that I post my slides and sample files, so here they are.

There is a read me file enclosed in the download, so please refer to that for more information. As always, let me know if you have questions and I will do my best to post the answers here.
[download] [comments] [back to top]

FileMaker AppleScript Functions
8/11/2006 - About a month ago, I got so frustrated with one of the popular FileMaker file management plugins, that I decided to write a simple replacement with AppleScript. The fact of the matter was, I just needed to do a few common operations, for instance: check for the existence of files, folders, and mounted disks; delete, move, and rename files and folders; select files and folders... you get the idea.

So, I wrote the AppleScripts, and then embedded them in scripts in a FileMaker file called AppleScript.fp7. This allows me to drop the AppleScript.fp7 file into any solution and just call the scripts from existing solution files. I use script parameters to modify the behavior of the called AppleScripts and script results to return information from the AppleScript to the calling file.

For example, let's say I have a file called PDF.fp7 that stores references to PDF files on my hard drive. To add a new PDF record to the system, I would write a script in PDF.fp7 that calls the Select File script in AppleScript.fp7. The Select File script in AppleScript.fp7 would prompt me to select a PDF, and then return a reference PDF back to the calling script in the PDF.fp7 file. I could then make a new record in the PDF.fp7 file and store the path to the selected PDF in a text field called "PathToFile".

If that does not make sense, just check out the demo scripts. If it still does not make sense, just email me ;)
[download] [comments] [back to top]

Move/Rename File (and create necessary folders)
6/21/2006 - This AppleScript takes a file and moves it to another location. If you like, you can specify a new name for the file in the target path, which will cause the file to be renamed during the move. Folders in the target path will be created if necessary. Files may be moved across volumes, but the volumes must be mounted, of course.

The script takes two "parameters", the SourceFilePath and the TargetFilePath. In this example, the values are hard coded into the script. It is expected that they would be specified dynamically at runtime, perhaps from a FileMaker database, or with native Applescript user input constructs.

It was written on OS X 10.4.7 with ScriptEditor 2.1.1(81). As always, please let me know if you have any comments, questions, or issues.
[download] [comments] [back to top]

Dyanamic Value Lists with Create in FileMaker Pro 8
5/31/2006 - This is just an example of a suggested user interface design regarding dynamic value lists. As you probably already know, adding a value list to a field is a big help for data entry. It becomes even more powerful when the values are based on records in the database, which makes them dynamic.

One of the limitations of dynamic values lists, however, is that you can not opt to allow that user to create a new value via the value list itself, because that would involve creating a new record in the table that underlies the list. I think this example file demonstrates a pretty good alternative. If you agree, disagree or have a better example, please let me know.
[download] [comments] [back to top]

Export Email Addresses as CSV from Address Book
5/10/2006 - Don't you wish Address Book on Mac OS X supported CSV as an export format? If you have Gmail, you probably do. Gmail supports importing contact lists, but only from CSV files. So I wrote this quickie little AppleScript that solves the problem. There is no error checking, so if you have commas in your contact names or email addresses, shame on you ;)
[download] [comments] [back to top]

PHP iCal Parser
5/7/2006 - I got to thinking about my Cross Platform iCal to FileMaker 8 example file and it occurred to me that I could rewrite it in PHP using almost the exact same logic. So, I just couldn't help myself and wrote it up. As with the FileMaker example, it just pulls basic event information, but that is typically all I am looking for. If you want something more sophisticated, check out PHP iCalendar.

This version has a couple of really big advantages over the FMP version, but my fave is that you can point it at .ics files that are available online. In other words, if you can subscribe to the calendar, you can parse it into records. This file just displays the results as a table, but if you know some PHP you should be able to easily modify the output code to write to a database.
[download] [comments] [back to top]

Cross Platform iCal to FileMaker 8
5/5/2006 - I got an email the other day from someone who wanted a Windows version of my iCal to FileMaker solution. I found this strange at first because iCal is a Mac-only application, but the person explained that they were looking to import .ics files directly.

This was a fun challenge which I believe I solved pretty well with this file. It has been tested on both Mac and Window and seems to work, but please let me know if you have trouble with it. There is a calendar file included for your testing pleasure. Have fun!
[download] [comments] [back to top]

FileMaker 8 to iCal
4/1/2006 - I recently received the following question via email:
"Hi Jonathan - Just did a Google search for FileMaker to iCal and found your site, just great! I downloaded the FileMaker to iCal Event database but see that it is only good in FileMaker 5/6. Do you have an update for FM 7/8? This would be a perfect solution for me, but I know absolutely nothing about AppleScript."

I have been meaning to update the FileMaker 5/6 version of this file for FileMaker 8 anyway, so I am happy to oblige. The differences between Applescripting the different versions are fairly significant and it is tough to find tutorials online. I hope y'all find this helpful. Let me know if you have any trouble - I didn't really test it very much :-]
[download] [comments] [back to top]

Summary Report with Percentages
3/30/2006 - I recently received the following question via email:
"I have a database of employees - a record for each one. The record includes a department number and a position. I have a layout that sorts by department, and within that department, by position. I have summary fields that report subtotals of positions within department and employees within department. I want to add fields reporting the percent that each subtotal represents of the whole staff. I can't figure out how to do this. Are you able to tell me how?"

The trick is to create a calculation field that evaluates to the same value for every record, and then point a "fraction of total" suummary field at the calc. Check out this file for more info.
[download] [comments] [back to top]

Process DDR
2/15/2006 - Have you ever wanted to take your FileMaker solution and generate a list of every relationship that has cascading delete turned on? Yeah, me too. Or maybe you want to compare all of the scripts in two versions of the same solution to determine if any of them have been changed.

There are excellent third party tools that solve these kinds problems, but sometimes they don't do exactly what I want. So, I cooked up this file which uses XSL to convert FileMaker's DDR XML grammar into the FMPXMLRESULT grammar, which can then be easily imported. This file uses a smidge of AppleScript, so you Windows dudes and dudettes will have to make a minor change for it to work.
[download] [comments] [back to top]

Disjoint Values
2/5/2006 - The other day, I had two intersecting sets of numbers and I needed to convert them to disjoint sets by removing the common values from each. This is an increasingly common situation in my FileMaker programming, so I figured I would work up an example file to get my head around it.

This file also showcases an excellent method for passing multiple distinct lists of values into a script as a parameter. I had been struggling for days to find an elegant way to handle this when I ran into Kieran MacMillan, who figured it out in about 10 minutes (thanks KMac!).
[download] [comments] [back to top]

Recovering Multiple FileMaker Files
1/13/2006 - If you have been using FileMaker Server version 8.1, you may have come across an annoying indexing bug. For example, finding no records when performing a find for a value that you know is a particular field. The good news is that the bug has been fixed in version 8.2. The bad news is that upgrading the server version does not fix indexes that have gotten messed up, it just prevents them from getting messed up in the future.

Once you have the server upgraded, you can go through and reindex all of your indexed fields one by one. If you have lots of files and indexed fields, this would be tedious and prone to error. In a break with tradition, FileMaker, Inc. suggests recovering the files in cases where a mass-reindex is required. I recently had to do this and I was in a big hurry to get it done, so I wrote this file to do it for me.

NOTE: If you are not familiar with the ramifications of recovering files, you should read FileMaker's help documentation on the Recover command, and this excellent page on the subject.
[download] [comments] [back to top]

Digital Asset Management with FileMaker and AppleScript
12/20/2005 - Recently, I built a file manager with FileMaker Pro 8 and AppleScript. I had a hard time finding reference materials on the topic, so I decided to post my findings for other poor souls who have to wrestle with this.

The workflow is this: user receives a file from a customer and copies it to their local hard drive. To make the file available to other users, they click an "Import" button in FileMaker that runs an AppleScript. Since the files are usually too large to be stored directly in FileMaker, the AppleScript duplicates the file to a shared file server. Then, a reference to the file is stored in FileMaker. I hope you find it useful.
[download] [comments] [back to top]

Ginko (User Defined Data Model)
12/19/2005 - Ginko is my proof-of-concept file for non-standard application development techniques with FileMaker. It includes fun stuff like custom menus, hierarchical portals, window-specific globals, etc...

Most importantly, it is an implementation of something I refer to as a User Defined Data Model (UDDM), which is to say, the data model is defined by the user over time, rather than by the developer up front. The data is stored in subject-predicate-object triples (like RDF), and the resulting structure is similar to that of a Topic Map. This is useful because defining the data model prior to application development is usually a limited success and results in an inflexible application.

It is a FileMaker 8 file, so if you don't have 8, download it from Filemaker's site (you'll be glad you did!). I have not had time to include a READ ME with it yet, so for the time being, just download the thing and email questions to me. Later on I will try to post an FAQ.
[download] [comments] [back to top]

FileMaker, Squids and Related Value Lists
11/20/2005 - I get quite a few questions about related value lists in relation to the ALWAYS Rules of Squidding. I put together this little demo file to help people grok how related value lists work with Squids. I hope you will find it helpful.
[download] [comments] [back to top]

Single Table Join
11/8/2005 - In April 2005, I wrote an article for FileMaker Advisor Magazine called "Add Power and Flexibility to FileMaker Solutions with a Single Table Join". It covers a useful technique for handling variable complexity in database systems. A lot of people dug the idea and wanted to see a demo file, so here it is.
[download] [comments] [back to top]

iCal to FileMaker
11/3/2005 - I have been getting a lot of interest in FileMaker/iCal integration, so here is a nifty little tool that sucks the events out of your iCal and dumps them into FileMaker. It was written for FileMaker Pro 8, so if you are still on previous version you will need to upgrade.

If you are using FileMaker Pro Advanced, you might need to edit the AppleScript to look for that application. It has been tested on OS X 10.3.9 with FMP 8.0v1 and iCal 1.5.5, and on OS X 10.4.2 with FMP 8.0v1 and iCal 2.0.2.

Please note that this was written assuming a mm/dd/yyyy date format and will need to be modified to work with other date formats (thanks to Gary Mitchell of Australia for the feedback!). Let me know if you have any trouble with it.
[download] [comments] [back to top]

Inventory Management System
9/13/2005 - In August 2005, I received an email regarding my Single Table Join article and how one might adapt it to an inventory management system. Although it could be done, I am not sure that a pure SJT model is quite appropriate for the situation because of the complex and highly specialized nature of typical inventory data relationships.

My feeling is that it makes sense to use an STJ when you need to define the roles of objects in a relationship. Typically, an inventory system only cares about one type of relationship between two inventory items. Namely, that Item A is a component of Item B. That being the case, I created a demo file that covers the basics of the recursive many-to-many inventory relationships. An article on this subject can be found here.
[download] [comments] [back to top]

Managing the Relationship Graph with Squids
9/13/2005 - In July 2005, I received an email asking a very common question: "How the heck do I name my [FileMaker] table occurrences so that they make sense?" My response to this dilemma is to break the graph into little "Squids" (zoom way out in the graph in the demo file to see why I picked that name). You can also read an article on the topic here.
[download] [comments] [back to top]

FileMaker 5/6 to iCal
9/12/2005 - In June 2005, I received an email requesting an AppleScript that took data from FileMaker Pro 5.5 and moved it into iCal. Here is an example FileMaker file with an embedded AppleScript that does just that.
[download] [comments] [back to top]