Is there any way to filter a SOQL or SOSL query on the Id field?

I’ve tried a number of ways including:

SELECT Id, Name FROM Account WHERE Id LIKE '0013%'

and

FIND { 0013 } IN ALL FIELDS RETURNING Account (Id, Name)

and all I receive is the same

INVALID_QUERY_FILTER_OPERATOR error.

Is this to say that I am unable to search based on partials of the Id field? I can only retrieve based a whole Id field?

Edit: The problem I am trying to solve is the use of the Id field as an integration value with another system.

e.g. We use the ID from salesforce in a seperate system to tie the records together. We have built a small tool where I would like to provide an auto-complete when typing in one of these IDs, directly from salesforce.

Answer

You can not use the LIKE operator with an Id field. As per the documentation:


LIKE

Expression is true if the value in the specified fieldName matches the characters of the text string in the specified value. The LIKE operator in SOQL and SOSL is similar to the LIKE operator in SQL; it provides a mechanism for matching partial text strings and includes support for wildcards.

  • The % and _ wildcards are supported for the LIKE operator.
  • The % wildcard matches zero or more characters.
  • The _ wildcard matches exactly one character.
  • The text string in the specified value must be enclosed in single quotes.
  • The LIKE operator is supported for string fields only.
  • The LIKE operator performs a case-insensitive match, unlike the case-sensitive matching in SQL.
  • The LIKE operator in SOQL and SOSL supports escaping of special characters % or _.
  • Do not use the backslash character in a search except to escape a character.

For example, the following query matches Appleton, Apple, and Bappl , but not Appl:

SELECT AccountId, FirstName, lastname
FROM Contact
WHERE lastname LIKE 'appl_%'

Please note the bolded section. In Salesforce, Id fields are actually considered a primitive type of Id. Please check out the Salesforce documentation, specifically the ID Field Type section. Essentially, there is no way to do that directly.

To be honest with you, I would be interested to even hear a use case for this type of situation. If you absolutely must do this, here is a workaround. Create a text field and use a workflow field to update it on create of the object. The field should be readonly. You can then search on this field. The reason you make it a text field rather than a formula is because the field can then be indexed. This has more to do with performance though. Good luck with whatever you are attempting!

Attribution
Source : Link , Question Author : Darien Ford , Answer Author : Jesse Altman

Leave a Comment