Manage User Access Based on a User's Relationship to Application Content

Often, you'll want to grant a user access to only certain records. Usually, the criteria on which you base this decision has something to do with the data each record contains.

Most of the time, the relationship between user and data is simple. For instance, the user's name appears in a specific field, like Assigned To or Sales Rep. Or you may want to show a user only those records they created (in Quickbase-ese the creator is called the Record Owner). If the situation is this straightforward, stop reading this topic and just add a custom access rule to a role. (Read how.)

But sometimes, the user's connection to the data isn't so clear-cut. In those situations, you'll need to be more creative with your access permissions. For example, imagine that you have an application that handles projects for a school district. You want teachers and school administrators to be able to open the application and see only records that relate to their own school. Sure you could set up a role for each school, but if your district contains more than ten schools, maintaining so many roles can be a burden. If user identities and school affiliations are part of your application, you can use that data to control what users see. This topic explains how to restrict a user's access based on their relationship to data in your Quickbase.

To accomplish this, you'll set up the application so that it checks to see who the user is and which records they're connected to (for example, which school they're in or what company). Then, you design a role that lets the person access only those records.

To manage user access based on a user's relationship to application content:
  1. Create the application.

  2. Add a table to track users.

    If it doesn't already exist, add a table to your application that contains a record for each user of the application. This table should include at least one user field along with other fields for whatever information you want to track. For example, the school district application would have a table called People into which you'd enter teachers and school administrators.

  3. Add a field to the People table that can determine which user's logged in.

    Create a new field that's a formula - numeric type field. Name the field Current User is this Person. Click the field name to open its properties page and insert the following formula in the Formula box: If(User()=[Name], 1, 0)

    That formula says: If the current user is the user listed in the Name field (this field must be a User type field), then display the result 1, otherwise the result is 0. Later, you'll use this field to create custom roles that specify access based on whether or not this field reads one or zero. (Read more about formulas.)

      Tip: If you don't want to include a User field within your People table, you can try a workaround. If a field contains a person's name, you can match a user identity to that name. Say you have a text field called Name in which you enter a person's full name (first name first and last name last). In that case, you can use the following formula instead: If(UserToName(User())=[Name], 1, 0)
      But be careful! This method is unforgiving. If you or a user makes a typo in a user's name, Quickbase won't recognize the person.

  4. Add a table to track the organization to which a user belongs.

    If you want to control access based on the company or department to which the user belongs, you need to create a table in your application to hold this information.

  5. Create relationships between all the tables.

    The table you created in Step 4 to track organizations is a parent table to the People table, which is the details table (or the many in a one-to-many relationship). In other words, many teachers can belong to one school. Other tables in your application must relate to these tables too—even if that relationship is through an intermediary table. For example the school district application also tracks projects and tasks. So it contains a Projects table which is a details table to schools (one school can have many projects). The application also contains a tasks table which is a details table to Projects (one project can have many tasks).

  6. Add summary and lookup fields to all the other tables.

    Now that the People table knows who's accessing the application, you need to share that intelligence with the other tables in your application. Not every table has a direct relationship with the People table, so how do you accomplish this? You create a series of fields that relay the original value from the Current User is this Person field from table to table. It's almost like a game of telephone with each table passing along the necessary info. Depending upon how the tables are related to each other, you'll add summary or lookup field to each one. That field references a field from another table that is or knows the value of the Current User is this Person field. For example, to set this up in the sample school district application you would:

    1. Create a Summary field in the Schools table that totals the Current User is this Person field. Call the summary field Current User is in School.

    2. Create a lookup field in the Projects table that draws in the value of the Current User is in School field from the Schools table. Name it Current User is in Related School.

    3. Create a lookup field in the Tasks table that draws in the value of Current user is in Related School. Name it Current User is in Project's Related School.

  7. Create the role.

    You only need to create one role. Within this role, create a custom access rule for each table. The rule is based on the Current User is this Person value which you wove throughout your application in Step 6. For example, in the school district application, you'd click the dropdown in the View column for each table and set a custom rule as follows:

    • People table: Current user is this person is 1

    • Schools table: Current User is in School is 1

    • Projects table: Current User is in Related School is 1

    • Tasks table: Current User is in Project's Related School is 1

  8. Save the role.

    Each user now can only access records belonging to their own school.

Related topics: