function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Alex AskewAlex Askew 

Formula to split array of product SKU's in text field into individual fields

Hi, 

I have an array of product SKU's all in one field seprated by commas e.g. SKU42, SKU08, SKU02, SKU12, SKU64

I would like to use a formula to split out the indidvidual SKU's into separte fields. e.g.
  • Text field 1 will just show the first SKU in the array (SKU42)
  • Text field 2 will just show the second SKU in the array (SKU08)
  • Text field 3 will just show the second SKU in the array (SKU02)
  • etc

Is that possible? I can only seem to get it to split out the first SKU at the moment using a formula but I'm struggling with the rest. 

Many thanks,
Alex. 

Best Answer chosen by Alex Askew
Peter FribergPeter Friberg
But don't forget to add a check for productvalues.size() > 0 before indexing [0], and size() > 1 before indexing [1] and so on, else it will throw an exception.
If spaces also are present in all/some position be sure to add .trim() after each assignment.
Try simthing like this, and just add more fields in the fieldNames list if you need: 
List<String> productValues = myObj.ProductList__c.split(',');
List<String> fieldNames = new List<String> { 'TextField1__c', 'TextField2__c', etc. };
for (Integer idx = 0; idx < productValues.size() && idx < fieldNames.size(); idx++) {
   myObj.put(fieldNames[idx], productValues[idx]);
}
Good luck!

All Answers

Akhil AnilAkhil Anil
Hi Alex,

Do you always have a fixed number of values in the array field and a fixed number of characters for each individual product ?
Alex AskewAlex Askew

Hi Akhil, 

No, it can vary but there's not likely to be more than 15 values in the array field.

Yes, there will be a fixed number of charcters for each individual product. 

Thanks,

Alex.
Alex AskewAlex Askew
The values will always be sperated by a space and a comma if that helps. 
Akhil AnilAkhil Anil
Since the values vary, this cannot be achieved using a formula field Alex. However, this can be easily done using an Apex trigger.
Alex AskewAlex Askew

Thanks Akhil. You couldn’t point me in the right direction for what I would need to put in my Apex Trigger to achieve this could you?

Thanks,

Alex.
Akhil AnilAkhil Anil
Sure Alex. You basically have to use a split() function in the trigger to fetch the individual values. You logic would be something like this.
  
List<String> productvalues = ProductList.split(',');
if(productvalues[0] != null)
   Textfield1__c = productvalues[0];
   
if(productvalues[1] != null)
   Textfield2__c = productvalues[1];

if(productvalues[2] != null)
   Textfield3__c = productvalues[2];

 
Peter FribergPeter Friberg
But don't forget to add a check for productvalues.size() > 0 before indexing [0], and size() > 1 before indexing [1] and so on, else it will throw an exception.
If spaces also are present in all/some position be sure to add .trim() after each assignment.
Try simthing like this, and just add more fields in the fieldNames list if you need: 
List<String> productValues = myObj.ProductList__c.split(',');
List<String> fieldNames = new List<String> { 'TextField1__c', 'TextField2__c', etc. };
for (Integer idx = 0; idx < productValues.size() && idx < fieldNames.size(); idx++) {
   myObj.put(fieldNames[idx], productValues[idx]);
}
Good luck!
This was selected as the best answer
Alex AskewAlex Askew
Thanks for your help both. I'm sure this is really obvious but I'm a complete beginner when it comes to Apex triggers. I usually just stick to formula fields, workflows and dabble a bit with process builder etc.  

I'm getting this error message below. Is it because I'm referencing the Opportunity object incorrectly? Tried searching for help but not having much luck. 

Error: Compile Error: Method does not exist or incorrect signature: [Schema.SObjectField].split(String) at line 2 column 30 
trigger product_array on Opportunity (before insert) {
List<String> productValues = Opportunity.Products__c.split(',');
List<String> fieldNames = new List<String> { 'Product_1__c', 'Product_2__c', 'Product_3__c' };
for (Integer idx = 0; idx < productValues.size() && idx < fieldNames.size(); idx++) {
   Opportunity.put(fieldNames[idx], productValues[idx]);
}
}

The field that holds the array of products is called 'Products__c'
The fields that I want to split the text into are called 'Product_1__c', 'Product_2__c', 'Product_3__c' etc. 
 
Alain CabonAlain Cabon
1) The string "SKU42, SKU08, SKU02, SKU12, SKU64" is not "splittable" with formulas as soon as you have more than two values (you must use some Apex code like above).

Only an array of product SKU's all in one field string like that: [1] SKU42 [2] SKU02 [3] SKU12 [4] SKU64 ... could be split with formulas (absolute positions for the bounds needed for each field). 

2)  You can also unload the IDs and the field containing the arrays of products with the free tool dataloader (SOQL SELECT) (or the workbench) and reload the exported data (UPDATE) with the dataloader adding just the new fields in the header ("Product_1__c","Product_2__c","Product_3__c") after removing the useless blanks and replacing single quote with double quotes (only some administration operation without coding) 
 https://developer.salesforce.com/page/Data_Loader

Alain
Alex AskewAlex Askew

Thanks all. I managed to get this to work using your Apex code Peter. I had to learn a bit about more about Apex Triggers/Classes but got there in the end ha. 

Just one question, how would I modify that to not run when the Product Array field is completely empty? It currently tries to run all the time and chucks up an error when it's empty. 

Thanks for all your help.
Alex.
 
Akhil AnilAkhil Anil
You can do it by simply wrapping the snippet within an IF condition like this
  
if(myObj.ProductList__c != null && myObj.ProductList__c != '') {
    List<String> productValues = myObj.ProductList__c.split(',');
    if(productValues.size() > 0) {
        List<String> fieldNames = new List<String> { 'TextField1__c', 'TextField2__c', etc. };
            for (Integer idx = 0; idx < productValues.size() && idx < fieldNames.size(); idx++) {
                myObj.put(fieldNames[idx], productValues[idx]);
            }
    }
}

That should do the trick !
Peter FribergPeter Friberg
Hi Alex Just add an if statement with String.isNotEmpty(myObj.ProductList__c) to check that your field is not empty :) Happy new year! Cheers Peter
Alex AskewAlex Askew

Thanks Peter and Akhil. Worked perfectly.
Happy New Year!

Thanks,
Alex.