BackgroundAbstractObject serves as the base class for all table-driven QBO3 classes. It implements the following key concepts:
Each AbstractObject-derived class has a configuration file associated with it, controlled by qbo.Application.Configuration.ObjectConfiguration.cs. These configuration files may contain the following sections:
Status and TypesEach QBO table includes a Status and {Table}Type column. The values for these columns are defined by power users from Design > Configuration > {Module}, via the Status or Types tabs. Technically, each possible defined Status is stored in the ObjectStatus table, and each possible defined Type is defined in the ObjectType table. A standard UI pattern is to render a
select list from the ObjectStatus or ObjectType table.It is also a common practice (but certainly not required) for a business unit to define 'Active' records as that are in a list of status, or not in a list of statuses. For example:
There may be many queries that need to re-use this concept of 'active' records. The ObjectStatus table include a column called 'FinalState', enabling a more generic query:
This allows use of a generic 'Active' filter by many queries, so that if the business unit adds new Status options, they can choose whether records in that Status are considered part of the active filter or not.
Saving DataSaving data for a single row in a table is simple. Assume we're dealing with our Contact table, and wish to change the Address and Postal Code for ContactID 12:
Contact.ashx/Save?ID=12&Address=123 New Address Street&PostalCode=90210With the same Save method, you can also save data in related tables. Assume the Contact's parent is a Bankruptcy, and we wish to update the Bankruptcy.Status column to be 'Debtor Deceased'. By using a {Parent}_{Column} pattern, you can update the parent table's data in the same call:
Contact.ashx/Save?ID=12&Address=123 New Address Street&PostalCode=90210&Bankruptcy_Status=Debtor DeceasedChild records may also be updated using a {Child}_{Index}_{Column} pattern:
Contact.ashx/Save?ID=12&Address=123 New Address Street&Messages_0_Subject=Mailing address updated based on email from borrower.For child records that are templated, you may use the template name in lieu of an index. Assume we have created a Message Template called "Current Address":
Contact.ashx/Save?ID=12&Address=123 New Address Street&Messages_CurrentAddress_Subject=Mailing address updated based on email from borower.State TransitionsFor clients with advanced requirements surrounding Status, QBO supports a 'state diagram' by creating rows in the ObjectState table. This allows power users to define allowed 'transitions', and this is enforced in the data tier by triggers. For example, the Loan table may allow:
By defining these transitions in the ObjectState table, if an attempt is made to change a Loan.Status from 'Current' to 'REO', the database will throw an error, ensuing that you cannot 'skip' a loan from an 'early' status like Current to an end status like 'REO'
Furthermore, if a state transition is marked as a Final State, the status may not be updated once set. This prevents 'inactive' records from being made active again.
If there are no ObjectState rows for a particular table, no status enforcement is done by the data tier transition trigger.
Standard MethodsTypically, an ID property is used to uniquely identify a record in QBO. For example, if you wish to modify a Process where ProcessID = 17, you can call:
Process/Save?ID=17&Status=In ProgressBy default, we assume that no compound keys constitute a unique key.
However, there are use cases where another field, or set of other fields, constitute a compound key sufficient to uniquely identify a record. In these cases, an AutoBind statement may be configured that will be invoked when setting properties to identify an ID.
For a Bankruptcy, the combination of a Court and CaseNumber is sufficiently to uniquely identify a Bankruptcy case. Thus, given a BankruptcyID = 22, CourtID = 1, and BKCaseNumber = 12345:
Bankruptcy/Save?CourtID=1&BKCaseNumber=12345In this case, the AutoBind statement would be:
SELECT BankruptcyID FROM Bankruptcy WHERE CourtID = @CourtID AND BKCaseNumber = @BKCaseNumberFor QBO mortgage installations, a question that frequently arises is whether or not a loan number is unique. Most small banks immediately claim that their loan numbers are unique. However, QBO does not assume this out of the box (we've worked with too many banks that purchased another portfolio, at which point the unique key becomes Portfolio + Loan Number).
In this case, the AutoBind statement would be:
SELECT LoanID FROM Loan WHERE Loan = @LoanFor a slight variation on this theme, the AgencyID and AgencyLoanID fields are a compound key (Agency represents the GSE: Freddie Mac or Fannie Mae). AutoBind for this example would be:
SELECT LoanID FROM Loan WHERE AgencyID = @AgencyID AND AgencyLoanID = @AgencyLoanIDor a slightly more user-friendly version:
SELECT LoanID, Agency.OrganizationID AS AgencyIDFROM Organization AS Agency INNER JOIN Loan ON Loan.AgencyLoanID = @AgencyLoanID AND Loan.AgencyID = Agency.OrganizationIDWHERE Agency.Organization = @AgencyFor both examples, an AutoBind statement may be created.
Pivot
The Pivot method will take the SQL CUBE output, and create a pivot table around any row/column combination desired. This is useful when the number of columns is not known when designing a query. Examples:
// This will render Decision/Dashboard with CreatedDate as rows, DecisionTemplate as columns, and DecisionCount as the fact values for each 'cell'.Decision.ashx/Pivot?Dimension=CreatedDate,DecisionTemplate&CreatedDateStart=1/1/2014// This will render Decision/MyCustomCube?MyParam=A&MyOtherParam=B with DecisionTemplate as rows, Status as columns, DecisionCount,MyCustomCal as fact values for each 'cell'.Decision.ashx/Pivot?From=MyCustomCube&Row=DecisionTemplate&Column=Status&Facts=DecisionCount,MyCustomCalc&MyParam=A&MyOtherParam=BParameters to the Pivot statement include:
To render pivot results in a 'generic' HTML table, you can use the Standard.xslt transform:
Process/Process.ashx/Pivot?Dimension=AssignedPersonID,ProcessTemplateID&CreatedDateStart=1/1/2013&Column=ProcessTemplate&Row=AssignedPerson&Transform=Templates/Report/Standard.xsltThis outputs the Pivot results in an HTML table, but without any CSS or Javascript applied. To render a pivot table in a 'nice' UI, you can use the Theme.ashx/Wrap endpoint: Theme.ashx/Wrap?ClassName=Process&Method=Pivot&Dimension=AssignedPersonID,ProcessTemplateID&CreatedDateStart=1/1/2013&Column=ProcessTemplate&Row=AssignedPerson&Transform=Templates/Report/Standard.xsltEvaluateThe AbstractObject/Evaluate method attempts to create a child object based on the output of a Matrix. Use cases include:
The Matrix may access any data point associated with the object being evaluated. For example, to launch a workflow based on the characteristics of a Loan, one could:
|