Thanks to those of you who attended the recent webinar on charting in FileMaker Pro 11.  A number of people have asked about getting the demo files used by Bob Bowers in the session … there’s a link to them below.

Download webinar files.

This coming Wednesday, May 12, I’ll be presenting a webinar on the new charting features of FileMaker Pro 11. This event is part of an ongoing series of webinars from FileMaker, Inc, and I’m honored to have been asked to be the presenter for this important new feature.

My objective for the webinar will be to provide a thorough introduction to the new charting capabilities. (Well, at least as thorough as one can provide in under an hour.) I’ll focus heavily on the methods for specifying the data series, but I’ll also cover some general reporting principles and provide some tips and ideas to help you get started.

If you’re interested in attending the webinar, you can register here. And if you can’t make it on Wednesday (10am and 1pm Central time), a recording will be available afterwards. Hope to see you there.

Understanding the difference between a table and a table occurrence is vital when developing a relationships in a FileMaker solution.   If the Relationships Graph for your FileMaker solution never has more than one table occurrence per table, then it may not be important to understand the differences.  However, once you do understand the difference it may help you to utilize relationships in FileMaker in ways you did not realize were possible.  To understand how tables, table occurrences, and relationships work together we must understand each one.

The best place to start is to first understand a table.  A table stores information about one specific kind of thing: for example, a given table may store information about vehicles, or swimmers, or scholarships. A table stores its data in a row-and-column format. Each row represents one instance of a thing: one vehicle, or one car, or one scholarship. These rows are often known as “records” in database terminology. The columns define the common data elements stored for each instance of a thing: a car’s weight and model year, or a scholarship’s amount. These columns are often called “fields” in database parlance. A table is thus the collection of these records and their field data. In FileMaker, tables are created in the Manage Database Dialog under the Tables tab. All database data in FileMaker is stored in tables.

A table occurrence is a visual representation of a given table in the Relationships Graph.  The best way to think of a table occurrence is as a window or point of view into the actual table. This window or viewpoint can be used to see and/or manipulate the data that resides in the table.  Layouts, for example, are based on a specific table occurrence and can access the data in the specific table occurrence or any data related to that table occurrence.  The use of these table occurrences will be easier to understand once we look at relationships.

Finally, what is a relationship?  A relationship is way of reaching data in one table based on the information that resides in the records of another table.  This is done by linking one or more fields in one table occurrence to one or more fields in another table. For example, you might link a customer ID on an Order record with a customer ID on a Customer record. That’s a way of saying “this order belongs to that customer.”  Another way to think about this is that relationships are a pre-specified query from one table occurrence to the next.  The relationship we just described, for example, would also allow you to find all orders for a given customer. By using these relationships we can report on data in other tables and also display their information through portals from the standpoint of the current table occurrence.

Now that the definitions are out of the way, why are these tools useful?  And why does FileMaker allow more than one table occurrence per table in the relationships graph?  If a relationship is like a pre-specified query into another table, there are times when we need to search for data in one table in multiple ways.  That is why we need table occurrences.

Imagine a straightforward Company-Contact Management System.  We may have the following tables: Company, Contact, Address.  The way these tables are related is as follows:
- one Company can have multiple Contacts
- one Company can have multiple Addresses
- one Contact can belong to only one Company
- one Contact can have multiple Addresses, and these addresses may not need to directly relate to that contact’s company

We don’t need to create a table specifically for Contact Addresses and another for Company Address to implement this solution.  We can simply create one Address table and have it appear as two table occurrences:
- Contact Address will be a table occurrence that is based on the Address table and directly relates to the Contact table occurrence
- Company Address will be a table occurrence that is based on the Address table and directly relates to the Company table occurrence.

Here’s how the Relationships Graph might look. Table occurrence with the same color are based on the same underlying table.

Why not just relate both Company and Contact to the same Address table occurrence?   FileMaker doesn’t allow for loops in the Relationships Graph. A loop would exist if there were ever more than one pathway between any two table occurrences. Since Company and Contact will need to be related to each other, by relating them to Address we would form a loop.  Now, why are loops forbidden?  Well, computers aren’t as smart as we are. A computer can only follow specific instructions.  Imagine that your computer is “standing on” the Company table occurrence and you tell it that you want to see the Address that belongs to a specific Contact.  Well, the computer sees that it is ”standing on” the Company table occurrence and that there are two ways to get from there to Address. One is by going directly to Address and the other is by going to Contact and then on to the Address.  Since both ways are valid the computer cannot decide which way to take on its own.  This is why we need table occurrences. By having multiple table occurrences that point to the same table, all we need to do is to tell the computer the specific table occurrence which we want to grab the data through (in the above Graph it would be Address1).  Then the computer can go to that specific table without any confusion. Between any two table occurrences in the Relationships Graph, there will only ever be one unique path

Through this example we can see how table occurrences are just a window or way to look into the table to see or manipulate data.  We can also see that relationships are just a way in which we direct FileMaker from a perspective on one table to a perspective on another table (a table occurrence can also be thought as a “perspective on a table”).

I hope this will help people understand the difference between tables, table occurrences, and relationships.  For a more in-depth understanding of these concepts, data modeling in general, and many more aspects of FileMaker I highly recommend taking the FileMaker Training Series: http://www.soliantconsulting.com/training

The other day I was unit testing a FileMaker script that reassigned a bunch of field values in a set of records. I needed to pay close attention to the values being captured at each step, so I was using the debugger and the data viewer to keep an eye on things.

I tend to use a lot of SetVariable steps, to keep my code tidy and efficient. So in this particular case, I was primarily concerned with the values being stashed into a set of variables. But when I looked for them in the data viewer’s Current pane, they often weren’t there at all.

Sometimes this happens normally, when the value of your variable is null. Keep an eye out for that so it doesn’t catch you off guard. But in my case, I knew those values weren’t null — so where were those variables in my viewer? Every once in a while they’d show up as expected. Weird!

Finally I saw the pattern: if the data viewer was opened BEFORE the script started, all the variables showed up properly. But if I waited until a few steps into the script to open up that data viewer window, my script variables never showed.

So keep that data viewer window open during your debugging, so you get the complete view of Current expressions.

I had been closing the data viewer when I went to adjust my code, because it stays on top and tended to obscure my view.  Then I’d forget to reopen it until I was already running the test.  Once I figured out the behavior, I started resizing the data viewer window instead, shrinking it and moving it on top of my debugger window while I edited my script, to keep it always open but out of the way.

There is a rather obscure but very useful feature available in one of the FileMaker design functions. The function FieldNames ( fileName ; layoutName ) ostensibly returns a list of the fields on the requested layout in the specified file. Nothing surprising there, but what if the layout you specify does not exist in the file? Aha! That’s when some interesting behavior kicks-in.

If, instead of passing the function FieldNames a layout name for the second parameter, you pass it the name of a table occurrence, FileMaker will return the full list of fields for said table occurrence. Fantastic! Of course, to make use of this behavior, you cannot have a layout with the same name as the table occurrence. So here’s one of my FileMaker development best practices…never, ever, EVER name a layout with the same name as a table occurrence.

Strangely enough, when you make a new table FileMaker creates a layout with the same name as the table occurrence. Do yourself a favor: chuck the layout, or at least rename it, and add a neat new function to your developer toolkit.

In a future posting to be titled “Generating Field Objects via Script and XML”, I’ll explain how you can leverage the FieldNames function to manage development layouts. It will include a great demonstration file that you won’t want to miss.

Dawn Heady is a Technical Project Lead for Soliant Consulting. During her 20 years as a FileMaker Developer she has been a business owner, a CIO in the credit restoration industry and the Vice President of a company specializing in FileMaker plug-ins. Dawn is a FileMaker 11 Authorized Trainer, a regular DevCon speaker, and a FileMaker 7 Idol finalist for her solution “Interface-RAD”.

Conditional Formatting is a FileMaker layout-level feature that allows you to customize the appearance of text-based objects. You can use Conditional Formatting to dynamically control the font, style, size, text color, and fill color. The conditions used may be based on the text object’s value or on the result of a calculation.

Since applying Conditional Formatting to multiple objects works differently than similar FileMaker features, there are a few important points to keep in mind when working with Conditional Formatting:

1. Unlike Buttons or Tooltips, FileMaker 10 offers no Layout Mode visual indicator of Conditionally Formatted objects. [Note: a visual indicator was added with FileMaker 11.]
2. When you select two or more objects and attempt to apply Conditional Formatting, the dialog will display the Conditional Formatting settings of the frontmost text-based object. This is true regardless of the Conditional Formatting previously applied to the other objects and whether or not the objects are grouped.
3. As long as at least one of the selected objects can have Conditional Formatting applied to it, the dialog will display. Of course, the Conditional Formatting will only be applied to the appropriate objects.
4. The Conditional Formatting dialog gives no indication that this feature was previously applied to other objects currently selected. This is different from the behavior of Field Borders and Anchors, which each display a horizontal line in the dialog checkbox when settings vary for the selected objects.
5. Should you proceed to make changes in the Conditional Formatting of multiple selected objects, all text-based objects will receive the new formatting and any previously specified Conditional Formatting settings for these objects will be lost.

So what does this mean for us?

It probably goes without saying, but I’ll do so anyway…be VERY careful when applying Conditional Formatting to multiple objects at once. It is very easy to blow away the conditional formatting of objects.

The Database Design Report that you can generate (using FileMaker Pro Advanced), documents the conditional formatting applied to layout objects. Check it or test each object’s Conditional Formatting individually before changing a layout’s Conditional Formatting.

There are a few measures that you can take to combat the lack of visual indicators on Conditionally Formatted objects. In fact, it is fairly simple to create your own visual indicators. You can display notes on screen containing reminders of the Conditional Formatting applied to the layout. Then apply Conditional Formatting to the note that will hide it from view in all modes except Layout Mode.

You can even create a homegrown function much like the “Show Tooltips” menu item. Add a final condition to each Conditionally Formatted object with the formula $$HIGHLIGHT_CONDITIONAL_FORMATTING. Set the fill color to bright yellow or some other obnoxious color. Then use a script to toggle the global variable, and thus the obnoxious fill color, of all Conditionally Formatted objects.

Of course, one developer’s limitation is another developer’s feature. We can choose to use these unexpected behaviors to our advantage. For example, let’s say you intend to apply Conditional Formatting to all the fields of a portal. You can just tweak the frontmost field and once you are satisfied with its Conditional Formatting, select all the portal’s fields and quickly apply the frontmost field’s Conditional Formatting to all the selected fields.

You can also remove Conditional Formatting quickly from multiple objects by ensuring that the frontmost of the selected objects does not have Conditional Formatting applied.

Conditional Formatting is a powerful feature, but developers should proceed with extra care and caution.

Dawn Heady is a Technical Project Lead for Soliant Consulting. During her 20 years as a FileMaker Developer she has been a business owner, a CIO in the credit restoration industry and the Vice President of a company specializing in FileMaker plug-ins. Dawn is a FileMaker 11 Authorized Trainer, a regular DevCon speaker, and a FileMaker 7 Idol finalist for her solution “Interface-RAD”.