Business Use case :-
You have a requirement where you would need to group/roll up/summarize child records at the parent level.
example : List of products at the Opportunity Level. Here Opportunity is (Parent) and the Opportunity Line Items is (Child).
Problem Statement :
Before Spring 23 we didnt had a straight functions in recipe to group/roll up/summarize child records at the parent level. . we could acheive this by set of steps to put together all child records at the same level as the parent but we will end up having an individual column for each child value instead of a single field for all.which is time consuming as well as run time increses process.
The first thing we used to do is to determine how many rows deep we need to go per each Parent. based on the deep level we would create that much individual column of child records then we will concatenate all those columns to have a single field for all child records/values. below two screnshot illustrates whole steps.
Solution
In Spring 23 we have couple of new below listed functions to get a array or list of comma-separated values for a particular column while doing grouping operation. these direct functions eliminates the additional steps and developing time .
collect_list()collect_set()array_distinct()
collect_list()
Function returns all the current input column values with the duplicates.
Forumla: collect_list(field)
collect_set()
Function returns all values from the present input column with the duplicate values eliminated
Formula: collect_set(field)
array_distinct()
Function removes duplicate values from the array.
Formula: array_distinct(mvField)
Solution Approach 1 (with dupliactes)
The first array creation function is called collect_list(). It can be used either to group the values or aggregate them with the help of a windowing operation.
We will begin adding Multiple row formula/ Window functions to start rolling up the child lines..For this specific case, we can group by Opportunity ID and collect all of the product into an array.
Enable a Multiple row formula, Partition it by Opportunity ID ,Order it by ProductName Ascending, and Output Type as Multivalue
In the following Sample records When observed deeply, 0062w000006TVa1AAG opportunity has 8 products with one duplicates.
The crucial highlight for the collect list is that the function keeps all the duplicated values inside of the array by keeping the sequence of the items.
Note:- Here sequence of the items means product Name sorted by ascending which was defined at the windowing operation .
Yayy…look at the ListOfProductString column/field we have every products in a single field without much sweat to get it done.
For the demontration purpose we have converted the multivalue field into string type.
please refer the below link for the multivalue limitations in CRM analytics.
This dataset is at the Opportunity Line Item level (child). so after the window function opeartion. I have filtered the last records of each partition for a single opportunity with array of products. so that we can augument oppty products to the opportunity records level.below is the formula for identifying the last row of each partition
Unifying Elements in the Arry without changing the order of sequence:
When we would like to eliminate the duplicate values by preserving the order of the items . we can use array_distinct() function before applying collect_list function.
In the following sample records , we can clearly observe that the initial sequence of the elements is kept. For instance, 0062w000006TVa1AAG opportunity, we see that the initial order of the items of Cables, Cases, Courses ,Light laptop, Mini tablet 2, Space phone 2, Space phone 3 was kept as Cables, Cases, Courses ,Light laptop, Mini tablet 2, Space phone 2, Space phone 3 after the collect_list() operation without losing the sequence.
Solution Approach 2(without dupliactes)
The second array creation function is called collect_set(). It can be used either to group the values or aggregate them with the help of a windowing operation.
The crucial highlight for the collect_set() is that the function eliminates the duplicated values inside of the array. However, it does not guarantee that the sequence of the items in the array.
By looking at the 0062w000006TVa1AAG opportunity, we see that the initial order of the items of Cables, Cases, Courses ,Light laptop, Mini tablet 2, Space phone 2, Space phone 3 was changed into Cables,Courses, Cases ,Space phone 2,Light laptop, Mini tablet 2, , Space phone 3 after the collect_set() operation.
Summary :
Conclusion
we can use collect_list() and collect_set()functions to generate arrays with different perspectives.
Thecollect_list() operation is not responsible for unifying the array list. It fills all the elements by their defined order in the window function and does not remove any of the duplications.
On the other hand, the collect_set()operation does eliminate the duplicates; however, it cannot save the defined order of the items in the array.
With the aim of unifying our elements in the array , we can easily use array_distinct() function before collect_list() function to eliminate the duplicated values without losing the sequence of the elements in the array.
Happy Learning
you can follow me on linked @linkedin.com/in/ramdoss-k-5157566a
