DataBasin - object inspector and updates

First, the underlying DataBasinKit framework got an important update.
[DBSoap update] now supports setting fields to null. That was quite a major detail missing: you could reset to blank even string fields.
This required me to fiddle a bit to generate the fieldsToNull list. Every field passed with an empty string value is considered to null.

<update xmlns="...">
<sobject xsi:type="sf:Account">
<id>....</id>
<fieldsToNull>AccountNumber</fieldsToNull>
<fieldsToNull>OtherCity</fieldsToNull>
<Name>New Name</Name>
</sobject>
</update>

The Object Inspector, the handy tool which allows you to inspect all field values of a record and knowing immediately their developer name given the Object Salesforce Id, how got update powers!

As the Screenshot (here on MacOS) shows, changed values show in a different color (non-updatable fields show in italics and their Cell is not editable). The total number of fields to be changed is summed up in the status field. Only fields marked as changed are updated when the Update button is pressed, other are left as-is and not overwritten for safety.

Further work has been done in the Inspector and full search filtering is now available!
Just entering a a sting will filter out the relevant rows. Both the Field Name or Developer Name are matched, as well as the content! It is thus super-easy to look for all fields (also fields not at layout) which have a certain Value. All fields false? easy as in the screenshot:

DataBasin 0.8

DataBasin 0.8 is out!

After several months of development and testing, many news:

• DataBasin is now divided in its DataBasinKit framework which is LGPL'd and the application itself
• (CSV writing) Support of empty fields in empty semi-joined objects through query parsing
• (CSV writing) Support for writing fields in exact order as in user query, trough query parsing
• Select Identify supports LIMIT
• Customizable CSV file quoting and separator
• Support for COUNT and aggregate queries
• Object Inspector supports selection of values in cells

The most important news is the DataBasinKit separation.
The most important feature instead is DataBasin ability to parse the SOQL query and thus rearrange the output fields in CSV files not as Salesforce returns them but as the user requested them. The same feature allows related objects (. notation) to be null and retain the correct columns in the CSV file.

DataBasin: more powerful CSV writing for Select and Select-Identify

DataBasin, the clean-room impelmentation of a data extraction tool for SalesForce.com available for GNUstep and Mac just made a big leap forward!

If you have ever done a SELECT in salesforce.com, you might have noticed that the results are not ordered and that semi-joined fields ("." notation) are handled strange because a whole object is returned.

DataBasin extracts the  columns of the CSV file by checking the first row of the dataset and recursing on the field names of salesforce's response. The effects in DataBasin are the following:

• The field order is not preserved
• If a sub-object is queried, all those fields are grouped together in that object
• If more than one field is queried on a sub-object and on certain record this object is missing, the number of columns between records is inconsistent: salesforce.com doesn't return the whole object and DataBasin iterates record after record in the sub-object, but if it it is totally missing, only one empty column will be written, not as many as needed
• Capitalization of the column names doesn't get preserved (compared to the above issues, something really minor)

I added a new option available in write out fields in the order of the query.

This feature implies a more complex under the hood. For each record gotten in the response, the field names are reconstructed as before (e.g. by recursing inside sub-objects) and put in a Dictionary.

The original SOQL query is parsed and the field names extracted, then these names are used as keys for each record dictionary and written out.

• Order preserved, also with sub-objects
• Fields preserved and empty values are written even if whole sub-objects are missing
• Case is preserved

This approach looks fine and comes with a relatively small performance penalty, however it is delicate because during parsing of the query, many naming subtleties of Salesforce.com needs to be inferred, that is, DataBasin tries to infer how the name in the Response will be:

• field aliases
• aggregate names with no alias, which are called progressively Expr0, Expr1
• idiosyncrasies with aggregate functions. A field in a sub-objects is called Object__c.Field__c when in a regular query, but when used in an aggregate query, only Field__c is used, disregarding the join-depth
• COUNT() vs.COUNT(Id)

I hope I have not missed any! in any case, enjoy!

This feature is available now both in Select and Select-Identify, since it is a problem of the CSV Write backend and enhances DataBasin operation usability greatly, bringing it on par with DataLoader.
Since DataLoader performces similar transformation and sometimes produces wrong results, I left this feature as optional with the flag "Write fields in query order".

DataBasin: advanced SObject describe

I enhanced DataBasin object's describe. First, I do parse the RecordTypeInfo now, but that was the easy part.  Salesforce.com returns only the record type ID and name, but programmatically, this is not very useful. The important bit would be the Developer Name of each record type.

I enhanced the results by querying automatically the RecordType table, extracting the Developer Name, matching it through the RT Id and merging back the results, so that the resulting DBSObject has a complete Record Type information, totally transparent.

DataBasin 0.6

DataBasin 0.6 released!

• Support "." as Semi-Join
• Select Identify works now with multiple columns (all in AND as condition)
• Support LIMIT clause in Select Identify
• Improved Exception catching and display for Select Identify
• Object Inspector supports selection of items
• Log window with selectable log level
• Progress and estimated completion time displayed in Log
• Remember logins
• internal API improvement
• vast 64bit and portability improvements
• Support count and aggregate queries

Select Identify is now vastly improved. Supporting multiple columns and LIMIT, coupled together with the other improvements of this release, makes it an invaluable tool during support and maintenance of a Salesforce org. How else could you check the status of a lot of objects, say Cases or Accounts?
The object inspector is always handy too.

DataBasin allows you to work with SalesForce.com much easier and also from your favourite free OS  using GNUstep like any BSD or GNU/Linux or at need on Solaris or Macintosh.

Many thanks to my colleagues, Claudio and Paolo amongst them, for finding bugs and testing the 9 internal beta releases!

DataBasin Object Inspector sorting

The fields are now sortable by Label and Developer Name. A little feature that was sorely needed within orgs with almost 500 fields per object!

This feature is currently not available in GNUstep.

DataBasin, Objective-C portable data access tool.

DataBasin: object inspection

DataBasin now sports an Object Inspector which works in the easiest way possible: insert a SalesForce.com ID: DataBasin will try to attempt do determine the object type, automatically describe it and finally load the data.

How many times did you have the need to quickly inspect an Object of which you got the ID in an exception email? Or how many times did you have to inspect the value of a field not visible on the layout?

This new feature required some extension to the underlying API of DB: the Soap class now has an identifier method which looks for an ID in all objects returned by the Describe Global in the attempt to identify the object it comes from.

The coolest addition however is in the DBSObject class itself: it is capable of loading or refreshing the values of a set of fields or of all known fields. And behold, a finesse: when loading the value of all fields, the queries will be split into executable queries without reaching the maximum SOQL size of 10K chars, as opposed to Apex Explorer which chokes if you select all fields of a very big object.

DataBasin 0.2

DataBasin v 0.2 was released, the free software, open source tool for working with Salesforce.com.

The core library was enhanced and new features were added.

Read more and download it from the DataBasin homepage.

First release of DataBasin

I'm pleased to announce the first release of DataBasin, a tool which allows to access Data on SalesForce.com using its WebService APIs. Core part of the tool is the API implementation in Objective-C, which uses the GNUstep WebServices for the low-level interaction.

The API implementation is designed to be reusable in other application and is LGPL'd.

Basic functionalities like Query, Insert and Delete are implemented (and of course log-in!) but the tool contains many rough parts, like the CVS importer/exporter so production use is not yet recommended.

As far as I know this is the only open source library for Salesforce.com, at least in Objective-C.

In the screenshot DataBasin running on Debian GNU/Linux.

A big thanks to many friends who helped me implementing the API and debugging the SOAP Calls. First of all Richard Frith-MacDonald who extended the GSWS to support SOAP in the first place. Then two colleagues, Andrea Rosa and Enrico Murru who helped track down many bugs.

DataBasin gets Update and a new inspector

DataBasin, the SOAP data access tool for Salesforce of the GNUstep Application Project, got new features! Check the screenshot.

The SOAP layer was enriched with the update call. To perform an update the steps are identical to an Insert and the already existing object describe is use to retrieve the list of available objects. Currently, as with Insert, the CSV column titles need to match exactly the Salesforce fields.

Also, DataBasin got a new inspector about the user properties retrieved during login. These informations can be handy during development, like the ID of the Org, the User, its profile and role. They are exposed in Output-only fields with support for copy-paste to other applications

DataBasin is now able to create records in sfdc

DataBasin is now able to create records in SalesForce.com starting from a CSV file.

First, as shown in the screenshot, the already implemented method describeGlobal retrieves the available objects to insert into. Once the user selects them the CSV file is read and the objects get created. There is no provision yet for mapping the fields, the first line is assumed to contain the field names.

The API implemented by DBSoap is now getting a little more complete step after step. The basic operations of creation and query are already possible.

describeGlobal implemented in DataBasin

I implemented describeGlobal in the DBSoap layer of DataBasin which communicates with salesforce.com. Thus a list of all objects in the system can be queried, standard and custom objects. The return is just an array of strings.

Step after step I am recreating a simple API interface to the salesforce.com Web services in pure Objective-C.

DataBasin, or how to access salesforce.com from Objective-C

After several weeks of work, I can announce my first concrete success: the download of the contact list from the CRM to a CSV file.

On supported platforms, like Java or C#, salesforce.com offers libraries for a convenient access to their APIs (webservice interfaces). For Objective-C and GNUstep of course there is nothing of the kind. Thus I started writing a layer to access the webservices directly using SOAP calls. To be able to write those, I use GNUstep's WebServices framework, which I discovered was still quite rough and incomplete. Richard Frith-MacDonald though promptly debugged it and extended it. Until the first login was possible, a lot of debugging and investigation on the calls was necessary, I was patiently supported by my colleague Andrea Rosa.

Currently the donload means that the Login method completes correctly and returns a valid session, which is then reused to perform the query request and interpret its response. It is a demonstration that the whole concept can work

My intention is to develop DataBasin to be a free Open Source alternative to the DataLoader tool supplied by salesforce.com. Once I reach that goal, I alreayd have further ideas on how to extend it.

DataBasin is part of the GNUstep Application Project and will be released there once ready for prime time.