Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance during the next 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!
SOLVED

Using LIKE in Salesforce Input WHERE Clause

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.

 

Highlighted

@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 ?

Highlighted
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.

Highlighted

@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 

Highlighted
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".

Highlighted

Cool. Glad to see it worked :)

Happy Data Blending !!!

 

Thanks

saravanan

Highlighted
Asteroid

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

Highlighted
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