[SalesForce] How to return full report (>2000 rows) using rest api

I am unable to fetch a full report using the Salesforce REST analytics API. As was posted on another question, I am using the following solution(python):

reportFilter = {
    "reportMetadata": {"sortBy": [{"sortColumn": "Contact.Id", "sortOrder": "Asc"}]}}
it = 0
ids = []
while True:
    ids, out, reportFilter = getReportAndConcatIds(ids, reportFilter)
    if out is True:
        break

which calls the following method:

def getReportAndConcatIds(ids, reportFilter):
    headers = {'content-type': 'application/json'}
    r = s.post(link, data=json.dumps(reportFilter), headers=headers)   
    j = r.json()
    for key in j['factMap']['T!T']['rows']:
        ids.append(key['dataCells'][0]['label'])

    reportFilter = {"reportMetadata": {"reportFilters": [{"value": ids[-1], "operator": "greaterThan", "column": "Contact.Id"}],
                                       "sortBy": [{"sortColumn": "Contact.Id", "sortOrder": "Asc"}]}}

    return ids, j['allData'], reportFilter

This performs as intended, it successfully retrieves the contact ids from a report generated within Salesforce, however, it continually loops, even if the report is only 5000 rows long.

After extensive printing I am positive that the ids are being added to the ids array after being extracted from the correct column.

Interestingly, after removing all duplicates from the ids array, the arrays length continues to grow, even after it exceeds the length of the inputted report.

Could someone please help shine some light on what I am doing wrong here?

Thanks in advance

Best Answer

Firstly - Please take a moment and vote for Salesforce Ideas: Increase the Analytics REST API Limit of 2000 Report Rows

... after removing all duplicates from the ids array, the arrays length continues to grow, even after it exceeds the length of the inputted report.

I suspect this is indicative of the underlying problem. The moment you start encountering duplicate Contact Ids something has gone wrong. That shouldn't be happening with this approach.

Try something like the following. Maybe consider it pseudo code, as I've never coded Python before.

for key in j['factMap']['T!T']['rows']:
    id = key['dataCells'][0]['label']
    # Check that id hasn't been processed yet
    if id in ids:
        raise Exception('Duplicate id: ' + id)
    ids.append(id)

If this starts throwing errors then the problem is likely around j['allData'] and checking if it is true. Again, I'm not across Python and how it handles typing and JSON processing. It seems like it isn't correctly detecting the allData true case.

The only other likely scenario I can think of is that ids[-1] isn't correctly returning the highest encountered contact id so far. If it was I'd think the report would eventually stop returning any further rows.

Related Topic