Patterns - quandis/qbo3-Documentation GitHub Wiki

QBO includes patterns across many of our modules, including:

First, some terminology:

QBO's API rigorously follows naming patterns. However, due to popular demand, there are a handful of exceptions within the UI (and documentation):

Core QBO classes such as Attachment, Contact, Decision, Ledger, Message, etc., can be bound to any parent object using 'soft' keys comprising a pair of attributes: Object and ObjectID.

For example:

// Bind a message to Contact 1
Message/Save?Subject=Foo&Object=Contact&ObjectID=1 

// Bind a message to Ledger 17
Message/Save?Subject=Bar&Object=Ledger&ObjectID=17

// Bind a message to Attachment 22
Message/Save?Subject=Bar&Object=Attachment&ObjectID=22

For the SQL geeks:

-- See all messages associated with ContactID 1
SELECT * FROM Message WHERE Object = 'Contact' AND ObjectID = 1

-- See all messages associated with Attachment 22
SELECT * FROM Message WHERE Object = 'Attachment' AND ObjectID = 22

QBO usage of the Object / ObjectID attributes is pervasive.

Templates are used to drive the behavior of instances, with a template row paired with instance row.

The ITemplate interface provides functionality for templated classes, such as ProcessTemplate, ImportFormTemplate, AttachmentTemplate, and DecisionTemplate. Each of these classes determines the behavior of an instance class (Process, ImportForm, Attachment, Decision, etc.).

All template classes implement the ITemplate interface, defining:

The ITemplate.Repeatable attribute defines how often an instance may occur per parent:

• Once: only one instance per parent is allowed
• One active: only one active instance per parent is allowed
• Many: may instances per parent are allowed|

When the GenericObject.SetProperties sees parameters including Object, ObjectID and Template, a SelectByTemplate statement is called, and these statements are designed to pay attention to the template table's Repeatable column and the instance table's 'active' column(s). For instance, ImportForm/SelectByTemplate works as follows:

• ImportFormTemplate.Repeatable = 0: if an ImportForm row based on the template exists, it is selected ("reused")
• ImportFormTemplate.Repeatable = 1: if an ImportForm row based on the template and with no ActualCompletion date exists, it is selected ("reused")
• ImportFormTemplate.Repeatable = 2: no existing ImportForm row will be selected, effectively creating a new row

The QBO UI leverages API calls using a standard Javascript library. For any given module, we typically implement the following UI components:

When looking at a Summary page, the source of the data display is generally obvious. For example, when viewing Contact/Summary?ID=1:

• The data at the top of the page is coming from the Contact table: FirstName, LastName, etc.
• The data in each of the generic panels at the bottom of the page are straight forward:
  • The Messages panel is displaying rows from the Message table that apply to ContactID=1
  • The Documents panel is displaying rows from the Attachment table that apply to ContactID=1
  • The Workflow panel is displaying rows from the Decision table that apply to ContactID=1

Other modules may have more complex relationships. For example, Loan/Summary?ID=1:

• The data at the top of the page is primarily coming from the Loan table
  • The Address data is coming from the Property table
• If the record has a parent object, QBO will display a tab for that parent
  • E.g. Loan has a parent Property, so Loan/Summary will include a Property tab display data from the Property table
• The data in each of the generic panels at the bottom of the page are straight forward (see above).

If you're curious about where a particular data point comes from, leverage QBO's API:

• Loan/Summary?ID=1 will render the standard UI, but
• Loan/Summary?ID=1&Output=Xml will render all the data associated with LoanID=1
  • Output=Json if you prefer JSON

In the sample XML snippet below, you can see that data is available from the Borrower, Loan, and Property tables.

<LoanCollection>
  <BorrowerItem>
    <BorrowerID>43733</BorrowerID>
    <Borrower>Bobby Bankrupt</Borrower>
    <LoanID>401504</LoanID>
    <ContactID>108183</ContactID>
    <Contact>Bobby Bankrupt</Contact>
    <FirstName>Bobby</FirstName>
    <LastName>Bankrupt</LastName>
  </BorrowerItem>
  <LoanItem>
    <LoanID>1</LoanID>
    <Loan>123456789</Loan>
    <PropertyID>170283</PropertyID>
    <Address>30 Woodlawn Avenue</Address>
    <City>Waltham</City>
    <State>MA</State>
    <PostalCode>02451</PostalCode>
    <Property>30 Woodlawn Avenue, Waltham, MA 02451</Property>
  </LoanItem>
  <PropertyItem>
    <PropertyID>170283</PropertyID>
    <Property>30 Woodlawn Avenue, Waltham, MA 02451</Property>
    <Address>30 Woodlawn Avenue</Address>
    <City>Waltham</City>
    <State>MA</State>
    <PostalCode>02451</PostalCode>
    <Latitude>42.386653</Latitude>
    <Longitude>-71.248708</Longitude>
  </PropertyItem>
</LoanCollection>

QBO implements a normalized inheritance pattern in the data tier.

The Contact table provides for name, address and latitude/longitude information. Rather than replicate these fields across multiple table and modules, the following tables 'inherit' from Contact by including a ContactID column:

SELECT * 
FROM Organization 
  INNER JOIN Contact ON Contact.ContactID = Organization.ContactID

The Process table backing the Process module provides for open and closed dates, outsourcing to external vendors, tracking workflows and accounting. Many tables in QBO inherit from Process.

For the data geeks, some sample queries help convey these relationships:

-- See all Bankruptcies that have no ClosedDate
SELECT * 
FROM Bankruptcy
  INNER JOIN Process ON Process.ProcessID = Bankruptcy.ProcessID
WHERE Process.ClosedDate IS NULL

-- See all Closings with Status = 'Active' in California
SELECT * 
FROM Closing
  INNER JOIN Process ON Process.ProcessID = Closing.ProcessID
  INNER JOIN Property ON Property.PropertyID = Closing.PropertyID
WHERE Process.Status = 'Active'
  AND Property.State = 'CA'

-- See all processes outsourced to Acme Lawfirm; this would include Bankruptcy, Foreclosure, Eviction, and other such rows.
SELECT *
FROM Process
  INNER JOIN Organization ON Organization.OrganizationID = Process.AssignedOrganizationID
WHERE Organization.Organization = 'Acme Lawfirm'

QBO data tables include a trigger to populate a corresponding {Table}History with any changes made to the table. For example:

This pattern includes:

• A column in the history table matching each table column except for Created* and Updated*
• A RevisionDate column timestamped by the trigger to indicate the date the change was made
• A RevisionPersonID column stamped by the trigger indicating the user causing the change
  • this requires that the UserID be available via SET CONTEXT; see standard UPDATE statements

History tables provide an audit trail of changes, and are not intended for OLTP queries. They are useful for manual review on a case-by-case basis.

History tables should be purged frequently; 3 months of history is generally recommended but may be modified according to a client's data retention requirements.