cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Salesforce advanced settings and bad SOQL query

I'm querying the Salesforce Accounts object and my Flow works fine in testing but is limited to 256 records. To open it up for all my records (I currently have 868 Accounts), I go to Settings, enable pagination, and set a max limit. The Flow fails and the query it's showing isn't valid: "WHERE ORDER BY". I don't have anything in the 'Filter Query' field, but it's still including a WHERE in the query. The full output body:

 

{
  "status"400,
  "message""Salesforce failed to complete task: Message: \nSharePoint_ID__c FROM Account WHERE  ORDER BY Id ASC LIMIT 256 OFFSET 0\n                                          ^\nERROR at Row:1:Column:50\nunexpected token: 'BY'\r\nclientRequestId: XXXX",
  "source""Microsoft.Azure.Connectors.Salesforce",
  "errors": []
}

 

 

2018-04-12_15-01-22.png2018-04-12_15-00-56.png2018-04-12_14-50-52.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: Salesforce advanced settings and bad SOQL query

I'm going to accept my own reply as solution in case it helps anyone. I still think it's a bug in the Salesforce connector. If you're using a column in the ORDER BY field, you should also include that same column in the SELECT QUERY field. Otherwise it will send a bad SOQL query to Salesforce.

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: Salesforce advanced settings and bad SOQL query

Hi @Bjorn,

 

Could you please share a full screenshot of your flow's configuration?

Could you please show more details about the SharePoint_ID_c column and the ID column?

Does the ID column that you specified represent the Account ID column of your Salesforce Accounts object?

Further, do you want to sort the records of your Accounts object based on the Account ID column?

 

The error message told that there is something wrong with the OData query within the Order By field of the "Get records" action, please check if the ID column is existed in your Accounts object.

 

If you want to sort the records of your Accounts object based on the Account ID column, I have made a test on my side and please take a try with the following workaround:6.JPG

Within Order By field, type the following formula:

Id desc

Note: The Id represents the Account ID column in your Accounts object.

 

The flow works successfully as below:7.JPG

 

 

More details about OData query within Salesforce, please check the following article:

https://help.salesforce.com/articleView?id=odata_query_string_options.htm&type=5

 

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Resolver I
Resolver I

Re: Salesforce advanced settings and bad SOQL query

Hi Kris.

 

 

SharePoint_ID__c is just a custom field on my Salesforce Account object that I use to store the corresponding Sharepoint ID. Here's a valid SOQL query from Salesforce Workbench:

2018-04-16_10-42-57.png

 

 

 

I added your descending to my Order By. But what finally made it work was adding Id to my Select Query, now I select two fields, Id and SharePoint_ID__c and the Flow works:

 

2018-04-16_11-00-38.png

 

 

I think the issue is when you use pagination (limit and offset) you also need to select the column that you're using in the Order By field, Id in my case. When I removed Id, the Flow failed. In SOQL it's a valid query, I can use Id in my Order By but not actually select it:

 

 

2018-04-16_11-19-04.png

 

 

 

When I tried your exact syntax (Order By Id desc, but only select the standard field 'Name') I also got an error:

2018-04-16_11-10-56.png2018-04-16_11-28-41.png

 

2018-04-16_11-39-04.png

 

 

Selecting both Id and Name got it working:

2018-04-16_11-32-12.png

 

 

I'm not sure how you got your example to work since that same thing gave me an error. What version of the Salesforce API are you using in your connection? I'm using v40.0.

 

 

 

 

 

Highlighted
Resolver I
Resolver I

Re: Salesforce advanced settings and bad SOQL query

I'm going to accept my own reply as solution in case it helps anyone. I still think it's a bug in the Salesforce connector. If you're using a column in the ORDER BY field, you should also include that same column in the SELECT QUERY field. Otherwise it will send a bad SOQL query to Salesforce.

View solution in original post

Highlighted
Helper I
Helper I

Re: Salesforce advanced settings and bad SOQL query

@Bjorn How did you get the system to pull back more than 200 records, when I try it is only pulling in 200 of our over 400 records. 

Highlighted
Resolver I
Resolver I

Re: Salesforce advanced settings and bad SOQL query

Like I said in my answer, use Pagination and in the query use an 'Order By' column and also make sure you have that column in the Select. That's what worked for me.

Highlighted
Frequent Visitor

Re: Salesforce advanced settings and bad SOQL query

@rickmontezuma  I found the pagination option Bjorn mentioned under settings on the get records element.  

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (7,962)