Apex Code Development You need to sign in to do that
Don't have an account?
Jeremy Frink How to use SQL Query with MultiSelect Picklist
I am attempting to query our Salesforce system from a Marketing Cloud Data Extension. One of the fileds that I want to Query using SQL Query from the Marketing Cloud is Contact Role. This field happens to be a multiselect picklist. I would like to be able to query multiple items in my picklist. For instance, return all contacts who have "Director of Nursing" and "Administrator" selected in the MultiSelect Picklist. Unfortunately it only returns exact matches. Any tips to get this to work correctly?
Select C.Name, C.Email, C.Organization_Physical_State__c, C.Contact_Role__c
From Contact_Salesforce C
Where C.Contact_Role__c IN ('Administrator', 'Director of Nursing') AND
(C.Email IS NOT NULL AND
C.Closed__c = 'False' AND
Account_Salesforce.Closed__c = 'False' AND
C.Organization_Record_Type__c = 'Long Term Care' AND
Organization_Account_Type__c = 'Nursing Home' )
AND
(C.Organization_Physical_State__c = 'South Dakota' OR
C.Organization_Physical_State__c = 'North Dakota')
Select C.Name, C.Email, C.Organization_Physical_State__c, C.Contact_Role__c
From Contact_Salesforce C
Where C.Contact_Role__c IN ('Administrator', 'Director of Nursing') AND
(C.Email IS NOT NULL AND
C.Closed__c = 'False' AND
Account_Salesforce.Closed__c = 'False' AND
C.Organization_Record_Type__c = 'Long Term Care' AND
Organization_Account_Type__c = 'Nursing Home' )
AND
(C.Organization_Physical_State__c = 'South Dakota' OR
C.Organization_Physical_State__c = 'North Dakota')
Select Account_Salesforce.Organization_Closed__c as Organization_Closed,
Account_Salesforce.Name as Organization_Name, C.AccountID, C.Contact_Closed__c, C.Name, C.Email, C.Organization_Physical_State__c, C.Contact_Role__c From Contact_Salesforce C
INNER JOIN Account_Salesforce
ON C.AccountID = Account_Salesforce.ID
Where (C.Contact_Role__c Like '%Administrator%' or
C.Contact_Role__c Like '%Director of Nursing%') AND
(C.Email IS NOT NULL AND
C.Contact_Closed__c = 'False' AND
Account_Salesforce.Organization_Closed__c = 'False' AND
C.Organization_Record_Type__c = 'Long Term Care' AND
Organization_Account_Type__c = 'Nursing Home' )
All Answers
Hope that your day is off to an amazing start. The code you're looking for is below. Hope this helps and may God bless you abundantly.
Select C.Name, C.Email, C.Organization_Physical_State__c, C.Contact_Role__c
From Contact_Salesforce C
Where C.Contact_Role__c INCLUDES ('Administrator', 'Director of Nursing') AND
(C.Email IS NOT NULL AND
C.Closed__c = 'False' AND
Account_Salesforce.Closed__c = 'False' AND
C.Organization_Record_Type__c = 'Long Term Care' AND
Organization_Account_Type__c = 'Nursing Home' )
AND
(C.Organization_Physical_State__c = 'South Dakota' OR
C.Organization_Physical_State__c = 'North Dakota')
Best Regards,
Anthony McDougald
Anthony, thank you so much for your assistance. However when I use the Includes statement it gives me an error in the Syntax Validation.
An error occurred while checking the query syntax. Errors: An expression of non-boolean type specified in a context where a condition is expected, near 'INCLUDES'.
I can't seem to figure out why this is occuring as everything that I have read says that "INCLUDED" are supposed to work for MultiSelect Picklists.
Any other tips would be appreciated.
Best Regards,
Jeremy
Select Account_Salesforce.Organization_Closed__c as Organization_Closed,
Account_Salesforce.Name as Organization_Name, C.AccountID, C.Contact_Closed__c, C.Name, C.Email, C.Organization_Physical_State__c, C.Contact_Role__c From Contact_Salesforce C
INNER JOIN Account_Salesforce
ON C.AccountID = Account_Salesforce.ID
Where (C.Contact_Role__c Like '%Administrator%' or
C.Contact_Role__c Like '%Director of Nursing%') AND
(C.Email IS NOT NULL AND
C.Contact_Closed__c = 'False' AND
Account_Salesforce.Organization_Closed__c = 'False' AND
C.Organization_Record_Type__c = 'Long Term Care' AND
Organization_Account_Type__c = 'Nursing Home' )