Populate field through lookup in a relationship to help permissions

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
HI,

I am trying to limit access to records in an app depending on a territory field and if a user (from Roles table) is assigned to that territory. The idea being is we just need to update the roles table with new starters or the territory summary if we create a new territory to update the records. I am then using a formula such as If(UserToName(User())=[Full Name], 1, 0) to see if the logged in user is assigned to the territory and if so allow access to view and edit the record


I have a few different tables in an app and have a a 1 to many relationship set up from the record table to the territory Table where I am trying to look up the district from the main table in the Territory table to then pull in some of the details.

Join clause

The look up is set up so I can only see the relevant record from the drop down by I cant figure out how I can get this to populate in rather than having to go to every record and manual click in the field and then save the record. I think I need to do a revers relationship but I am not sure how to do this and the settings needed to get the population. Also wondering if there is a better way to do the permissions. 

Thank you
Photo of James

James

  • 172 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
The best way to do this is to have a table of users where the Key field is set to be the userid.

Then have any fields you like on that user field such as Region.

The magic is then you create a formula user field called [Current User] in any other table, with a formula of

User()

 and make a  relationships based on that field to the Users table.  Then you can look up any user attributes and make a formula to control the access permissions with a custom Rule.

The beauty of this is that you get to create a single Role called "Allowed to see own Region Only, and it will apply to all those regular users who need to be limited to their "Own" Region.
Photo of James

James

  • 172 Points 100 badge 2x thumb
HI sounds like a really good solution. Do You have a step by step guide for this. I have created a table to show the roles and another for territories.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
I suggest that you start working your way down my instructions above and post back where you get stuck.

Abandon your current thinking and start fresh with this approach.