Issue with External Id while upserting from external system using Salesforce SOAP API

Question

We are facing some issue during upsert, please find details below:

We have following objects:

and sample data for the above objects:

The external system is trying to upsert TankRead__c with the following :

<soapenv:Body>
    <urn:upsert>
        <urn:externalIDFieldName>SeqNo__c</urn:externalIDFieldName>        
        <urn:sObjects xsi:type="TankRead__c">                         
            <SeqNo__c>5</SeqNo__c>
            <Volume__c>100</Volume__c>
            <TankNumber__r xsi:type="Tank__c">
                <TankNumber__c>1</TankNumber__c>
            </TankNumber__r>
            <SiteID__r xsi:type="Site__c">
                <SiteID__c>1171</SiteID__c> 
            </SiteID__r>                                
        </urn:sObjects>
    </urn:upsert>
</soapenv:Body>

and getting below error:

Error: Field name provided, TankNumber__c is not an External ID or indexed field for Tank__c.

From the error it is observed that the error is because TankNumber__c is not unique(which is as per the design).But combination of TankNumber__c and SiteId gives unique value. Can you please let me know how to enforce or include TankNumber__c and Site__c combination in the request. We tried the following and failed:

<soapenv:Body>
<urn:upsert>
    <urn:externalIDFieldName>SeqNo__c</urn:externalIDFieldName>        
    <urn:sObjects xsi:type="TankRead__c">                         
        <SeqNo__c>5</SeqNo__c>
        <Volume__c>100</Volume__c>
        <TankNumber__r xsi:type="Tank__c">
            <TankNumber__c>1</TankNumber__c>
            <SiteID__r xsi:type="Site__c">
                <SiteID__c>1171</SiteID__c> 
            </SiteID__r>
        </TankNumber__r>
        <SiteID__r xsi:type="Site__c">
            <SiteID__c>1171</SiteID__c> 
        </SiteID__r>                                
    </urn:sObjects>
</urn:upsert>

Error: More than 1 field provided in an external foreign key reference in entity: Tank__c

Please let us know if there is a solution for this.

Update Even we had thought of including a new field in Tank concatenating TankNumber and Site as Unique/External Id field. For concatenated field(say TankSite) how do we set it in SOAP request?

<soapenv:Body>
<urn:upsert>
    <urn:externalIDFieldName>SeqNo__c</urn:externalIDFieldName>        
    <urn:sObjects xsi:type="TankRead__c">                         
        <SeqNo__c>5</SeqNo__c>
        <Volume__c>100</Volume__c>
        <TankNumber__r xsi:type="Tank__c">
            <TankSite__c>
                SiteID__r.SiteID__c +TankNumber__c
            </TankSite__c>
        </TankNumber__r>
        <SiteID__r xsi:type="Site__c">
            <SiteID__c>1171</SiteID__c> 
        </SiteID__r>                                
    </urn:sObjects>
</urn:upsert>

Thanks.

Answer 1

See my comment

You can add a field to tank concatenating the number and site and set that to external id. Then populate with a WFR.

Use that in the upsert