Common FileMaker™ Mistakes #1

I hope you find this article interesting and, even better, useful to you in your efforts.


After you’ve read the post, please take a moment to add a comment.  (It helps me to know if I’m hitting the target with what I’m posting about).  Anybody who comments will automatically be entered into a monthly drawing for a copy of my new book, due for release at the end of 2017.  (1 out of 10 people will win a copy)

If you have bought either of my two previous books and haven’t yet written a review on the iTunes store, please do so and then email me a copy of the review (so I know your email address) and you’ll also be entered into a different monthly drawing where 1 out of 10 will also get a free copy).

Thanks and please don’t hesitate to get in touch if I can help you.

Michael Rocharde
(303) 856-5778


FileMaker™ is nothing like a spreadsheet

I see many FileMaker™ solutions where it is obvious that the developer is used to working with spreadsheets and develop their FileMaker™ solutions the same way.

Let’s say, for example, that you have a vehicle dealership and each vehicle is available in up to 6 different colors.  The developer creates 6 fields in the database for the colors (Color 1, Color 2, Color 3, Color 4, etc) but some vehicles only come in one color so that means 5 empty fields, taking up space and not being useful or used.

On a data entry layout, this would look like this:

An old time FileMaker™ developer might use a repeating field with 6 repetitions which will work but comes with some limitations all of its own; the major one of which is that all of the data is actually in a single field and you cannot sort or break out that data easily.

In a modern FileMaker™ solution (or any other database for that matter), the colors would be in a separate table called Colors linked to the vehicle by the vehicle ID.  The relationship (using the Vehicle ID) allows for the creation of related records so that when you’re in a vehicle record, you simply create the color record by typing the color into the field or selecting from a drop down list of colors.

So let’s first take a look at the Colors table

And now let’s look at the relationship between the two tables

The important part of this relationship is in the bottom right under Colors

This allows you to add new colors directly from the Vehicle record by automatically inserting the primary key for the vehicle (_pkID) into the VehicleID field in the Colors table.  (It is also set to delete all the colors entered if a vehicle is deleted.  If you didn’t do this and then deleted a vehicle you would be left with ‘orphaned’ color records and you always want to avoid that).

All of the above options use a value list called Colors – Fixed

There are several disadvantages to this methodology but the main one is that if you need to add new colors, you have to go back and edit the value list.  No big deal but still a pain.

(This example is a one-to-many relationship in that you have one vehicle with many colors.    If that is confusing think of it as a parent child relationship where one parent can have multiple children.  The Parent in this example is the Vehicle and the Child are the Colors).

If you had lots of colors and only some of them were used for each vehicle, you would create a join table linking the vehicle ID to the Vehicles table and the ColorID to the colors table, thus allowing you to have an unlimited number of combinations and you’d only ever type the basic information in once.

So let’s take a look at the changes we would make to the database.  (Normally we would simply change the Colors table by removing the Vehicle ID but, in this example, we’re adding a new Colors table (Colors2) so that you can download the example file and play with it).

The highlighted field would be deleted and the Colors table will only have a primary key and a field for the color.

So here are the fields in the Colors2 table

Note that we’ve added a field called Constant, which is a number field that is set to auto-enter a value of 1 in each new record.

We then add a new table which we are going to call Join_Vehicles_Colors and that table will have three fields

and we now modify the relationship as shown

and if we look at the relationship

we see that we can create related records in the Join_Vehicles_Colors table via the VehicleID.

Now before we proceed, there are a few key things that you need to understand.

In the Colors2 table, we’ve created a few records

Notice that every record has a primary key (_pkID).  This is essential whether you use a formula as I’m using here (get(uuid)) or some other method but, please don’t use an auto-entered serial #, as it is too easy to end up with duplicate records.  (The primary key is a field that the user is never able to access.  If, in this example, we didn’t have a primary key and had been using the color itself for the relationship and then edited the name of the color, all of the related records would instantly disappear).

In the portal for Join_Vehicles_Colors

We attach a value list to the ColorID field

And, as you can see above, it uses the primary key and the color itself but there is one more setting that you can’t see here and that is

and that is to Show values only from second field.  (If we didn’t do this, we would see the primary key and the color and we don’t need to (or want to) see that.  (The primary key is used and that is what the field is actually set with but all we see is the chosen color).

Now let’s see how this all works.

Firstly we’re going to look at our Join-Vehicles_Colors portal with no records

Notice that we are displaying the _pkID field for the vehicle which you would never do but it is necessary to illustrate the next step in the process.

So now we click into the ColorID field and the value list of colors displays

and we make our selections

You’ll notice that the Vehicle ID of every row is the same as the _pkID of the vehicle itself.  (This is auto-entered because the relationship is allowing the creation of multiple records using the primary key of the vehicle).

In reality, we wouldn’t be displaying either key field and the portal would simply look like this

BTW, if you are using a pop-up menu (as I am here) and ever see it displaying like this

instead of the values you were expecting to see, go into Layout Mode and into the Inspector and check the setting shown

Override data formatting with value list should be checked.

You may not remember but, in the relationship graph

We had added a relationship called Add_Colors2.  This relationship goes to the Colors2 table and allows the user to add new colors directly from the Vehicle record

Note that we are allowing the creation of related records but we’re not deleting them if a vehicle is deleted.  That’s because the colors in the Colors2 table are used by all vehicles.

In the portal itself, we see just the color field where we type colors in.

On the screen, that portal looks like this

with placeholder text telling you what to type in the new field.

And we’re done.

Multimedia eBook
$14.99 at the iTunes Store

Multimedia eBook
$14.99 at the iTunes Store

Leave a Reply

Subscribe To Our FileMaker Tips & Tricks

Join our mailing list to receive the latest FileMaker tips, tricks and video how tos from Michael Rocharde

You have Successfully Subscribed!

Subscribe To Our
Video Posts

Join our mailing list to receive info on new videos as we post them

You have Successfully Subscribed!

Website Apps