Need help with lookup

[Anonymous]

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 

[JR1]

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.

[TRF]

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

[Anonymous]

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 

[JR1]

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.

[Anonymous]

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 

[JR1]

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.

[Anonymous]

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 

[Anonymous]

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

[Anonymous]

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