The Cubic Compass RETS Importer is a utility designed to interact with RETS-compliant MLS systems and import their data into Salesforce in an easily-customizeable way. This tool utilizes an XML-based scripting system to define it's behaviors on import. This Wiki Node will document this scripting language (called RIF) and provide examples of implementation.

Basic Syntax

The root node of the xml file is 'fieldmap', which contains a single required attribute 'version' which determins which version of RETS the map handles. Supported versions are: RETSTax (for Tax Data), RETS1.8 (for normal Listing data being imported into a schema which does not support rooms) and RETS1.8Rooms (Normal Listing Data/Schema includes Rooms). The only currently supported version of RETS is 1.8, but if demand for earlier versions exists it is fairly easy to introduce backwards compatibility. With some tweaking it is even possible to import data in a format as early as RETS 1.0 using the 1.8 parser.

The fieldmap node contains one or more objects, which map directly to an object in Salesforce. This can be a stock object (Account, Contact) or a custom object depending on your requirements. Below is a prototypical, 'blank' fieldmap:

<fieldmap version="RETS1.8Rooms">
  <object type="Account">
    <map>
    </map>
  </object>
  <object type="Contact">
    <map>
    </map>
  </object>
</fieldmap>

A great deal of the functionality of RIF is provided by attributes on the object tag. The only required attribute is 'type', which tells the importer what Salesforce object should be mapped to the object in question. The others are all optional, but provide powerful additional functionality, as defined below.

Object node Attributes

The following attributes may be applied to the object node in the fieldmap:

name: Provides a convenient short-hand way of referencing the object. Does not impact functionality.

pushdown: Specifies a name by which the Salesforce ID of the created or updated object can be referenced in later <object> nodes. This feature is used to provide the ability to create a normalized object schema, such that objects have relationships in salesforce. Building atop our example above, if we wanted to create an Account, and then a Contact linked to that Account, we would need to add a pushdown attribute to Accounts object tag, like this:

<object type="Account" pushdown="accountId">

As the importer runs, this will allow you to reference the Account created or updated in the first step in later object's by using the name 'accountid'.

nocreate: Can be set to 'true' (technically you could also set it to 'false', although this is the default value). Any object set to nocreate will not create a new object in salesforce. Normally the importer will first try to find an existing object to update, and in lieu of that create a new object, but if the object is set to nocreate in the fieldmap, it will ignore this second case, and provide a warning ("object does not exist, but the map is set to noCreate, skipping").

countname: By default the importer merely keeps a running total of the object's it has imported, not distinguishing by type. By adding a countname attribute, you can tell the importer to keep seperate track of Create/Update/Total counts for a specific object. Simply set the countname attribute to any string value and the Importer's status E-mail will affix a block containing these custom counts. If we were to expand our Account object tag to read:

<object type="Account" countname="Accounts" pushdown="accountId">

We would see something like the following at the bottom of the status E-mail:

[Custom Count] Accounts Total: 100
[Custom Count] Accounts Created:  8
[Custom Count] Accounts Updated: 92

errorkey: The Importer is designed to isolate errors - that is errors occur on a per-object basis and will not contaminate subsequent objects. In the event that there is an error during the import, a one-line explanation of what happened will be displayed and appended to the status Email. To aid in debugging the issues, an error-key can be defined, which will display some value off the RETS record (a unique ID, for example). The value for this attribute should be a valid String Substitution command (see next section for more information on String Substitutions).

authpd: This attribute defines a name for the Authentication value for the object, which can be references in subsequent objects. See the section on Authentication for more information.

checkfield: Contains the name of a field in the RETS (in XPath syntax, as explained in the next section) which will determine whether or not to skip the record. If the value of the checkfield is blank the importer will skip the record.

The map node, and String Substitutions

As you can see from our example, the object node contains a map node. While the object node can contain other groups, the map node is required, and contains a direct mapping of Salesforce fields to RETS fields. The following is a small example map:

    <map>
       <Street_Name__c>[property.address.streetname]</Street_Name__c>
       <Street_Number__c>[property.address.streetnumber]</Street_Number__c>
       <Unit_Number__c>[property.address.unitnumber]</Unit_Number__c>
    </map>

The names of the nodes within the map represent salesforce properties. You will note that the fully-qualified name is required, so for custom properties, include the double-underscore-c at the end. The inner text of the node represents a String Substitution command that tells the importer what data to pull out of the RETS and put into the property. These examples use a simple XPath syntax, starting at the node in the RETS XML that begins a distinct record (ResidentialProperty in this example). The square-brackets around this name is the command to insert an element from the RETS XML directly, but the String Substitution syntax supports many additional functions, including constant insert, pushdown inserts and conditionals.

This is where the pushdown object-attribute comes into play (see previous section for more information on this attribute). If you have a pushdown attribute set on an object, the resulting sforce ObjectID will be stored in a sort of variable, which can be referenced just like data from the XML, albeit without the XPath. With the following Account object tag:

<object type="Account" countname="Accounts" pushdown="accountId">

You could reference the Account's ID in later objects by a simple:

[AccountId]

This allows you to define rather complex object relationships in a very simple manner. The following are valid constructs within the String Substitution script:

[] - Insert value from RETS or a pushdown value from an earlier object (ie:  [AccountId], [property.address.streetname])

"" - Insert fixed string/constant (ie:  "Not Applicable").

+ - Add a single space (a shorthand for " ")

?=VAL VAL: TRUE | FALSE - Conditional Equals.  If the two VALs are equal, the TRUE element will be inserted, otherwise the FALSE element will be inserted.  This is useful both for
creating internal logic in the fieldmap, as well as for conforming with Salesforce typing requirements.  For example, sforce expects 1 and 0 for boolean values.  If your XML uses 
'true' and 'false' you could do something like this:

   ?="true" [some.rets.property]: "1" | "0"

Note that conditionals can be nested indefinitely, so either (or both) VALs, as well as the TRUE and FALSE conditions can themselves contain conditionals.

??VAL VAL: TRUE | FALSE - Conditional Contains.  This works functionally the same as ?=, but checks not for equality, but to see if the first VAL contains an occurence of the 
second.  The same rules apply.

{ } - Grouping of values, used in conditional clauses.  If you want the TRUE or FALSE clause of a conditional (or either of the VALs) to contain multiple substitution elements 
you can group them using these.  For example, to check whether one of a couple of different fields are set to 'ACTIVE', you could use the following:

   ??{ [some.rets.property][some.other.property] } "ACTIVE": "1" | "0"

As in most any scripting/programming language, white-space characters (tabs, spaces and line-feeds) are ignored, so complex String Substitutions can be formatted for clarity as you see fit.

KeyFields

In addition to the required 'map' node in objects, there are additional nodes that can be added to gain additional functionality. The most common is <keyfields>, which allows the importer to do more than just insert records. In most instances, you will want to perform an UPSERT operation, wherein a record is modified if it's already in Salesforce, and added if it's not. The keyfields node tells the importer how to check and see if an object exists - it essentially defines the Primary Key for a record.

Syntactically it is the same as the map element, containing one or more Salesforce Field->String Substitution elements, all of which will be checked against salesforce and the RETS data. For example, the use the address and postal code as a primary key, the following can be used:

    <keyfields>
       <Street_Name__c>[property.address.streetname]</Street_Name__c>
       <Street_Number__c>[property.address.streetnumber]</Street_Number__c>
       <Unit_Number__c>[property.address.unitnumber]</Unit_Number__c>
       <Postal_Code__c>[property.address.postalcode]</Postal_Code__c>
    </keyfields>

Note that every element in the keyfield must be a match in order to identify a record, so careful assessment of the data and selection of keyfields is necessary to ensure high data-quality.

Authentication

In many cases, RETS data being importer is being imported against existing data in Salesforce, either data that was hand-entered, imported via the Salesforce importer, or imported via different RIFCode in seperate processes. A common example is using Tax data to import global properties, and then using listing data to import actual instances of a sale. In cases like this, it can often be useful to introduce some kind of authentication process to check for potential data-quality issues that may require RIF changes or manual intervention. The Importer's authentication process is similiar to it's keyfield system -- that is, it checks a series of Salesforce properties against the appropriate RETS data, and will set a variable to '1' or '0' depending on whether or not a perfect match is found. That variable (just like a pushdown variable) can then be used in later <object> tags as a value. Commonly a checkbox field called 'Authenticated' would be added to the appropriate Salesforce object, and then set based on the RIFCode.

There are two elements to setting up Authentication, the first is the addition of an authfields node within the appropriate object. This is formatted precisely like a keyfields or map node, with one or more fields inside containing elements to check. For example, to authenticate on address:

    <authfields>
       <Street_Name__c>[property.address.streetname]</Street_Name__c>
       <Street_Number__c>[property.address.streetnumber]</Street_Number__c>
       <Unit_Number__c>[property.address.unitnumber]</Unit_Number__c>
       <Postal_Code__c>[property.address.postalcode]</Postal_Code__c>
    </authfields>

In addition, you will need to add an attribute to the <object> tag in question to define the name of the pushdown variable which will contain 1/0 based on the result of the authentication process. This attribute is 'authpd'. In the example below, the Property object is not being modified in the map (it is imported elsewhere), but we authenticate our data against it:

  <object authpd="AuthPD" type="DLOG_Property__c" name="Property" pushdown="PDProp" nocreate="true">
    <keyfields>
       <TaxID__c>[tax.taxinformation.listingtaxid]</TaxID__c>
    </keyfields>
    <authfields>
       <Street_Name__c>[property.address.streetname]</Street_Name__c>
       <Street_Number__c>[property.address.streetnumber]</Street_Number__c>
       <Unit_Number__c>[property.address.unitnumber]</Unit_Number__c>
       <Postal_Code__c>[property.address.postalcode]</Postal_Code__c>
    </authfields>
    <map>
    </map>
  </object>

This is executed as follows: First the importer will try to locate the appropriate DLOG_Property__c object by referencing the TaxID. If it does not find it, [AuthPD] will be set to '0' and nothing will be created due to the nocreate attribute. If we did not have the nocreate attribute, the system would create a blank Property at this point, which would remain blank due to the map element being empty. If it does in fact find the appropriate record, the system will then do a line-by-line comparison of everything in the authfields block. If everything is a match [AuthPD] will be set to 1 and can then be used as a boolean value in a later object. If there is no match, the system will set [AuthPD] to 0 and continue on.