How to retrieve a value from a parent field with SOQL via partner API in java

Question

I have a parent-child relationship with timesheets relating to users. I am trying to retrieve an Employment ID field from the parent record with SOQL and this is my query, where the UserID is a field that relates (Lookup) to the User Object.

Select Hours__c,Kilometers__c, UserID__r.EmployeeNumber from Timesheet__c

When I run this query in the APEX Dataloader I get the results I am expecting, but when I run it in my java code, the EmployeeNumber is always empty?

When I reference the field I have tried this

com.sforce.soap.partner.sobject.SObject[] records = qResult.getRecords();

for (int i = 0; i < records.length; ++i) {
  sKilometers = (String)records[i].getField("Kilometers__c");
  sHours = (String)records[i].getField("Hours__c");
  sEmployeeNumber = (String)records[i].getField("UserID__r.EmployeeNumber");
 ...

Also tried this

 sEmployeeNumber = (String)records[i].getField("EmployeeNumber");

However, neither returns any value for EmployeeNumber.

I also tried this combination.

sEmployeeNumber = (String)records[i].getChild("UserID__r").getField("EmployeeNumber");

after looking at this example (Creating a Partner WSDL Application - row 062) http://wiki.developerforce.com/page/Introduction_to_the_Force.com_Web_Services_Connector

but that also did not return any value.

I am surprised that the query works fine in the Data Loader, but not when I run it in my code. Could someone give me some hints or advice to what is the correct way to reference the field? It would be most appreciated.

Answer 1

If the field is a relationship field you need to cast it to XmlObject and then use getChild for the specific related field you referenced in the SOQL. Its not very elegant to drop down into XML terminology, but I guess this reflects the fact that the Partner API is a generic API and relationships can be varied.

    QueryResult result = connection.query("select Id, Name, Account.Name from Contact");
    com.sforce.soap.partner.sobject.SObject record = result.getRecords()[0];
    XmlObject value = (XmlObject) record.getField("Account");
    System.out.println(value.getChild("Name").getValue());

So in your case something like this...

    XmlObject value = (XmlObject) record.getField("UserID__r");
    System.out.println(value.getChild("EmployeeNumber").getValue());