cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Bjorn
Level: Powered On

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
Bjorn
Level: Powered On

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
Community Support Team
Community Support Team

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.
Bjorn
Level: Powered On

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.

 

 

 

 

 

Bjorn
Level: Powered On

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

rickmontezuma
Level: Powered On

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. 

Bjorn
Level: Powered On

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.

GKS
Level: Powered On

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
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,133)