topic Re: Need help with lookup in Talend Studio

Need help with lookup

Anonymous — Mon, 12 Sep 2016 13:13:14 GMT

Talend Version: 6.21
Hello Everybody
We have an ERP ( Oracle Database) --> SF Data Process with our Quotes.
The Quotes are creating an Opportunity on Salesforce an these ones are linked with the Quote.
The Problem is that the ERP Recreates a new Quotes when an Update is created on ERP-Side.
For example first Quote 100234 will be update an than the new Data Record is 100234-001,100234-002 etc.
So my idea is to do an Lookup back from the Quote to the Quote ERP Query and compare the first number first 100234 and then compare if are updated Quotes availiable ( example 100234-001 or 100234-002).

So which Component from Talend may fit for this problem ?

regards john

Re: Need help with lookup

JR1 — Mon, 12 Sep 2016 15:01:15 GMT

It should be possible to achieve this via the "Upsert" operation in your tSalesforceOutput or tSalesforceOutputBulkExec component. In addition, you should introduce an external id in the Quote/Opportunity object(s) - depending on how you intend to use these. Please consult the Salesforce.com documentation for details on how to use "Upsert" and external ids. Your suggested solution seems a bit complicated and to be honest, I do not understand it. Feel free to ignore my suggestion if you definitely want to use your approach.

Re: Need help with lookup

TRF — Mon, 12 Sep 2016 15:39:38 GMT

Hi,
The suggestion from JoRoesecke is the good one.
You need to define an external for each Salesforce object you want to be able to use the UPSERT statement.
If the record exists with the value from the input flow, then the Salesforce record is updated, else it is inserted.
Regards,
TRF

Re: Need help with lookup

Anonymous — Mon, 12 Sep 2016 16:01:28 GMT

Hello JoRoesecke:
I just try to explain this a little bit better.

ERP-Side:
1. Quote is created as 100234
Talend Side:
2. Talend Process will be fired up the Quote-Creation on SF
SF-Side:
3. The Quote create per Process an Opportunity based on the Quote Number ( 100234)
ERP-Side 5 Days later:
The Quote ( 100234) will be updated as 100234-001 ( for example the price has changed)
Talend Side:
Talend Process will be fired up the update Quote 100234-001 to SF.
SF-Side:
The Quote create per Process an Opportunity based on the Quote Number ( 100234-001)
The Problem :
On the Opportunity in SF which are related to the Quote 100234 are still Activities and Notes,attachment etc.
So these Opportunity needs a new Relation with these Quote 100234-001 ( this is the update one from 100234).
ATM it doesn't matter if the old Quote 100234 still exist it only counts that the relation from the Opportunity will be Updated.
i hope now it's a little bit more clear.

regards john

Re: Need help with lookup

JR1 — Mon, 12 Sep 2016 17:27:23 GMT

OK, I think I understand what you want to achieve. Please correct me if I am wrong but you want to initially create an SFDC Opportunity from an ERP quote (in your example 100234). Then the users will add activities, attachments, etc. to it. Later, in ERP the quote is updated and gets the ID 100234-001, which subsequently creates a new SFDC Opportunity with ID 100234-001. You now want to link this new Opportunity to the original one. Is this correct? It is a bit confusing as "Quote" is an SFDC object as well. Strictly speaking, an opportunity is not a quote but I take it as it is in your case.

If I am correct in my assumption, you should still consider giving the Opportunity in SFDC a new external ID field (e.g. "ExternalId__c") holding the ID of the Opportunity (100234) as well as a new Lookup(Opportunity) field called "ParentOpportunity__c". When loading the new (updated) Opportunity 100234-001, extract the original ID from this new ID e.g. via the Java split method (yourId.split("-")[0]) and pass it on to the field ParentOpportunity__c. Then - after setting the Output Action to "UPSERT" and selecting "ExternalId__c" as the "Upsert Key Column" - you will have to dive a bit deeper into the "Advanced" settings of the tSalesforceXXX component. Add a new record to "Relationship mapping for upsert" and provide the following values (I hope I got this right as I cannot test it right now):

Column name in Talend schema: "ParentOpportunity__c"
Lookup relationship field name: "ParentOpportunity__r"
Module name: Opportunity (without double quotes!)
External ID name: "ExternalId__c"

As you can see, this is a bit more complicated. I believe, you will have to try it yourself. It is important that you understand what is happening here. Lots of information on external IDs, upsert statements and relationship mappings can be found in the Salesforce.com documentation which I cannot reproduce here due to lack of time and space. I hope this helps.
Note: this of course only works if your updated IDs always have exactly the format you described.

Re: Need help with lookup

Anonymous — Tue, 13 Sep 2016 09:25:02 GMT

Hello JoRoesecke
Yes your are right with your assumption but i have forgot that the Quote obejct is custom may this is important or not just for completeness...
Okay i will try to Build an Test Projekt ....so far at this point .. thanks for helping

Additional:

If you have some tipps which I can solve the problem with an talend component wil be great cause my Java Knowledge is very n egligible.

regards john

Re: Need help with lookup

JR1 — Tue, 13 Sep 2016 20:26:10 GMT

I do not know what kind of Talend component you are looking for. You can achieve this with a simple job like tFileInputExcel --> tMap --> tSalesforceOutput(BulkExec). The Java split should be placed into the expression of the corresponding field in the output of the tMap. This is all the Java knowledge you need for this. The rest of the magic will happen in the Salesforce component. Sorry, but there is no simpler way to do this - you will need some Salesforce integration knowledge to achieve this.

Re: Need help with lookup

Anonymous — Thu, 15 Sep 2016 08:12:09 GMT

Okay it seems that i'm going to don't understand the whole thing sometimes I understand only .

I have the 2 Objects
Quotes and Opp's which plays a role.
I have Fields in :
Quote (example 1006709) :
1. No Linked Fields to Opp.
Opp:
1. Opp.Name example ( 1006709John Doe Corp) ( concatenate the name of the Quote and the Companyname ) will be created per Process from Salesforce.
2 QuoteField ( example. 1006709) ( Lookup to Quote).
New Created Fields:
On. Opp:
External ID ( i have to add what ? )
Parent Opportunity ( for example 1006709)
[list=*]

But i don't understand the handling with these Fields what you describe.

Cause the opp's will be created from the Salesforce System based on some Criterias not from Talend.
[list=*]

When i make an upsert on my ERP-System should i use the Quote-Number 1006709 ( unique) or another one ?

Here is my standard Job from Talend to Load the Quotes from ERP to the Quotes to Salesforce.

to be honest ...I'm a litte bit confused ...i will lose the common theme .. may i think too complicated

regards john

Re: Need help with lookup

Anonymous — Mon, 26 Sep 2016 12:55:19 GMT

Any further hints or tips ...
Thnx in advance
regards john

Re: Need help with lookup

Anonymous — Wed, 05 Oct 2016 13:33:11 GMT

Some other hints to solve the problem ?
may someone could help which has some experience with sf ?
or could point me out the joes way ( thanks for that) a little bit more in detail ( may the way to create an unique id integrate the lookup properly )

regards john