How can I unconcatenate related entities in Taleo Connect Client?

Question

I have an export query for Taleo Connect Client that retrieves the numbers for each Application submitted by a specific Candidate entity (Recruiting 15A model). The candidate is filtered based on their candidate ID 1234.

When I run my query, the results file lists all of a candidate's applications in single entry, however I would like to have each Application listed as it's own entry.

Current result:

CandidateID,ApplicationID
1234,(Applications:1)=15160;(Applications:2)=18433;(Applications:3)=19347

Expected Result:

CandidateID,ApplicationID
1234,15160
1234,18433
1234,19347

How can I make my export query list separate entries for each Application?

TCC export query (candidate_app_sq.xml):

<quer:query productCode="RC1501" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="Candidate" locale="en" mode="CSV-ENTITY" csvheader="true" largegraph="true" preventDuplicates="false" xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:subQueries/>
  <quer:projections>
    <quer:projection alias="CandidateID">
      <quer:field path="Number"/>
    </quer:projection>
    <quer:projection alias="ApplicationID">
      <quer:field path="Applications,Number"/>
    </quer:projection>
  </quer:projections>
  <quer:projectionFilterings/>
  <quer:filterings>
    <quer:filtering>
      <quer:equal>
        <quer:field path="Number"/>
        <quer:string>1234</quer:string>
      </quer:equal>
    </quer:filtering>
  </quer:filterings>
  <quer:sortings/>
  <quer:sortingFilterings/>
  <quer:groupings/>
  <quer:joinings/>
</quer:query>

Stevoisiak · Answer 1 · 2019-09-04T15:37:14.377

Instead of exporting a specific Candidate and extracting each Application, export a list of Applications and filter the results based on Candidate number.

To do this, I changed my export query to use the Application Entity as the projectedClass and updated my projection paths accordingly.

Export Query:

<quer:query productCode="RC1501" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="Application" locale="en" mode="CSV-ENTITY" csvheader="true" largegraph="true" preventDuplicates="false" xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:subQueries/>
  <quer:projections>
    <quer:projection alias="CandidateID">
      <quer:field path="Candidate,Number"/>
    </quer:projection>
    <quer:projection alias="ApplicationID">
      <quer:field path="Number"/>
    </quer:projection>
  </quer:projections>
  <quer:projectionFilterings/>
  <quer:filterings>
    <quer:filtering>
      <quer:equal>
        <quer:field path="Candidate,Number"/>
        <quer:string>1234</quer:string>
      </quer:equal>
    </quer:filtering>
  </quer:filterings>
  <quer:sortings/>
  <quer:sortingFilterings/>
  <quer:groupings/>
  <quer:joinings/>
</quer:query>

Output:

CandidateID,ApplicationID
1234,15160
1234,18433
1234,19347

Note: Remember to update projection paths when changing your projectedClass.

Old Path (Candidate)      New Path (Application)
"Number"               →  "Candidate,Number"
"FirstName"            →  "Candidate,FirstName"
"LastName"             →  "Candidate,LastName"
"Application,BillRate" →  "BillRate"
"Application,Grade"    →  "Grade"
"Application,Number"   →  "Number"

Stevoisiak · Accepted Answer · 2019-09-04T15:38:16.693

To export separate lines for each value, change the export mode to CSV-Report (CSV).

<quer:query productCode="RC1501" model="http://www.taleo.com/ws/tee800/2009/01"
    projectedClass="Candidate" locale="en" mode="CSV" csvheader="true" largegraph="true"
    preventDuplicates="false" xmlns:quer="http://www.taleo.com/ws/integration/query">

Explanation

The Taleo Connect Client User Guide (page 41) lists two export modes for CSV files: CSV-Entity and CSV-Report. When the export mode is set to CSV-entity, the data for each root entity is combined into a single line.

CSV-entity: Based on the T-XML export mode, it handles multiple values and multilingual fields. All data related to the root entity is located on the same line. When a column contains multiple values (multilingual or relations of maximum cardinality "N"), the values are serialized inside a single column. The prevent duplicates, grouping, and joining features are not supported by the CSV-entity export mode.

CSV-report: Uses a flat file format (that can be directly imported as an Excel spreadsheet) to represent the data. This mode exports exactly the same information as the XML mode, hence has the same strengths and weaknesses.

To output separate lines for each record, the export mode should be set to to CSV Report (mode="CSV").

Example

<quer:query productCode="RC1501" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="Candidate" locale="en" mode="CSV" csvheader="true" largegraph="true" preventDuplicates="false" xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:subQueries/>
  <quer:projections>
    <quer:projection alias="CandidateID">
      <quer:field path="Number"/>
    </quer:projection>
    <quer:projection alias="ApplicationID">
      <quer:field path="Applications,Number"/>
    </quer:projection>
  </quer:projections>
  <quer:projectionFilterings/>
  <quer:filterings>
    <quer:filtering>
      <quer:equal>
        <quer:field path="Number"/>
        <quer:string>1234</quer:string>
      </quer:equal>
    </quer:filtering>
  </quer:filterings>
  <quer:sortings/>
  <quer:sortingFilterings/>
  <quer:groupings/>
  <quer:joinings/>
</quer:query>

Results:

CandidateID,ApplicationID
1234,15160
1234,18433
1234,19347

How can I unconcatenate related entities in Taleo Connect Client?

2 Answers2

Explanation

Example