Salesforce PHP 1

mike · #1 05-18-2007, 08:38 AM

After working with Salesforce.com over the past few months, I have been able to constantly improve performing inserts, updates, and upserts using the PHP Tool Kit. With guidance from David Claiborne and Nick Tran, author of the Salesforce PHP Tool Kit, I have created a full update process for my organizations data. This tutorial will focus on using Oracle as the local database and pushing data to Salesforce.com. I also use AdoDB, which is a database abstraction layer.

One item that I would like to focus on is performing updates/inserts to Salesforce via the upsert command. This really gives an organization much more flexibility to keep data refreshed from an internal data system and your organizations sforce instance.

I will be using the Account Object for this tutorial. First, you must have declared an external id on a custom field either already created or one that is being created. Please understand that an external id has to be one of the following field types:

1) Text
2) Number
3) Email
4) Auto Number

As a sforce administrator, you login to Salesforce.com and go to Setup > App Setup > Accounts > Fields. Then scroll down to Account Custom Fields and Relationships. Either Create a new field that you will be utilizing or edit a existing field which is a valid field type and make it an external field.

Now that you are all set, it is time to write the php script which will perform the update!

In your PHP script set add this line to the top of your script:

PHP Code:


			
 ini_set("soap.wsdl_cache_enabled", "0");

Make sure that you include the PHP Tool Kit class scripts to login to Salesforce

PHP Code:


			
 require_once ('./includes/soapclient/SforcePartnerClient.php');
require_once ('./includes/soapclient/SforceHeaderOptions.php');

This is the Salesforce Login information that is passed when performing the upsert:

/

PHP Code:


			
/ Salesforce Login information
$wsdl = './includes/soapclient/partner.wsdl.xml';
$userName = "salesforce username";
$password = "salesforce password";

// Process of logging on and getting a salesforce.com session
$client = new SforcePartnerClient();
$client->createConnection($wsdl);
$loginResult = $client->login($userName, $password);

Then perform your loop through the Oracle database to get your records for the upsert to Salesforce.com:

PHP Code:


			
 while ($record_count == $number_records)
        {

            // Reset the php execution time to 20 seconds every time we get new
            // records from the database
            set_time_limit(20);


            $sql = "SELECT   id AS ownerid,
                    external_id as \"external_field__c\",
                    address as \"business address\"
                    FROM database_table
                    WHERE ROWCOUNT BETWEEN $starting_record AND ($starting_record + ($number_records-1))
                    ORDER BY ROWCOUNT";

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

            $all_fields = array();
            $i = 0;
            while ($row = $recordSet->FetchRow())
            {
                $all_fields[$i]['OWNERID'] = $row['OWNERID'];
                $all_fields[$i]['EXTERNAL FIELD'] = htmlspecialchars(stripslashes(strip_tags($row['EXTERNAL_FIELD__C'])));
                $all_fields[$i]['BILLINGADDRESS'] = htmlspecialchars($row['BILLINGADDRESS']);
                // You can add addtional rows here if needed
                $i++;
            }

            $record_count = $i;

            $sObjects = array();

            foreach ($all_fields as $fieldset)
            {
                $sObject = new sObject();
                $sObject->type = 'Account'; // Salesforce Table or object that you will perform the upsert on
                $sObject->fields = $fieldset;
                array_push($sObjects, $sObject);
            }
            //This passes the client = the login to sales force
            // the $sObjects = data to upsert
            // $file_updated = accounts which are updated
            // $file_created = accounts which are inserted
            // $file_failed =  accounts which failed
            $success = upsert_accounts($client, $sObjects, $file_updated, $file_created, $file_failed);

            // Update the overall counts
            if (is_array($success))
            {
                $accounts_created = $accounts_created + $success[0];
                $accounts_updated = $accounts_updated + $success[1];
                $accounts_failed =  $accounts_failed + $success[2];
            }

            $starting_record = $starting_record + $number_records;

            ob_start();
            $total_record_count = $total_record_count + $record_count;
            echo $total_record_count." records processed.
";
            ob_end_flush();
        }

The following function does the heavy lifting which performs the upsert to sforce and updates or inserts the accounts:

PHP Code:


			
 function upsert_accounts($client, $sObjects, $file_updated, $file_created, $file_failed)
{
    $accounts_created = 0;
    $accounts_updated = 0;
    $accounts_failed = 0;
    try
    {
        // The upsert process
        $results = $client->upsert("external_field__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
            $data2 = $sObjects[$k]->fields['NAME'] . ", " . $sObjects[$k]->fields[
                'SA_ID__C'];

            if ($result->success)
            {
                if ($result->created)
                {
                    $accounts_created++;
                    file_put_contents($file_created, $data2 . "\n", FILE_APPEND);
                }
                else
                {
                    $accounts_updated++;
                    file_put_contents($file_updated, $data2 . "\n", FILE_APPEND);
                }
            }
            else
            {
                $accounts_failed++;
                // The errors object also contains fields and status_code
                $errMessage = $result->errors->message;
                file_put_contents($file_failed, $data2 . ", " . $errMessage . "\n", FILE_APPEND);
            }
            $k++;
        }
        // Put the result counts into an array to pass back as the result.
        $success = array();
        array_push($success, $accounts_created, $accounts_updated, $accounts_failed);
        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 '
' . print_r($e, true) . '';
        return false;
        exit;
    }
}

ejones · #2 01-29-2008, 02:47 PM

"I will be using the Account Object for this tutorial. First, you must have declared an external id on a custom field either already created or one that is being created. Please understand that an external id has to be one of the following field types:"

So do I have it right that there are no unique keys in the sales force data? I.e your are making one here to allow unique updates?

mike · #3 01-29-2008, 03:25 PM

Quote:

Originally Posted by ejones

"I will be using the Account Object for this tutorial. First, you must have declared an external id on a custom field either already created or one that is being created. Please understand that an external id has to be one of the following field types:"

So do I have it right that there are no unique keys in the sales force data? I.e your are making one here to allow unique updates?

You can use the Id from each object as a Key if you want to perform an upsert, or a custom one.

The one that we use is a custom text field that we created called the sa_id__c. It works perfectly to update or create accounts in our instance of salesforce. It runs on a cron once a week and has never received any sort of Warning or Fatal Error or connection issue