Author: Yuri Gee
Date: 29 Oct 2024
5 minute read time
In today's SugarClub post, I'm diving into how you can mix and match Sugar Logic functions to pull content from multi-select fields in SugarCRM. Share your thoughts and any creative uses you've found for these features.
Let's explore Sugar Logic’s text search and parsing tricks in Sugar Sell, Serve, and Enterprise.
Scope, Formulas and User View
In this article, I’ll walk you through a calculated formula designed to extract the display label of the first value in a multi-select field.
In SugarCRM, a multi-select field lets you pick several string values from a list. This list is set up in Studio as {Item Name, Display Label}, where the display label is what the user actually sees.
Pulling the display label is super useful for counting and showcasing a record’s multi-select values as individual categories across reports, graphs, other fields, and related records. Assuming each multi-select item name is under 30 characters and doesn’t include the character `|`, here are the calculated formulas to pull the first value and save it to a TextBox or DropDown field:
getDropdownValue("multiselect_list",strReplace("|","",subStr(strReplace("^","||||||||||||||||||||||||||||||",subStr(toString($multiselect_test_c),1,30),true),0,30),true))
strReplace("|","",subStr(strReplace("^","||||||||||||||||||||||||||||||",subStr(toString($multiselect_test_c),1,30),true),0,30),true)
Here’s what happens when we apply these formulas to the test multi-select field, Case Status, as shown in the picture below.
The formulas above rely on a few key Sugar Logic functions — specifically `strReplace`, `subStr`, and `toString`— to extract data. In the next section, I’ll walk you through each part to explain why this works and what each function does.
Breakdown of Functions
- `toString($multiselect_test_c)`:
This function converts the multi-select field's object into a string format (where options' item names are separated by `^`). This step is necessary because the following string manipulation functions (`strReplace`, `subStr`) require the input to be a string.
- `subStr(…,1,30)` or `subStr(…,1,max(0,min(subtract(strlen(toString($multiselect_test_c)),1),30)))`:
`subStr` extracts a portion of the string, starting at position 1 (to exclude the first occurrence of ^). The length of the substring is determined by the non-negative number shorter of the two values: 30 or the length of the string (`strlen`) - 1. This limits the extraction to no more than the first 30 characters of the multi-select’s first option, ensuring that the value stays within bounds for whatever field it's being placed into.
The shorter version assumes that `subStr` correctly handles the situation when the length parameter exceeds the actual number of characters between the start position and the end of the string.
- `strReplace("^","||||||||||||||||||||||||||||||",...)`:
This part replaces the second separator character (`^`) in the multi-select string with a much longer string of marker characters (`||||||||||||||||||||||||||||||`). This replacement helps the formula easily identify and extract segments of the multi-select field's value. The marker string serves as a clear delimiter to distinguish the end of the first option within the multi-select field, which can be of arbitrary length between 0 and 30 characters.
- `subStr(…,0,30)`
This part cuts the string in such a way that the first value and parts of the marker will be in the extracted segment of the string, ensuring that the string does not contain any other value or delimiter (`^`).
- `strReplace("|","",...)`:
After the marker characters are used to help extract the segment of the string, `strReplace` is used again to remove the markers from the final result, leaving you with a clean portion of the original multi-select field value.
- `getDropdownValue("multiselect_list", ...)`:
This function is used for a TextBox field to convert the multi-select item name to a display label that should be visible to a user. It should not be called for a Dropdown field because a Dropdown field accepts the option's item name as its input.
What These Formulas Do
The formulas aim to extract the first option from a multi-select field and process it for two different purposes:
- TextBox Field: Displays the display label of the first multi-select item.
- DropDown Field: Displays the value of the first multi-select item, without converting it to the display label.
Formula Limitations
- Extracts Only the First Item: These formulas are designed to extract only the first option from the multi-select field. If multiple options are selected, this formula won’t process them all—it will only focus on the first one. If you need to process other options, you can adapt the formula to set markers on other values and process each option one by one until all options are extracted.
- Character Limit: The use of `subStr` and `min(strlen(...), 30)` limits the extraction to the first 30 characters (equal to the length of marker characters). This works well if each multi-select item is relatively short, but it could potentially cut off longer values. Increasing the marker length will allow for the processing of longer multi-select items, albeit with the trade-off of using longer strings during the extraction process. To avoid repeating marker-related calculations on the field each time, you can add conditional functions to calculate it only once.
Wrapping up
I'd love to hear your thoughts on these formulas! Have you found other creative ways to use Sugar Logic? Share your feedback and let’s keep the conversation going.