Salesforces comments migration with tSalesforceInput Talend component

[M1626783817]

Hi,

I would like to get data from Salesforce with tSalesforceInput, and I use this query :

"SELECT Id,Body,ParentId,CreatedById,CreatedDate,

TYPEOF CreatedBy WHEN User THEN Name END, (SELECT Id, CommentBody, FeedItemId,CreatedById,CreatedDate,ParentId, TYPEOF CreatedBy WHEN User THEN Name END FROM FeedComments) FROM FeedItem Where ParentId = 'xxxxxxxxxx'"

It works, and I can get data from CommentBody with Talend component (with this : FeedComments_records_CommentBody),....., But the problem that I can't get Data returned by this part (TYPEOF CreatedBy WHEN User THEN Name END) on Salesforce component. On workbench query results, the column name is User.Name, I tried to do FeedComments_records_User_Name, but it doesn't works. On Talend component limitation they say, that we cannot use guess schema, but I'm trying to do it manually. My question, It's possible to do it like that or it's a part of the limitation ?

Thank you.

[gjeremy1617088143]

Hi, Talend doesn't allow to have dot in column name cause it is forbidden in java variable name.

Send me Love and kudos

[M1626783817]

Thanks for your reply. Yes that's true but I'm not using dot, referrer to Talend documentation, we have to add underscore instead of dot. But still doesn't work, the problem comes from here, TYPEOF CreatedBy WHEN User THEN Name END, Soql doesn't support Alias except for aggregated field and table(not sure). And Alias is also a limitation for Salesforce component in Talend. Thank you

[gjeremy1617088143]

[gjeremy1617088143]

so maybe FeedComments_User_Name

maybe don't make transformation in soql ,just get CreatedBy.User.Name

[gjeremy1617088143]

yes is saw this alias can be used only on aggregated field and table in soql query 😞 .

[M1626783817]

Thank you. This works perfectly with parent---> child query, but in my case TypeOf, it didn't works, I got a blank field as results.

[gjeremy1617088143]

just try if you can get the type with parent child query , maybe CreatedBy.User.Name , you have to try directly on saleforce to see if you can get it, and in the talend job you will filter on those wich are only users

[M1626783817 · Accepted solution]

Hi,

Thank you, I solved this problem like this :

RelationshipName_records_LookupFieldName_FieldName.

This is the schema:

  

In Talend, instead of using (TYPEOF CreatedBy WHEN User THEN Name END) you can use createdBy.Name and Talend can guess the schema automatically. and at the end the whole query will be :

"SELECT Id,Body,ParentId,CreatedById,CreatedDate,

CreatedBy.Name , (SELECT Id, CommentBody, FeedItemId,CreatedById,CreatedDate,ParentId, CreatedBy.Name FROM FeedComments) FROM FeedItem Where ParentId = 'xxxxxxxxxx'"

Thank you.

[gjeremy1617088143]

I'm glad you find a solution 🙂