)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Multi-select picklist fields from Salesforce custom objects need special handling when syncing to SharePoint because the semicolon-separated format doesn’t always match SharePoint’s requirements.
Here’s how to extract these complex field types and transform them into SharePoint-compatible formats using spreadsheet formulas.
Handle multi-select picklists with Coefficient
Coefficient provides excellent capabilities for importing multi-select picklist values from Salesforce custom objects while maintaining the semicolon-separated format. You can then transform these values in your spreadsheet to match SharePoint’s specific requirements.
How to make it work
Step 1. Import custom objects with multi-select picklists.
Connect to Salesforce through Coefficient and select your custom objects containing multi-select picklist fields. The import will preserve the semicolon-separated format that Salesforce uses for these field types.
Step 2. Apply filtering based on picklist values.
Use Coefficient’s filtering capabilities to filter records based on specific multi-select picklist values. You can filter for records containing specific options within the multi-select field, helping you target the right data for SharePoint sync.
Step 3. Transform multi-select values for SharePoint.
Create transformation formulas to convert the semicolon-separated values. Use SUBSTITUTE(A2,”;”,”,”) to convert to comma-separated format, or SPLIT(A2,”;”) to separate values into individual columns. For binary columns, use formulas like =IF(ISNUMBER(SEARCH(“Option1″,A2)),”Yes”,”No”) to create separate columns for each picklist option.
Step 4. Create SharePoint-compatible field mappings.
Build a mapping structure that shows how each transformed picklist value corresponds to SharePoint list columns. Some SharePoint fields may require specific formats or value mappings, so create lookup tables to ensure proper data translation.
Step 5. Set up automated processing.
Configure Coefficient’s scheduled refresh to keep your multi-select picklist data current. As new values are added to Salesforce picklists or existing records are updated, your transformation formulas will automatically process the changes.
Step 6. Prepare for integration tool consumption.
Format your processed data so Power Automate or other integration tools can easily consume it for SharePoint sync. Ensure column headers match SharePoint field names and that all transformed values are in the correct format.
Master complex field synchronization
This approach handles the complexity of multi-select picklist fields while giving you flexibility in how they appear in SharePoint. Get started with Coefficient to streamline your complex field mappings.
