Eliminating duplicate databases that are the same

[Roxy201]

Hi, I'm wondering if someone could point me in the right direction here.

I've got a database that manages clients. It's pretty basic from the users side. I'm needing to separate the information available to users who will log in to the database, based on the territory that they manage.

What is the best structure to use here? I understand that I can use account privileges to make certain things available, and is it possible when setting that up to lock the user out viewing information about clients from other areas / territories?

I'm thinking it's a relationship that will filter out the clients in each territory that is a part of the solution (i.e. duplicate the 'client' table and then create a relationship between the tables based on territory). But really I'm not sure.

I also need to display the information in two different layouts, as there are two different groups of people in each territory.

Basically, I want the user to think they are logging into their 'own' database, but still make the whole thing accessible to myself. The people it's for are currently running separate databases for this, which means that anytime something is changed in one database, it's then changed in the other three, making it pretty labor intensive (not to mention boring and repetitive!)

I would like to build the navigation like this too: User logs in >> A 'homepage' (lists Territory 1, 2, 3, 4 etc) is displayed, and a button takes them to the area they wish to access >> They then can access all the layouts (or most of them) with only the information relevant to their territory being accessible (They have a small accounts section as well, so this would be handy in the invoices for them to be only able to invoice their own territory).

Anyway, maybe this is beyond my scope, not sure, but would love to give it a go, so any pointers in the right direction would be great...

Thank you

[Fenton]

First, you need a "Staff" table. It would have the person's Account name.* It would also have their Area. The users would not have access to the hidden layouts (more later) of this table, other than a List view with innocuous info (names, phone#'s, etc.).

You would want a relationship from this table to the other main one based on Area. This would allow you to Go To Related Record [ Main~Area; Show only related records; whichever layout in the main file ].

You'd need a startup script in this file, in File Options. It would first Find, with Get (AccountName) in the Staff Account name field. That gets you on the person's record. Then Go To Related Record (as above).

All other files need to either not be visible from Open Remote, or also have a startup script which runs that Staff script. So you can't just open another file and go from there.

In the Main file, if you really really want people to ONLY see their own Area, then you can block other areas by using a Record-level access restriction, in their "User" Privilege Set (all "users" use the same Privilege Set). For Records, for that table, View is "Limited". In the pop-up calculation. Oops, first we need to create a relationship (from Main to Staff table).

Relationship "Staff~Account": Get (AccountName) = Staff::Account name

Now, back to the calculation: Staff~Account::Area = Area

This will stop them being able to see any other Area. It will also slow down Finds considerably, especially at startup.** But any Find will be automatically filtered. Which saves you a lot of work. Otherwise you'd have build a custom Find method, and every Find would have to be scripted to filter to their own Area.

In either case you must script Show All Records, which will otherwise bypass the View restriction. Just run a Find for the Account's Area instead.

*The Staff file can also be used to script account creation and deletion, without having to go into Accounts & Privileges (A & P). There are script steps for this. For security reasons you may not want to store the password in a field however. Just use a variable (or global field on earlier versions) to create the account.

**A more lightweight restriction is to let them View all records, but restrict Edit to only their own area. You will still want to script all Finds, to keep them in their area. The advantage of using Edit instead of View is speed. As I said, the View restriction can be pretty slow on large files.

[They must have "Editing only" as their menu access in A & P. If you leave it as "All", then they can just hit the command key combo for Find, and you cannot script that; unless you do a Custom Menu Set.]

[Roxy201]

Relationship "Staff~Account": Get (AccountName) = Staff::Account name

Sorry, I'm a little confused here, as the initial relationship is between "Staff~Area". Should the relationship be "Staff~Area & Staff~Account"? Or am I missing something completely!

It's a lot to digest, but I think I'm clear on the calculations in the A&P, and the 'Find' in the Open Script. And YEP definitely only want Staff to be able to view their related info only.

Thanks Fenton, this is a great help towards eliminating tedious activities, which from my understanding is one of the bonuses of using FMP in the first place!

Edited November 1, 2007 by Guest

[Fenton]

No, I think I was right. The relationship is not between Area. Because what you are trying to do is tie an account's Area (in the Staff table) to the currently logged in person. In other words, you first have to establish WHO the person is, using Get (AccountName), and use that to tie to their record in the Staff table (where the person's Area is).

So you use that relationship, of the person to their Staff record, to check what Area they belong to. You then use that to test against the Area of the Main table's record, to see if they are supposed to View (and/or Edit) the Main table's record.

It is the View record-level access in Accounts & Privileges that restricts the visibility of records, via the calculation:

Staff~Account::Area = Area

But it can't do that unless you first have the relationship to Staff based on Get (AccountName). It's kind of a 2-step process.

[Roxy201]

Oh dear... This is going straight over my head...

My point of confusion is around the Get(AccountName), and exactly where that goes in relation to the fields that will create the relationship between Staff~Account...

When referring to the 'Main' Table, this is my 'Contacts' table yes..? And it's just one file at this stage too...

I added both a 'Staff' & 'Accounts' Table and created the relationship between "Staff~Contacts":Area.

Then there is the relationship between "Staff~Account":Get(AccountName)= Staff::AccountName ... This is the bit that's confusing me, deciphering where to put what...

It kept me awake last night, so hopefully some clarity will bring some sleep!!!

Thanks for everything so far.

[Fenton]

Get ( AccountName ) is a FileMaker function to identify who is logged in. To use it in a relationship you put it into an unstored calculation field. A common use is to point that to a "staff" table, which has 1 record per staff member. It would point to a field which exactly matches the Account name for the person; you must have this. You can put unique "attributes" of the staff member into their record, such as, in your case, an Area field.

The value obtained will be an unstored value. But it can be used to compare to a stored value in the originating table of the relationship. This can be used in a calculation in Accounts & Privileges, as a "record-level access" restriction (next to "Records:"). It is possible to use a calculation in View, Edit or Delete. If you put a restrictive calculation in View, then any record which fails the calculation will not be readable. It will say "<>" in all the fields.

It is well-integrated with FileMaker's Find operations. Those non-viewable records will automatically be excluded from Finds and relationships. The exception is "Show All Records", which explictly, well, "shows all records" :-]

You need to write your own script instead, which either Finds their records, or just Finds in a constant field (because the records are automatically filtered).

I can see why you'd be confused as to how to set all this up. First concentrate on using Get ( AccountName) in an unstored calculation field. Then using that in a relationship to a Staff table. You should be able to see that staff person's 1 record, and its fields.