Posted a couple of minor updates to Cloud Converter, mostly small bug fixes that affected the metadata explorer feature. If you already have it installed as a web tab in your org, there’s nothing you need to do. You automatically have access to these. Enjoy!

Had an interesting challenge the other day: all the best last data migration plans were failing. Why? The tool of choice — which one is not important — failed. What to do?

I used Cloud Converter. Quelle surprise, right? I could have used something else, but it’s some decent code that I’m familiar with — so I used it. How?

I first did a test to be sure the API was working using a SalesforceSession object. Why test? Well, you never know. Why the Cloud Converter provided "SalesforceSession"? It’s easy. One line of code or so with a valid user and I established that the user was good, that the API was functional and that the user had rights to the API.

Sweet!

Then I moved to the source documents. I had two kinds: Simple CSV’s and Complex CSV’s.

Simple CSV’s were easy. I added a standard Java FileReader to the sample code, pointed it at the file, and split the data into component parts. Then I built a collection of "Sproxy" objects, added the SalesforceSession to a SalesforceDAO and executed an upsert. Start to finish, about 5 minutes for the first file.

Complex CSV’s were tougher. The difference between simple and complex was that the data contained commas. I know, this seems like it should be relatively easy, but at the time I was drawing a blank. So I imported those Complex CSV’s into Excel, saved them as Excel files and then used another Cloud Converter class, ExcelConverterService.

ExcelConverterService reads an XLS and breaks the data in the cells into a collection of type safe objects. Then I did the same thing I had done with Simple CSV’s: iterated through the data, added it to a Sproxy collection and threw it at the API.

Eh voila – instant data load.

Truth be told, I would rather not have had to do it this way. But when all else failed, it was nice to have an extra tool in my kit.

You can find Cloud Converter over on Google Code or on Developer.Force.com.

Follow Reid: Twitter.com/ReidCarlberg. A few people on my team contributed to this at different points.  Props to Shoby Abdi for getting it all together.  Twitter.com/ShobyAbdi.

 Had a great note today from a Salesforce Sales Engineer in Belgium. David wrote:

 (After David, I have one more screen shot.)

*****

Just wanted to let you know that I’ve spent some time with the new version of the Cloud Converter code you’ve got up on Code Share : It works like a charm and it will be very useful for us and our customers.

I’ve tested it on a local [MS] SQLExpress Server.

Converts nicely to:

I’m sure you’ve seen this in action before but I hadn’t, it’s very cool. Beautiful usage of our Metadata API. The java code is really easy to adapt to any db or table.

*****

Thanks, David.

This next screen shot shows a few other things — namely, the lookup relationship and long field types.

You can follow Reid Carlberg on Twitter or email him at rcarlberg@modelmetrics.com.

One of the absolute best features of the Salesforce.com/Force.com API is the ability to handle lookup relationships gracefully using external IDs.  You can easily have one object with an external ID and then add another object with a lookup relationship to the first without having to know the ID Salesforce.com assigned it. You’ve probably seen this in action in the Apex Data Loader. It saves a huge amount of time.*

I recently had to implement this on my own outside of an existing tool.  I had a hard time finding any useful documentation in this area.  This write up aims to fill that gap.

Let me start by describing the objects we’re working with.  I’m using custom objects, but this can work with standard objects as well.  Let’s call them ObjectA and ObjectB.  ObjectB has a one-to-many relationship with ObjectA.  ObjectA has an external ID field (unique, case insensitive) called “ExternalIdOnObjectA”.  ObjectB as a lookup field to ObjectA.  The table for ObjectA is populated.  We’re upserting a group of ObjectB.

The goal is to create something that says, in effect, “when adding this ObjectB, determine the value to store in the field LookupToObjectAFromObjectB using this value, which you can lookup on ObjectA using the field ExternalIdOnObjectA.”  The XML looks something like this:

<…other fields…>
<LookupToObjectAOnObjectB__r>
<type xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">ObjectA__c</type>
<Id xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:nil="true" xsi:type="xsd:string" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<ExternalIdOnObjectA__c>CC001</ ExternalIdOnObjectA __c>
</ LookupToObjectAOnObjectB __r>
<…other fields…>

A couple of things worth noting:

* The field on ObjectB we’re calling out is the “__r” version and not the “__c”.  If you knew the Salesforce.com ID, you would set it to the “__c” version and be done.
* Note the content of the LookupToObjectAOnObjectB tag includes standard information you would see as part of a regular SObject: Type, indicating that it goes to ObjectA, and ID which in this case is empty since we don’t know it.
* Before closing, we define the ExternalIdOnObjectA field value.

That’s it.  So how do we do this in Java?  Setting a field value in Java is normally just a matter of adding a MessageElement to the SObject.

MessageElementBuilder.getMessageElement(FieldName, FieldValue)

And this is no different.  By the way, “MessageElementBuilder” here is just a little utility class I have that handles – wait for it – building message elements.  It’s the same kind of thing you see in the sample code from

By now you’ve probably figured out where we’re going.  The next step is to build an SObject for the reference first and then set that SObject as the FieldValue for the message element.

SObject foreignKeyToResolve = new SObject();
ret.setType(“ObjectA__c”);
ret.set_any(new MessageElement[] {
MessageElementBuilder.getMessageElement
("ExternalIdOnObjectA__c", "CC001") });

SObject objectBInstance = new SObject();
ret.setType(“ObjectB__c”);
ret.set_any(new MessageElement[] {
MessageElementBuilder.getMessageElement
("LookupToObjectAOnObjectB__r", foreignKeyToResolve) });

And that’s all there is to it. Hopefully this saves you some time down the road.  FWIW, I was working in Java on the Cloud Converter when I wrote this.  However, you should be able to use this technique with any of the toolkits — .NET, PHP, etc.

Good luck!

* In fact, upsert with foreign key resolution saves so much time that I’ve refused to use integration toolkits that don’t support it out of the box.  The good news is that the two major vendors who I have had the joy of talking with about this issue have responded quickly.

You can follow Reid Carlberg on Twitter.

Developing Cloud Converter has been a great excuse for me to get deeper into the Salesforce.com / Force.com Metadata API.  Although working with it isn’t rocket science, I’ve come across a few interesting tidbits I thought I would share.  Before I get too deep into this, I should encourage you to read the docs – they’re pretty useful and contain some very useful sample code.

1. Know when to use the standard API’s “Describe” functionality instead of the Metadata API.  In general, the various “describe” calls will give you a great READ ONLY view into your metadata.  If you don’t need to create, update or delete, use Describe.  Otherwise, use the Metadata API. Oh and you’ll want the Metadata API for developer stuff – Apex, Visualforce, etc.

2. Keep in mind that the Metadata API has a different set of limitations and conventions than the standard API.  For example, the standard API is generally synchronous while the Metadata API is asynchronous.  The standard API lets you do several hundred operations at once.  The Metadata API, 10. 

3. Object types matter.  In the Partner WSDL with the standard API, you use generic SObjects and can often combine object types into a single operation.  Not so in the Metadata API.  In the Metadata API, when you’re working with Custom Objects, you can only work with Custom Objects.  You can’t, for example, mix in Custom Fields.

4.  Sequence matters.  For example, you have to create a Custom Object before you can create Custom Fields or a Custom Tab. 

5.  Naming conventions matter.  For example, some object use Dot Notation in the name, others use Hyphen Notation – the same as Dot but with a Hyphen.  For example, if you want to specify that a custom field should be on a particular custom object, MyField on MyObject, for example, you specify a name of “MyObject__c.MyField__c” and the Metadata API will handle it.  On the other hand, if you want to create a Layout called “My Object Layout” for MyObject__c, you would give it a full name of “MyObject__c-My Object Layout”.

6.  The Metadata API is hackable.  No, not in the War Games way (to be honest, I haven’t tried) – hackable in that you can force it to do some interesting things in ways other than specified in the docs if you get stuck.  For example, in Cloud Converter, I decided I didn’t want to go through the trouble of editing the default layout.  Instead, I created a new one and used the Metadata API’s “update” method.  Worked great!

7. Use the source.  I only figured out the hackable part after I spent some time looking at the Metadata XML files you can download from Force.com.  They’re pretty easy to read and will very likely clear up any trouble you’re having figuring out how to do what you want to do.

8.  The Metadata API appreciates some TLC.  You will occasionally need to do things other than the core “create”, “update” and “delete” functions.  For example, I found that I had to bounce my Metadata API session from time to time.  Once was when I was finished creating the fields and needed to then build a layout. The Metadata API was unaware of those fields until I bounced it.  Another example is that I needed to let the system sleep for a bit between creating the custom tab and loading data.  A third example was when I wanted to create Lookup fields.  When I created those in the same call as the regular fields, it failed.  When I segmented them out to their own create call, it worked.  None of these were big deals – just something to keep in mind.

9.  The Metadata API is very literal.  For example, if you create a new custom tab, it will create a tab.  And that tab will be hidden from all profiles until you tell it otherwise.  There are probably a few other things like this.  I’ve been updating this profile setting manually when testing required me to.  See #10 for how stupid this was.

10. The Metadata API is powerful.  If you’re doing something manually that’s vaguely related to metadata, stop.  You can probably do it with the Metadata API and save yourself a bunch of time.  For example, if you, unlike me, read the docs when you ran into the profile / custom tab issue in #9, you would have found the “ProfileTabVisibility” object designed to handle just that. 

That should get you started.  If you run into any other hints or lessons learned I’d love to hear about them.  Feel free to email or comment.

Good luck!

You can follow Reid Carlberg on Twitter.

I’ve been working on some Cloud Converter enhancements for the last few days.  Everything is uploaded to Google Code and ready for you to use.

The details:

After an extremely helpful note from an early user, I have changed the way we connect to databases.  It’s now more generic.  This is a good thing.   You specify your DB driver, credentials and query string and CC does the rest.  Remember that you’ll need to import your database driver JAR file if you’re hitting something like Oracle, etc.

You can now specify an “external id” field type.  This is extremely useful for tables that include a surrogate id (identity field, etc).

You can now create picklists from any data you like.  So, for example, if you have a field which contains “red”, “yellow” or “blue”, and that field today is controlled by the UI rather than the DB, you can convert that into a picklist by specifying the field name and query.

You can now create lookup fields that reference other objects perfect for keeping track of your one-to-many type relationships.

You can use foreign keys to determine your lookup field values.  This is the standard upsert based foreign key resolution that’s included as part of the regular salesforce API.

Data can now be loaded as either upsert or insert depending on your needs.

There’s a class that illustrates these pretty clearly – CloudConverterScript_Sample.  Open it in your favorite IDE and you can take a look through how it all works.  I’ve also added a road map section to the readme.txt to keep track of some longer-term goals.

Note: Cloud Converter is undergoing active development.  If you have code modifications that you do not want overwritten by a simple update from Google Code, you should copy those off to a different project before downloading this latest update.

Questions? Comments? Suggestions?  Love to hear them.

Please drop me a note at rcarlberg@modelmetrics.com.

 Cloud Converter(???????????)??????????????????????????(??????????)??????