It's not me, it's you. My relationship with the FileMaker Relationship Graph

It was 1997, FileMaker Pro 4 had just been released and all was good in the world. We now had a plugin API and I was about to have access to a plethora of new external functions.

As much as the plugin API was great I still lay awake at night, dreaming of a graphical representation of the relationships between my databases (tables). It would help other developers understand the structure of my solutions and provide me with a snazzy diagram to include in my system documentation for clients.

Fast forward to 2004 and the Relationship Graph was born. This is fantastic! I can create relationships by dragging and dropping lines, colour code relationships based on my own criteria and my system documentation now contains a detailed ER diagram that is generated automatically from FileMaker.

Fast forward to 2010 and my relationship with the Relationship Graph has soured. While I still appreciate the graphical representation of relationships between tables it is time consuming and frustrating to work with. Where did it all go wrong?

The scenario
I regularly work on solutions that have upwards of 50 tables. These tables are often defined in a single FileMaker file which has relationships defined using the anchor buoy method. As I'm working away I find something isn't working quite as I expected so I need to check on the criteria defined for a relationship. Perhaps I've made a typo, selected the wrong operator - whatever the cause, I need to locate the relationship Projects_Contacts_ProjectID

The problem
I open the Relationship Graph and am greeted by a small text block on the screen that provides me with a road map to where particular table occurrence groups are located on my graph. This is a hack I've developed to try and alleviate the problem of locating a relationship. I can start navigating to a particular region of the graph or I can attempt to set a world speed typing record and jump to the correct relationship.

I decide to start typing.

I type Proj and jump to the first Table Occurrence that starts with the word Projects. At least I'm in the right neighbourhood! I also use colours to distinguish between key tables so I look for the Contacts table colour (dark blue) and find a couple of relationships which I then scan through until I locate the relevant relationship.

Now this doesn't sound too bad but this is the best possible scenario as it's a solution that I've developed and I control the layout of the Relationship Graph . Heaven help me if I open the Relationship Graph to be greeted by a plate of Spaghetti served up by another developer!

Even if I've been dedicated and kept my Relationship Graph clean there is still the potential for things to go wrong. If someone changes the sort order of fields on the Manage > Database > Fields dialog this also affects the order of fields on the Relationship Graph. If the graph was initially set-up using "View By: field name" and it's now being viewed by Creation Order, the lines between Table Occurrence now intersect. If another developer decides to "do me a favour" and clean up the various crossed lines, this arrangement is saved with the graph. Switching back to the fieldname order I'm now greeted with another mass of scrambled lines.














ABOVE: Just a small small section of the Relationship graph for a complex FileMaker solution. I can zoom out and see the entire graph on a pin head or zoom in and only see 1% of the defined relationships. Like finding a TO in a TO stack?

Possible Solution
At its most simple I just want an interface to locate and edit a relationship quickly. I'd even be happy with the old Define Relationship dialog box. I understand that in some cases relationships are based on multiple fields so you can't display the left and right side fieldnames, that's fine. Just let me get to a relationship based on relationship name, check the configuration and make the changes as required.

What about the display on the relationship graph? Won't this change the appearance of objects on the relationship graph? Yeah probably, and I'm not sure exactly how this should be handled.

What I do know is this.

The year is 2010. I've just received a shiny new copy of FileMaker v11 and I'm spending too long trying to find specific relationships on the FileMaker Relationship Graph. So much so that I'm now dreaming of an interface that was found in a FileMaker version released 10 years ago.

Can someone at FileMaker Inc. please do something about the relationship graph?

Finding New Find Techniques

It wasn't that long ago that you actually received paper manuals when you purchased new hardware or software. Nowadays everything is a mixture of HTML and PDFs that you can search through when required, but who reads manuals?

Most tech savvy people will happily place user guides to the side when faced with the job of using new software, hooking up a home theater system or assembling their next purchase of flat pack furniture.

Having used FileMaker for the best part of 10 years I feel I've got a strong grasp of my favourite database platform. Imagine my surprise when a recent trip into the Help documentation rewarded me with a new appreciation of the FileMaker find function.

More specifically, while I use the Find function every day I was unaware of the way in which some of the wildcards can be used, particularly when it comes to searching on date fields.

Try a few of these gems.

Search for date values matching a specific month

1

Returns all records within January of the current year (like searching on 1/1/2010..31/1/2010 but much quicker than typing the full date range and doesn't require you to know how many days are in the month you're searching on

1/2009

Returns all records within January of the specified year, in this case 2009

Search for dates values that fall on a particular day

Mon

Searches for all dates that occur on a Monday in the current year

Wed 3/2008

Searches for all dates that occur on a Wednesday within March of 2008

While all of these searches could have been carried out manually or by script these methods are quicker to enter, much more flexible and don't require any additional calculation fields to help provide the required smarts, i.e. an extra calc field that returns the DayName of the stored date.

Further investigation of the help documentation uncovered further gems when searching on time and timestamp values.

Looking for some extra tips? Read the manual :)

All Sorted

Something to keep in mind if you're allowing users to edit and/or create records from within a list or table view is the impact a sort order has on the end-user experience.

A few key points to remember:

1. By default, FileMaker Pro stores and displays records in the order they were added to the file.

2. The Sort function lets the end user rearrange a found set of records, so they can be viewed in a different sequence.

3. The found set of records will remain sorted until a new find request is performed or the records are sorted by different criteria.

This all sounds pretty logical but have you noticed that when you add a new record to a sorted found set, the new record will be displayed in the "correct" position in the sort order as soon as the record is committed?

While this might make sense it can cause confusion for the novice user. Why? Because, as soon as their new record is committed it will move to the correct position in the sort order which might not be visible on screen. This is fine if the user was expecting this to occur, not so fine if they're now puzzled by their "disappearing" record(s).

You should also be aware of this behaviour if you're planning on duplicating a sorted set of records using a loop and the Goto Record [Next] script step or looping through a sorted portal. If the new records are being modified in such a way that they will be "moved" to a position in the list that makes them eligible for duplication you'll find yourself with all sorts of headaches.

Endless loop anyone?

When wireless becomes powerless

Anyone who supports a FileMaker solution has received one of those calls. The type that has the ability to turn your day on its head. The feature that has worked reliably for years suddenly stops functioning as intended for no obvious reason.

Me: "Has there been any change in hardware?"
Client: "No."
Me: "Any change in software?"
Client: "No."
Me: "Any other problems started occurring?"
Client: "No."

Taking a deep breath you know that you just might be about to go on a four hour wild goose chase.

In this instance the client reported that the database was behaving erratically when performing data entry. It was working ok earlier in the day but now it was missing some of the text being typed or there was a delay and letters were repeated intermittently. Initially I thought it may have been a performance issue, perhaps the processor was so busy that there was a lag in input being displayed in FileMaker.

Me: "Do you have many applications open at the moment?"
Client: "Oh, about seven things open."
Me: "Try quit a few of the applications you're not using and see if things improve."
Client: "That looks better now. I'll give it a go and let you know if the problem returns."

Fifteen minutes later and I received another call. The problem had returned.

Me: "What happens when you try entering text into another application, say a Word document?"
Client: "Same thing! Sometimes it's ok, then other times it doesn't display the text being typed. This has never happened before."
Me: "Do you have a wireless keyboard?"
Client: "Yeah..."
Me: "I think I might know what's going on here. Try replacing the batteries and call me back if the problem persists."

I never did get the call back.