Blog

I'm doing a project for a state government organization somewhere in these glorious United States of America. The clients have been really cool and fun the whole time. They are sane, sober, professional, smart and engaged, yet still able to keep their distance from stuff they shouldn't micromanage. The project is pretty much on schedule. The budget is more or less on track.

The job is to do another one of our ExpressionEngine / Salesforce integrations. The client has a membership oriented website. This means that people pay my client to be a member of their website for a period of time and through that fee, they get access to special stuff and privileges. My job is to connect several Salesforce objects to their EE counterparts and establish the methods through which those objects will be kept in sync with one another. More specifically, Salesforce has a type of object called an Account. An Account represents a company or organization. Then there is a Contact object which is the way that the people who work for the organization are tied to it. It's a one to many relationship; one account, many contacts.

So we are mapping the Account and Contact objects to EE. This means we are mapping Accounts into an EE channel called Members and we are mapping Contacts into the standard EE member object. Everything was going along fine, as normal, until we came to the concept of locations. An organization that might be a member of my client's website might have more than one location. For example, they may be a farm which has a primary location, the farm, and related locations, which are booths at various regional farmer's markets. My client did a smart thing when they first built the website, they created a Pixel & Tonic Matrix field to capture the various locations. Rows are individual locations and columns are of course the street, city, state, zip, etc.

So my client has a smart little Matrix field using Brandon's smart little addon. (Actually not little, it's quite a complex and subtle critter! Thankfully well designed as we will shortly see.) My task is to map the locations in the EE Matrix field into SF, bidirectionally. When someone changes their EE member profile, the profile that represents their organization, that change needs to go up into SF. And when someone changes the data on the SF side, it needs to come back to EE. Why? Well my client has reps who go out to talk to member organizations to collect data and try and be of service in various ways. That's a customer relationship job, ideally suited for a CRM system like Salesforce.

Mitchell, when do you get to the 'free' part?

We're there now actually. When we first took the job, I didn't notice the Matrix field filled with locations. I'm like that sometimes. I like to throw myself at stuff and clean up the mess later. Yes I should grow up. I'm working on it. So anyway, when I bid the job I did not add time in for the complexity of this Matrix field. There is actually no analogue to a Matrix field in Salesforce that I know of. At least there is not one through the standard set of fields that come with Salesforce. So the client and I got a little stuck. We needed to either drop the locations bit until phase 2 or we needed to find some way to make it happen. You can create custom objects in Salesforce and you can create custom fields in those objects. So it is possible to create a custom location object that would have a one to many relationship with an SF Account. And right here is how you get me to do work for free.

I should get therapy and get over this, but the fact of the matter is that I am a sucker for hard coding problems. I get seduced by them. I bet you can relate. I see a problem that I do not know with certainty how to solve, but I see enough of the solution poking its head up over the top of the ridge that I can't help but chase after it. It's my dog nature perhaps. Aside from this, I had assured my cool, fun client when we started the job that we would get through the weeds together. My estimate had a little padding in it in case there was something knotty like this, though certainly not enough to cover the expense of this level of problem. Nevertheless, the 3 step formula above kicked in and I offered to take care of this Matrix thing off the books. I do think this client will turn in to a long term client. They need a whole new e-commerce system pretty soon right?

Ok so here are the technical bits. I don't usually blog about actual how-to stuff, but it's fun today. So I have a new object in Salesforce. It's called, at the API level, Locations_c. That _c part designates a custom thing. Salesforce appends that to the end of custom fields and custom objects alike. The Locations_c object has custom fields; the usual suspects like street, city, state, zip, etc. Importantly, Locations_c has a field called Account_c. This field is a special field type in SF called a Lookup field. It is very much like a Playa or Relationship field in EE in that it lets you attach one record to another through a relationship.

Now on the EE side we have that Matrix field I mentioned. The job here is to translate these 2 fields to one another during the various sync events that may take place. Someone may update a channel entry on the EE side and have that push up to SF in real time. Or someone may update the SF object and need that sucked into EE. We do that last part on a cron job for reasons I may blog about one of these days. A Matrix field saves it's data in a separate DB table. It's capable of great complexity, but the API we interact with keeps things quite simple. Whether a channel entry is being updated on the EE side or whether we need to update a channel entry from the SF side, since my code funnels itself through the channel entries API in EE, we get to interact with the Matrix field just the same way any other EE software would. So all we have to care about now is how Matrix thinks at the level of form POST. And here's how that looks:

[field_id_37] => Array
(
    [row_order] => Array
    (
        [0] => row_new_0
    )

    [row_new_0] => Array
    (
        [col_id_2] => Home
        [col_id_3] => 555 Meat St.
        [col_id_4] => 
        [col_id_5] => Carne Asada
        [col_id_6] => Texas
        [col_id_7] => 79707
        [col_id_8] => 555-555-1212
        [col_id_9] => US
        [col_id_11] => 
        [col_id_22] => 
        [col_id_24] => 
    )

    [deleted_rows] => Array
    (
    )
)

The above is an example of a fresh new Matrix field being submitted. row_new_0 means a new Matrix row was created and needs to be saved to the DB. Here's what the array for an existing row looks like:

[field_id_37] => Array
(
    [row_order] => Array
    (
        [0] => row_id_3490
    )

    [row_id_3490] => Array
    (
        [col_id_2] => Home
        [col_id_3] => 555 Meat St.
        [col_id_4] => 
        [col_id_5] => Carne Asada
        [col_id_6] => Texas
        [col_id_7] => 79707
        [col_id_8] => 555-555-1212
        [col_id_9] => US
        [col_id_11] => 
        [col_id_22] => 
        [col_id_24] => 
    )

    [deleted_rows] => Array
    (
    )
)

The row_order array does the smart job of both indexing the list of other rows in the Matrix field but also saving the specific order of them. And lastly here's the delete condition on a row in the Matrix field:

[field_id_37] => Array
(
    [row_order] => Array
    (
        [0] => row_id_3490
    )

    [row_id_3490] => Array
    (
        [col_id_2] => Home
        [col_id_3] => 555 Meat St.
        [col_id_4] => 
        [col_id_5] => Carne Asada
        [col_id_6] => Texas
        [col_id_7] => 79707
        [col_id_8] => 555-555-1212
        [col_id_9] => US
        [col_id_11] => 
        [col_id_22] => 
        [col_id_24] => 0009976757fAAC
    )

    [deleted_rows] => Array
    (
        [0] => row_id_3490
    )
)

Matrix keeps all the data in the arrays, but adds the row to be deleted into a deleted_rows array. Once that POST array hits the Matrix field type, Matrix parses those arrays and does DB stuff with it. But we don't care about that since the API on that POST array can be relied upon to handle our data.

Our job now, when someone submits a channel entry and wants that data to land up in SF, is to parse that POST array and turn it into something that SF can understand. Here's where life gets both tricky and fun. And yes, here's where you seduce me into doing work for free. See in that POST array example above where there is a Matrix column called col_id_24? See it's value? 0009976757fAAC. That's a unique Salesforce id. In fact, it is the id of the location record on the SF side that matches up with that Matrix row. I had previously saved it. Remember, the example directly above is one where someone has marked a Matrix row for deletion. So I need to intercept that form POST and pass a delete event over to SF. In my code, which lives in an extension file of my CRM module, I listen to the entry_submission_end() extension hook that is native to EE. I receive the POST array through that hook. I process it, grab data I care about and fire off at least one API call to SF. In this case I have a little sub-loop that looks for 'deleted_rows'. I have a field map that is saved elsewhere so that I have an easy programmatic way of extracting data from EE arrays based on their SF counterparts. In this case I have a mapping that tells me Location_c. Account_c is saved in 'col_id_24' of any given Matrix row of field_id_37. So I can assemble an array of SF locations and I can loop to delete them from SF.

But what happens when I need to create or update a new location? Similar to the delete approach, I loop through the POST array I receive through the EE extension hook and I extract the data I care about. I assemble one array for both create and update actions because with SF you just call an 'upsert' method on the SF API. If your upsert call lacks an SF record id, SF assumes you want to create, otherwise it updates. Because I have a complete mapping of SF fields to EE Matrix rows, as below, I can map fields before I send them to SF. (I trimmed off some array elements to protect the innocent.)

private $locations_matrix_fields    = array(
    'col_id_2' => 'Name',
    'col_id_3' => 'Street__c',
    'col_id_5' => 'City__c',
    'col_id_6' => 'State__c',
    'col_id_7' => 'Postal_Code__c',
    'col_id_8' => 'Phone__c',
    'col_id_24' => 'Id'
);

So that's juicy. I can handle the CRUD in the EE to SF direction, meaning, I have a way to Create, Read, Update and Delete. But I also need to be able to CRUD from SF to EE. In my CRM module for EE I have an abstracted sort of a method that I can call to sort of pretend like I am submitting a channel entry through the EE CP or an SAEF form. EE provides a channel entries API which I use for this. I need only assemble a POST array such as EE and Matrix are expecting and all behaves accordingly. So I call to SF and get the locations for a given Account. I loop through those and check the local DB to see if I already have a record for the locations. Additionally, if I need to delete, I capture that case as well. The result is a Matrix array as you see above. I assemble that and pass that through to the channel entries API. That API in turn invokes that Matrix field type. As long as my array is of the correct format, all should fall into place.

I had all of the above coded up and working before I realized I had missed something. In order for all of this junk to work, I need to correctly save the SF location id in the corresponding EE Matrix row. In the case of saving a new row and creating an SF location record for it, I needed a way to get the new location id back from SF after I create a location record and save it into the Matrix row all before the row is sent back to EE. I had built my EE to SF sync on the wrong extension hook. entry_submission_end() does not let me alter the Matrix data in time to catch it before it's saved to the DB. I could of course go back and hit the DB myself, but that would be a lot of trouble and worth it only if there were not another extension hook I could use. Good thing Brandon saw me coming. matrix_save_field() does just what I need it to do. It gives me the field's data array AND it lets me alter it before it is processed by Matrix itself. So a couple of little code refactorings and I am up and running!

If you are liking the sound of the CRM module I am talking about and you want to buy a copy from the Solspace store for a project you are working on, I'm sorry; you can't. Each and every EE / SF project we have done using this CRM module has involved custom coding such as you see above. The process of integrating two systems together through their API's tends to be sufficiently complex and custom that some level of custom coding is called for each time. So if you think you or your client needs a solution like this, reach out and contact us. Much of the solution is scalable, but at least a little of it has to be custom coded.

And how do you get me to do it for free? Well I don't really want the word to get out far and wide, but it's likely that if you are cool and fun and you can demonstrate that you can remain cool and fun for a long period of time and you have a really funky, difficult, yet still somehow within reach problem to solve, I just may get seduced into helping you out, at least a little bit, for free!