Get complete list of ALL Opportunities from API (v1) When Opportunity count is greater than 1000 record limit

[LifePort_IT_Team]

Hello,

In short, how can we retrieve the complete list of opportunites and their custom field data using the API if I have 3,439 or more records?

We sync our Keap opportunity data with our local ERP Database, and have some applications that track opportunity changes over time etc. To do this we have some calls using the RestSharp libray in a WinForms application that sends API URL requests, and we then parse the JSON responses into a database using MS SQL.

A loop creates the URL Strings to call incrementing the offset for each 1000 Records as such:

For index As Integer = 1 To 4
If index = 1 Then
'Create a client and populate the call URL
OppClient = New RestClient(“https://api.infusionsoft.com/crm/rest/v1/opportunities/?limit=1000&offset=0&optional_properties=custom_fields”)
ElseIf index = 2 Then
'Create a client and populate the call URL
OppClient = New RestClient(“https://api.infusionsoft.com/crm/rest/v1/opportunities/?limit=1000&offset=1000&optional_properties=custom_fields”)
ElseIf index = 3 Then
'Create a client and populate the call URL
OppClient = New RestClient(“https://api.infusionsoft.com/crm/rest/v1/opportunities/?limit=1000&offset=2000&&optional_properties=custom_fields”)
ElseIf index = 4 Then
'Create a client and populate the call URL
OppClient = New RestClient(“https://api.infusionsoft.com/crm/rest/v1/opportunities/?limit=1000&offset=3000&optional_properties=custom_fields”)
End If
Next

This method used to return a distinct list of the 3K plus records we have, but recently we noticed the parameter “&offset=XXXX” (where XXXX is the offset value) seems to be ignored now.

And now we are getting tons of duplicate records, and then missing others in the response strings. But the total record count of the 4 separate URL Requests returns the exact number of records we have in KEAP, indicating that the request needs to be sorted someway to achieve the appropriate offset.

I had a similar issue with the Contacts endpoint a while back (we have 9k plus contact records), and I was able to resolve the duplicate record issue by adding a “&order=id” parameter into the Request URL like so, (this approach is still working for the contacts endpoint):
“https://api.infusionsoft.com/crm/rest/v1/contacts/?limit=1000&offset=8000&order=id&optional_properties=custom_fields”

However, adding the “&order=id” or parameter to Opportunities endpoint returns a bad request.

for example:
https://api.infusionsoft.com/crm/rest/v1/opportunities/?limit=1000&offset=3000&order=id&optional_properties=custom_fields

Plus a lot of other combinations that I cant post due to the URL limit on your discussion forums, and I cant seem find anything in your documentation that demonstrates how to parametrize your API’s url strings for different end points.

I should clarify we are having no issue sending the requests, getting a response, and decoding the JSON with the original parameters so i did not provide that code, just the part that builds the request string (let me know if you need more information).

How do we need to parametrize the URL Request string to correctly get the first 1000 records, then offset and get the next 1000 records such that all 3934 distinct records are returned?

This is critical to our budgeting and Revenue forecast operations, Please get me a response as soon as possible.

Respectfully,

Beau Mills

[LifePort_IT_Team]

Turns out I was quite confused in my reading of the dox…i did not realize the list under the Query Parameters is all of the parameters and that the "Enum:"s are a restrictive list of usable fields for the parameter, I thought they were just generic examples of fields. Using the “&Order=date_created” parameter for the Opportunities endpoint sorts for the offset and returns JSON results as expected. Sorry to waste your time.

Anyone else running into this issue the correct formulation of the of the URL call for my app was to increment the offset by the limited records (1000) and adding the “&Order=date_created” parameter as such:

For index As Integer = 1 To 4
If index = 1 Then
'Create a client and populate the call URL
OppClient = New RestClient(“https://api.infusionsoft.com/crm/rest/v1/opportunities/?limit=1000&offset=0&order=date_created&optional_properties=custom_fields”)
ElseIf index = 2 Then
'Create a client and populate the call URL
OppClient = New RestClient(“https://api.infusionsoft.com/crm/rest/v1/opportunities/?limit=1000&offset=1000&order=date_created&optional_properties=custom_fields”)
ElseIf index = 3 Then
'Create a client and populate the call URL
OppClient = New RestClient(“https://api.infusionsoft.com/crm/rest/v1/opportunities/?limit=1000&offset=2000&order=date_created&optional_properties=custom_fields”)
ElseIf index = 4 Then
'Create a client and populate the call URL
OppClient = New RestClient(“https://api.infusionsoft.com/crm/rest/v1/opportunities/?limit=1000&offset=3000&order=date_created&optional_properties=custom_fields”)
End If
Next

if your order parameter yields a bad request, double check the Enum list in the documentation.