1

I am currently trying to do a SOQL query with a like using the % wildcard.

According to the documentation:

The % wildcard matches zero or more characters.

This does not seem like it's working. For example:

SELECT
Id,
UserType 
FROM user
WHERE UserType like '%PowerPartner%'

Returns back 0 results

SELECT
Id,
UserType 
FROM user
WHERE UserType = 'PowerPartner'

Returns back 2 results

Is there something I am missing?

Now that I typed this all up, it has me thinking that the issue is due to the fact UserType is a picklist. Can you not use a like on a picklist field?

  • 2
    Normal picklist fields work with LIKE. That implies that UserType is not a normal picklist field. Whatever it actually is, SOQL doesn't treat it like a String. – Jeremy Nottingham Jun 30 '17 at 15:26
  • May or may not be related but I had similar issue with Sometext % moreText in an SOQL returning results with a value of Sometext and noText - I think there is something going on with SOQL wildcards because that value should not have been returned IMHO. – Eric Jun 30 '17 at 15:27
  • @sfdcfox - I thought is was someText any number then moreText - so you are saying the moreText part is ignored? I do not use wildcards that often so this is good to know if that is the case – Eric Jun 30 '17 at 15:29
  • @Eric Oh, wait, I misread your comment. That does sound weird. Hmm, I'll have to research that. – sfdcfox Jun 30 '17 at 15:34
  • @sfdcfox - Don't bother. I cannot replicate it I just know it was a weird result.....If I can replicate it I will post a question – Eric Jun 30 '17 at 23:37
0

So I did some more research into the issue and it looks like it's a bug on the Salesforce side. Now I can only theorize on why it's happening.

The UserType field is a bit interesting it's a picklist that has labels and actual values according to the documentation.

  • Standard: user license. This user type also includes Salesforce Platform and Salesforce Platform One user licenses. Label is Standard.

  • PowerPartner: User whose access is limited because he or she is a partner and typically accesses the application through a partner portal or community. Label is Partner.

  • CSPLitePortal: user whose access is limited because he or she is an organization's customer and accesses the application through a Customer Portal or community. Label is High Volume Portal.

  • CustomerSuccess: user whose access is limited because he or she is an organization's customer and accesses the application through a Customer Portal. Label is Customer Portal User.

  • PowerCustomerSuccess: user whose access is limited because he or she is an organization's customer and accesses the application through a Customer Portal. Label is Customer Portal Manager.

  • CsnOnly: user whose access to the application is limited to Chatter. This user type includes Chatter Free and Chatter moderator users. Label is Chatter Free.

I've tested this on some of the Values with Labels. It appears when doing a Like on a SOQL search it only checks the label.

Some Examples:

SELECT
Id,
UserType 
FROM user
WHERE UserType like '%Partner%'

Returns results back

SELECT
id,
userType
from
user
WHERE UserType like '%PowerCustomerSuccess%'

Returns no results back

SELECT
id,
userType
from
user
WHERE UserType like '%Customer Portal Manager%'

Returns results back

The above tests seems to confirm my suspicion.

  • Odd, as I was able to SOQL for %scOn% without issue and it found the CsnOnly records. – Daniel Ballinger Jul 6 '17 at 8:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.