7

Records are returned when I execute this

sfdx force:data:soql:query -u Dev --json --query "SELECT Id, Name, (SELECT Id, Name, AccountId FROM Contacts) FROM Account WHERE Id IN ('0010z00001T1uXSAAZ','0010z00001T1SbXAAV')"

No records are returned when I execute this

sfdx force:data:tree:export -u Dev --json --query "SELECT Id, Name, (SELECT Id, Name, AccountId FROM Contacts) FROM Account WHERE Id IN ('0010z00001T1uXSAAZ','0010z00001T1SbXAAV')" --prefix export --outputdir sfdx-out --plan

In the commands above Dev is the alias of a sandbox, not a scratch org. The result is the same whether I use 15 or 18 digit record Ids in my export: {"status":0,"result":{"records":[]}}

  1. Why doesn't this data:tree:export command return the same records as this data:soql:query command given that the same query is used in each against the same sandbox?
  2. How can I troubleshoot this issue?
Improve this question

1 Answer 1

Reset to default
8

I've had a look at reproducing this. I got the same result when the data:tree:export included any specific ID's in the where clause. As soon as I took the where clause out of the SOQL query that data started exporting as expected. It even included the data for the Account I was trying to specify by ID.

I'm not certain at the moment, but I suspect it is related to how the export removes all specific record Id's from the result.

E.g. The ID on my Account of interest gets removed and replaced with:

"attributes": {
    "type": "Account",
    "referenceId": "AccountRef1"
},

From the data:tree:export docs:

The query for export can return a maximum of 2,000 records. For more information, see the REST API Developer Guide: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_composite_sobject_tree.htm

I captured the SFDX CLI logging at trace level by appending a --loglevel trace, it didn't provide any more clues. I suspect I'd need to capture the underlying API requests to get to the root cause.

I think the actual answer lies in this file (on Windows at least):

%LocalAppData%\sfdx\plugins\node_modules\salesforce-alm\dist\lib\data\dataExportApi.js
Improve this answer
4
  • 4
    Daniel, I suspect you're right on this. Inclusion of Ids in the where clause appears to be a limitation at the moment. Swapping the Ids out for account names in the where clause had my export functioning as expected.
    – SF1Dev
    Mar 11, 2019 at 1:49
  • I also encountered the same behavior. But this may be limited to Id only, as i am able to get data with other where clauses like lastmodifieddate=today.
    – Rajendra Singh Nagar
    Mar 13, 2019 at 17:36
  • 1
    I'm encountering the same issue as well. I'm new to it after coding for over a decade in other environments. SF seems full of gotcha limitations - that's my first impression, anyway.
    – dmgig
    Nov 22, 2019 at 20:02
  • 1
    I believe the actual issue is that the query string is transformed to lowercase. You can see this when certain errors output the query string in all lowercase. In soql, querying by ids is case sensitive, hence the no results. If you make the where clause WHERE Id != '0010z00001T1uXSAAZ', you'll get every record including the record with Id == '0010z00001T1uXSAAZ' since there are no records with the lowercase version '0010z00001t1uxsaaz'
    – digglemister
    Jul 30, 2021 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.