You would set the Corporate_Number__c field on the account__r object (the __c is the foreign key Id, and the __r is the actual relationship). there's a more detailed example at http://www.pocketsoap.com/weblog/2008/09/1824.html

Thanks Simon, but my confusion is not understanding how relationships work but more understanding how to perform an upsert with only the external ID's.

I am already providing in the upsert a value for the Corporate_Number__c field which as you say is the required foreign key value but I still get an error saying "Required field missing: Account__c".

If I put the value of Corporate_Number__c in to the sObject in both Corporate_Number__c and Account__c I get an error saying that the field value for Account__c is not in the right format which is what I would expect.

I am looking for a way to say in the upsert something like ...

Order.Corporate_Number__c = Account.Corporate_Number__c

Which in turn should resolve the Account ID for the required relationship.

Despite having populated this field it seems to insist I have not populated something vitally important to the object chain.

Also because I am using the Partner API I am populating sObjects not Order and Account Objects ...

It works something like this (not vitally important I'm guessing but someone in the community may wish to use this as it works well at converting a data table in to an sObject array) ...

public DataTable[] UpsertData(DataTable newData, string ExternalIDField)

{

   try

   {

       DescribeSObjectResult[] desc = service.describeSObjects(new string[] { newData.TableName }); 

                sObject[] upsertableObjects = proc.BuildObjectArray(newData, desc[0]);

       UpsertResult[] serverResults = service.upsert(ExternalIDField, upsertableObjects);

        }

There's obviously more to it than that, when given larger than a single batch size of records in the datatable it batches them up then upserts and I also have a lot of code that handles things like the results to build something similar to what the data loader pushes out but these are the core lines of code.

The "proc" object that I call BuildObjectArray on simply takes the given data table and for each column sets a value on a sObject using something like this ...

public sObject[] BuildObjectArray(DataTable Data, DescribeSObjectResult ObjectDescription)

 {

    List<sObject> results = new List<sObject>();    

    List<string> fields = new List<string>();

    // identify the fieldset we are interested from the object definition in question.

    foreach(DataColumn col in Data.Columns)

    {

         foreach(Field field in ObjectDescription.fields)

         {

              // do a case insensitive match (just in case)

              if(field.name.ToLower() == col.ColumnName.ToLower())

                  fields.Add(field.name);

         }

    }

// process data

foreach (DataRow row in Data.Rows)

{

    List<XmlElement> values = new List<XmlElement>();

    sObject sfObject = new sObject();

         sfObject.type = Data.TableName;

    foreach (string field in fields)

        {

         if (row[field] != DBNull.Value)

             values.Add(GetNewXmlElement(field, row[field]));

       }

      sfObject.Any = values.ToArray();

      results.Add(sfObject);

}

the method "GetNewXmlElement" does a bit of working out considering things like weather a .net datetime object should be converted to a datetime string or date string.

With this in mind I have also tried to do things like add a field called something like "Account__r" to see if that helps but don't seem to be getting anything working right now ...

So to clarify ...

judging by your sample I have to create a second sObject that I then hand in to the first sObject as an element to which the server will interpret this and go find the internal unique id based on the given external id?

You have to build a nested account__r sobject structure, you can not set it through the account__c field. (see the example xml in the weblog post as a guide)

Ok thanks for the feedback Simon.

So assuming I have understood you right and bearing in mind i'm not using the enterprise API but the partner one instead I essentially need something like this ...

foreach (DataRow row in Data.Rows)

{

    List<XmlElement> values = new List<XmlElement>();

    sObject sfObject = new sObject();

    sfObject.type = Data.TableName;

    foreach (string field in fields)

    {

        if (row[field] != DBNull.Value)

            values.Add(GetNewXmlElement(field, row[field]));

    }

    sfObject.Any = values.ToArray();

    results.Add(sfObject);

}

.... AND ...

XmlElement GetNewXmlElement(string Name, object nodeValue)

{

    System.Xml.XmlElement xmlel = doc.CreateElement(Name);

    if (IsRelationshipField)

    {

        sObject relatedObject = new sObject();

        relatedObject.type = relatedObjType       

        XmlElement refElem = doc.CreateElement(extIdFieldName);

        refElem.InnerText = nodeValue.ToString();

        relatedObject.Any = new XmlElement[] { refElem };

        //Somehow add the xml for the entire sObject instance "relatedObject" to the XML element value ...

       xmlel.InnerText = relatedObject.ToXML(); <-- this call does not exist ... how do i do this?

    }

 Is there some trick to grabbing a sObject as xml as there does not appear to be anyhting available to do this or am I to assume that relational data cannot be uploaded using the partner WSDL?

That's more of .NET question than anything else. You can possibly use the Xml Serializer framework to serialize out just the sobject structure, or XmlElement itself can have children, so you can just manually build the nested elements using XmlElement. I'm pretty sure i've posted an example of this in the past.

With all due respect I kind of figured that was the point to this forum ... .Net questions in relation to Salesforce.

I don't think the XmlSerialiser class produces a soap worthy xml result as it tends to produce a different format, having said that I could translate it with a transform.

It all seems a bit much though just to xml from an object.

Also ...

The sample you posted above relies on declaring inline namespaces for each upserted object within which the referenced nested objects live in a sort of ownership hierarchy, this is built dynamically by the framework when the upsert method is called from what I can see as it identifies the structure of the data given to it.

I guess i'm on my own again ... Thanks for the input anyway.

As i mentioned, you can nest elements with XmlElement, here's a full example. (using VS.NET 2008)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;

namespace partnerApiTest
{
    class Program
    {
        static void Main(String[] args)
        {
            Program p = new Program();
            p.upsert(args[0], args[1]);
        }

        void upsert(String un, String pwd) {
            sf.SforceService s = new sf.SforceService();
            sf.LoginResult lr = s.login(un, pwd);
            s.Url = lr.serverUrl;
            s.SessionHeaderValue = new sf.SessionHeader();
            s.SessionHeaderValue.sessionId = lr.sessionId;

            sf.sObject contact = new sf.sObject();
            contact.type = "Contact";

            XmlElement cid = makeElement("contactExtId__c", "SF");
            XmlElement fn = makeElement("FirstName", "Simon");
            XmlElement ln = makeElement("LastName", "Fell");
            XmlElement acc = makeElement("Account", null);
            acc.AppendChild(makeElement("type", "Account"));
            acc.AppendChild(makeElement("extId__c", "SFDC"));
            
            contact.Any = new XmlElement[] {cid, fn, ln, acc};

            sf.UpsertResult sr = s.upsert("contactExtId__c", new sf.sObject[] {contact})[0];
            if (sr.success)
                Console.WriteLine("success recordId is {0}", sr.id);
            else
                Console.WriteLine("error {0} {1}", sr.errors[0].statusCode, sr.errors[0].message);
        }

        private XmlDocument doc = new XmlDocument();

        private XmlElement makeElement(String name, String innerText)
        {
            XmlElement e = doc.CreateElement(name);
            if (innerText != null) e.InnerText = innerText;
            return e;
        }
    }
}

Wow, unexpected and extremely helpful.

If understand this correctly the sObject definition contains a property called "Any" with each XmlElement in it looking something like this ...

<id>idValue</id>

... for simple value types.

When a reference to an external object is required the XmlElement name should be <externalObjectName> and that should then contain the value in its own child.

In my case i'm uploading order data that has a relationship to the account object so I gathered it should produce something like this:

<account>

     <type>account</type>

     <Corporate_Number__c>58664222</Corporate_Number__c>

</account>

However that produces this ...

INVALID_FIELD: No such column 'Account' on entity 'Order__c'. If you are attempting to use a custom field, be sure to append the '__c' after the custom field name. Please reference your WSDL or the describe call for the appropriate names.

So I figured I had something wrong.

When I compared that to your Enterprise WSDL example above I noticed that the syntax was inline with the expected format in that I had nested the "Account" within the sObject as a field and then nested the expected id within that.

Have I missed something?

Thanks again for your persistence in helping me.

You sample shows this ...

    <create xmlns="urn:enterprise.soap.sforce.com">
         <sObjects xmlns:q1="urn:sobject.enterprise.soap.sforce.com" xsi:type="q1:Case">
               <q1:Account>
                       <q1:extId__c>00001</q1:extId__c>
               </q1:Account>
               <q1:Subject>test case</q1:Subject>
          </sObjects>

   </create>

The outer create and sObjects elements are created by .Net as part of the call so I have little control over these unless I get very low level with my coding and start overriding protocol stuff.

Now my confusion here is that "create" is obviously the name of the call you intend to make (so in my case that would be upsert) then sObjects is likely the array to create / upsert (fairly straightforward).

Essentially what I am producing is somehting like this ...

    <upsert xmlns="urn:enterprise.soap.sforce.com">
         <sObjects xmlns:q1="urn:sobject.enterprise.soap.sforce.com" xsi:type="q1:Case">
              <q1:Order__c>

                     <Account>
                          <q1:extId__c>00001</q1:extId__c>

                     <Account>
               </q1:Order__c>

              <q1:Order__c>

                     <Account>
                          <q1:extId__c>00002</q1:extId__c>

                     <Account>
               </q1:Order__c>

         </q1:sObjects>

             ...

   </upsert>

And of course the Server sees this as me trying to populate a field called "Account" on the Order custom object, however I am not trying to do that, I'm trying to associate the Order with the given account external Id.

Does this help clarify my problem?