Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Using LIKE in Salesforce Input WHERE Clause

mpurdy
8 - Asteroid

 

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.

 

7 REPLIES 7
s_pichaipillai
12 - Quasar

@mpurdy

 

looks like Alteryx does not like the "LIKE" Operator 

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

SF.PNG

@KaneG 

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

mpurdy
8 - Asteroid

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
12 - Quasar

@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
8 - Asteroid

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
12 - Quasar

Cool. Glad to see it worked :)

Happy Data Blending !!!

 

Thanks

saravanan

mpurdy
8 - Asteroid

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

BobSnyder85
8 - Asteroid

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

Labels