+ Start a Discussion
Alex Waddell 12Alex Waddell 12 

HELP! Export Nested Query from Workbench or other tool

Hello everyone,

I wrote a nested Query on the Workbench to do cross object reporting, the report works fine when i "View As" a List but when i try to Bulk CSV this report, i get the error "InvalidJob: Unable to find object: HealthCloudGA__Encounters__r"

I read online that workbench cannot export nested queries, i have tried to go to dataloader.io and export my query from there but that also does not work. Can anyone point me to a tool that can export this report? below is ther query and a picture of it working
Select Name,(Select id, HealthCloudGA__HospitalizePeriodStart__c from HealthCloudGA__Encounters__r),(Select On_Service_Date__c,Discharge_Date__c from cases Where RecordTypeId = '01236000000OJLqAAO'  ) from Account where Primary_Insurance__c = 'Health Net'

User-added image
Alain CabonAlain Cabon

9 Easy Ways to Export Data as .CSVhttps://douglascayers.com/2016/03/20/salesforce-easy-ways-to-export-data-as-csv/

There is no tool for this kind of exports so the only option is Code Your Own Tools (Visualforce + Apex).

In fact, you can resolve your problem quite easily with the dataloader + DB Browser for SQLite ( a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite, zero installation of database server ) if you have just a few hundred thousand records per object (< 1.000.000 records per object would be ideal).

http:// http://sqlitebrowser.org/ (http:// http://sqlitebrowser.org/)

Exports ( Bulk mode ) with the dataloader:

1) select Account__c, Id, HealthCloudGA__HospitalizePeriodStart__c
from HealthCloudGA__Encounters__c
where Account__r.Primary_Insurance__c = 'Health Net'

2) select AccountId, On_Service_Date__c,Discharge_Date__c 
from Case
where RecordTypeId = '01236000000OJLqAAO'
and Account.Primary_Insurance__c = 'Health Net'

3) select Id,Name
from Account
where Primary_Insurance__c = 'Health Net'

Import of the exported CSV files directly with  DB Browser for SQLite (the tables are auto-created by the tool according the headers of the CSV files (string) and the loadings last some seconds if you just have a few hundred thousand records per object).

Create three indexes for the fields used by the join Account__c, AcccountId and Id (last few second)

SQL with the common: left outer join + export of the result (CSV format):

select a.Name, h.Id, h.HealthCloudGA__HospitalizePeriodStart__c, c.On_Service_Date__c,c.Discharge_Date__c 
from Account a 
left outer join HealthCloudGA__Encounters__c h on a.Id = c.Account__c
left outer join Case c on a.Id = c.AccountId;


If you have just a few hundred thousand records per object, the loadings, the creations of indexes and the requests last some seconds (zero database server installation, zero table created manually, just install  DB Browser for SQLite).

SQLite 2.7.6 is significantly faster (sometimes as much as 10 or 20 times faster) than the default PostgreSQL 7.1.3 installation on RedHat 7.2 for most common operations.
SQLite 2.7.6 is often faster (sometimes more than twice as fast) than MySQL 3.23.41 for most common operations.
Alain CabonAlain Cabon
You can also try a real report in Salesforce. You just need to create the  report type (+ export Excel)