OverviewThis introduction will cover methods of importing data to and exporting data from QBO3, including:
Saving single recordsQBO3 is based on a RESTful API, where each table in the QBO database has a matching URL endpoint. For example:
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=Doewill 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=RobertWhen 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:
// Create a record for Jane DoeContact/Contact.ashx/Save?FirstName=Jane&LastName=Doe// Assuming the ContactID is 128, update the Jane Doe record with a statusContact/Contact.ashx/Save?ContactID=128&Status=DeceasedAll QBO3 tables contain an identity column matching the table name, with 'ID' appended. For example:
When updating records, you may specify the full identity column name (e.g. ContactID), or an abbreviated 'ID'. // These calls are the sameContact/Contact.ashx/Save?ContactID=128&Status=LivingContact/Contact.ashx/Save?ID=128&Status=LivingSaving parent and child recordsWhen saving a record, QBO3 supports a syntax for saving parent and child records. Assume we've create a Process bound to an Organization:
// Save a task, updating the Process' statusDecision/ImportForm.ashx/Save?ID=567&ActualCompletion=2/1/2017&Process_Status=Under ReviewSaving records with a SubscriberIDSometimes, 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-00330000000xEftMGHIn 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 AutoBindThere are several use cases where a QBO3 client may choose to consider a compound key unique. For example:
In such cases, one may create an AutoBind statement that queries the database to retrieve a matching ID. Autobind works as follows:
Thus, if we implement Contact/AutoBindByNameAndSSN: // This will create a contact recordContact/Contact.ashx/Save?FirstName=John&LastName=Doe&USSSN=123456789// This will update the previously created Doe+123456789 recordContact/Contact.ashx/Save?LastName=Doe&USSSN=123456789&Status=DeceasedCustom savesIf 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:
(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 = @StateEND 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 recordContact/Contact.ashx/SaveAndReview?FirstName=John&LastName=Doe&State=CA// This will update the existing John Doe in CA record; no extra task createdContact/Contact.ashx/SaveAndReview?FirstName=John&LastName=Doe&State=CA&Status=DeceasedSaving data via ExcelSaving 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
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:
With a method signature, you only need the class name:
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:
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 XMLSaving data via XML offers several advantages:
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:
Saving data via text filesIf 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 monitoringBeing 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:
|