QBO3: Comparing workflows from multiple vendors

Question

Question: How can QBO3's Import File Mapping module be used to import multiple copies of the same task.

Background

We have a use case where multiple vendors (2-3) are simultaneously performing a workflow for us, and we wish to reconcile when vendors report being at different stages of the workflow. For example, we are an investor monitoring the status of a foreclosure where:

• An attorney is executing the foreclosure (doing the work), reporting to us
• A servicer is tracking the attorney's progress and reporting it to us, and
• We subscribe to a data feed from the court containing each foreclosure docket

Our goal is to report on cases where the "current workflow step" differs between the servicer, attorney or court.

To accomplish this, we've set up:

• Each of the tasks to be tracked
• A Servicer workflow, containing each of these tasks
• An Attorney workflow, containing the same tasks
• A Court workflow, containing the same tasks

Challenge

We receive customized Excel spreadsheet from the servicer and attorney. The servicer spreadsheet contains columns that should map to tasks:

• LoanNumber
• ForeclosureReferral
• NoticeOfDefault
• NoticeOfSale
• SaleHeld

Our challenge is that there are 3 copies of each of these tasks:

• One bound to the servicer workflow
• One bound to the attorney workflow, and
• One bound to the court workflow

Answer 1

Normally, the combination of a loan number and a task name is enough to identify a unique task in QBO3. This is not true for this use case.

However, knowing the source of the data can help uniquely identify the task record:

Loan + ImportType (Legal | Servicer | Court) + Task

Create a stataement: ImportForm/AutoBindMonitoring, and require parameters Loan, ImportType, ImportForm

SELECT ImportForm.ImportFormID
FROM Loan
  INNER JOIN Process
    ON Process.ObjectID = Loan.LoanID 
      AND Process.Object = 'Loan'
      AND Process.Sibling = 'FCMonitoring'
      AND Process.DateClosed IS NULL
  INNER JOIN Decision 
    ON Decision.ObjectID = Process.SiblingID
      AND Decision.Object = Process.Sibling
      AND Decision.Decision LIKE @ImportType + '%'
  INNER JOIN DecisionStep
    ON DecisionStep.DecisionID = Decision.DecisionID
  INNER JOIN ImportForm
    ON ImportForm.DecisionStepID = DecisionStep.DecisionStepID
      AND ImportForm.ImportForm = @ImportForm
WHERE 
  Loan.Loan = @Loan

So calling:

ImportForm/Save?Loan=X&ImportType=Servicer&ImportForm=Foreclosure Referral&ActualCompletion=Y

would find the matching ImportFormID, and proceed from there.

Assuming that you have a column called 'ForeclosureReferral' in your spreadsheet, your mappings would need to include:

• ForeclosureReferral -> Tasks_0_ActualCompletion
• "Foreclosure Referral" -> Tasks_0_ImportForm
• "Servicer" -> Tasks_0_ImportType
• Loan -> Tasks_0_Loan

That is, for each task date column, you would need 4 mapping rows, three of which are just "hard coded" to give you the parameters required for AutobindMonitoring.