Value Lists of Unrelated UserNames

[Cassetti]

Hello,

I'm trying to build a type of user permission system which will restrict access to specific records and activities (such as exporting data, emailing customers, etc)

When a new user is detected in my database currently, it creates a record in a table called "User_Preferences" and stores the Get(AccountName) to the field called Username

As training for my assistant, I assigned him the task of creating a new table for personnel records to keep track of employee contact information. I want to link these two together with a field in the personnel table called r_username

Is there a way to build the value list so only users in the User_Preferences table which have no relating personnel record show up?

[bcooney]

May I suggest changing the table to Users, and storing their AccountName in an AccountName field, and creating NameFirst and NameLast fields.

Do not build yourself a permission system. Use FM's Accounts and Privileges.

Why do you need a new table for personnel? And certainly, they would not be related by r_username, but by UserID.

And, yes, you can "filter" value lists. This is called a conditional value list. Search the forum for plenty of examples.

Edited September 22, 2009 by Guest

[Cassetti]

Thanks for you're reply, I'll consider that. Unfortunately I had to build this system to retrofit an existing complex database of only 14 users. I built the system initially as part of a test database which became a new place to hold custom settings for each user. The new personnel db was created as a sort of basic training for my assistant. The first name and last name is a good idea for relating the two.

The Permission system in accounts and privileges does not allow me to give my users permission to choose what records our parnters overseas have access to. I am actually already testing some Privilege sets for our remote users which will restrict access to tables with confidential information.

Thanks again, that put me on the right path.

[bcooney]

"The first name and last name is a good idea for relating the two. "

No! Don't relate tables using text values. Use a unique numerical ID field.

"The Permission system in accounts and privileges does not allow me to give my users permission to choose what records our parnters overseas have access to. I am actually already testing some Privilege sets for our remote users which will restrict access to tables with confidential information."

Yes, it does. You need to "tag" the record in some way so that your record access priv rule works for that priv set.

[Cassetti]

My mistake, I mistyped my thoughts.

I do use only unique ID numbers to relate records. Unfortunately My boss who built the initial database had no concept of this. (even though he has db experience dating back to the early 1990s!)

I have spent over 180 hours fixing a mistake he made by relating contacts to the rest of the database with a calculation field based on company name plus full name (correcting a typo would break relationship to entire database!)

I'll look into that privilege rule you're talking about, that will save me a lot of time if i can integrate it