A READER ASKS: A Robust Role Based FileMaker Solution

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).

I just watched a few of your FMP tutorial videos on your "FileMakerThoughts" YouTube channel -- thanks for them, very helpful.I hate to impose on your time, and I know just blanketing you with a not-so simple FMP question is not all that proper, but truth is, I haven't been able to get any responses from the FMP Forum, and I'm dead tired of reading my FMP 10 Bible (no matter how great and thorough it is) and viewing Lynda.com videos (again, as good as they are). Problem is, I know just enough to get me in trouble. Let me quickly explain...

I know from reading on the internet that my problem isn't unique. In fact, it appears to be quite common. But I cannot find any tutorial that tackles the problem in FMP. I want to create an FMP database for my division's program. Further, I want to normalize the data as best possible (see, I know a bit too much already and I think it's getting in the way of progress). Essentially, I want to develop this DB correctly, the first time.

I wish to have a single PERSON entity table that contains general data (FirstName, LastName, etc.). From there, I need to make each of those people assigned to roles (STUDENT, FACULTY, ALUMNUS, PROFESSIONAL, VISITING LECTURER, etc.). Being assigned a role attaches various related data. For instance, if you are a STUDENT, the Student Record layout would show related ReviewScores in a portal.

I've attempted to complete an ERD, but I cannot get past having a bunch of 1:1 relationships to solve the roles, which I know is a no-no in general and not proper in FMP.

I get TO's (somewhat) and I am fairly comfortable getting around FMP.

So (he asks humbly...), what's the best way to set up this approach in FMP?

Or is it not correct (?).

Essentially, I don't want to create a bunch of tables for each role (that just splits up PERSON). I'm not crazy for thinking this way, am I???

Some specs:

- A PERSON can be part of one or more roles at a time (i.e. STUDENT, FACULTY, ALUMNI, PROFESSIONAL)
- Each role has associated data connected to it (i.e. ALUMNUS has "GraduationDate"; FACULTY has "HireDate" and related Advisees).
- A STUDENT will be an ADVISEE of a FACULTY.

It gets really confusing when I need to create value lists that only dynamically show those in a particular role, as when one would select the proper FACULTY ADVISOR on the Student Record screen.

Again, with all of these roles "in" the PERSON table, am I now in the strange world of self-joins (that I find so difficult to get my head wrapped around)?

Any assistance / help is greatly appreciated.

I'm not sure how much help I can be on this one either. I would have to spend some considerable time with your solution to recommend something and (with the day job) I just don't do that level of support these days.

Honestly, I simply do not agree with some of the viewpoints about normalization from my fellow seasoned developers. In fact, I think the conversations about normalization do as much harm as they benefit the FileMaker community. The SQL world rules of normalization simply are not absolutes in the FileMaker world.

It sounds like a reasonable approach is to have a join table that has every unique combination of a person and a role. Then you can join this to your staff table and on startup, you can write a routine that finds a person's staff record and then locks them out from viewing any other records. You can have relationships flow through the join table so that it filters associated options on the other side.

Off the top of my head, I can also think of a half dozen other ways to go about doing this including multiple line key fields, security schema using extended privileges and even a heavily scripted routine.

I would recommend that you do some experiments with a new file and work out the kinks there first. Then you can migrate the working code into your solution. Take a peek at some of the FileMaker sites that have a large number of example files like John Mark Osbornes databasepros.com (http://databasepros.com/resources.html).

You might also try the new free FileMaker Technet (http://www.filemaker.com/technet/).