Hello world upsert?

ejones · #1 01-31-2008, 01:40 PM

Could I get some help with the upserts? I have read your guides on them and honestly i still don't quite understand how it works.

I understand they you need to create the sObjects for the upsert, but how do you uniquely identify them? (I come from a strong SQL background ... so the lack of a "where" clause just dosent make any sense to me .. unless i missed it)
Also, the sobjects you receive in a select have a field type of "SimpleXMLElement Object". How do I change my fields array into that type?

I then see you use the 'upsert_accounts' function you wrote ... but what are these extra parameters $file_updated, $file_created, $file_failed ?

Could you do up a very very simple ("hello world" simple) example upsert?

ejones · #2 01-31-2008, 01:47 PM

Forgot to mention: Thanks for the guides by the way ...

mike · #3 01-31-2008, 09:13 PM

Ej

I will take a look at what I have and try and post a hello world example tomorrow or maybe we could chat on IM if you need some specific answers.

If not, like I said, I will post something tomorrow

mike · #4 02-04-2008, 09:47 AM

Let's see if I can help you a little more with an example

You probably already know this but an upsert to Salesforce is basically an insert and update being performed at the same time on a field that is identified by on a field that you added in Setup > Customize > Object > Fields:

See thumbnail

So let's continue, we use the sa_id__c field that we created as this external Id field. This is an Id from our internal system that puts an Id tag on the Account.

so lets say you have a simple sql query that gets all the information from your local database where you return 1000 rows

PHP Code:


			

$sql = "Select Account, Name, sa_id__c
        From Accounts";

**remember I use ADOdb to connect to my DB's**

So once you perform the query, You loop through all the records:

PHP Code:


			
$recordSet = $conn->Execute($sql);

$all_fields = array();
$i = 0;
while ($row = $recordSet->FetchRow())
{
$all_fields[$i]['ACCOUNT'] = $row['ACCOUNT'];
$all_fields[$i]['NAME'] = htmlspecialchars(stripslashes(strip_tags($row['NAME'])));
$all_fields[$i]['SA_ID__C'] = htmlspecialchars($row['SA_ID__C']);
$i++;
}

so then setup the new Sobject to perform the Salesforce UPSERT:

PHP Code:


			
$sObjects = array();

foreach ($all_fields as $fieldset)
{
    $sObject = new sObject();
    $sObject->type = 'Account';
    $sObject->fields = $fieldset;
    array_push($sObjects, $sObject);
}

the array_push will put all the data into an multi-dimensonal array from $sObject into $ sObjects

then create a function to pass the data and connection object to the function

PHP Code:


			
$success = upsert_accounts($client, $sObjects);

the other stuff in my previous example was 3 files that I created to keep records and info for our local process, you can ignore that

So next is the function:

PHP Code:


			
function upsert_accounts($client, $sObjects)
{
    try
    {
        // The upsert process
        $results = $client->upsert("sa_id__c", $sObjects);

        $k = 0;

        // This loop processes $result to build the log files
        foreach ($results as $result)
        {
            // Build string from fields in $sObjects array
            // At this point, the record has already been upserted
            // We just need the data for the log file
            // The string is the same, regardless of the result
            if ($result->success)
            {
                if ($result->created)
                {
                    //Records is created here if it does not exist in Salesforce
                }
                else
                {
                    //If it already exists(eg your external Id
                    //(1111 == 1111) then update the account in Salesforce 
                    //with your data and do something with your results
                }
            }
            else
            {
                // if it errors, do something to report it
            }
            $k++;
        }
        // Put the result counts into an array to pass back as the result.
        $success = array();
        array_push($success);
        return $success;
        exit;
    }
    catch (exception $e)
    {
        // This is reached if there is a major problem in the data or with
        // the salesforce.com connection. Normal data errors are caught by
        // salesforce.com
        echo '<pre>' . print_r($e, true) . '</pre>';
        return false;
        exit;
    }
}

One line of code inside the function performs the upsert, let's break that down:

PHP Code:


			
$results = $client->upsert("sa_id__c", $sObjects);

upsert is a function in the PHPTOOLKIT that tells the API that you are peforming an upsert

the SA_ID__C tells the API that the field to key on (the external field you identified) is passed to the function, and the $sObjects is the data

all the other code in my previous examples show that we do so many records at a time, you can only process 200 records at a time to Salesforce (from what I understand, I maybe wrong about that)

let me know if this helps

~Mike

ejones · #5 05-30-2008, 10:00 AM

Thanks for the example. Helpded alot.

However I am still running into problems. My code is this:

PHP Code:


			
        $sObjects = Array();
        $fieldset = Array();
        
        $fieldset['SA_ID__C'] = '3';
        $fieldset['NAME'] = 'Grand Hotels SLA - UPSERT BABY!';

        $sObject = new sObject();
        $sObject->type = 'Opportunity';
        $sObject->fields = $fieldset;
        array_push($sObjects, $sObject);
       
        try {
            $this->setSalesForceConnection(blah blah);
            $this->setSoapClient(blah blah);    
            $mylogin = $this->getSalesForceConnection()->login(blah, blah);
            
            $results = $this->getSalesForceConnection()->upsert("SA_ID__C",$sObjects);
            return $success;
        }
        catch (Exception $e) {
            $this->setErrorCode("***WARNING!*** ERROR CONTACTING SALESFOCE: ".$e->faultstring);
            return -1;
        }

I have looked it over a bunch of times and from what I can tell it *should* work. Unfortunatly I keep getting back the following:
***WARNING!*** ERROR CONTACTING SALESFOCE: INVALID_FIELD: No such column '' on entity 'opportunity'.

Any ideas on what i did wrong?

ejones · #6 05-30-2008, 02:59 PM

NVM .... problem solved.

Turns out I had an out dated version of PHP installed on the server and that what was causing the issue.

I upgraded to the newest version, 5.2.6, and the problem went away.

mike · #7 05-30-2008, 03:44 PM

Sorry ejones for not replying to your post earlier, I am getting ready to fly to Washington DC to attend the DC PHP Conference

I am glad that you were able to get it done, great work man

~Mike