Per User Preference Table

[AZ_Tech]

I am at a bit of a loss here, I am a novice with Filemaker and could really use some help from anyone that can point me in the right direction. I program a fair amount but normally use a program called Topspeed against a MSSQL database. The way Filemaker does things is a bit foreign to me. What I am trying to dois create a teacher evaluation system for a district and what I need or would like to be able to do is this. When a principal connects I would like them to only see the current year data and also only the data the applies to their school building. Normally with my normal enviornment I would create a configuration file that they would pull up and set the year and the school. Then each time the user would log in that file would load the values into global variables that would be used to filter the data. But with filemakers relationship structure I am at a loss to come up with a good way to do this. I know its because I have a small brain! Any suggestions or docs that could enlighten me would be really great. I have pawed through the forumns but haven found anything specific, that maybe because I am a bit unfamiliar with the terminology so I apologize if this has been answered. Thanks for any help.

[bcooney]

I'll try to point you in the right direction:

1. Security is where you limit a user's edit/view of data by Record Level Access rules (RLA).

2. Find is another method to create "found sets" of records.

3. Setting global fields and using the global field in the left side of a relationship allows you to filter the records visible in the child table.

Your data model will dictate what combination of the above you use.

What is your data model?

[AZ_Tech]

What I have is three buildings with three different principals, and each principal will need to do what they call observations and evaluations on each teacher at their building so I have created a table for the school and a table for teachers with a schoolid field in it and created the relationship, a table for administrators with a schoolid field related to the school table, observation table related to teachers, evaluation table related to teachers. I have a report that at this point shows observation table at this point is district wide, I created a what I call is a view or self relationship to show only observations for a building and for reports and or teachers for reports. Originally I had a year table also to start the whole thing. And it works for the most part other than the principals must select which school they are looking at etc. They must select themselves as an evaluation and the school before they get a list of teachers to pick from to do an observation. They get confused at times. I have been able to filter the lookups and only get the correct teachers once a school is selected but they don't want to see the other schools at all really. I guess what I am wanting is some way to set the year and the principal or schools so they only see current year and records that that have to do with their building where ever whenever without them having to choose. I would like to have them connect and set the current year and school in preferences and not touch it again until there is a year. If it wasn't for trying to maintain multiple databases I would create new database for the year and a building and be done with it. If my development was complete I could live with that but I would rather not. I have been experimenting with using get (accountName) to get who the user is and the pull a record in a preference table and relate off of that table but I guess the relationships don't work unless they are active on a layout which it seems the preference table would have to be the parent on the viewed layout. Most of my problem is wrapping my brain around the way filelmaker does things. Its getting easier but I have a long way to go. I don't know if any of this makes sense or not but I appreciate any input you can offer.

[bcooney]

In a system I developed, the user lands at their People record. From that "home" layout, they see portals of records that they are allowed to edit or view. Perhaps, this is the model you can adapt. All navigation is tightly scripted from the "home" layout.

[AZ_Tech]

That sounds like an Idea, not sure how you mean land at a home layout, could this be done on a per user basis? What do you mean by "Their People record" I am not sure If I am understand what you mean by model, I assumed data wise or data schema, but I have the user landing at a layout with buttons listing each operation, which when pressed for instance "New Observation" take them to a new observation layout, or where the can edit teachers, class, school etc. Which in and of itself will get complicated because I am going to want it to run on IPads and PC based machines so I am going to need to pull the device info and redirect to layouts based on the device being used.

[Fitch]

You don't necessarily need a People table, it's just a common approach to storing information about users of the database.

You are on the right track with Get(AccountName). I would recommend that as part of your startup script, you use Set Variable to stash the account name, e.g. Set Variable($$USER ; Get(AccountName) ). This makes it a little more efficient to work with. If you're going to have a portal-driven interface, you may want to use a global field, but not necessarily. Read on.

Next, you're going to want a field in all your tables that auto-enters the account name upon creation, let's call that field "Creator." You may have to manually update this field for some tables (e.g., School).

Finally, under File > Manage > Security, you'll use custom record privileges for View, Edit, and Delete, with the calculation: $$USER = Creator.

The effect of that is that whenever the user does a Find or looks at a portal, he/she will see only their own records.

A couple of things to keep in mind:

1. Show all records - this command will show the user records she doesn't have access to, but she'll see a blank "no access" layout. Not ideal. A good workaround is to use custom menus to run a wildcard * Find, which will respect the security settings.

2. Even though the user only sees her own records, the toolbar still displays the total number of records, which may be a little confusing. The only workaround is to hide the toolbar, which you may want to do anyway for other reasons.

[AZ_Tech]

Thanks Fitch for the pointers, these are valuable for sure, it gets me to thinking, unfortunately seems like that hurts these days! :-) I really appreciate the time taken. A couple of questions in your example Set Variable($$USER ; Get(AccountName) ) can that $$USER variable be used anywhere at that point? Can I pull a value out of a table and add to a variable like that? Does the table I am trying to pull info from have to be on a layout or have focus to get info out of it? My orginal plan was to have a table that kept user info mayble like the People table and it would store their account name and also the school they are at. I would create a script that runs initially on login and would take their user account and do a find on this table and load the school into a variable like you have done above. Figured if I could limit the layout to only the one school I could use the relationships and portals to show the desired subset. But I was dismayed I could not seem to find a way to filter a layout, or at least with what I know. I can a portal but not the layout. Maybe some thing like your custom menu or something like that be done. thanks

[Ron Cates]

I think you're on the right track by using a People table ( Principals, Users whatever you prefer to call it ). When the Principal logs in go to the peoples table and do a find for that principals record where you would have a field that stores thier school. Set a variable like $$SCHOOL to the school, then goto the layout where they are currently selecting thier name and school and use the set field script step to set those fields to $$USER and $$SCHOOL. If they never need to see the other users or schools you wouldn't even have to show the fields on the layout. Just the button to go to the list of teahers.

In answer to your question about the variable

$variable ( single dollar sign ) - persists within the script that creates it. Once the script finishes running the variable is cleared.

$$VARIABLE ( double dollar signs ) - is a global variable that persists until FileMaker is closed and can be called anytime durring the current session. Thus if it is set by a script that runs when the file is openned it will always be available.

Note: They are not case spacific it is just common practice for many developers to use all caps for naming global variables and lower case for script variables.

[Ron Cates]

Here is what the basic script might look like. Adding in error checking would be recomended but I just wanted to show the basics.

Set Variable [ $$USER; Value:Get ( AccountName ) ]

Go to Layout [ “Principals” (People) ] // - a layout based on your People table

Enter Find Mode [ ]

Set Field [ People:name ; $$USER ]

Perform Find [ ]

Set Variable [ $$SCHOOL; People:school ]

Go to Layout [ "Function Layout" (FunctionTable)] // - Not sure what table you have this layout based on

Set Field [ FunctionTable::principal ; $$USER ]

Set Field [ FunctionTable::school ; $$SCHOOL]

[dansmith65]

Why set $$USER variable to value of Get ( AccountName )? It's been mentioned twice now in this thread, and I don't get the reason for it. It actually creates a security vulnerability if you use the variable in your permissions calcs because all a malicious user would need to do is be able to change the value of $$USER variable to gain access as another user.

FYI: I was curious, so I ran some speed tests and there is no discernible speed difference between using a variable vs. Get ( AccountName )

[Ron Cates]

A valid point. Mostly I went with it because it was already mentioned in the thread and I didn't want to add confusion by suggesting something different at this point. I felt the OP would benefit more from an example of the basic technique before getting into security risks and such. As an alternative to the way he is currently setup where the user just selects the name from a drop down it is actually a security improvement.

[AZ_Tech]

As far as the security, really all Principals have access to all of the information, the idea was just to make it easier for them by filtering what they see. As it is right now when entering an observation they must select the school, the year, the principal etc. before anything. Same with a report or find. I am sure as their experience grows this will be less of an issue. Maybe I am just trying to hard to make it conform to my old habits. In an ideal world they could go to that configuration file, change the "Working Year" or "Current School" and work in the new year on the desired school until they go change it again. I would imagine that if what I have done already was cleaner or better designed I wouldn't be in this boat. The questions I am trying to answer have a two-fold reason, I am considering re-doing a simple fund accounting system with filemaker and principals here would apply, they would want to select a school, make journal entries etc until they are done and move to another school. I have purchased several books and been through them but I haven't really found anything that goes into these type topics in detail. So your pointers help me greatly. I am going to setup some test database and do some experimenting. Again thanks for the pointers and help.

[Fitch]

Why set $$USER variable to value of Get ( AccountName )? ... It actually creates a security vulnerability if you use the variable in your permissions calcs because all a malicious user would need to do is be able to change the value of $$USER variable to gain access as another user.

You make it sound like a wide-open door. Maybe it does open it a crack, but I'm not so sure. Since you can't access global variables in the data viewer unless you're a full access user, it doesn't seem like much of a vulnerability.

[dansmith65]

You make it sound like a wide-open door. Maybe it does open it a crack, but I'm not so sure. Since you can't access global variables in the data viewer unless you're a full access user, it doesn't seem like much of a vulnerability.

Yes, I did word that a little strongly. My main question was just why do it in the first place? The only advantage I can think of is if you need to process the account name somehow, like to normalize it when external authentication is being used and users can login with/without the domain name portion of their username.

[LaRetta]

I realize I'm jumping in late but I agree with Dan ... why set a variable with Get ( AccountName ) when it is accessible directly. But the idea is correct ... load the global variables and use them to perform a find (or restrict security or set a global field for relationships). But I suggest that you load the variables with the User's unique ID instead since Users' account names can change and using it to find records may not find all records for a single user.

I have been experimenting with using get (accountName) to get who the user is and the pull a record in a preference table and relate off of that

Staff table should contain a field called AccountName which must match their security AccountName. When file opens, search for the current account name in the staff table and load the staff ID into a global variable to use throughout the system. You can also store the User's SchoolID in their record since you indicated it would not change for the school year (assign staff each school year right in the staff table). Then both variables (StaffID and SchoolID) would be loaded from the single location (the Staff record) at startup. And like Tom says, there is no security risk because you do not store the password in the record - only their account name (which is public information anyway). :^)

[bcooney]

And...their staff record is their "home page" so to speak. That is what I was trying to describe.