by Matt Petrowsky, <matt@filemakermagazine.com>

A User file for Login Procedures
BONUS: LOGIN.FP3 & USERS.FP3
PLATFORM: Mac/Win

One powerful features of a database system is the ability to monitor and control access assigned to users at login. One of the weaknesses of the built-in security and login process of FileMaker is the inability to log usage activity. With a log, you can track who has entered the system and when they come and go. The built-in support for passwords and login with FileMaker Pro is without specific user control. You can create basic passwords for specific groups but beyond that you don't know who is logging in and when. The database won't tell you that Mary logged in on the weekend, sacrificing her own time for the completion of a crucial project.

This article demonstrates a method where you can use the user name feature found in FileMaker Pro preferences in conjunction with a user file to track when a user logs in. With this approach it is not necessary to make the user log in a second time; tracking of login and logout can be transparent to the user.

Where to Start?

There are a couple of features of FileMaker Pro that you need to be aware of that make this solution possible. The first, User Name, is in the Application Preferences for FileMaker Pro and is found under the General section. On a Macintosh, the User Name can be drawn from one of two locations. The first option is to take take the user name from the name that is given to the computer. This comes from the Sharing Setup control panel. The second option is a custom setting. This custom setting must be selected via a radio button in the Macintosh version. On Windows, this custom setting is the only option. It is the value that is stored in this preference that is used whenever you use the Status(CurrentUserName) function in a script or calculation.

Using Status functions require that you set them to be unstored so that they will refresh. But, in our solution, we'll be using a startup script that will check to see what the Status(CurrentUserName) is and whether it matches any of the users in our USERS.FP3 file. If there is no match, we give the user a dialog that suggests that they contact the system administrator so that their name can be added to the USERS.FP3 database.

The concept is quite simple and very powerful for controlling database access. Making this solution work can be a little tricky. It requires the use of a startup script, validation scripts and a closing script. The closing script with additional reporting structure can reveal time spent within the database system. Specification of startup and closing scripts is set under Document preferences.

The Key Parts

We'll obviously need a USERS.FP3 file to store the names of users who work in the database. It is important that the user name specified in FileMaker's application preferences exactly match the entry in the USERS.FP3 file for the solution to work properly.

Another interesting element of this solution is the self-join relationship. Because you are checking the Status(CurrentUserName) you need to set that name to a field and compare it to the entire database to evaluate whether there is a matching value. To accomplish this, the first part of the LOGIN script sets a global field to the Status(CurrentUserName). Once the field is set, we'll need to evaluate a calculation utilizing the count function to see if the count of records with that user is empty or not. In other words, only users with a record in the user file should be logging into the file which makes the count one. Here is the structure of the self-join relationship:

Relationship Name: USERS_SelfRelate_User Name
Match data from field in current file:            With data from field in related file:
USERS.FP3                        USERS.FP3
User Global            =        ::User Name

If there are no occurrences in the User Count field, then you know that the user name specified in the application preferences is not a valid user name. Part of the LOGIN script in the USERS.FP3 file looks at this fact. When there are no occurrences, then the script will show the user some type of message and you may choose to shut the files down so that the user cannot gain access.

Minor Customization & Tweaking

There are many tweaks that can be applied to this technique to make it more robust. One could be to use an additional field named Access Level in which you could associate a numerical value that is specific to a user's access. By checking this field before performing certain scripts, you can control where users can go and what they can do in the database.

Another addition that can be made is a separate log file that logs each of the users sessions and even what they do while they are in the database system. While this may be extreme for most database systems it can provide a valuable tool for environments that need to keep track of what users do to their mission critical data.

One of the caveats to the way that the bonus files are structured is the fact that the login/logout log is maintained in a FileMaker Pro text field. If you were not previously aware, there is a 64000 character limit in text fields. After this limit has been reached, the field will no longer track the login and logout correctly. Using a separate log file would solve this problem.

A User Log Made Easy

This article and the associated bonus files demonstrate how you can incorporate a valuable login and logout technique that takes advantage of the built-in FileMaker Pro user name feature. Since the user name is stored by FileMaker Pro itself, it eliminates the need for a passworded login and still tracks what users do in the database in terms of logging in and logging out.


## END ##