Roles and Permissions across different locations and roles

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I am sure the question I am asking is not something new but I just can't seem to understand the right way to approach this. 

We have stores and there are a number of projects in each stores.  These stores/projects are in different states and  managed by a number of roles in each state.  These could be PM or Equip Coord or Refit Manager.  When one of the staff member logs in, I want them to only access records relevant to their state and the role they are in.  With some hand holding by the QB support, I established a states table and linked it with store.  Then I created a state based role and gave permission to this role in each table by pointing to the state.  But this approach will make it necessary for me to create 5 states times three roles = 15 roles and add all of these to each table.  To me this does not sound very dynamic process. 

Will appreciate if someone could review this and advise any alternative approach, if at all.             

Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 4 years ago

  • 0
  • 1
No problem, this can be done with 1 Role.

Basically the goal is to create a table to control access where the Key field is a composite of the User, the State, and the Role.  Then use that to control access.


Here are the Steps:

  • Create table called Access Control
  • Make a field called [User, State, Role] (note that the actual field name will not have the [ ] 's
  • edit the field list to make this the Key field to the table
  • Make a formula field called [OK to Access?], with a formula of "true" (without the quotes).
  • make a field for User (a User field)
  • make a multiple choice field for State with the choices being the States
  • make a multiple field for the Role with the choices being the Roles
  • make a formula field called [User, State, Role (formula)] with a formula of List(",", ToText([User]),[State],[Role])
  • make a form Rule which is always true which says "When [OK to Access]=checked, Change the value of the field [[User, State, Role], to the value in the field [User, State, Role (formula)].  Uncheck the box at the bottom of the form rule to ensure it always fires.
Great, you now have a tale which allows you to set the access controls for any combination of Users, States and Roles.  now we need to figure out how to get the detail Projects to records to know if they are OK for the User to see.


no problem.

In the Projects table make a three formula field called [User, State, PM] with the respective formulas of


List(",", User(), [State],"PM")
List(",", User(), [State],"Equip Coord")
List(",", User(), [State],"Refit Manager")

Note that this is where the Dynamic magic occurs.  The calculations for this field will be User dependent as the function User() will return the current user.

Now, make three Relationships where 1 Access Control has many Projects based on each of these respective fields being the reference field on the right hand side.  Initially do not have any lookup fields and immediately delete the automatic fields on the left hand side of the relationships for Add Access Control.

Lookup the field OK to access on each Relationship and call them
[OK to Access as PM?]
[OK to Access as Equip Coord?]
[OK to Access as Refit Manager?]

Lastly make a new field called [OK to Access] with the formula of

[OK to Access as PM?]
OR
[OK to Access as Equip Coord?]
OR
[OK to Access as Refit Manager?]

Yu'r basically done!

Just make a single Custom Role Permission Rule based on [OK to Access]  is checked.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thank you so much. I am putting it together. could you clarify on the field OK to Access?  How do I add the formula as 'True"? Also in the last bullet point when you talk about form to have a rule,  would that be Projects form?   

 
The way to force a formula checkbox field to always be checked is to type

true

In the formula box. No quotes.

The form rule would be on the access control form.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot. I am chugging along.  I am stuck on the last part where I created three formula fields in the projects table. However, these formula entered in these fields is not accepted.  Hopefully I will resolve this after I receive your response.  After that I need to establish relationships.  I tried  but I am unable to see these newly created fields to make them reference fields.  

At any stage would it be possible for you to quickly review what i have done?  

  
These need fixing:

List(",", ToTex(User()), [State],"PM")
List(",", ToText(User()), [State],"Equip Coord")
List(",", ToText(User()), [State],"Refit Manager")
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks. This resolved the issue. I don;t see the required fields in the relationship window as you indicated.  I have attached a screenshot. I guess i am missing something. 
You will need to have three relationships and three "reference fields on the right side of the relationship.

Let's just get one working first.

[User, State, PM]
The formula for it was posted above

List(",", User(), [State],"PM")

That needs to be the right side of the relationship, not [Related Access control].  Then lookup the field called [OK to Access?] and name it [OK to Access as PM?]

Then repeat for the other two relationships.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks Mark.  Just to get this right, I created three fields in projects table with the formula you created.

User,State, PM 

User, State, Equip Coord

User, State, Refit Manager

I then clicked on the relationship while in the projects and selected Access Control table. I chose one access control to many projects option.  This presented with the screen I sent you a screen shot of.  On the right hand side, when I drop down I don't see the projects fields you are referring to.   I attach a copy with the drop down as visible.  However assuming you meant that I need to create three separate relationships,  I  changed the drop down 'related access' to the newly created field in table projects.  Then I added a look up field 'ok to access' and  renamed it 'OK to Access PM'. I hope I am right thus far.  

Now I am on the following step: 

Lastly make a new field called [OK to Access] with the formula of 


[OK to Access as PM?]
OR
[OK to Access as Equip Coord?]
OR
[OK to Access as Refit Manager?]


Is this to be three new fields in the projects table and if so what type of field?       

 

 



 
I see the [OK to Access] field on your screen shot as a drop down choice.  It's there.  Choose it and then rename it to be [OK to Access PM?] for the relationship based on the field [User, State, PM]  (not the generic [Related Access] )

Then do the same lookup field on the next two relationships and rename according to each relationship.  ie rename the next one to be called [OK to Access Equip Coord?]
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks once again.   I managed to create these three extra fields.  Please review the screen shot of the projects table field listing. Now very last step: 

Just make a single Custom Role Permission Rule based on [OK to Access]  is checked.


Could you provide some instruction?  I have created one PM role for but how does that relate to what I did?  Also can I test it as a PM? 

 



 



 
The will only be on Role called

Field Managers

Put yourself in the table for a state and Role and then put yourself in a project. Ina Role and do your testing.

Delete the Role for Pm or rename it.

The Custom Rule is simple where [Ok to Access?] is checked.