Background
Getting access to all data related to a single object in QBO 3 is easy: just call the Summary() method (e.g. Loan.ashx/Summary?ID=1). The Summary method will return an XmlReader over all the data related to the object in question, including ancestors, descendants, and foreign key objects. To build this result, there are a few components:
Summary method: called ObjectData with the default parameters defined in the Summary statement
Summary statement: this contains 'default' parameters to be used by the Summary method
ObjectData method: this takes the output of ObjectTree, and builds a SQL statement concatenating the SelectXml statements for every related object
ObjectTree statement: this uses a Common Table Expression to generate a list of rows that are a pointer to each record related to a target object
Tailoring Summary Results
It is possible for the Summary statement to return too much information. By default, all ancestors and children are included in Summary results. If an object has hundreds or thousands of children, and those children are rarely needed in the Summary statement, they can be excluded. To tailor summary results, create a Summary Statement from Design > Configuration > Installed Modules > {Module} > Statements, and add one or more of the following parameters:
AncestorDepth (int): determines how many generations of ancestors will be returned. Enter 0 to return no ancestors, 1 to return the immediate parent, etc.
DescendantDepth (int): determines how many generations of descendants will be returned. Enter 0 for no descendants, 1 for children, 2 for grandchildren, etc.
Ancestors (string): a space-delimited string of ancestors to be included (if NULL, all ancestors will be included)
Descendants (string): a space-delimited string of descendants to be included (if NULL, all descendants will be included)
ForeignKeys (string): a space-delimited string of foreign key columns to include in the output (if NULL, no foreign keys will be included)
MaxCount (int): determines the maximum number of child records of a given table and generation to include
Note: when specifying the Ancestors and Descendants parameters, ensure you prefix and suffix the value with a space. E.g:
"Decision DecisionStep Message Process Ledger" will ignore Decision and Ledger because they don't have a space on both sides
" Decision DecisionStep Message Process Ledger " will work as intended
Examples:
Loan.ashx/Summary?ID=1&AncestorDepth=0 // this will not include Property
Loan.ashx/Summary?ID=1&DescendantDepth=1 // this will include children but not grand children (e.g. Decision but not DecisionStep)
Loan.ashx/Summary?ID=1&Descendants= Decision DecisionStep Process Ledger LedgerItem // this would exclude Attachments and Messages
SummaryFlat
QBO data is stored in may tables. The AbstractObject/Summary() method returns a record, it's parents, siblings and children as multiple data tables, translated to XML. A rich DataSet or XML structure such is not usable by some third party software such as MS Word and Adobe Acrobat. For such products, AbstractObject/SummaryFlat will produce a single DataTable concatenating multiple DataTables together.
For example, assume we have the following structure:
Property
Loan
Borrowers
Valuation
ImportForm
Comparables
Messages
Attachments
Decisions
Assume that we wish to include Property, Loan, Borrower, Valuation, Comparable and ImportForm data in the PDF. We can 'shape' the data by including parameters matching the data structure relationships between the tables:
ImportForm/SummaryFlat?Valuation_=
&Valuation_Loan_=
&Valuation_Property_=
&Valuation_Comparables_=6
will create a single table structured as follows:
ImportFormID
ImportForm
... all other ImportForm columns ...
... all ImportForm.XmlData elements ...
Valuation_ValuationID
Valuation_Valuation
... all other Valuation columns ...
Valuation_Loan_LoanID
Valuation_Loan_Loan
... all other Loan columns ...
Valuation_Property_PropertyID
Valuation_Property_Property
... all other Property columns ...
Valuation_Comparable_0_ValuationComparableID
Valuation_Comparable_0_ValuationComparable
... all other ValuationComparable columns for the first ValuationComparable record
Valuation_Comparable_1_ValuationComparableID
Valuation_Comparable_1_ValuationComparable
... all other ValuationComparable columns for the second ValuationComparable record
... and so on, until we reach the 6th one (because we passed Valuation_Comparables=6)
Valuation_Comparable_5_ValuationComparableID
Valuation_Comparable_5_ValuationComparable
... all other ValuationComparable columns for the sixth ValuationComparable record
This is a very 'wide' table, but generally speaking, Word and Acrobat can handle it.
Choosing what relationship to include requires some thought. Once you've 'shaped' the data by determining the parameters you want, you can create a reusable Statement that stores these parameters in a configuration entry. For example, assume a Valuations system might create an ImportForm statement called 'ValuationStandardOutput', with the BaseOperation set to 'SummaryFlat', and the parameters defined above. Once done, multiple Import Form Templates, Attachment Templates, and such can reuse the 'ValuationStandardOutput' statement.
Example
A mortgage servicing client makes heavy use of the ImportFileQueue table to store 'ad-hoc' data. The ImportFileQueue rows are usually bound to a Decision. To make a SummaryFlat statement for their loan table, the following steps were taken:
A 'Workflows' child class was added to the Loan.config file
An 'AdHocData' child class was added to the Decision.config file
A new 'AdHocSummary' statement was created under the Loan module as follows:
BaseOperation: 'SummaryFlat'
Parameters:
Workflows_
Workflows_AdHocData_
Performance Optimization
The Summary statement is a powerful tool that attempts to include all parents and descendants associated with a record, vastly simplifying power user configuration. However, if a given record has thousands of children or more, this can also cause performance problems. To limit the number of descendants associated with a record, consider:
Effective purge policies: delete 'transactional' data like ImportFileQueue and SmartWorklistMember after a reasonable period of time (1-3 months)
This is done from Design > Configuration > Data Tuning
Tailored repeatability: for templated items, consider one-per-parent instead of many-per-parent when you can
Set 'SelectGenericEntityColumns' to false: this will omit ParentLabel, GrandParent, and GrandParentID from {Select.ForeignKey} and {From.ForeignKey}, but is much faster
This is done from Design > Configuration > Modules > Matrix > Settings tab
MaxCount: the most recent items are usually the most is relevant; setting ObjectTree's MaxCount parameter to 100 or so will limit descendant data to the 100 most recent rows per table
e.g. a Loan/Summary?ID=X&MaxCount=100 limits the number of Attachments to 100, ImportForms to 100, ImportFileQueues to 100, etc.
to apply to 'under the hood' operations, this should be done from Design > Configuration > Modules > {Module} > Statements > Summary statement parameters