Use Cases

Subscribe to posts

ComputerShare Global Viewpoint Document Imaging

posted Dec 3, 2015, 7:18 AM by Eric Patrick   [ updated Dec 8, 2015, 8:58 AM ]

Overview

ComputerShare offers a document imaging system called Global Viewpoint ("GV"). Quandis offers an IFileObject-based plugin that wraps GV so documents can be listed and read, as well as an IService-based plugin to wraps GV so documents can be 'synched'.

IFileObject Usage


GV does not mimic "file paths", so the IFileObject API implementation is a bit out of the ordinary.  Key GV concepts to understand include:
  • Each document has a UniqueId, which is mapped by QBO to Attachment.PathURL
    • e.g. instead of "Loan/12345/Loan Application.pdf", the PathURL will be something like "3b13c81093594c03a2151b6fa5986e59"
  • The IFileObject/List method ignores the path parameter, but maps the pattern parameter to various GV search criteria
    • e.g. typical one would call List("Loan/12345/", "*.*", 0) to search for all documents in the folder Loan/12345
    • e.g. for GV, we call List(null, "Loan=12345", 0) to search for all documents associated with Loan 12345
    • e.g. for GV, we call List(null, "Loan=12345&Classification=VAL003", 0) to search for all valuations for Loan 12345
      • the possible GV classification codes depend on the GV installation, and are beyond the scope of this post. Contact GV support for your classification codes
    • e.g. for GV, we call List(null, "Loan=12345&UploadDate=1/1/2015", 0) to limit documents returned to those uploaded after 1/1/2015.
    • supported GV search criteria include: Loan, Classification, UploadDate. Other criteria can be added by extending the GlobalViewpointFile/List method.

IService Usage

GV clients have a use case that involves 'copying' a subset of GV documents for a given loan to the QBO Attachment table. The subset of loans is determined by GV's Classification code.  The IService plugin does two things:
  • Checks the Attachment table to determine the most recently copied GV file, and
  • Calls IFileObject/List to retrieve all matching documents uploaded after the last sync
Example method signatures:
  • Sync GV valuation documents for a given loan: SLSLoan/GVSync?SLSLoan=12345&Classification=VAL003
  • Sync GV documents added since 1/1/2015: SLSLoan/GVSync?SLSLoan=12345&UploadDate=1/1/2015
  • Sync GV VAL and CORE documents and uploaded since 1/1/2016: SLSLoan/GVSync?SLSLoan=12345&Classification=VAL003,COR023&UploadDate=1/1/2016

Project Management: Time tracking

posted May 29, 2014, 6:06 AM by Eric Patrick   [ updated May 29, 2014, 11:16 AM ]

Background

Quandis has a prospective client that wishes to replace their home-grown time tracking software, and eventually grow it into a CRM system. The basic requirements include:
  • Create and maintain a "Project", which is a consulting engagement with a client
  • Within a "Project", pick from a standard set of "Tasks" which tasks will be involved in the project
    • each task may be assigned to a consultant, and should track both budgeted and actual hours spent on the task
  • Within each task, allow consultants to enter the time they've spent working on the task, including a description of what they did
  • Show a rollup of all time spent on a project
  • Show dashboard pivot tables breaking down time by task, project, or consultant

Data Analysis

The first step in implementing these requirements were to translate the client's terminology into QBO terminology, and determine if we needed any new data structures. 
  • Client => Organization: since a client is a company, with contacts and and address, an obvious fit
  • Consultant => Person: consultants are the users in the system
  • Project => Process: since a project is a consulting engagement, it should have a start and end date, and may be assigned to a manager.  Process is a great fit.
    • "Client" => Organization: because a project is a consulting engagement for a client, we'll make the Process and child of the Organization table (Process.Object/ID = Organization)
    • "Manager" => Process.AssignedOrganizationID
    • "Budget" => Process.CostLedgerID, since the CostLedger is used to track financial data related to a Process
  • Task => ImportForm: because ImportForm are tasks that can have custom fields added to them
    • "Consultant" => ImportForm.AssignedPersonID
    • "Budgeted Hours" => ImportForm.ProjectedWork
    • "Actual Hours" => ImportForm.ActualWork
  • Task Template => ImportFormTemplate: the 'standard' set of tasks available are ImportFormTemplates
  • Time => LedgerItem: we're going to bill somebody for this time, so LedgerItem was a good fit
    • "Hours" => LedgerItem.UnitCount
    • "Amount" => LedgerItem.AmountMoney
    • "Consultant" => LedgerItem.PayorID
    • "Task" => LedgerItem.Object/ID
    • "Project" = > LedgerItem.LedgerID == Process.CostLedgerID

User Interface

Single page apps are a bit of a rage these day, and QBO configuration can be pretty daunting. This project was implement as a single page app to simplify configuration / training, and to help determine how difficult it is to build a one-page app in QBO3.

To that end, the following patterns were established:
  • The URL for the 'home page' would be /Process/Process.ashx/Projects
  • Clicking on a project from the home page opens a new tab for that project, rather than navigating away from the home page
  • The Project Summary page would include:
    • a Project panel
    • a Tasks and All Timesheet Entries panel
    • a standard Messages/Documents/Contacts panel
  • All power-user configuration would be handled in 1 page, appearing as a configuration ('gear') icon from the home page
The customization of QBO3 to completed this project entailed no new code, but several XSLTs and custom statements. Thus, a theme (Empty C# ASP.NET Web Project in Visual Studio) was created, with the following structure:
  • Config (folder)
    • Setup.ProjectManagement.xml: contains the seed data required for this project, including custom statements as ConfigurationEntry nodes
  • Templates
    • Accounting
      • Project.Timesheet.xslt: customized LedgerItem.Search.xslt that show timesheet entries in the Project Summary view
      • Project.Timesheet.Edit.xslt: customized LedgerItem.Edit.xslt to focus on entering time spent on a task
    • Decision
      • ImportForm.TaskList.xslt: customized ImportForm.Search.xslt that includes hours
      • ImportForm.TaskSetup.xslt: customized ImportForm.Search.xslt that enables quick data entry of standard tasks in a project
      • FormTemplates:
        • Timesheet.Edit.xslt: customized layout for ImportForms that includes custom fields on the left, and timesheet info on the right
    • Process
      • Project.Home.xslt: application home page
      • Project.Config.xslt: application configuration page
      • Project.Search.xslt: customized Process.Search.xslt to display budgeted hours and such
      • Project.Summary.xslt: customized Process.Summary.xslt to render in a tab instead of a full page, with the desired panels described above
      • Project.Select.xslt: customized Process.Select.xslt to render budgeted hours as well as basic Process fcolumns.






Searching: Vendor Module Smart Search

posted Jul 6, 2012, 9:11 AM by Eric Patrick   [ updated Jul 6, 2012, 9:12 AM ]

Background

In QBO 2 vendor module , when Client (e.g. ort@quandis.com) logs in, they can search different vendors. You will see the left hand side, it has all kind of filters and logic in the searching; it is using the rBrokerSearch.sql stored procedure to get this job done.

For the QBO 3 vendor module, we want to approach the same searching (even GUI wise may even add more search filters later on). In the meantime, we still want to keep the smart search box on the top. How can I add my logic for those search filters and logic to the Smart Search? Also there are more fields need to be pulling out (e.g. data from Geography) If you look at the rBrokerSearch.sql, a lot of logic involved with Collections, Geography, etc.

Smart Search Implementation

Come up with sample real-world searches clients might do. Something like this:
  1. Show me all agents that cover Tustin, CA
  2. I need a BPO agent that covers 92610
  3. I remember working with a broker named Cassidy in CA last year; is he in the system?
  4. Look up the broker whose email address is epatrick@quandis.com
  5. Show me agents that do Appraisals or Inspections in Orange County
From a list such as this, create the likely search string that should get you the desired results (e.g. what do you expect a client to enter into the Smart Search box?).
  1. "Tustin, CA"
  2. "BPO 92610"
  3. "Cassidy CA"
  4. "epatrick@quandis.com"
  5. "Orange County, CA Appraisal Inspection"
From this, break down the tables being queried:
  1. "Tustin, CA": Geography
  2. "BPO 92610": Collection, Geography
  3. "Lai CA": Contact, Geography
  4. "epatrick@quandis.com": ContactMethod
  5. "Orange County, CA Appraisal Inspection": Geography, Collection
From this list, create a search term for each table being considered:

Contact

Searching the entire Contact table can be expensive, so the LastName term should be limited to words with more than 3 letters. 

<SearchTerm Name="LastName" Table="Contact" Column="LastName" Clause=" 
SELECT Broker.BrokerID, Term.Row, 8, 'Contact', ContactID, 'LastName', Contact.LastName 
FROM @Term AS Term  
INNER JOIN Contact WITH (NOLOCK) 
ON Contact.LastName = Term.HintValue 
INNER JOIN Broker WITH (NOLOCK)
ON Broker.ContactID = Contact.ContactID
WHERE 
ISNULL(Term.HintTerm, 'name') = 'name'
AND Term.Words = 1
AND (
Term.HintTerm = 'name'
OR LEN(Term.HintValue) &gt; 3 
)
"/>

ContactMethod

<SearchTerm Name="Email" Table="ContactMethod" Column="ContactValue" Clause=" 
SELECT Broker.BrokerID, Term.Row, 10, 'ContactMethod', ContactMethodID, 'ContactValue', ContactMethod.ContactValue 
FROM @Term AS Term  
INNER JOIN ContactMethod WITH (NOLOCK) 
ON ContactMethod.ContactValue LIKE Term.HintValue + '%' 
INNER JOIN Broker WITH (NOLOCK)
ON Broker.ContactID = ContactMethod.ContactID
WHERE ISNULL(Term.HintTerm, 'email') = 'email' 
AND CHARINDEX(Term.HintTerm, '@') > 0
AND Term.Words = 1
" />

Geography

Searching the Geography table is perhaps the most resource intensive search. As a result, we don't want to search for cities matching 'someone@company.com'. We can achieve this by pre-qualifying the search term to ensure it looks like a zip code.

<SearchTerm Name="PostalCode" Table="Geography" Column="PostalCode" Clause=" 
SELECT Geography.ObjectID, Term.Row, 3, 'Geography', GeographyID, 'PostalCode', Geography.PostalCode
FROM @Term AS Term  
CROSS JOIN dbo.GeographyList('Broker', Term.HintTerm) AS Geography
WHERE ISNULL(Term.HintTerm, 'zip') = 'zip' 
AND LEN(Term.HintTerm) = 5
AND Term.Words = 1
AND ISNUMERIC(Term.HintValue) = 1
" />

Additional terms may be added to search for City, County or State.

Collection

In the Vendor Module, Brokers can be part of a 'BPO', 'Appraisal', 'Inspection', or other product-based collections. At first glance, if a user searches for 'BPO', it may seem desirable to return all brokers that a member of the 'BPO' collection. However, in the production Vendor Module, this would return thousands of brokers, which is not a particularly useful result. If a user searches for 'BPO 92610', however, we can assume them want to view brokers that perform BPOs in the 92610 zip code. Brokers that cover 92610 would be added to the smart search result by the Geography search term described above.  Instead of 'BPO' causing additional brokers to be added, we want the term 'BPO' to simply weigh brokers who do BPOs more heavily than those who don't.

In short, we limit our results to the brokers that are already part of the result set.

<SearchTerm Name="Product" Table="CollectionMember" Column="CollectionMemberID" Clause=" 
SELECT Geography.ObjectID, Term.Row, 2, 'Geography', GeographyID, 'PostalCode', Geography.PostalCode
FROM @Term AS Term  
INNER JOIN Collection WITH (NOLOCK)
ON Collection.Collection = Term.HintTerm
AND Collection.SourceObject = 'Broker'
INNER JOIN CollectionMember WITH (NOLOCK)
ON CollectionMember.CollectionID = Collection.CollectionID
INNER JOIN @Result AS Result
ON Result.ID = CollectionMember.SourceObjectID
WHERE ISNULL(Term.HintTerm, 'product') = 'product' 
" />


1-3 of 3