ISO FileMaker Magazine

Here's yet another video in this series which doesn't have much to do with the Separation Model. However, it's a really fun video because it deals with something so perfectly usable that it'd be a crime not to use it within your solution somewhere - barcodes.

You see, humans are prone to error and you can almost always reduce error when you use something the machine can both generate and read.

Barcodes have been one such answer to this "human error" dilemma for many decades. Even the most simple of barcode implementations can save many painful hours of mundane data entry. With a little creativity, I implemented inexpensive student ID badges for easily tracking attendance.

In this video, I discuss my super simple implementation of using barcodes within the KarateApp solution and give you a good deal of information about how you can implement in your own specific ways. With scanners and barcode fonts being both cheap and easy, it's a no-brainer for use in any FileMaker solution!

Reporting is one of the most field hungry things you can ever do within FileMaker. So, what do I mean by field hungry? Well, take your standard timestamp value for example. It's composed of many different pieces of information - many you may want to report on. It has a day name, day value, month name, month value, month number, year number, hours, minutes, seconds and maybe even a time zone.

WOW! That's a lot of extra fields. I count at least ten different additional fields. I'd really hate to add even a portion of these extra fields to any data table which requires them for reporting. It's just a lot of extra "cruft" in my data file.

So what are the options? Well, to give you a hint, you can easily create a "Reporting" file and do everything within that dedicated file. In fact, in many cases this is a very safe and sound solution.

Then again, we have to take into account what our deployment model is. This is really the determining factor of how we're going to report. Is the UI file on the client device or being shared on the server? Do we want to maintain older reports so they can be printed in the future? Are we going to save a PDF version, save the data or simply use the original data? Will the original data change?

What about what's needed in order to print the report? How about security and access? Should all users be able to see, access and print all reports in the system?

These are all questions you'll have to ask of yourself when designing your own system. In this video I give you a ton of solid information about how printing might work in the Data Separation model. Give it a watch!

In this video in the series, we're again laying some groundwork for future videos which deal with "separation issues". The content covered isn't super specified to the Separation Model, but does provide a wealth of knowledge about integrating a vital feature which is inevitably added to most any database - calendar widgets.

Even if your database never deals with scheduling events, you're going to need to filter out information based on dates, times and date/time ranges - if only for reporting purposes.

In order to make this happen, you'll often need some form of a calendar widget. Yeah, FileMaker does provide a great little calendar widget which can be used right on fields - and you may be able to get by with that alone - but really, "Why stop there?". Once you've implemented the calendar widget solution from this video, you may be hard pressed to use any other method. It's universal, it's flexible and quite powerful. Here's a hint, "Use a 'Calendar' utility table." Watch the video for more useful information!

For anyone who has worked with FileMaker for more than few years, the limitations found in the sorting script step are simply "the way it is". You can't dynamically specify either a sort field or the direction based on a calculated value or variable - like you can with the output path of an export for example.

These limitations have always been something to work around and overcome. Over the years, I've used many different methods for sorting. They typically always centered around the script which was used for handling the sort.

It's always been easy to add as much logic as required to a script, but making things as easy as possible and portable has always been the holy grail. I mean really, who wants to keep coming up with a new way to manage sorting list views and portals when you've got a solution to build. This stuff should just be built in.

Of course, part of the fun and appeal of FileMaker is the fact that it's like a puzzle and it always seems to present that worthy little challenge. So consider this video an answer to that challenge.

In this video, you'll learn, and have access to, what I consider to be one of the most elegant ways to managing sorting list views. If having your list views (and portals) provide the same features that every desktop user has grown used, (e.g. sorting columns and indicating direction) then you owe it to yourself to get this one down. It's just way too elegant and way too easy to implement once you've got the basics!

In all likelihood, the only database you'll provide [Full Access] for all users is the database you ONLY use for yourself. Otherwise, we're going to be adding security - plain and simple.

In the Data Separation Model, there's a number of options which all depend on how you're going to deploy. If you're going the low cost route, then using External account authentication, by far the easiest method, is not going to be an option. External authentication requires FileMaker Server and preferably (although not required) a deployment of either Active Directory (Windows) or Open Directory (Macintosh). So, understanding how external authentication compares to using FileMaker's built-in accounts is critical to your deployment strategy. Using the low cost route means you need to get a few things right when using the Separation Model.

In terms of implementation, this will very likely employ the use of a default username and privilege set for the UI file, while the data file retains the collection of usernames and passwords which protects the data.

In this video, I take a fair amount of time to walk through the various settings and areas which you'll need to be familiar with. I discuss what to watch out for, and how the separation model will impact your security. If you've not taken the time to really understand what FileMaker is doing, from a security standpoint, then this video will have some great pointers for a better understanding.

When I was younger we frequently went camping. This trip felt like we were camping, but I forget if this particular trip was a planned camping trip or just an outing where my Father was helping a friend with chopping up some felled trees due to clearing some land. I remember being so excited about being able to take the small hatchet we had always kept by the fireplace.

I was finally old enough to wield that cool looking ax. The one I had always wanted to snatch up and start swinging around like some crazed arena fighter. Sharp, shiny, hard and heavy, I knew this was a cool tool (especially to boys), and I wanted to use it about as badly as that cool sword shaped letter opener my Grandfather had.

We get to the site, and I start whacking like crazy at the myriad of trunks scattered about the ground. Despite my careless abandon, I remember the incident to this day. My Father was helping out this friend who had not worked with a chainsaw before. While the unknown horrors could be imagined that this friend lost a leg or something worse, it was a bit more mundane, but still a lesson to be learned.

You see, while using a chainsaw, if the tip catches on something, even the log you're sawing, the chainsaw can kick right back into your face. While this was a very minor kickback incident (no injuries this time) and turned out to be a good lesson, I remember it well - as my Father told us both about how to avoid this potential hazard.

Switching over to the world of FileMaker and the design of your database, there are a number of things in your solution which could become hazardous kickbacks - although none that will physically take off a leg. The topic of this video covers one of those potential issues and shows you not only how to avoid the issue, but how to approach your development. You have to think about every action and its consequences.

The current state of the example KarateApp database leaves us with a problem and a solution which will help you in many other similar situations. This video is certainly one worth watching if performance impact is of interest to you!

FileMaker has a few unique features because of it being its own database. Unlike SQL, where you make selections across multiple tables (which FileMaker does support the use of some SQL - via a plugin), there's this concept of multi-keys for the purpose of relating many to many via one single record - or via one single field actually, it's known as a multi-key field.

Knowing about this unique feature, and using it when it makes sense, is something you can take advantage of. So, the trick is knowing the difference between when to use a multi-key relationship versus using an actual join table.

In this part of the series, I showcase how I interrelate a group of people into a grouped family. I do this without using a Join table because the situation is such that the records which relate together are pretty static. Once the relationship is made it's pretty much permanent. I also show you one of the most powerful custom functions you'll ever use when using FileMaker's multi-key feature. It's something you'll use often once you see how useful multi-keys can really be.

There are a lot of reasons to love using web viewers for the purpose of data display. Your data can be presented in a more compact fashion. You can style data in such a way that it looks like it's part of FileMaker. You can add in functionality easily, which may be more difficult using plugins - such as the ability to initiate a VOIP phone call right within the web viewer. Quite simply, a web viewer is always updating and being refreshed - and offers one of the most flexible ways to present data.

The scary part, which really (and I mean REALLY) shouldn't scare you at all, is HTML. If you're creating anything in FileMaker then you simply can't be ignorant about HTML/CSS. Beyond HTML, if you really want to harness some of the true power of a web viewer, then you learn a bit of Javascript and discover all the magic you can do with power tools like jQuery.

In part 5 of this series, I showcase how I use the web viewer to present the data being collected in the Attributes table. I show you how I extract the data and discuss why it's so flexible. While the effort to display in a web viewer may be a bit beyond simply adding a field to a layout, the presentation options is what makes it so appealing. Data displayed within a web viewer can optionally be shown based on access and hiding it is much easier than hiding a field on a FileMaker layout (which can't even be done unless using some sort of portal trick).

It was 2002, and I remember working with FileMaker 5/6 and the concept of the Data Separation model being discussed at the annual FileMaker Developer conference. I remember thinking, back then, that FileMaker just wasn't the tool to even consider this concept - even though you had multiple files for one database. While an avid FileMaker user/developer, I was rapidly becoming familiar with PHP 4 and knew that true separation was similar to what you found in a PHP setup - a backend (mysql), middleware (php) and a frontend (web browser). FileMaker always had to have those "extra" calculations somewhere in order to make your user interface even halfway decent.

Today, however, it's a completely different situation. In fact, I might go so far as to say the Data Separation model should be your first consideration when starting any new FileMaker solution. The reason is pretty simple - it's even more possible today, than ever before, to keep things clean and organzied. There are functions and methods to keep your data file close to 100% clean of extra fields, calcs, scripts and other "extra" stuff that just comes along with developing in FileMaker.

In this video, I showcase how I address one of my own self-imposed problems and also remind myself of the one custom function which, when used with script triggers, can answer a ton of the issues you have when wanting to display things in the interface. This especially applies when you formerly had to add a lot of that "extra" stuff to make your FileMaker solution work the way you want.

If you're missing that one piece of knowledge keeping you from a clean data file, then this video will certainly have what you need!

In this Part 3 about the Separation Model, we head into the actual structure of the data. I start to address the issues you're faced with when trying to keep the data file as clean of "extra stuff" as possible. One REALLY good thing to ALWAYS remember is that adding more stuff to your FileMaker file means more to manage (and re-figure out) later on.

When it comes to using the separation model, you've always got a few things to worry about. The first of which is creating related records. As with all things, there's always more than one way to tackle something. Certainly, you can use FileMaker's default method of adding related records by simply throwing a portal onto the layout and connecting a data table occurrence with another data table occurrence. You could also continuously add fields until your tables have thousands of them (hint, not a good idea).

Within this project, I've opted to use FileMaker's Obligatory Create (yes, it's my own made up term for what FileMaker does). This is in contrast to adding a more generic record creation script within the data file and having that script report the id of the newly created record. The later approach is a method which I first saw at Pause on Error in Portland where the MVC (model/view/controller) was the topic of focus.

Using a Global Transfer method, in conjunction with an Obligatory Create, you gain a number of advantages. You can pre-validate your data, ensure requirements have been fulfilled and apply any data formatting prior to actual storage.

These are the topics discussed in this video, plus anything else related to moving the solution forward. One interesting thing to note is that nothing is permanent within your solution. When you start to investigate the files, try to find where things aren't quite working and see if you can figure out how you would solve them. Hint, the counts for items within the filtered portals on the Edit tab are not functioning properly. They'll need to be fixed.... which is coming up in the next video in this series.