Scenario 4: Upserting the Contact module based on mapping relationships with the external IDs in the Account module - 6.1

Talend Components Reference Guide

EnrichVersion
6.1
EnrichProdName
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for Data Quality
Talend Open Studio for ESB
Talend Open Studio for MDM
Talend Real-Time Big Data Platform
task
Data Governance
Data Quality and Preparation
Design and Development
EnrichPlatform
Talend Studio

This scenario upserts the records in the Contact module based on mapping (matching) against the external IDs in the Account module:

Linking the components

  1. Drop a tSalesforceConnection, two tFixedFlowInput, two tSalesforceInput, two tSalesforceOutput and two tLogRow from the Palette onto the workspace.

  2. Rename two tFixedFlowInput components as external ids to insert and emails to upsert, two tSalesforceInput components as Contact (in) and Account (in), two tSalesforceOutput components as Contact (out) and Account (out), and two tLogRow components as external ids inserted and emails upserted.

  3. Link tSalesforceConnection to external ids to insert using the OnSubjobOk trigger.

  4. Link external ids to insert to Account (out) using a Row > Main connection.

  5. Link external ids to insert to Account (in) using the OnSubjobOk trigger.

  6. Link Account (in) to external ids inserted using a Row > Main connection.

  7. Link Account (in) to emails to upsert using the OnSubjobOk trigger.

  8. Link emails to upsert to Contact (out) using a Row > Main connection.

  9. Link emails to upsert to Contact (in) using the OnSubjobOk trigger.

  10. Link Contact (in) to emails upserted using a Row > Main connection.

Configuring the components

  1. Double-click tSalesforceConnection to open its Basic settings view.

    In the Username and Password fields, enter the authentication credentials.

  2. Double-click external ids to insert to open its Basic settings view.

  3. Click the Edit schema button to open the schema editor.

    Click the [+] button to add three columns, namely Name, AccountID__c and AccountBizLicense__c, all of the String type. Note that AccountID__c and AccountBizLicense__c are customized fields in the Account module, with the attribute of external ID.

    Click OK to close the editor.

    Select the Use Inline Content (delimited file) check box in the Mode area and enter the data below in the Content box:

    Google;US666;C.A.666
    Talend;FR888;Paris888
  4. Double-click Account (out) to open its Basic settings view.

    Select the Use an existing connection check box.

    Select insert in the Action list and Account in the Module list.

  5. Double-click Account (in) to open its Basic settings view.

    Select the Use an existing connection check box.

    Select Query in the Query mode list and Account in the Module list.

    In the Query Condition box, enter the filter statement: "name like 'Tal%' OR name like 'Goo%'".

  6. Click the Edit schema button to open the schema editor.

    Click the [+] button to add three columns, namely Name, AccountID__c and AccountBizLicense__c, all of the String type.

    Click OK to close the editor.

  7. Double-click external ids inserted to open its Basic settings view.

    Select the Table (print values in cells of a table) check box for a better view of the results.

  8. Double-click emails to upsert to open its Basic settings view.

  9. Click the Edit schema button to open the schema editor.

    Click the [+] button to add four columns, namely Email, AccountID, AccountBizLicense and LastName, all of the String type.

    Click OK to close the editor.

    Select the Use Inline Content (delimited file) check box in the Mode area and enter the data below in the Content box:

    andy@talend.com;Paris888;FR888;Andy
    anderson@talend.com;C.A.666;US666;Anderson
  10. Double-click Contact (out) to open its Basic settings view.

    Select the Use an existing connection check box.

    Select upsert in the Action list, Email in the Upsert Key Column list and Contact in the Module list.

    Go to the Advanced settings view to set the relationship mapping:

    Click the [+] button to add two lines and select AccountBizLicense and AccountID in the list under the Column name of Talend Schema column.

    Enter the lookup relationship fields in the Lookup field name column, namely Account and Account__r.

    Enter the lookup module name in the Module name column, namely Account.

    Enter the external id fields in the External id name column, namely AccountBizLicense__c and AccountID__c, which are the customized fields (with the external id attribute) in the Account module.

    Note

    • Column name of Talend Schema refers to the fields in the schema of the component preceding tSalesforceOutput. Such columns are intended to match against the external id fields specified in the External id name column, which are the fields of the lookup module specified in the Module name column.

    • Lookup field name refers to the lookup relationship fields of the module selected from the Module list in the Basic settings view. They are intended to establish relationship with the lookup module specified in the Module name column.

    • For how to define the lookup relationship fields and how to provide their correct names in the Lookup field name column, go to the Salesforce website and launch the Salesforce Data Loader application for proper actions and information.

  11. Double-click Contact (in) to open its Basic settings view.

    Select the Use an existing connection check box.

    Select Query in the Query mode list and Contact in the Module list.

    In the Query Condition box, enter the filter statement: "Email like 'And%'".

  12. Click the Edit schema button to open the schema editor.

    Click the [+] button to add two columns, namely LastName and Email, all of the String type.

    Click OK to close the editor.

  13. Double-click emails upserted to open its Basic settings view.

    Select the Table (print values in cells of a table) check box for a better view of the results.

Executing the Job

  1. Press Ctrl + S to save the Job.

  2. Press F6 to run the Job.

    As shown above, the insert and upsert actions have been completed successfully.