Problem
Validate data based on a user’s role
Text
Enforce business policies for what kind of data is allowed.
Solution
var Text roles = ToText(UserRoles("ID"));

If( 
// Role with ID# 13 is "Sales Rep" 
(Left($roles,3) = "13 " or
Contains($roles,"; 13 ;") or
Right($roles,3) = " 13" or 
(Contains($roles,"13") and Length($roles) = 2)) and
[Discount %] > 0.1, 
"Sales reps may set discounts up to 10%. Please adjust discount or speak with your manager to discuss a higher discount for this client.", 

// Role with ID# 10 is "Sales Manager" 
(Left($roles,3) = "10 " or
Contains($roles,"; 10 ;") or
Right($roles,3) = " 10" or 
(Contains($roles,"10") and Length($roles) = 2)) and
[Discount %] > 0.15, 
"Sales managers may set discounts up to 15%. Please adjust discount or contact the finance team to discuss a higher discount for this client." 
)
Let’s say you’re tracking sales opportunities, and you want to enforce business policies for what kind of discounts are allowed. Sales reps can discount up to 10%, sales managers can discount up to 15%, and any discount greater than 15% requires approval from the finance team. You can build a custom data rule using a formula - multi–select text field to enforce this policy. Consider an app with the following roles: Administrator, Finance Team, Sales Manager, and Sales Rep.

Administrators and members of the finance team should be able to set any level of discount, but the discount levels sales reps and sales managers can apply should be limited.

Custom data rules allow you to harness the power of the Quick Base formula language to prevent the user from entering invalid data, displaying custom error messages you write in terms your end users will easily understand. You can enter complex, sophisticated business logic to determine exactly who should be allowed to enter certain types of data. 

“ToText(UserRoles("ID"))” is needed in this formula in six different places. Therefore, we’ll use a formula variable to improve legibility and speed up performance.

You can find a role’s ID by going to Users - Manage Roles, then clicking on a role. On that page, look at the end of the URL to get the role ID.

While you’ll need to replace the number in Contains(UserRoles("ID"),"13") with the role ID from your app, do not replace “ID” with anything specific to your app. Use the exact string “ID” in your custom data rule. The UserRoles() function has three different modes, and you use the text in quotation marks to choose which mode to use. You should use the "ID" mode for validating data for the same reason that Record ID# is a unique identifier for records. Each role has a unique role ID, but the names of roles may be similar (as in Sales Rep and Sales Manager above).

The multi–select text portion of the formula is UserRoles("ID"), which returns a list of the numeric role IDs for all roles the current user has been granted in the app. In order to interpret the list of role IDs, we need to convert the list into a single piece of text. That gives us ToText(UserRoles("ID")). That expression is used in the formula above, which is entered in the table’s advanced settings as the custom data rule.

In the example above, ToText() returns a semicolon-separated list. To search the list to match either the sales rep role, or the sales manager role, these notes apply:

If the app includes many roles, there might be a role with ID #13 and another role with ID #130. To avoid such false positives, match "; 13 ;" instead of matching "13".
If the sales rep role is the first role in the list, there won’t be a semicolon before the number 13. Similarly, if the sales rep role is the last role in the list, there won’t be a semicolon after the number 13. Use the Left() and Right() functions to account for this.
If user is assigned only a single role in the app, then match on just 13 and then make sure there aren’t any more characters in the list with the Length() function.
Multi-select text
Show fields from Show fields from Show fields from a related table
Report Name *
Description
Reports and Charts Panel
Each table has a panel listing its reports and charts, organized in groups.
Please wait while your new report is saved...
Field label
Column heading override
Justification
What does auto mean?
Fields in:

Fields to Extract:

Name for the new table:
Items in the new table are called:

When you bring additional fields into a conversion, Quickbase often finds inconsistencies. For example, say you're converting your Companies column into its own table. One company, Acme Corporation, has offices in New York, Dallas and Portland. So, when you add the City column to the conversion, Quickbase finds three different locations for Acme. A single value in the column you're converting can only match one value in any additional field. Quickbase needs you to clean up the extra cities before it can create your new table. To do so, you have one of two choices:

  • If you want to create three separate Acme records (Acme-New York, Acme-Dallas and Acme-Portland) click the Conform link at the top of the column.
  • If the dissimilar entries are mistakes (say Acme only has one office in New York and the other locations are data-entry errors) go back into your table and correct the inconsistencies—in this case, changing all locations to New York. Then try the conversion again.

Read more about converting a column into a table.

We're glad you're interested in doing more with Quickbase!

Now we need to make you official before you share apps or manage your account.

Verifying your email lets you share Quickbase with others in your company.

Your work email
Your company