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
Linda 98Linda 98 

trigger to get values from multi-select picklist to SOQL

Having a trigger to fire when a value is changed.
On update of the field, it should consider multi-select picklist values(List of roles-As it's multi-select datatype, may have one role is chosen or multiple chosen) and use them in a query to contact(Multi-select picklist field). Further, I would like to use those contact ids to create records in another custom object.
I saved the roles on record(trigger.new) into a list. But when I try to query to contact, I am not able to get the values.

For instance,
Values on Record 1-
checkbox -checked to true(which fires trigger)
Roles- Manager; Owner; Supervisor

Contact records-
contact 1-
Roles :Manager;Supervisor
Contact 2-
Roles-Owner
Contact 3:
roles-Manager;Architect
Contact 4-
Roles-Manager
Contact 5-
Roles: Analyst;Architech

In the above example, I would like to get all the contact IDs except contact 5. I even want Contact 3 even his roles include Architect which is not in our record roles list. As he is having a manager role, I would like to collect it too.

I used  
conroles=[select id,Roles__c,Title from contact where Roles__c IN :Roleslist];
Roleslist=List of roles on Record.
But its only fetching first roles contact's.

Split(',') doesn't seem to work, What am I doing wrong?
Best Answer chosen by Linda 98
Andrew GAndrew G
Hi Linda

Using some ideas from Alain (thanks), i created the following trigger that should give you the complete idea on how to solve your issue.

Contact_linkage__c is just a custom object with Contacts as a related list.  Contact has a field linking to the custom object.  I'm only dealing with a single record, so this trigger would not survive a bulk insert of the Contact_Linkage object.
  
trigger linkContacts on Contact_linkage__c (after insert, after update) {
	
	List<String> roles = new List<String>();
	Contact_linkage__c firstRecord = trigger.new[0];
	String query = 'SELECT Id,Name,Role__c FROM Contact';  //start building a query string

	for (Contact_linkage__c my : trigger.new ) {
		if (my.Link_Stuff__c ) {   //a check box on my custom object
			if (roles.size() == 0 ) {
				roles = my.Role__c.split(';');
			} else {
				roles.addall(my.Role__c.split(';'));
			}
		}
	}
	
	if(roles.size() > 0 ) {
		for (Integer i =0; i<roles.size(); i++ ) {
			if (i == 0 ) {
				query += '  WHERE Role__c INCLUDES (\''+roles[0]+'\'';   //first record so need the WHERE 
			} else {
				query += ',\''+roles[i]+'\'';  //now we just add the roles using the backslash to escape and put the quote marks in
			}
		}
		query += ')';  //close the query string
		List<Contact> contacts = new List<Contact>();
		contacts = Database.query(query);
		for( Contact contact : contacts ) {
			contact.Contact_linkage__c = firstRecord.Id;
		}
		if (contacts.size() > 0 ) {
			update contacts;	
		}
	}
}

HTH

Andrew

All Answers

Alain CabonAlain Cabon
@Linda 98

multi-select picklist values?  

It is not a comma but a semicolon. 
SELECT Id, MSP1__c FROM CustObj__c WHERE MSP1__c = 'AAA;BBB'
SELECT Id, MSP1__c from CustObj__c WHERE MSP1__c includes ('AAA;BBB','CCC')

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_querying_multiselect_picklists.htm
 
Linda 98Linda 98
Yes. I used spilt(';') and added to a list. So my roleslist has (manager,owner, supervisor) in my debug log.
Alain CabonAlain Cabon
@Linda

You can test your query directly by usng the developer console.

i didn't see your filter at the beginning with the semicolons: Manager; Owner; Supervisor 

But that was commas here indeed.

It is just "includes" instead of "in".
  
select id,name from contact where role__c includes ('Manager','Owner','Supervisor')
Linda 98Linda 98
Ok let me try with includes.Thank you.
Linda 98Linda 98
I am not able to use include as I am using variable name. Could you please help me with the syntax using a variable rather than hard coded values. Thank you
Linda 98Linda 98
My values in debug log are as below (Manager,owner, supervisor) Instead of ('manager','owner','supervisor') Is that a culprit??
Alain CabonAlain Cabon
@Linda

The following code works (dynamic SOQL). 
  String[] roles = new String[]{'\'Manager\'','\'Owner\'','\'Supervisor\''};
  String roleids = String.join(roles,',');
  string query = 'select id,name from contact where role__c includes (' + roleids + ')';
  system.debug('query:' + query);
  List<Contact> cnt = Database.query(query);
  system.debug('cnt size:' + cnt.size());

The following syntax doesn't work indeed:
 List<Contact> cnt = [select id,name from contact where role__c includes (:roleids)];
 
Andrew GAndrew G
Hi Linda

Using some ideas from Alain (thanks), i created the following trigger that should give you the complete idea on how to solve your issue.

Contact_linkage__c is just a custom object with Contacts as a related list.  Contact has a field linking to the custom object.  I'm only dealing with a single record, so this trigger would not survive a bulk insert of the Contact_Linkage object.
  
trigger linkContacts on Contact_linkage__c (after insert, after update) {
	
	List<String> roles = new List<String>();
	Contact_linkage__c firstRecord = trigger.new[0];
	String query = 'SELECT Id,Name,Role__c FROM Contact';  //start building a query string

	for (Contact_linkage__c my : trigger.new ) {
		if (my.Link_Stuff__c ) {   //a check box on my custom object
			if (roles.size() == 0 ) {
				roles = my.Role__c.split(';');
			} else {
				roles.addall(my.Role__c.split(';'));
			}
		}
	}
	
	if(roles.size() > 0 ) {
		for (Integer i =0; i<roles.size(); i++ ) {
			if (i == 0 ) {
				query += '  WHERE Role__c INCLUDES (\''+roles[0]+'\'';   //first record so need the WHERE 
			} else {
				query += ',\''+roles[i]+'\'';  //now we just add the roles using the backslash to escape and put the quote marks in
			}
		}
		query += ')';  //close the query string
		List<Contact> contacts = new List<Contact>();
		contacts = Database.query(query);
		for( Contact contact : contacts ) {
			contact.Contact_linkage__c = firstRecord.Id;
		}
		if (contacts.size() > 0 ) {
			update contacts;	
		}
	}
}

HTH

Andrew
This was selected as the best answer
Linda 98Linda 98
Thanks, Andrew and Alain. Yes, I was able to solve this multi-select picklist issue as suggested by Alain.
I also bulkified it to create multiple records. I am now trying to filter the result and then capture the contact id. That is, I am trying to check the size of contacts, if they are more than 10, then I would like to check contact level(Custom picklist field) on the list of contacts and choose only first 10 contacts whose contact_level__c is 'top'
If no contact has it then just need to include those contacts.
How can I do that??
Please guide me with this thank you!!
Andrew GAndrew G
Hi Linda

Depending on how you have bulkified your trigger, you could use the LIMIT  n 
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_sosl_limit.htm

If that doesnt work based on the bulkification, if you use the ORDER BY in soql you may be able to loop using a counter
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_sosl_select_order_by_with_limit.htm

psuedo code 
List<contact> orderedResults = [SELECT ... WHERE contactLevel = 'Top' ORDER BY parentId]

Contact previousContact;
Integer contactCount;

previousContact = orderedResults[0];

for (Contact c: orderedResults) {
  if (c.Id == previousContact.Id )   {     //skip first loop
    if(contactCount < 10 && c.parentID == previousContact.ParentID ) {
        //build your Map list for updating the parent
        contactCount++;
    } else if  { c.ParentId <> previousContact.ParentId {
     contactCount = 0;
    }
  }
}
//loop your map now to update all the parents.

Regards
Andrew