Author: Yuri Gee
Date: 21 Nov 2024
5 minute read time
In today’s SugarClub post, I'll be showing you how to extract values from a multi-select field in SugarCRM and display them as individual categories in an Advanced Report. I’d love to hear your thoughts and any creative ways you've used these features.
Let's delve into the Advanced Reports and Custom Queries that can be utilized in Sugar Sell, Serve, and Enterprise.
Scope, Formulas and User View
In this article, I will walk you through creating a custom query that extracts and displays the options from a multi-select field in SugarCRM records, presenting them as individual categories in an Advanced Report.
In SugarCRM, a multi-select field lets you choose multiple string values from a predefined list. This list is set up in Studio as {Item Name, Display Label}, where the display label represents the value visible to users.
Here's the custom query to extract the item names from the multi-select field multiselect_test_c for each Case record.
select distinct c2.id, c2.case_number, c2.name, a.category from (
select 'High Priority' as category
union select 'Pending Review Urgent'
union select 'Client Feedback Required'
union select 'Escalation Required'
union select 'Waiting on Client Action Needed'
union select 'Technical Issue'
union select 'Billing Inquiry FollowUp'
union select 'Completed Verified'
union select 'Internal Review' ) as a
inner join cases_cstm c on (find_in_set(a.category,
replace(c.multiselect_test_c,"^","")) <> 0 )
inner join cases c2 on c2.id=c.id_c and c2.deleted=0
group by a.category, c2.id order by c2.id desc, a.category desc;
Here’s what occurs when we use this query in a Custom Query or Advanced Report:
The query above produces individual categories organized by each case ID.
Breakdown of Query
- (select 'High Priority' as category
union select 'Pending Review Urgent' …. ) as a :
This query generates a table named 'a' containing multi-select item names, excluding a blank entry.
- replace(c.multiselect_test_c,"^","") :
It transforms the original multi-select values stored in the database into a comma-separated string by removing the ^ characters. This assumes all records have correctly formatted field values (e.g. ^value1^,^value2^ or null)
- find_in_set(a.category, replace(…)) :
The find_in_set function searches for a category in the comma-separated string of multi-select values. It returns 0 or null if the category is not found in the string or if the string is null.
- inner join cases_cstm c on (find_in_set(…) <> 0 ) :
This query joins table 'a' with the cases_cstm table so that each row contains a case ID uniquely identifying each case in that category. If no cases exist for a particular category, the category is omitted.
- select distinct c2.id, c2.case_number, c2.name, a.category from ( select 'High Priority' … ) :
It displays columns such as case ID, number, name, and case category from the table joined in step 4. These columns undergo additional join and grouping in steps 6 and 7. If you are joining tables in a 1:1 correspondence (such as cases and cases_cstm joined on case ID), using the distinct keyword is optional.
- inner join cases c2 on c2.id=c.id_c and c2.deleted=0 :
This inner join of the table from step 4 filters out deleted cases. It requires joining cases where the deleted flag is stored.
- group by a.category, c2.id:
This part groups the table rows by category and case ID.
- order by c2.id desc, a.category desc :
The final table is ordered by case ID and category in descending order, with the columns displayed according to the statement in step 5.
What This Query Does
The query extracts the options from the multi-select field for each case, presenting a list of cases sorted by case ID, with each category assigned to that case on a separate row.
To count the number of cases in each individual category, you can use the following query:
select count(*), category from (
… [original query with step 8 – order by – removed ] … ) as t group by category order by category desc;
Query Limitations
- Extracts only parent items: These formulas are designed to extract elements from parent module records where the multi-select field is created. If you have multi-select fields in child records, the query should be modified to include joins that connect child and parent records.
- Requests adding each new category (and special treatment for blank fields): When new options are added to the multi-select list, the query should be modified to include these new options. For correct treatment of blank values, the query needs to be altered to handle null value checks.
- Applies to MySQL database: This query is created for MySQL database. For other databases the syntax of the query can be vary.
- Custom queries cannot be put on a standard reports dashlet: Default report dashlets allow only Basic reports. The Advanced report generated from the custom query can be scheduled and delivered via email.
- Display value presentation: If the display labels of your multi-select list differ visually from the item names stored in the database, you may need to replace these values in the resulting select query. Alternatively, you may want to extend the Select Union elements to include an additional column for the display value.
Wrapping up
I’d love to hear your thoughts on this query! Have you discovered any other creative ways to use Custom Queries and Advanced Reports? Share your feedback, and let’s keep the conversation going.
You might also find the following article helpful, as it covers using pre-calculated Sugar Formulas for retrieving values from a multi-select field.