Hi I am trying to download data from a salesforce object and write to a Blob. The object is huge so I put a filter condition **SystemModstamp ge 2022-01-01T00:00:00Z AND SystemModstamp le 2022-03-03T23:59:59Z** With this filter condition i am expecting about 33000 records What i get is the first 200 records. I see there is a json element "@odata.nextLink" - which possibly points to the next data set. My question is - how can I design a logic app that gets the first data set add its to a CSV table and then recursively uses the nextlink to get the next data set and add it to the same CVS table - so after all iterations i can write the contents of the table to a Blob. Thanks Regards Sid

@Siddharth Kumar You can leverage Until action till your json element has @odata.nextLink field. Before the until loop, you can define the variable of type string and inside the loop you can leverage Append to string variable that will keep appending your CSV string data. Once the Until loop is completed you can leverage the Create blob V2 action of storage blob connector to create the file with your variable that has your CSV table string.

@MayankBargali-MSFT Is there not a limitation with Salesforce where you cannot get more than 50K records. I thought you had to use the topcount and skip count to get around this?. I have been trying this approach but cannot get this working also. Can someone post a screen of the flow like @Hussain Zuheb EXT Infosys - Fluido has requested so we can see. Much appreciated. Steve

Azure Logic App - Salesforce get records continously using the the odata.nextlink

Accepted answer

MayankBargali-MSFT 68,656 Reputation points

2022-03-07T13:32:24.103+00:00

@Siddharth Kumar You can leverage Until action till your json element has @odata.nextLink field. Before the until loop, you can define the variable of type string and inside the loop you can leverage Append to string variable that will keep appending your CSV string data. Once the Until loop is completed you can leverage the Create blob V2 action of storage blob connector to create the file with your variable that has your CSV table string.
Please sign in to rate this answer.

2 people found this answer helpful.
Siddharth Kumar 71 Reputation points

2022-03-07T19:30:55.513+00:00

Hi @MayankBargali-MSFT
Got it. Thanks for clarifying that.
Also I then understand that i cannot use the salesforce connector out of the box as this does not work with a Url to get records? Probably have to write an Azure function to get an access token and then use the Http task to get records in a loop?

Sid

MayankBargali-MSFT 68,656 Reputation points

2022-03-08T08:35:17.14+00:00

@Siddharth Kumar Logic app salesforce connector does have the Get records action and Get record action to get the records from the Salesforce and you can explore different action/trigger documented here. Can you share more details on Url to get records as I am not sure what do you mean whether you want to call the salesforce REST API or you want to get the URL by passing the records?
As get records do have the Salesforce Object Type where you define the table from where you want to retrieve the records along with that you can define other parameters like Filter Query etc.

In case if there are scenarios where the connectors action/trigger cannot help then you can leverage the Native HTTP connector to call any HTTP/HTTPS endpoint.

Siddharth Kumar 71 Reputation points

2022-03-08T14:30:49.853+00:00

Hi @MayankBargali-MSFT

I tried the approach as you suggested - I have an until block recursively calls the salesforce GetRecords task - I am querying the Accounts Object and have a select statement for Id, FirstName, LastName

The SF GetRecords task is getting me 200 records at one go as expected. The JSON output of the salesforce object has the headers, body and the "@odata.nextLink". However recursively I keep getting the same first 200 records from Salesforce on every iteration of the Until task.

The Salesforce Output has the @odata.nextLink

"@odata.nextLink": "https://logic-apis-eastus2.azure-apim.net/apim/salesforce/7e8d929391eb445fa933c641f1d5ebfc/datasets/default/tables/Account/items?$orderby=Id%20asc&ConnectorNextRecordsFilterClause=(Id+%3e+%270011200001ChPBoAAN%27)&$select=FirstName,Id,LastName"

My understanding is that this would point to the next data set to fetch - however what I am trying to understand is as to how to use this URL to get the next 200 records on the next iteration and so on using a Salesforce task.

Found a similar article where they have leveraged the Http request to recursively leverage this URL to get the data -
handling-pagination-with-the-ms-graph-api-in-logic-apps-and-power-automate

Siddharth Kumar 71 Reputation points

2022-03-08T20:43:53.33+00:00

hi @MayankBargali-MSFT
Not sure about the "@odata.nextLink but think I have a solution that can work.

SOQL queries have an offset clause to enable paging in the data set being requested for a salesforce object.

Since i get 200 records at a time and that's the salesforce page size - maybe start the Until task requesting Salesforce data with a Skip count of 0 in Getrecords, and then increment skip count by 200 on every iteration. When size of result data is less than 200 records stop the iteration. (Or at each iteration increment skip count by size of result set to avoid hard coding the 200 and eventually should get 0 results)

Will test this out.

Siddharth Kumar 71 Reputation points

2022-03-08T20:43:53.347+00:00

hi @MayankBargali-MSFT
Not sure about the "@odata.nextLink but think I have a solution that can work.

SOQL queries have an offset clause to enable paging in the data set being requested for a salesforce object.

Since i get 200 records at a time and that's the salesforce page size - maybe start the Until task requesting Salesforce data with a Skip count of 0 in Getrecords, and then increment skip count by 200 on every iteration. When size of result data is less than 200 records stop the iteration. (Or at each iteration increment skip count by size of result set to avoid hard coding the 200 and eventually should get 0 results)

Will test this out.

MayankBargali-MSFT 68,656 Reputation points

2022-03-09T14:53:17.04+00:00

@Siddharth Kumar Logic App Salesforce connector does support pagination as documented here. I think you should able to retrieve the data once you have enabled the pagination. You can find the pagination option under the setting of your action. I couldn't test it at my end as I don't have test data in my salesforce instance for testing purposes where the pagination could be leveraged. Please test it and let me know if it helps you.

Siddharth Kumar 71 Reputation points

2022-03-09T15:09:59.113+00:00

Hi @MayankBargali-MSFT
Thanks. I did try the pagination. With Pagination I still need to set a threshold limit. I set a threshold of 5000 records - it returns 5000 records in one execution of GetRecords. However there are possibly more records in the table(33k in my case) - so some looping will still be needed to get the remainder. Correct?

Siddharth

MayankBargali-MSFT 68,656 Reputation points

2022-03-09T15:21:20.557+00:00

@Siddharth Kumar You don’t need to do anything to follow the next links. Logic Apps internally does that if the pagination is enabled. Logic Apps will keep on following the next links until the threshold number of items is retrieved. Can you share the screenshot of your run history. I can connect with you offline to provide quick assistance if needed.

Siddharth Kumar 71 Reputation points

2022-03-16T01:43:36.14+00:00

Based on our last call I used the Until task to get records from salesforce. I turned on pagination on getrecords and set a threshold of 1000. I also have a Skip clause for the object which is initialized to 0. After the data fetch and appending the salesforce data to a string variable, I have a condition where I check the record count in the result. If the record count is less than 1000 I set the condition for the until loop to false. If its greater or equal to 1000 increment the Skip for the run of salesforce get records.

Hussain Zuheb EXT Infosys - Fluido 1 Reputation point

2022-07-01T13:36:14.15+00:00

@MayankBargali-MSFT and @Siddharth Kumar Could you pls share the flow in more details if its working for you. I want to do the same. I feel you have already explained enough but my knowledge is very limited in the logic app so still not able to guess how many variables are you using and how are you setting those variables and what are you assigning to the create CSV component. Hope you can share steps in more detail. Looking forward to your help. thanks

Steve 1 Reputation point

2023-01-06T16:13:49.18+00:00

@MayankBargali-MSFT Is there not a limitation with Salesforce where you cannot get more than 50K records. I thought you had to use the topcount and skip count to get around this?.

I have been trying this approach but cannot get this working also. Can someone post a screen of the flow like @Hussain Zuheb EXT Infosys - Fluido has requested so we can see.

Much appreciated.
Steve
Sign in to comment

Azure Logic App - Salesforce get records continously using the the odata.nextlink

1 additional answer