How to set schema for generating the child-to-parent relationship query - Cloud - 8.0

Salesforce
Version
Cloud
8.0
Language
English
Product
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 Real-Time Big Data Platform
Module
Talend Studio
Content
Data Governance > Third-party systems > Business applications > Salesforce components
Data Quality and Preparation > Third-party systems > Business applications > Salesforce components
Design and Development > Third-party systems > Business applications > Salesforce components
Last publication date
2024-04-15

This section demonstrates how to set the module name and schema columns for generating a child-to-parent relationship query.

The following two child-to-parent relationship query examples will be used for demonstration purpose in the following steps.

  • SELECT Name, Account.Name, Account.Owner.Name FROM Contact, a child-to-parent relationship query with standard object and fields, and

  • SELECT Id, FirstName__c, MotherOfDaughter__r.FirstName__c FROM Daughter__c, a child-to-parent relationship query with custom object and fields.

    Note that here you must use the relationship name with __r instead of __c. For more information, see Understanding Relationship Names, Custom Objects, and Custom Fields.

Procedure

  1. Set the module name with the name of the object specified in the FROM clause, Contact and Daughter__c in above examples.
  2. Create a column for each field in the field list (separated by commas) after SELECT in the schema dialog box.

    For the first example, you need to create three columns for the three fields Name, Account.Name, and Account.Owner.Name.

    For the second example, you need to create three columns for the three fields Id, FirstName__c, and MotherOfDaughter__r.FirstName__c.

  3. Set the name of each column with the name of each field and replace all dots in the column name with underscore characters.

    For the first example, the names of the three columns are set to Name, Account_Name, and Account_Owner_Name.

    For the second example, the names of the three columns are set to Id, FirstName__c, and MotherOfDaughter__r_FirstName__c.

  4. Set the type of each column.

    The schema for the first example should be set like this:

    And the schema for the second example should be set like this:

    Note that the underscore character '_' is used as a separator between the relationship name and the field name in Talend schema, so only the underscore character '_' that goes after '__r' or '__c' will be replaced by the dot character '.' when generating the query. If the underscore character '_' is a part of any custom name in the schema, for example, Contact_custom_field__c, which should be Contact.custom_field__c in the query, you need to replace '_' in the generated query with '.' manually.