Possibly frowned upon, right!? I hear you.
But as we know in the real world, we often need to preserve the client's design for various reasons.
In this example, we have a multi select pick-list that is updated over the client's API and shows the user the countries that the Account is selling in on their platform.
The client asked me to create a system that would let them have weekly reporting per country that the Account sells into and also get reporting visibility across these countries for all Accounts in Salesforce.
First, the object model;
New objects;
- Country - just a record that has a Country label assigned to it
- Account Country - for each Country an Account sells into according to the pre-existing multi pick-list, we create an Account Country record with the appropriate label
- External Reporting - our weekly reporting loads here, and we also link new records to prior records, so we can easily see shifts in the data from week to week or month to month
I won't go into detail here about the fields on the objects, but you can probably see that the country records only need the required lookups and a country value and the external reporting records the required lookups and any weekly values you are loading from your external data system.
In addition, I created formula fields that calculate "% Change" so we can plot trends and changes in data over time.
Country Requirements:
- Add a new country when a new country is added to the Account multi select pick-list.
- Set the status of a country to 'inactive' if that country is later removed from the list.
Step one lets create the flow to manage this;
- First, we load all of our pick-list values into a variable (AccountShippingCountries) in the first assignment
- Next, we perform a Get on all the Account Country records related to the Account that triggered the flow
- Loop through those Account Country records and assign the Account Country names into a new variable "AccountCountries"
Note that I use a loop counter and a formula field to create a variable that stores the country list as a semicolon separated string, just like how the default storage is for the multi select pick-list values are (this is important to do for the next step).
The Formula to use is; (you will need to trade the exact merge field for the name of the country on the records you are using)
IF({!LoopCount} <= 1,
{!Loop_Current_Countries.Related_Country__r.Unique_Country__c},
";"+{!Loop_Current_Countries.Related_Country__r.Unique_Country__c}
)
We now have the current field values versus actual present records on the Account.
We then use some custom code in an action to compare the two strings.
We then set all the common values from the output of the above comparison to 'Active'
We then use a similar process to create any new Account Country records using the output from the Compare function of "uniqueValuesA".
And then lastly the function "uniqueValuesB" to either delete or in this case 'set their status to inactive'.
If you delete the records it will delete the related External Reporting Records - that is why based on the client's feedback I added a status value, so old reporting data is not immediately lost.
So now on the Account I have selected...
These Account Countries...
And these External Reporting records from the API feed...
In my next post, I will show you how to automatically relate the External Reporting records to the correct Account Country records so that your data integration team doesn't have to work this out, and you have full bulkified management of this process in just a single flow!
Below are the apex classes for the flow action, credits go to https://github.com/DouglasCAyers/sfdc-compare-multi-select-picklists
You may be better creating the apex classes manually, as the code base is not always installable from GitHub directly.
Apex Class -
public class MultiPicklistFieldCompareInvocable {
@InvocableMethod(
label = 'Multi-Select Picklist Field Compare'
description = 'Compares two field values from multi-select picklists (or any ; delimited text) and determines similarities'
)
public static List<Response> execute( List<Request> requests ) {
List<Response> responses = new List<Response>();
for ( Request req : requests ) {
// avoid null pointer exceptions
Set<String> picklistValuesA = String.isBlank( req.picklistA ) ? new Set<String>() : new Set<String>( req.picklistA.split('(\\s*;\\s*)') );
Set<String> picklistValuesB = String.isBlank( req.picklistB ) ? new Set<String>() : new Set<String>( req.picklistB.split('(\\s*;\\s*)') );
System.debug( 'values in first picklist: ' + picklistValuesA );
System.debug( 'values in second picklist: ' + picklistValuesB );
Set<String> commonValues = new Set<String>( picklistValuesA );
commonValues.retainAll( picklistValuesB );
System.debug( 'common values in both picklists: ' + commonValues );
Set<String> uniqueValuesA = new Set<String>( picklistValuesA );
uniqueValuesA.removeAll( picklistValuesB );
System.debug( 'unique values in first picklist: ' + uniqueValuesA );
Set<String> uniqueValuesB = new Set<String>( picklistValuesB );
uniqueValuesB.removeAll( picklistValuesA );
System.debug( 'unique values in second picklist: ' + uniqueValuesB );
Response res = new Response();
// if this is called from flow, flow can't check if a variable list is empty
// but it can check if the variable is null. So if any set is empty we return null.
res.commonValues = ( commonValues.size() > 0 ) ? new List<String>( commonValues ) : null;
res.uniqueValuesA = ( uniqueValuesA.size() > 0 ) ? new List<String>( uniqueValuesA ) : null;
res.uniqueValuesB = ( uniqueValuesB.size() > 0 ) ? new List<String>( uniqueValuesB ) : null;
responses.add( res );
}
return responses;
}
// ------------------------------------------------------
public class Request {
@InvocableVariable(
label = 'First Multi-Select Picklist'
description = 'Value from first multi-select picklist field to compare'
)
public String picklistA;
@InvocableVariable(
label = 'Second Multi-Select Picklist'
description = 'Value from second multi-select picklist field to compare'
)
public String picklistB;
}
public class Response {
// invocable variables don't support Sets so using Lists
@InvocableVariable(
label = 'Common Multi-Select Values'
description = 'Common values found in both multi-select picklists'
)
public List<String> commonValues;
@InvocableVariable(
label = 'First Multi-Select Picklist Unique Values'
description = 'Unique values in first multi-select picklist that are not in second multi-select picklist'
)
public List<String> uniqueValuesA;
@InvocableVariable(
label = 'Second Multi-Select Picklist Unique Values'
description = 'Unique values in second multi-select picklist that are not in first multi-select picklist'
)
public List<String> uniqueValuesB;
}
}
Test Class -
@isTest
private class MultiPicklistFieldCompareInvocableTest {
@isTest
static void test_null_field_values() {
String picklistA = null;
String picklistB = null;
MultiPicklistFieldCompareInvocable.Request req = new MultiPicklistFieldCompareInvocable.Request();
req.picklistA = picklistA;
req.picklistB = picklistB;
Test.startTest();
List<MultiPicklistFieldCompareInvocable.Request> requests = new List<MultiPicklistFieldCompareInvocable.Request>{ req };
List<MultiPicklistFieldCompareInvocable.Response> responses = MultiPicklistFieldCompareInvocable.execute( requests );
Test.stopTest();
System.assertEquals( null, responses[0].commonValues );
System.assertEquals( null, responses[0].uniqueValuesA );
System.assertEquals( null, responses[0].uniqueValuesB );
}
@isTest
static void test_only_common_values() {
// in apex, the values from multi-select
// picklists are simply delimited strings
String picklistA = 'Apple;Banana';
String picklistB = 'Apple;Banana';
MultiPicklistFieldCompareInvocable.Request req = new MultiPicklistFieldCompareInvocable.Request();
req.picklistA = picklistA;
req.picklistB = picklistB;
Test.startTest();
List<MultiPicklistFieldCompareInvocable.Request> requests = new List<MultiPicklistFieldCompareInvocable.Request>{ req };
List<MultiPicklistFieldCompareInvocable.Response> responses = MultiPicklistFieldCompareInvocable.execute( requests );
Test.stopTest();
Set<String> expectedCommonValues = new Set<String>{ 'Apple', 'Banana' };
System.assertEquals( expectedCommonValues.size(), responses[0].commonValues.size() );
System.assert( expectedCommonValues.containsAll( responses[0].commonValues ) );
System.assertEquals( null, responses[0].uniqueValuesA );
System.assertEquals( null, responses[0].uniqueValuesB );
}
@isTest
static void test_only_unique_values() {
// in apex, the values from multi-select
// picklists are simply delimited strings
String picklistA = 'Apple;Banana';
String picklistB = 'One;Two';
MultiPicklistFieldCompareInvocable.Request req = new MultiPicklistFieldCompareInvocable.Request();
req.picklistA = picklistA;
req.picklistB = picklistB;
Test.startTest();
List<MultiPicklistFieldCompareInvocable.Request> requests = new List<MultiPicklistFieldCompareInvocable.Request>{ req };
List<MultiPicklistFieldCompareInvocable.Response> responses = MultiPicklistFieldCompareInvocable.execute( requests );
Test.stopTest();
System.assertEquals( null, responses[0].commonValues );
Set<String> expectedUniqueValuesA = new Set<String>{ 'Apple', 'Banana' };
System.assertEquals( expectedUniqueValuesA.size(), responses[0].uniqueValuesA.size() );
System.assert( expectedUniqueValuesA.containsAll( responses[0].uniqueValuesA ) );
Set<String> expectedUniqueValuesB = new Set<String>{ 'One', 'Two' };
System.assertEquals( expectedUniqueValuesB.size(), responses[0].uniqueValuesB.size() );
System.assert( expectedUniqueValuesB.containsAll( responses[0].uniqueValuesB ) );
}
@isTest
static void test_common_and_unique_values() {
// in apex, the values from multi-select
// picklists are simply delimited strings
String picklistA = 'Apple;Orange;Banana'; // obj1.picklist__c
String picklistB = 'Apple;Pear;Plum;Orange'; // obj2.picklist__c
MultiPicklistFieldCompareInvocable.Request req = new MultiPicklistFieldCompareInvocable.Request();
req.picklistA = picklistA;
req.picklistB = picklistB;
Test.startTest();
List<MultiPicklistFieldCompareInvocable.Request> requests = new List<MultiPicklistFieldCompareInvocable.Request>{ req };
List<MultiPicklistFieldCompareInvocable.Response> responses = MultiPicklistFieldCompareInvocable.execute( requests );
Test.stopTest();
Set<String> expectedCommonValues = new Set<String>{ 'Apple', 'Orange' };
System.assertEquals( expectedCommonValues.size(), responses[0].commonValues.size() );
System.assert( expectedCommonValues.containsAll( responses[0].commonValues ) );
Set<String> expectedUniqueValuesA = new Set<String>{ 'Banana' };
System.assertEquals( expectedUniqueValuesA.size(), responses[0].uniqueValuesA.size() );
System.assert( expectedUniqueValuesA.containsAll( responses[0].uniqueValuesA ) );
Set<String> expectedUniqueValuesB = new Set<String>{ 'Pear', 'Plum' };
System.assertEquals( expectedUniqueValuesB.size(), responses[0].uniqueValuesB.size() );
System.assert( expectedUniqueValuesB.containsAll( responses[0].uniqueValuesB ) );
}
}