Hey all,

I'm new to FMP (using v. 8) but not to databases in general. I have
three tables as follows:

---------
users
---------
user_id
name
email

---------
roles
---------
role_id
name

----------
user_roles
----------
user_id
role_id


Now, the purpose here is to have a flexible set of roles and allow users
to have multiple roles. When I set up a layout in the users table with
a portal to the user_roles table, it views everything perfectly. I see
the name of the role(s) that user is assigned in the user_roles table.

What I haven't been able to do is edit it.

Actually, *ideally* I want to be able to create a list of the available
roles, check the ones that the user has, and let the editor check or
uncheck to assign roles. I'll settle for typing in a role when editing
the user.

Allow creation is checked for both sides of the user <--> user_roles
(via user_id) relationship. It is not checked for the roles <-->
user_roles (via role_id) relationship.

For whatever reason, I never get to edit roles in the user layout. It
never gets reached in the tab order and double clicking does no good.

Anybody out there that can help a newbie out?

R

You design is perfect. The idea is to view in the user table via a
portal - as you do - and to edit in the user_roles table as you have
there one record per couple user-role. To set it up is just a matter of
relationships for the proper info to appear in the user-roles table.
It can be made transparent to the user.
Remi-Noel

"StupendousMan" <Stup (AT) ndou (DOT) man.doesnt.want.email.thank.you> a écrit

Well, thanks. I was hoping that I had messed up something, because I
can view but not edit. I've got pretty far with the checkbox concept as
well, but the check boxes are not accurately reflecting the data! Yikes!

Rick


Remi-Noel Menegaux wrote:

I assume you want something like the following;
- your looking at a User record
- you have a portal of all roles
- there is a checkbox beside each role which, when checked, means a
User_Role record exists
- clicking on the checkbox creates / deletes the User_Role record

Easier said than done, but here's how to do it:

Quick overview so that this is clear as mud. A calculation checkbox
field in ROLE takes 1 user's ID and looks at User_Role to determine if
a matching record based on role_id and user_id exists, if so then it
evaluates to a 1 else blank. So you have to script which user_id the
ROLE records are going to look at and then script the creation &
deletion of USER_ROLE records whenever you click on the calculated
checkbox.


1) add the following fields;
USER::ID_Role_g (in User file a GLOBAL number field)
USER::One (calc number, calculation = 1)
ROLE::ID_User_g (global number)
ROLE::One (calc number, calculation = 1)
ROLE::CheckBox_c (calc number, calculation = 1)
USER_ROLE::ID_User_g (global number)
USER_ROLE::ID_Role_g (global number)
USER_ROLE::One (calc number, calculation = 1)



2) add the following table occurrences;
"User To Role All" base table = ROLE
link USER::One to THISTABLE::One

"User To User_Role" base table = User_Role
link USER::user_id to THISTABLE::user_id AND
link USER::ID_Role_g to THISTABLE::role_id

"Role To User_Role" base table = User_Role
link USER::role_id to THISTABLE::role_id AND
link USER::ID_User_g to THISTABLE::user_id


3) create a value-list "One" with custom value 1


4) Now change the calculation formula for the field ROLE::CheckBox_c
to;
Role To User_Role::One



5) create the USER layout;
- create a portal showing records from "User To Role All" displaying
the fields "CheckBox_c" and the role name. Format the "CheckBox_c"
field as a checkbox field using the value-list "One
- put the related field "User To Role All::ID_User_g" on the layout but
NOT in the portal
- create some records manually by going to each table and creating them
there
- go back to the USER table and verify that role records are being
displayed
- enter the user_id of the record that your looking at into the "User
To Role All::ID_User_g" field and click out of the record, the checkbox
in the role records should be indicating which USER_ROLE records exist
for that user. Please note, if you flip to the next user record, but
don't update the "User To Role All::ID_User_g" field, the role records
will be showing inaccurate data.


6) put a script on the checkbox along the following lines;
If the checkbox = 1
Need to delete the related User_Role record
set ID_Role_g to the id of the role record clicked on
go to related record "User To User_Role"
delete the record
return to original layout
else
set ID_Role_g and ID_User_g in USER_ROLE
go to User_Role layout
create the record filling in the IDs
return to original layout
end if

that is exactly what I want. I'll have a go at your suggestion.

Thanks very much.

R

FP wrote: