Working with Data

QBO 3 is designed to be very friendly to web developers.  Basic operations are very straight forward. 

Contact Table Examples

To insert a row into the contact table:

/qbo3/Contact/Contact.ashx/Insert?FirstName=Bob&LastName=Smith

Some items to note:
  • The above URL is relative; it assumes that QBO 3 components have been deployed to a folder called qbo3 from the root of the website
  • The server will return an XML node containing serialized Contact record; this will include a newly created ContactID
  • Any Contact columns may be passed on the query string; in this example, only FirstName and LastName are supplied
  • Contact.Contact may not be null, and the Contact.Fields.cs file is programmed to set Contact = {LastName}, {FirstName} if not otherwise specified
To select a row from the contact table

/qbo3/Contact/Contact.ashx/Select?ContactID=1, or
/qbo3/Contact/Contact.ashx/Select?ID=1

Some items to note:
  • Every QBO table includes an primary key identity column named {Table}ID; e.g. ContactID, LoanID, ValuationID, etc.
  • For most operations requiring a primary key, you may specify the actual column name, or just ID
  • The server will return an XML node containing serialized Contact record; it does not return the raw data table directly
To update a row in the contact table

/qbo3/Contact/Contact.ashx/Update?ID=1&MiddleName=Fred, or
/qbo3/Contact/Contact.ashx/Update?ID=1&MiddleName=Fred&Phone=800.5551212

Some items to note:
  • These example use ID instead of ContactID; either is allowed
  • Phone is a column in the Contact table,
  • The Contact class is smart enough to realize Phone is a ContactMethod, and 
    • if there is an exsiting Phone number, it will be updated, otherwise
    • a new ContactMethod for the phone number will be inserted
To list a bunch of records from the Contact table

There are a lot of options here, including:
  • List all Contacts with the last name of "Smith": 
    /qbo3/Contact/Contact.ashx/Search?LastName=Smith
  • List all Contacts with a last name starting with "Smith":
    /qbo3/Contact/Contact.ashx/Search?LastName~=Smith
  • List all Contacts with a last name of "Smith" but a first name that is not "Fred":
    /qbo3/Contact/Contact.ashx/Search?LastName=Smith&FirstName!=Fred
  • List all Contacts with a last name of "Smith" but a first name that is not like "Fred":
    /qbo3/Contact/Contact.ashx/Search?LastName=Smith&FirstName^=Fred
Special operators in the query string include:
  • ~=: uses a SQL "LIKE"
  • ^=: uses a SQL "NOT LIKE"
  • !=: uses a SQL != (ha!)
To delete a bunch of records from the Contact table

/qbo3/Contact/Contact.ashx/Delete?ID=1&ID=2&ID=15, or
/qbo3/Contact/Contact.ashx/Delete?ID=1,2,15

Some items to note:
  • A query string like ID=1&ID=2 is easily generated by providing a web form with a bunch of check boxes, and having a user check the relevant boxes
  • As with all functionality, the user invoking the request must have permission to execute the operation; this is controlled from the Contact.config file
  • You will see a recurring pattern of "work with a bunch of IDs" in the examples to come; the DbStatements that contains {Where.IdList} work this way
To mark a bunch of Contacts with a Status of 'Delinquent'

/qbo3/Contact/Contact.ashx/BulkUpdate?Status=Delinquent&ID=1,2,15

Valuation Examples

Unlike Contacts, most tables have a variety of foreign keys. Valuation is an excellent example; it includes:
  • PropertyID: foreign key to a Property record
  • ClientID: foreign key to an Organization record
  • BrokerID: foreign key to a Broker record
When inserting or updating data in QBO 3, foreign key data may be passed with a prefix matching the foreign key column, sans the 'ID'. For example:

/qbo3/Mortgage/Valuation.ashx/Insert?Valuation=Test 123&Property_Address=234 Center Street&Property_State=CA




Comments