Using formula - multi-select text fields

Formula – multi–select text fields enable you to dynamically build lists of text items, which can be used to:

  • Validate data based on a user’s role

  • Combine many checkboxes into a single column for reporting

  • Synthesize complex record data into a single column for reporting

Validate data based on a user’s role

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. To learn more, read about formulas for custom data rules.

Here’s a custom data rule you could use to enforce the discounting policy above:

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." 
)

“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.

Combine many checkboxes into a single column for reporting

Some app builders prefer to ask yes/no questions using a series of checkboxes, instead of a single multi-select text field. This is a style choice, and it can be helpful depending on the preferences of your end users, however, it can be challenging to include that information on a report since it can be awkward to include all the checkboxes as separate columns. Using a formula – multi–select text field, you can consolidate that checkbox information into one tidy column.

For example, a patient intake form might include a series of checkboxes asking for pain symptoms:

You could use a formula - multi–select text field to consolidate that information into one column:

Here’s the formula to create the Pain column above:

Split(List(" ; ",

If([Lower back pain?] = true, "Lower back"),
If([Upper back pain?] = true, "Upper back"),
If([Neck pain?] = true, "Neck"),
If([Chest pain?] = true, "Chest")

))

Each If statement adds an item to the list if its corresponding checkbox is checked. Then, those are combined into a single piece of text using the List function. The Split function converts from text to multi-select text. You can extend this formula if your table has more checkboxes to include, adding an If statement for each checkbox field.

Synthesize complex record data into a single column for reporting

Quick Base apps can be used to track very complex items and workflows, resulting in tables that contain hundreds of fields. Selecting the right fields to include in reports makes it easy to see what’s going on at-a-glance, then allowing you to click into the form view to get more information about an individual record. You can use a formula – multi–select text field to display a list of tags in a single column, reducing the number of single-record forms you need to click into.

For example, consider a table tracking issues for a software product:

You can use a formula – multi–select text field to give a high-level summary of the issue:

Here’s the formula to create the Tags column above:

Split(List(" ; ",
If([Security issue?] = true, "Security issue"),
If([Exists in production?] = true, "Production issue"),
If([Priority] = "Critical", "P0 – Critical issue"),
If([Priority] = "Major", "P1 – Major issue"),
If([Frequency] = "Intermittent" or [Frequency] = "Cannot reproduce", [Frequency]),
[Discovery Method]
))

Like the previous example, if the Security issue? or Exists in production? checkbox is checked, then a corresponding tag is displayed. The Priority field, however, only displays a tag if the issue is either critical or major. This helps to distill the most important facts about a record and would focus the app’s users on the most pressing issues. Similarly, the Frequency field only adds a tag to the list if the issue happens intermittently or can’t be reproduced. The Discovery Method tag indicates whether the issue was discovered by a customer or an internal employee. You can use patterns like these to build a list of tags based on as many fields as you want, no matter what types of fields they are.