by John Mark Osborne, <jmo@best.com>, http://www.best.com/~jmo
and Marie P. Folker, <editor@iso-ezine.com>, http://isoproductions.com
Record Locking
Allow your users to see only the records they created
BONUS: RECLOCK.FP3
PLATFORM: Mac/Windows
Ever since FileMaker Pro 2.0 came out folks have been asking for a good way to let each user see only the records they created. By using relationships and a script or two, we can accomplish this seemingly impossible task.
A note about this solution--it is meant as a springboard rather than a complete, ready-to-implement solution. The method is sound but you will want to do some serious thinking about the experience your users will expect.
A Preamble
This is a pretty complex setup which involves alot of elements working together. We are going to start out by giving you an overview of the solution and then get down to the nitty gritty of how it works.
When a users starts out with this database, they must enter their username and a password. Whenever they create new records, their username is entered in a creator field. Later, when they want to access their records again, their username (that they entered to get into the database) is compared against the creator field. As the user flips through records, they are viewing related fields rather than actual fields. Why?
A self-join relationship is being used. A self-join is a relationship that relates a field in one database to a field in the same database. Not only that but the fields used in the relationship are concatenated. When a record is created, a calculation joins the creator name with that records serial number. Another calculation joins the username with that records serial number. If they match, you get data in the related fields. If they don't, the related field are blank. Magic!
The scripts come into the picture to make sure that users don't wander amok through the database and to handle the mechanics of user logins and password checking. There's also a nifty little trick in the Next and Previous scripts used to navigate through the records. A Skip Records radio button allows you to choose whether users can only their own records or all records with sensitive data filtered out.
Let's Get Down To Business
As I promised, we'll go through the details of making this solution work. First, let's talk about the fields you'll need to create.
Current User (Global, Text)
Password (Global, Text)
Creator Name (Text)
Serial (Auto-Enter Serial)
Global Match Calc (Calculation, Unstored) = Serial & Current User
Match Calc (Calculation) = Serial & Creator Name
You can probably guess how these correspond to our brief outline of the solution above. You'll also need to create a relationship. As we said before, the relationship will be to the file you are in (RECLOCK.FP3). Relate the Global Match Calc field to the Match Calc field.
Global Match Calc =:: Match Calc
This relationship works the magic that allows us to information only when the Current User and Creator Name match.
Putting The Solution Together
To start seeing how this solution works, you'll want to put a few fields on a layout. Let's start with the Data Entry/Data View Layout. You'll want to put whatever fields on the layout that are acceptable for everyone to see in the normal way you would put fields on a layout. In our solution file, it seems reasonable that anyone can see the name field and the Creator Name field (the manager in our example). That way, they can see a list of who works for a particular manager. The salary and phone number fields are sensitive information and shouldn't be viewable by anyone. Those will be handled a little differently. The fields will be added to the layout as related fields.
Records::Salary
Records::Phone
For the sake of seeing how this solution works, we've added a few behind the scenes fields to the Data Entry/Data View Layout as well. When the solution is really put in action, the user is locked out of everything. The makes it a little hard to see what is happening so we start you out on this layout without the lockouts in place. When you are ready to see a minimal setup for this solution, click the Run in "Real" Mode button.
The fields in the Behind the Scenes area on the layout tell the story. When a user logs in to the database, they will be prompted for a username and password. These are entered into the Current User and Password fields. When experimenting with solution, you can simply type values into the fields displaying in the Behind the Scenes Area. You can use the following values to try things out:
User=Bob, Password=opensesame
User=Marie, Password=marge
Once you've entered values in Current User and Password, you are ready to try things out. If you were really running this solution, you would be shuffled from a layout where you can enter the username and password to this Data Entry/Data View Layout. You would not be able to choose virtually any menu items. You also wouldn't be able to access the Status Area to switch layouts.
Now that you have effectively "logged in," take a look at the Salary and Phone fields. If the value in Current User matches the value in Manager, you can see them. If they don't, you can't. The mechanism can be seen in action by watching the Global Match Calc and Match Calcs which are visible in the Behind the Scenes area.
Scripts Control The Solution
Now might be a good time to take a look at the scripts which actually run the show. A script would normally run as soon as the database is opened. It has been disabled in this solution for easy examination. The script that runs when you click the Run in "Real" Mode button starts things up. That script is Go To Layout Check Password:
Allow User Abort [Off]
Toggle Status Area [Hide, Lock]
Set Field ["Current User", """"]
Set Field ["Password",""""]
Go to Layout ["Main"]
Go to Field ["Current User"]
Loop
Pause/Resume Script
End Loop
First, we lock the database up using the Set User Abort and Toggle Status Area script steps. Next, we make sure the Current User and Password fields are cleared out. Finally, we switch to the Main layout where the entry fields for Current User and Password are located. The Go to Field step drops us in the Current User field so that we're ready to start typing. Finally, the Loop at the end of the script ensures that our Allow User Abort [Off] and Toggle Status Area [Lock, Hide] stays in force through our database experience (unless of course you find our secret escape button).
The next script to come into play runs when we click the Login button. This runs the Check Password script:
If ["(Current User="Bob" and Password = "opensesame") or (Current User="Marie" and Password = "marge")"]
Go to Layout ["Data Entry"]
Else
Show Message ["Your Password is incorrect!"]
End If
The only real mystery should be the calculation in the If statement and that shouldn't remain one for long. In this solution, you need to hard code the possible usernames and passwords into this calculation (and one other that we'll see later). First, we check to see if its true that both Current User="Bob" and also that Password="opensesame". Both have to be true--that's the way ANDs work. If they aren't both true, we've got one more shot. The calculation then checks the "Marie" name and password. Again, both have to be true. Either pairs of values can be true and we get in--that's the way ORs work.
In With The New, Out With The Old
Someday, your users will want to add records and someday they will want to delete records. Since the scripts will lock the users out of menu actions like that, you will need to provide that capability for them. Check out the New Record and Delete Record scripts. They should be pretty straightforward after looking at the previous scripts. Just remember that your users won't be able to do much on their own so the interface will have to do everything for them.
New Record Script:
New Record/Request
Set Field ["Creator Name", "Current User"]
Exit Record/Request
Delete Record Script:
If ["((Current User = "Bob" and Password ="opensesame") or (Current User = "Marie" and Password ="marge")) and (Current User=Creator Name)"]
Delete Record/Request[]
Else
Show Message ["Sorry, this is not your record, you may not delete it."]
End If
Again, the only mystery here is that calculation. Its the same as the previous one we explored with a small addition. Everything we said before stands but an additional AND has been added. Not only does one of the two pairs need to be true but also Current User must equal Creator Name. This assures that only the owner of a record (the manager) is able to delete a record.
We're In The Home Stretch Now
Did I mention how to get out of this solution once you start it running in "Real" Mode? We have graciously provided the All Stop! button which will stop any scripts that are running and give you access back to the menus and Status Area. The biggest caveat we can offer about creating a solution like this is beware Allow User Abort [Off] and beware Toggle Status Area [Hide, Lock]. We've both locked ourselves out of more solutions that we'd like to admit. Enjoy!
## END ##