Search code examples
salesforcetalendsoql

How to reference sub-objects in talend schema

So I have the following SOQL query that includes the ActivityHistories relationship of the Account object:

SELECT Id, Name, ParentId, (SELECT Description FROM ActivityHistories) 
  FROM Account 
  WHERE Name = '<some client>'

This query works just in in SOQLXplorer and returns 5 nested rows under the ActivityHistories key. In Talend, I am following the instructions from this page to access the sub-objects (although the example uses the query "up" syntax, not the query "down" syntax. My schema mapping is as follows: Talend schema mapping

The query returns the parent Account rows but not the ActivityHistory rows that are in the subquery:

Starting job GetActivities at 15:43 22/06/2016.
[statistics] connecting to socket on port XXXX
[statistics] connected
0X16000X00fQd61AAC|REI||
[statistics] disconnected
Job GetActivities ended at 15:43 22/06/2016. [exit code=0]

Is it possible to reference the subrows using Talend? If so, what is the syntax for the schema to do so? If not, how can I unpack this data in some ay to get to the Description fields for each Account? Any help is much appreciated.

Update: I have written a small python script to extract the ActivityHistory records and dump them in a file, then used a tFileInput to ingest the CSV and then continue through my process. But this seems very kludgey. Any better options out there?

Solution

  • I've done some debugging from the code perspective and it seems that if you specify correct column name, you will achieve the correct response. For your example, it should be: Account_ActivityHistories_records_Description

    The output from tLogRow will be similar to:

    00124000009gSHvAAM|Account1|tests;Lalalala

    As you can see, the Description from all child elements are stored as 1 string, delimited by the semicolon. You can change the delimiter on Advanced Settings view on the SalesforceInput.