Introduction: Importing and Exporting Data

Overview

This introduction will cover methods of importing data to and exporting data from QBO3, including:

• • Saving single records

  • Saving parent and child records

  • Saving records without a primary key (ID)

  • Custom saves

  • Saving data via XML

  • Saving data via Excel

  • Saving data via text files

  • Automating imports via FTP monitoring

Saving single records

QBO3 is based on a RESTful API, where each table in the QBO database has a matching URL endpoint. For example:

• Contact records can be accessed via /Contact/Contact.ashx

  • Contact Method records (phone number, emails, etc.) can be accessed via /Contact/ContactMethod.ashx

• Message records can be accessed via /Message/Message.ashx

• Attachment records can be accessed via /Attachment/Attachment.ashx

  • Attachment Template records can be accessed via /Attachment/AttachmentTemplate.ashx

• etc.

Each of these endpoints supports many "operations", such as Insert, Update, Select, Search, Delete. For example, to insert a new Contact record:

Contact/Contact.ashx/Insert?FirstName=John&LastName=Doe

will insert a record into the Contact table, returning the Contact with a ContactID primary key. Let's assume that the ContactID for John Doe is 127.

To update the John Doe record with a middle name, call the Update operation:

Contact/Contact.ashx/Update?ID=127&MiddleName=Robert

When updating records, you only need to specify the columns that are being updated. Any columns not specified will be 'left alone' in the database. If you intend to make a column NULL, you can specify the column with no value:

Contact/Contact.ashx/Update?ContactID=127&MiddleName=

Rather than calling Insert or Update, you can instead call the Save operation, which simply:

• • Inserts a record QBO cannot figure out an ID, else

  • Updates a record

// Create a record for Jane Doe

Contact/Contact.ashx/Save?FirstName=Jane&LastName=Doe

// Assuming the ContactID is 128, update the Jane Doe record with a status

Contact/Contact.ashx/Save?ContactID=128&Status=Deceased

All QBO3 tables contain an identity column matching the table name, with 'ID' appended. For example:

• • Contact.ContactID 

  • Message.MessageID

  • Attachment.AttachmentID

  • AttachmentTemplate.AttachmentTemplateID

When updating records, you may specify the full identity column name (e.g. ContactID), or an abbreviated 'ID'.

// These calls are the same

Contact/Contact.ashx/Save?ContactID=128&Status=Living

Contact/Contact.ashx/Save?ID=128&Status=Living

Saving parent and child records

When saving a record, QBO3 supports a syntax for saving parent and child records. Assume we've create a Process bound to an Organization:

• Organization: Acme Insurance (ID=123)

  • Process: Contract Renewal (ID=234)

    • Message 1: Hello World (ID=345)

    • Message 2: Welcome to Thunderdome (ID=456)

    • Task 1: Contract Drafted (ID=567)

    • Task 2: Contract Approved (ID=678)

// Save a task, updating the Process' status

Decision/ImportForm.ashx/Save?ID=567&ActualCompletion=2/1/2017&Process_Status=Under Review

// Save a task, updating the Process' status and the Organization's OrganizationType

Decision/ImportForm.ashx/Save?ID=567&Process_Status=Under Review&Process_Organization_OrganizationType=Approved Insurer

// Save a Process, adding a child message

Process/Process.ashx/Save?ID=234&Messages_0_Message=It's a jungle out there

Saving records with a SubscriberID

Sometimes, QBO needs to accept updates from other systems without the other system knowing the QBO ID for a record. QBO3 supports a 'SubscriberID' field as a unique identifier for a record. Assume we wish to import a Contact record from SalesForce:

Contact/Contact.ashx/Save?FirstName=John&LastName=Doe&SubscriberID=na1.salesforce.com-00330000000xEftMGH

In this case, QBO3 will check to see if the SubscriberID exists in the ObjectSubscription table. If it does, QBO3 will retrieve the ID of the Contact record from ObjectSubscription.SubscribedObjectID, and proceed as if the ContactID has been passed to the Save method.  If the SubscriberID has not been seen before, Insert is called, and the newly created ContactID and SubscriberID are then added to the ObjectSubscription table.

Saving records with AutoBind

There are several use cases where a QBO3 client may choose to consider a compound key unique. For example:

• • Contact last name + social security number

  • Loan servicer + servicer loan number

  • Foreclosure attorney + attorney case number

  • Attachment parent + filename

In such cases, one may create an AutoBind statement that queries the database to retrieve a matching ID. Autobind works as follows:

• • for each autobind statement defined

    • if the relevant parameters are present, execute the statement

    • if the statement has returned at least one row containing an ID, use that ID

Thus, if we implement Contact/AutoBindByNameAndSSN:

// This will create a contact record

Contact/Contact.ashx/Save?FirstName=John&LastName=Doe&USSSN=123456789

// This will update the previously created Doe+123456789 record

Contact/Contact.ashx/Save?LastName=Doe&USSSN=123456789&Status=Deceased

Custom saves

If QBO's standard Save, Insert and Update methods don't cover a use case, you can always created a custom statement to handle the data directly. For example, assume that:

• • We don't have a ContactID available

  • We're not using SubscriberIDs or Autobind

  • We'll pretend that FirstName + LastName + State is unique

  • When saving a contact, we want to ensure there is a task in the system to review any newly added contacts. 

(There are more elegant ways to do this via a data listener, but we'll use it as an example of a custom statement.) 

Create a statement called 'SaveAndReview':

IF EXISTS (SELECT 1 FROM Contact WHERE FirstName = @FirstName AND LastName = @LastName AND State = @State) BEGIN

  UPDATE Contact SET {Select.Update} WHERE FirstName = @FirstName AND LastName = @LastName AND State = @State

END ELSE BEGIN

  INSERT INTO Contact ({Select.Insert}) VALUES ({From.Insert})

  INSERT INTO ImportForm (ImportForm, Object, ObjectID) VALUES ('Review new contact', 'Contact', SCOPE_IDENTITY())

END 

Once this has been created, you can call:

// This will create a John Doe record, and insert a task to review the record

Contact/Contact.ashx/SaveAndReview?FirstName=John&LastName=Doe&State=CA

// This will update the existing John Doe in CA record; no extra task created

Contact/Contact.ashx/SaveAndReview?FirstName=John&LastName=Doe&State=CA&Status=Deceased

Saving data via Excel

Saving a single record is nice, but limited. The important concept to grok is a method signature:

{ClassName}/{Operation}?{Parameters}

In the examples above, we've dealt with several method signatures, including

• • Contact/Insert

  • Contact/Update

  • Contact/Save

  • Contact/SaveAndReview

There are several ways to call such method signatures in bulk, perhaps the most popular being Excel via the BatchEngine.

Assume you wish to import 10,000 contacts from another system, and that you are able to get the data from another system into Excel. If you structure the Excel spreadsheet appropriately, the BatchEngine will call a method signature for every row on every sheet in the workbook.  You layout would be something like this:

Note that when invoking a method signature via a web browser, you specify a path to a specific .ashx handler:

• Contact/Contact.ashx

• Message/Message.ashx

• Attachment/Attachment.ashx

With a method signature, you only need the class name:

• Contact

• Message

• Attachment

The .ashx handlers are simply organized into folders for development reasons.

This concept of method signatures is rooted very deeply in QBO3, and is a pattern you will see repeatedly, including:

• Workflow steps may call any method signature

• ImportFile/Apply may apply a method signature to the results of any QBO3 operation

• Attachment templates use a method signature to retrieve data for mail merges

• etc.

There are other IImport engines that can read Excel data, but the ability of the BatchEngine to apply any method signature to each row of data in a spreadsheet is the most flexible.

Saving data via XML

Saving data via XML offers several advantages:

• 1. Multiple records may be saved from a single XML document

  2. Nesting of parent and child records is "natural", and does not require tracking parent/child keys

  3. Any method signature may be applied to each node of the XML document

In an example above, we demonstrated the ability to save a Task and have it update a parent Process data element. In a slightly more elegant tweak, this XML will save a process as well as child tasks and messages:

<ProcessCollection>

  <ProcessItem>

    <Process>Contract Renewal</Process>

    <Object>Organization</Object>

    <Parent>

      <Organization>Acme Servicing</Organization>

    </Parent>

    <Forms>

      <ImportFormItem>

        <ImportForm>Contract Review</ImportForm>

        <ProjectedCompletion>2/1/2017</ProjectedCompletion>

      </ImportFormItem>

    </Forms>

    <Messages>

      <MessageItem>

        <Message>Added a Contract Renewal Process via XML!</Message>

      </MessageItem>

      <MessageItem Operation="SaveEmail">

        <Message>Contract renewal begun</Message>

        <ToAddress>me@company.com</ToAddress>

      </MessageItem>

    </Messages>

  </ProcessItem>

  <ProcessItem>

    <Process>Contract Renewal</Process>

    <Parent>

      <Organization>Baker Bridge Construction</Organization>

    </Parent>

    <Forms>

      <ImportFormItem>

        <ImportForm>Contract Review</ImportForm>

        <ProjectedCompletion>2/1/2017</ProjectedCompletion>

      </ImportFormItem>

    </Forms>

  </ProcessItem>

</ProcessCollection>

Some patterns to note:

• • The root node may be anything you want; <GobbeldyGook> will work, though it may raise eyebrows

  • Nodes that end with "Item" will be compared to installed modules; if there's a match, the node is "imported"

  • The Parent node under Process is a bit "special": generic objects (that use Object/ID pattern) know to interpret Parent properly

  • Child collections (in this case, Forms and Messages) can be defined or extended via configuration

    • prefer 'Tasks' to 'Forms'? No problem

  • If on operation is specified, Save is assumed

  • The Message/SaveEmail signature will save a message, and email the message to recipients

Saving data via text files

If none of the above meets your use case, consider more traditional ETL processes offered by the SqlBulkEngine and other plugins. If none of our IImportEngine implementations meet your needs, you can create your own plugin, and, er, plug it in to our Import infrastructure.

Automating imports via FTP monitoring

Being able to import Excel, Xml, or text data is nice, and the QBO UI provides a drag-and-drop interface to upload and process such files. Getting such files into QBO automatically (without a user dragging and dropping) can be achieved by:

• • Creating a File Object entry that maps to some sFTP site (or any other file repository, like S3, Azure, Dropbox etc.)

  • Create a job to watch the File Object for files meeting some naming pattern (using ImportFile/Watch)