topic Re: Salesforces comments migration with tSalesforceInput Talend component in Talend Studio

Salesforces comments migration with tSalesforceInput Talend component

M1626783817 — Fri, 15 Nov 2024 23:55:57 GMT

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.

Re: Salesforces comments migration with tSalesforceInput Talend component

gjeremy1617088143 — Tue, 20 Jul 2021 13:34:27 GMT

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

Send me Love and kudos

Re: Salesforces comments migration with tSalesforceInput Talend component

M1626783817 — Tue, 20 Jul 2021 13:42:31 GMT

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

Re: Salesforces comments migration with tSalesforceInput Talend component

gjeremy1617088143 — Tue, 20 Jul 2021 13:44:30 GMT

Re: Salesforces comments migration with tSalesforceInput Talend component

gjeremy1617088143 — Tue, 20 Jul 2021 13:45:46 GMT

so maybe FeedComments_User_Name

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

Re: Salesforces comments migration with tSalesforceInput Talend component

gjeremy1617088143 — Tue, 20 Jul 2021 13:49:33 GMT

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

Re: Salesforces comments migration with tSalesforceInput Talend component

M1626783817 — Tue, 20 Jul 2021 14:09:28 GMT

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.

Re: Salesforces comments migration with tSalesforceInput Talend component

gjeremy1617088143 — Tue, 20 Jul 2021 14:14:09 GMT

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

Re: Salesforces comments migration with tSalesforceInput Talend component

M1626783817 — Wed, 28 Jul 2021 13:23:27 GMT

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.

Re: Salesforces comments migration with tSalesforceInput Talend component

gjeremy1617088143 — Wed, 28 Jul 2021 14:14:44 GMT

I'm glad you find a solution 🙂