Author: Yuri Gee
Date: 27 Nov 2024
5 minute read time
This SugarClub post continues the series on extracting multi-select fields. In this article, I'll demonstrate how to filter related records and extract values that satisfy binary conditions using SugarLogic functions. As an example, I'll show you how to extract multi-select field values from related records into a Basic Sugar report.
I'd love to hear your thoughts and any creative ways you've utilized these features!
Scope, Formulas and User View
In this post, I will guide you through creating a Basic report where Leads can be sorted based on the extended status of related Meetings. The extended status is stored in a multi-select field with several options that can be chosen simultaneously.
First, I use Sugar Formulas in the Meetings module to convert options from the multi-select field into Integer fields, where 1 indicates that the Meeting has the option selected and 0 otherwise. This process should be repeated for each multi-select option that needs to be extracted.
Field #1: option_needsfollowup_c - Integer
Formula #1: ifElse(contains(toString($multi_select_c),"Needs Follow Up"),1,0)
Field #2: option_awaitingapproval_c - Integer
Formula #2: ifElse(contains(toString($multi_select_c),"Awaiting Approval"),1,0)
Next, I calculate the binary conditions in the Leads module for each related record using the rollupSum formula. If the resulting value is greater than 0, it indicates that at least one related record has the option enabled; otherwise, the value is 0.
Field #3: option_needsfollowup_c - TextField
Formula #3: ifElse(greaterThan(rollupSum($meetings,"option_needsfollowup_c"),0),"Needs Follow Up","")
Field #4: option_awaitingapproval_c - TextField
Formula #4: ifElse(greaterThan(rollupSum($meetings,"option_awaitingapproval_c"),0),"Awaiting Approval","")
Here’s what occurs when we place the fields on the layout and update the related Meetings’ Extended status:
Meeting 1 (using one option for Extended status):
Meeting 2 (using several options for Extended status):
Each unique status option gets transferred to the parent Leads record’s corresponding field. By default, it happens automatically once the related Meeting is saved. These fields can be hidden from the user's view and used exclusively in reports.
Here is how it looks in the Summation Report with details, where records can be sorted by individual categories and the report can be placed on the dashboard. The report is created in the parent Leads module, which allows it to easily avoid duplicates as multi-select values are already pre-calculated in the parent.
Breakdown of Formulas
Formula #1 (Meetings module): Store binary count for a multi-select option in a Meeting record. ifElse(contains(toString($multi_select_c),"Needs Follow Up"),1,0)
- toString($multi_select_c):
Convert the original object representing the multi-select field to a string.
- contains(toString(...),"Needs Follow Up"):
This formula returns true if the multi-select option “Needs Follow Up” is enabled and requires specifying the multi-select option by item name.
- ifElse(…):
Returns 1 if the option is enabled in the record’s multi-select field and 0 otherwise.
Formula #3 (Leads module): Store multi-select option on a Lead if the option is enabled on at least one related Meeting. ifElse(greaterThan(rollupSum($meetings,"option_needsfollowup_c"),0),"Needs Follow Up","")
- rollupSum($meetings,"option_needsfollowup_c"):
Sums counts for multi-select option “Needs Follow up” for every related record (see formula #1).
- greaterThan (…,0):
This formula returns true if the multi-select option “Needs Follow Up” is enabled in at least one related Meeting.
- ifElse (…,"Needs Follow Up",""):
Stores the category name representing the option if the option is enabled in at least one related Meeting; otherwise, it stores a blank value.
What These Formulas Do
The formula extracts the options from the multi-select field ‘Extended Status’ and stores them as binary flags (Integer fields) for each option on a Meeting record.
When a related record is saved, the rollupSum formula on the parent Lead record is executed, updating its count of individual options for all related records.
If the count is greater than 0, it indicates that at least one related record has the option enabled. The parent Lead record is then updated to reflect this option. This update is also visible in the basic Sugar report created in the Leads module, such as a Summation report with details.
Formula Limitations
- Extracts only values that can satisfy binary or a few predefined conditions: The proposed approach works in a situation that has only limited possible outcomes. An option either is present or not, the record meets the criteria or not. Such limited outcomes can be counted by rollup functions for all related records.
- Individual category cannot be counted in the Sugar (basic) Report: The categories can be sorted in the report, and the records can belong to a higher-level group. However, individual categories cannot be automatically counted independently of one another in a basic Sugar Report (vs Advanced Report). However, the categories can be counted in a Spreadsheet tool after downloading the report as CSV.
- New options require formula update: When new options are added to the multi-select list, the formulas and supporting fields should be updated to include these new options.
Wrapping up
I’d love to hear your thoughts on these formulas! Have you found any other creative ways to use Sugar Formulas and Basic reports? Share your feedback, and let’s keep the conversation going.
You might also find the following article helpful, as it covers using custom queries to retrieve values from multi-select fields without relying on pre-calculated formulas.
A similar approach using the rollupSum function to count related activities is illustrated in this Knowledge Base article. This post demonstrates how the method can be extended to any binary or multiple-choice condition, such as checking for an enabled option in a multi-select field.