This article covers an advanced user access method known as cascading permissions. Use this method when you need users to access data based on their relationship to that data. For example, your app has data for multiple companies, and you only want users to see data associated with their specific company. This method can help you avoid many roles that are very similar and user fields widespread throughout your app.
In this article
When to use advanced user access methods
There are many ways in Quickbase to give users access to only the data they need. Straightforward cases often only require roles and custom access rules.
- For example, a user's name appears in a specific field like Assigned To or Sales Rep. You only want users to access records where their name is in one of these fields.
To accomplish this, you add a custom access rule to a role.
Sometimes the information users need to access is more complex and determined by how the user is related to that data.
- For example, data in your app includes information about many schools or companies, and you want users to see only the data for the school or company they are associated with.
This can be accomplished by using an advanced user access method known as cascading permissions. Cascading permissions allow Quickbase to check who a user is and what records they should be connected to. Then you design a role with custom access rules that lets the person access only those records.
Cascading permissions may be useful in your app if:
- You frequently copy roles and make only minor tweaks, leading to lots of roles
- You have lots of user fields spread throughout your app so that you can set up permissions
About cascading permissions
Cascading permissions are not a setting or something you switch on in your Quickbase app. They are something you build into your app's structure to efficiently manage user access.
Cascading permissions, sometimes referred to as table-based permissions, allow Quickbase to identify individual users on a table and then base permissions on the information found in that table. In some situations, this can let you cut down on the number of roles and reports needed in an app.
Here are some use case examples for cascading permissions:
- Sales representatives use Quickbase as a CRM. They are spread across 5 locations, and need to see data relevant to each location.
- Your school district uses Quickbase to handle projects for the entire district. You only want teachers and school administrators to see data that relates to their own school.
- Contractors that use your app should only see tasks assigned to them. In addition, you only want them to see projects that contain tasks assigned to them.
In this article, we'll use the use case of a school district that uses Quickbase to manage projects to illustrate setting up cascading permissions.
Requirements
Before setting up cascading permissions in your app, you'll want to be familiar with the following topics:
- Creating apps
- Add tables
- Adding New Fields
- Adding formula fields to tables
- Creating table-to-table relationships
- Create a summary field
- Creating lookup fields
- Working with roles
- Custom data rules
Video tutorial
You can watch Cascading Permissions Set-Up on YouTube to learn more about this concept. The video also walks through how to set up cascading permissions in an app.
Set up cascading permissions
There are four steps to set up cascading permissions:
- Create a table to hold data about app users
- Add a formula field to the table that calculates the current user
- Use relationships to cascade that user field to other tables
- Create or edit roles so that permissions are based on that user field
The following sections give more details about each step.
Step 1: Create a table to hold data about app users
Create a table in your app. This table will hold data about your app users. It may be useful to name it something like Employees, Sales Reps, or Users.
Include a user field in this table. You may call it something like Name, User, or Person. For cascading permissions to work, each app user needs to have a correlating record on this table.
If you had an app that tracked projects for a school district, this table on your app might be called Employees. The table would contain a record for each teacher and school administrator.
Step 2: Add a formula field to calculate the current user
On your user table, add a formula - numeric field. Name it something like Is Current User, or Current User. The purpose of this field is to check who is logged in and currently accessing data.
Add this formula to the formula - numeric field:
If(User()=[Name], 1, 0)
If we translate this formula to plain English, it says:
If the current user who is signed in is the user listed in the Name field, display 1. Otherwise, display 0.
-
- Name refers to the user field created in the previous step. It may be called something different in your app.
Step 3: Use relationships to cascade that user field to other tables
Set up table relationships
Depending on your use case, you may need to create more tables. For example, you may need a table that tracks the schools included in the district. If you want to control access based on department, you would also need a table to hold this information for each employee.
Make sure relationships are set up between tables. In the school district example, the app tracks projects in the school district. The app has the following tables:
- Schools—all the schools in the district
- Employees—the user table that lists every employee and calculates who is signed in
- Projects—school district projects and initiatives
- Tasks—tasks associated with projects and initiatives
Here is how those tables relate to each other:
- Schools is a parent table to Employees (one school may have many employees)
- Schools is a parent table to Projects (one school may have many projects)
- Projects is a parent table to Tasks (one project can have many tasks)
- Projects is a parent table to Employees (one project may have many employees)
- Employees is a parent table to Tasks (one employee may have many tasks)
Every table is connected with a relationship. In some cases, it is indirect. Employees doesn't directly connect to Projects, but both of them are connected to Schools.
Add summary and lookup fields to cascade data
Add summary fields to parent tables and lookup fields to child tables to share information about who is accessing the app.
In our school district example, we only want to show employees the projects associated with their school. We would:
Check to see if the employee is associated to the school:
- Go to the Table-to-table relationships setting page in the Schools table
- Select the Schools to Employees relationship
- Add a summary field to the Schools table
- Select the second option: True / False If there are any Employees records related to that School
- Add a condition: Only show if the field Is Current User is greater than 0.
- Select Create
- Name the summary field something that helps you remember it's purpose. Something like Employee Matches Current User.
Cascade that summary field to any tables where you want to set permissions based on if the current user is associated to a certain school by adding it as a lookup field. For example:
- Go to the Table-to-table relationships setting page in the Projects table
- Select the Schools to Project relationship
- Add a lookup field to the Projects table
- Select the summary field you just created. In our example, it's called Employee Matches Current User.
Now you have the necessary fields in place to set up custom access rules.
Step 4: Create or edit custom access rules that rely on the user field
From the Roles page in app settings, create custom access rules that check the user field.
In the school district example, all teachers may be set up with the role Participant. This means we can edit a single role, and teachers will only be able to see projects associated with their school.
To do this:
- Select the Participant role
- Select Custom Rule from the dropdown under View
- Create a rule that says Employee Matches Current User is equal to checked
Add similar rules to any other applicable tables.