Using LIKE in Salesforce Input WHERE Clause

[mpurdy]

I am trying to use "LIKE" in the WHERE Clause (SOQL) of the Salesforce Input but I am getting the following error:

Error trying to retrieve query. HTTP/1.1 400 Bad Request ... Data from server: [{"message":"A query string has to be specified","errorCode":"MALFORMED_QUERY"}]

I am not sure what I am missing. If I remove the "%" character then it doesn't error, but it doesn't find anything either. My statement looks like this: Data_Source_ID__c LIKE 'ABC%'.

Thanks in advance for any help that you can give.

[s_pichaipillai]

@mpurdy

looks like Alteryx does not like the "LIKE" Operator 

why don't you filter the rows using FIlter tool in Alteryx

@KaneG 

Do you have any idea why the Like Operator is not working ?

[mpurdy]

Thanks for your suggestion. Unfortunately I have 1,000,000+ rows and I don't want to have to download them all from Salesforce just so that I can filter them in Alteryx locally and extract < 1000 that I need to process. Appreciate any other ideas you might have.

[s_pichaipillai · Accepted solution]

@mpurdy

i think i found the root casue of this issue 

from the below error message it sending the URL(API)  format with Like values. so the % operator causes the issue

Error: Salesforce Input (1): Tool #130: Iteration #1: Tool #21: Error trying to retrieve query. HTTP/1.1 400 Bad Request
... Data from server: 

<!-- ...................................................................................................
       ...................................................................................................
       ...................................................................................................
       ................................................................................................... --><table cellspacing=10>
<tr><td><span style="font-weight: bold; font-size: 12pt;">Illegal Request</span></td></tr>
<tr><td>You have sent us an Illegal URL or an improperly formatted request. </td></tr>
<tr><td></td></tr>
</table>

can you change your filter from Name like '%Gene' to m Name like '%25Gene%25'

plese check the URL Encoding ref for %

http://www.w3schools.com/tags/ref_urlencode.asp 

[mpurdy]

That has fixed the problem! Thanks so much for all your help. I thought it was having difficulties with the percentage symbol but I never would have guessed to URL encode it and replace it with "%25".

[s_pichaipillai]

Cool. Glad to see it worked :)

Happy Data Blending !!!

Thanks

saravanan

[mpurdy]

Looks like this was fixed in the Salesforce Input connector in Alteryx 10.5 (you can simply use "%").

[BobSnyder85]

It seems the "Like" where clause can not be used on field types that are stored in SFDC as "Reference". I was able to use the same command on a Picklist item but not a Reference Item.

This is on v2018.4