I’m not gonna lie: I’ve become a big fan of Quickbase.
In my history of designing applications, learning to code was necessary to achieve my goals. But Quickbase, for the most part, removes that need and instead lets me focus on data architecture and prototyping with no/low code.
But there are still some aspects of managing Quickbase apps that are a bit of a nuisance, particularly as an app matures, and with it, more Users and Roles are brought on board that require careful management.
But what if table access could be managed more easily, and with more flexibility, using a RACI? This post describes a prototype for doing just that.
What is a RACI?
RACI is an acronym that stands for Responsible, Accountable, Consulted, and Informed. It’s a tool used in project management and organizational workflows to define and clarify roles and responsibilities within a team or across different stakeholders.
- Responsible (R): This role refers to the person or individuals who are directly responsible for completing a specific task or activity. They are the ones who perform the work and ensure its completion.
- Accountable (A): The accountable role represents the person who is ultimately answerable for the overall outcome or result of a particular task or project. This individual has the authority to make key decisions and is responsible for ensuring that the task is completed successfully.
- Consulted (C): This role includes individuals or stakeholders who provide their input, expertise, or advice on a particular task or decision. They are consulted for their insights, but they are not directly responsible or accountable for the task’s completion.
- Informed (I): The informed role includes individuals or stakeholders who need to be kept up-to-date on the progress, decisions, or outcomes of a task or project. They are kept informed about relevant information but do not have active participation or decision-making authority.
By using a RACI matrix, teams can define and communicate the roles and responsibilities associated with different tasks or activities. In this example, it will define access permissions for an example table. The neat thing about this method is that it doesn’t require table-to-table relationships, only formula queries. Here we go!
Setting up the RACI table
The RACI table describes who has what permission in which Table. Each Record defines a single instance associating a User to a Table. If a User has access to more than one Table, they need to be defined individually. But, Records can be copied to expedite this process.
I’ve set ordered the fields in my form to create a sort of “RACI statement”, which I’ll demonstrate here also.
Fields To Setup
- Users (User)
- User Text (Formula – Text)
- RACI (Text – Multiple Choice)
- Table (Text – Multiple Choice)
- Optional: RACI Statement (Formula – Text)
Users
The first field, the “who” field that I’m using, is a simple User field that will display all of the Users of your app. You can use other selectors like groups or teams you’ve defined, as long as you have a way to evaluate and compare in each table.
User Text
This field is a formula text field, and converts the selected Users field to text for evaluating and comparing in other tables.
RACI
The RACI field is a multiple-choice text field, and contains the values Responsible, Accountable, Consulted, and Informed.
How these selections will translate into permissions is completely up to you. I’ll show you an example I’ve put together, that can be copied and pasted into every table of my app where I want to apply the respective permissions.
Table
This is also a multiple-choice text field, that contains table identifiers. It would probably be more effective to use table IDs, but that makes it difficult to read and present. You’ll need to add separate table entities for each table you want to control access to.
RACI Statement
Not essential, but fun. The RACI Statement is a text formula field that uses the formula:
ToText( UserToName( [Users] ) ) & " is " & [RACI] & " for " & [Table]I think it’s a nice way to confirm the setup.
Setting up a table
In your table where you want to control access via RACI, you’ll need to add 6 formula fields:
- Table Name (Formula – Text)
- Current User (Formula – User)
- Is Responsible? (Formula – Checkbox)
- Is Accountable? (Formula – Checkbox)
- Is Consulted? (Formula – Checkbox)
- Is Informed? (Formula – Checkbox)
Formula: Table Name (Formula – Text)
The formula for the Table Name field is a hard-coded value for the name of the table you’re working in, in this example: “Sample Table”.
There are arguably better ways, such as using the dbid() function. However, for this example, I wanted to focus on something reader-friendly. Whatever the value you use, it must match the value of the “Table” field in the RACI table.
Formula: Current User (Formula – User)
The formula for the Current User field is simply: User()
What it will do is confirm the current user, and match it against “Users” field in the RACI table for subsequent formula queries.
For the next 4 formulas, you’ll need to know how formula queries work, how to locate field IDs, and table shortcuts, which is outside the scope of this post. For more information on that, I suggest checking out Quickbase Junkie tutorials.
Formula: Is Responsible? (Formula – Checkbox)
If(
Size ( GetRecords( "{13.EX.'" & ToText( [Current User] ) & "'} AND {9.EX.'" & [Table Name] & "'} AND {6.EX.'Responsible'}", [_DBID_RACI]) ) > 0,
true,
// else
false
)Formula: Is Accountable? (Formula – Checkbox)
If(
Size ( GetRecords( "{13.EX.'" & ToText( [Current User] ) & "'} AND {9.EX.'" & [Table Name] & "'} AND {6.EX.'Accountable'}", [_DBID_RACI]) ) > 0,
true,
// else
false
)Formula: Is Consulted? (Formula – Checkbox)
If(
Size ( GetRecords( "{13.EX.'" & ToText( [Current User] ) & "'} AND {9.EX.'" & [Table Name] & "'} AND {6.EX.'Consulted'}", [_DBID_RACI]) ) > 0,
true,
// else
false
)Formula: Is Informed? (Formula – Checkbox)
If(
Size ( GetRecords( "{13.EX.'" & ToText( [Current User] ) & "'} AND {9.EX.'" & [Table Name] & "'} AND {6.EX.'Informed'}", [_DBID_RACI]) ) > 0,
true,
// else
false
)Each of these formula queries will check Records in the RACI table to determine what your permissions are in the table you’re using. In this example, the test user is “Responsible” for this table, demonstrated by the automatically checked checkbox. It should be copyable into any table where these formula checkboxes are created.
Adjust Table Access
The next thing to do is adjust table access permissions, and this is where the investment into controlling permissions at the front end pays off.
In this example, Administrators can manage everything, viewers can’t do anything, and Participants can add Records, as well as view and modify them according to Custom Rules.
The View rules are as follows:
The premise for this is simple: If you have any business in this table, then you can view Records.
You can have different rules to determine who can modify Records in this table:
In this example, we can assume that the people with permissions to modify Records are those that are expected to complete tasks, and are accountable for making sure they’re done. People that only need to be informed don’t necessarily need to be able to edit Records but should be able to view them.
While there is some up-front investment of time in setting this up, the benefit is that maintaining app table permissions in the long term becomes much simpler.
What do you think? Do you think a setup like this is worth the time to set it up? Let me know!