Build a FileMaker Pro User Account Management Solution, Part 1

 

Manage account creation/deletion, account disabling, passwords, and privileges for multiple users across a multi-file solution.

Figure 1: Accounts Main, the main account management layout — This FileMaker Pro layout uses View as List, rather than a portal to display the accounts.

Figure 2: A modular solution — The Master File contains the account management layouts and most of the scripts. The remaining files mirror each other exactly with respect to privilege set names, the utility layout, and the five function scripts.

Do you ever look back with fondness on the days when adding a new user to your database required you to ask all users to quit FileMaker Pro, then shut down FileMaker Server so you could re-open the database directly in FileMaker Pro, then enter that new user in every single file of your complex, file-laden solution? FileMaker Pro 7 brought an end to the not-so-good old days of FileMaker Pro account management. Now administrators have full access to accounts and privileges for files hosted by FileMaker Server 8 or FileMaker Server 7, and even better, it’s now possible to manage accounts using scripts, which makes it a lot easier to maintain multiple user accounts across a multi-file FileMaker Pro solution.

This three-part article series shows you how to create an account management system that takes a little effort to put together, but it’s highly portable and after you’re done, you’ll be able to move it to any database solution you create with little modification. In fact, with FileMaker Pro 8 Advanced, you can cut and paste all the necessary components into any solution in short order. In the long run, creating a system such as the one I describe can save you a lot of time if you have a large number of accounts to manage, and it ensures consistency across files. If you step back from the instructions, you’re also likely to learn advanced techniques for creating modular, reusable routines you can apply to a host of other database architecture and programming problems.

The first article of the series includes a brief primer on scripting account management, an outline of the account management solution this article will help you create, and an introduction to the interface for the solution. The second and third installments will provide a detailed look at all the scripts involved with the account management solution, giving you insight not only into account management, but also into segmenting scripts and structure to maximize efficiency and make your solutions more portable.

It’s possible to create and authenticate accounts outside FileMaker Pro using Active Directory in Windows or Open Directory on Macintosh, but often this approach isn’t viable for various reasons. This account management system is for administrators charged with managing multiple accounts across multiple files. Although it isn’t a user solution, you can adapt the techniques and principles I’ll show you to create a scripted, user password change routine. Finally, this solution is designed for a single administrator to manage many accounts at a given time, but you can adapt it for the unlikely scenario that more than one administrator must create and manage different accounts at the same time.

What’s possible

When you’re finished putting together this solution, you’ll have an interface for account creation and deletion, account enabling and disabling, password resetting, and even changing user privilege sets. The only Accounts script steps I don’t cover in this article are Re-login and Change Password; the latter provides an interface for users to change their own passwords, whereas this solution accomplishes something similar with the Reset Account Password script step. Figure 1 shows the main account management layout that gives an idea of the finished solution. It isn’t difficult to adapt the solution to batch account creation and deletion, and as I mentioned, you can apply what you learn here to creating an interface for users to change their passwords.

I’ve designed this account management system to handle groups of accounts that fall within uniform privilege sets. You must mirror privilege set names — but not restrictions — across all files in your solution. Accounts fall under privilege sets that share the same name across all files, so if you want a particular user to have special privileges this model can’t handle, you must manage that account manually. You can handle the vast majority of accounts and privilege sets uniformly, so this task shouldn’t pose much of a limitation.

It isn’t possible to use scripts to add, delete, or change accounts attached to the [Full Access] privilege set, but fortunately, most solutions only have a couple of full access accounts, so you must manually create them in each file even if you use Open or Active Directory to authenticate the other accounts.

The approach

The order in which you construct this solution makes a big difference in how quickly you get to the testing and deployment stage. Before I get to the details, here’s a brief overview. First, you choose a Master File in which you create an Accounts table that lists the account names in your solution. Then, create file references to that Master File in each of the files in your solution, along with a “utility” layout in each file that displays the fields in the Master File’s Accounts table. Next, create privilege sets in each of your files, then create the user interface layouts in your Master File, along with “control” scripts to run the solution. Finally, copy five key scripts into each of your files. Figure 2 shows a graphical outline of the solution.

Figure 3: Accounts Add layout — Notice it’s nearly identical to the Accounts Main layout, but the buttons you use to control the account creation process are different.

Figure 4: Accounts Change Privilege Set layout — A script switches to this layout to change the privilege set to control the process.

Details

The first step is to choose a file in your solution as the Master File for account management — the file that holds an Accounts table, the main scripts, and the user interface. It doesn’t matter which file you choose, but if you have a file that already contains most of your interface, that one might be a good choice. After you’ve chosen a home, create a table named Accounts for your account fields. It’s up to you what fields to create, but at the least, you need these seven fields:

• AccountName [Text, Validation = "Unique Value"]
• PrivilegeSet [Text]
• PrivilegeSet_New [Text]
• NewPassword [Text, Global Storage]
• NewPasswordAgain [Text, Global Storage]
• ActivationStatus [Text, Auto-enter Calculation replaces existing value]
• Marker [Text, Auto-enter Data]

Set the AccountName field’s validation to “Unique value” because each account name must be unique. FileMaker Pro has the NewPassword and NewPasswordAgain fields’ storage set to Global, so users can never access the temporarily stored passwords across the network. I set the ActivationStatus field to auto-enter a calculation that changes the text to red if the value in the field is set to Deactivated. If you decide to do the same, make sure you deselect “Do not replace existing value of field” in the field Options window.

Here’s the calculation for changing the color of the text:

Case(ActivationStatus = "Deactivated"; TextColor ( ActivationStatus ; RGB ( 130 ; 0 ; 2 ) ); TextColor ( ActivationStatus ; RGB ( 0 ; 0 ; 0 ) ))

You must set the Marker field to auto-enter the value “true” when you create it — you’ll see why later.

It’s time to move to the relationships graph and create two table occurrences (TOs) for the Accounts table you just created. For each table in a file, I always create one special TO I won’t use in any relationships and that won’t form the basis of a user interface layout. I call it a Base Table Occurrence because it’s a TO that reflects the table on which it’s based, without any involvement in the logical structure of the database. This Base Table Occurrence can simplify things in ways that aren’t immediately apparent, and you’ll see one of them in this solution. For now, just trust me and create a TO based on Accounts called zAccounts (the Base Table Occurrence), and another one based on Accounts called Accounts, whose form will form the basis of the interface layouts. Now create a new layout based on the zAccounts TO called — any guesses? — zAccounts. You might want to set this layout to Table view rather than Form view. You’ll create the interface layouts later.

Now go into each file in your solution and if it doesn’t exist already, create a file reference to the file you’ve chosen as your Master File (the one with the Accounts table). Next, go into the relationships graph and create a new TO for the Accounts table in your Master File. Be sure to name the TO zAccounts; this naming convention is going to be important later. Finally, create a layout called zAccounts in the file based on the zAccounts TO, including all the fields in the Accounts table — just like you did in the Master File. Repeat these steps for each file in your solution. When you’re done, each file, including the Master File, should have a layout called zAccounts with the fields from the Accounts table in the Master File.

Create privilege sets

After you’ve created the zAccounts layouts in all your files, you’re ready to create privilege sets for users. There’s no way to avoid manually creating privilege sets in each of the files in your solution, even if you were going to use Active Directory or Open Directory for account management. Each file has different tables, fields, layouts, and scripts, so it isn’t possible for a utility to copy privilege sets across files. For the purposes of this article, there are three user privilege sets: Studio (for people who work in the production studio), Accounting (which provides greater access than Studio, but doesn’t provide account administration privileges), and Management, with full account administration privileges. You can create whatever privilege sets you need, but it’s important to name each set exactly the same in each file in your solution, even if privilege sets of the same name restrict access differently in different files. In short, as I previously mentioned, you must mirror privilege set names — not restrictions, though — across all files in your solution, and spelling counts. Accounts the system manages fall under privilege sets that share the same name across all files, so you must manually manage accounts that fall outside the norm.

It’s a good idea to restrict access to the Accounts table and fields in your Master File using Custom Record privileges so privilege sets for users who aren’t allowed to manage accounts don’t have access at all.

Another important thing to remember is it isn’t possible to script account management for accounts attached to the [Full Access] privilege set, or any of FileMaker Pro’s default privilege sets. Finally, note that in this model of an account management system, access to account management is restricted to users with accounts in the Management and [Full Access] privilege sets.

Create the interface layouts

Before creating the scripts, it’s always a good idea to create the layouts to which your scripts refer. In this case, there are three layouts: Accounts Main, Accounts Add, and Accounts Change Privilege Set. You can call these layouts whatever you want; just remember to adjust references to the layouts in the scripts accordingly. As you might surmise from figure 1, the Accounts Main layout must have these fields: AccountName, PrivilegeSet, and ActivationStatus. Notice I’ve set all three fields to disallow entry. Create the buttons, but wait to define them until after you create the scripts.

Figure 3 shows the Accounts Add layout with the same three fields, but with different buttons, which FileMaker Pro defines after you create the scripts. In this case, you should set all three fields to allow entry. PrivilegeSet uses a drop-down list that contains the names of the privilege sets you’ve defined in Accounts & Privileges. Make sure you spell them exactly as you did in Accounts & Privileges, and remember you can’t script access to accounts with the [Full Access] privilege set, so don’t include it in the list. ActivationStatus should also use a drop-down list with the values “Activated” and “Deactivated.”

Figure 4 shows the last layout you must create, Accounts Change Privilege Set. First, set the fields AccountName and PrivilegeSet to disallow entry. PrivilegeSet_New allows entry and is a drop-down list that uses the same value list you used for the PrivilegeSet field on the previous layout. There’s also a hidden global field on this layout with the font size I’ve set to one point and the color to white. Can you guess what it’s for? Hint: It’s the second field in the tab order. If anyone knows a better trick to solve the single-field-with-a-drop-down-on-a-layout problem, let me know.

I’ve laid the groundwork for the account management solution, and now that the fields and layouts are in place, it’s time to create the scripts that make it all work. In my next article, I’ll show you how to create some very tight, efficient routines for account creation, account deletion, and account activation/deactivation. In the final article of this three-part series, I’ll show you how to create the scripts for changing account passwords and changing privilege sets.