+ Start a Discussion
Priyanka BardhanPriyanka Bardhan 

How to find out the unique combination from 2 fields which contains comma separated values?

Hi All,
Can anyone please help on the below approach?

Requirement - There is a custom object with 2 fields - Categories, Areas and both fields are of Long text area datatype. Both the fields stores comma separated values same like below:
Table structure records in those fields are like below:
ID       |      Categories               |    Areas
Rec1        C1, C2, C3, C4...               A1, A2, A3, A4...
Rec2        C2, C3, C7, C8                  A4, A5, A6, A9
Requirement is if user is trying to key new selections with combinations of category and area which already exists in above table then it should show validation.
Example - If user is trying to key C2, C4, C7, C8 and Area A3, A4, A6 then it should give an error message. 
Validation will be checking the combinations using the selections made during keying:
C2-A3, C2-A4, C2-A6
C4-A3, C4-A4, C4-A6
C7-A3, C7-A4, C7-A6
C8-A3, C8-A4, C8-A6
Above combinations needs to be validated with the table records combinations same format.
Solution Approach:
1. Implement static methods to do Salesforce SOQL query to get all the records first.
Define Static set and map variables. Set variable will be to define the combinations and Map will be if we want to have the record along.
2. Implement the below method to collect the values from comma separated to set list.
    public static Set<String> storeValuesInSet(String fieldValue){
        Set<String> value_lst = new Set<String>();
        //split the values and put them in a set.
        String[] values = fieldValue.split(',');
        for (Integer x = 0; x < values.size(); x++){
        return value_lst;
3. The values will be stored during for loops. One for loop will be to iterate through the object records. Since fields are long text area and value is comma separated, inside first for loop will call the method storeValuesInSet and then iteratinfg
Set<String> uniqueKeys = new Set<String>();
Below will be map values:
Rec 1, Set< C1-A1, C1-A2, C1-A3, C1-A4 >
Rec 1, Set< C2-A1, C2-A2, C2-A3, C2-A4 >
Rec 1, Set< C3-A1, C3-A2, C3-A3, C3-A4 >
Rec 1, Set< C4-A1, C4-A2, C4-A3, C4-A4 >
Rec 2, Set< C2-A4, C2-A5, C2-A6, C2-A9 >
Rec 2, Set< C3-A4, C3-A5, C3-A6, C3-A9 >
Rec 2, Set< C7-A4, C7-A5, C7-A6, C7-A9 >
Rec 2, Set< C8-A4, C8-A5, C8-A6, C8-A9 >
2. Once user clicked save button, we can use contains method of set variable to find duplicates.
Please let me know if there is any other good way for achieving this or whether the above is correct approach?.Thanks for your help. 
Thanks & Regards,
Waqar Hussain SFWaqar Hussain SF

Your requirements are not clear, Can you please elaborate, how do you key values?
Do you mean that the user should not able to create duplicate categories and areas?
Priyanka BardhanPriyanka Bardhan
Thanks for your reply.
Requirement : We have the below records saved in Instance object.

Table structure records in those fields are like below:

ID       |      Categories               |    Areas
Rec1        C1, C2, C3, C4...               A1, A2, A3, A4...
Rec2        C2, C3, C7, C8                  A4, A5, A6, A9
Rec3        C4,C9, C10                        A1, A3

From UI screen page, if I am trying to select C3, C10 category and A6, A9, A1 area and trying to save, then there should be validation method and it will look in instance records and since C3-A6, C3-A9, C10-A1 combination exists in Rec2, Rec3. Hence, it should display error message showing combination exists in Rec2, Rec3 and user shouldn't be allowed to save.

Priyanka Bardhan 
Waqar Hussain SFWaqar Hussain SF
OK My assumption is that you are trying to select category and area from VF page. 

See the following approach.

Create a set(property) of string, which will contain all existing combination. This set will contain all combination in string. Then in validate method, get all selected combination from UI in a list and itrate the list to validate each key, as below.
//this set will contain all existing combination
public set<String> existinCombination;
public boolean isValid ;

//This list will contain all selected combination
List<String> selectedCombination = new List<String>();

Public pageReference Validate(){
    for(string key : selectedCombination){
            isValid = false;
            ApexPages.Message myMsg = new ApexPages.Message(ApexPages.Severity.ERROR,'This combination '+key+ ' already exist.');

Let me know If you have any question.

Waqar Hussain
Skype : waqar.hussain991