import - QBO3: Comparing workflows from multiple vendors -

question: how can qbo3's import file mapping module used import multiple copies of same task.

background

we have use case multiple vendors (2-3) simultaneously performing workflow us, , wish reconcile when vendors report being @ different stages of workflow. example, investor monitoring status of foreclosure where:

• an attorney executing foreclosure (doing work), reporting us
• a servicer tracking attorney's progress , reporting us, and
• we subscribe data feed court containing each foreclosure docket

our goal report on cases "current workflow step" differs between servicer, attorney or court.

to accomplish this, we've set up:

• each of tasks tracked
• a servicer workflow, containing each of these tasks
• an attorney workflow, containing same tasks
• a court workflow, containing same tasks

challenge

we receive customized excel spreadsheet servicer , attorney. servicer spreadsheet contains columns should map tasks:

• loannumber
• foreclosurereferral
• noticeofdefault
• noticeofsale
• saleheld

our challenge there 3 copies of each of these tasks:

• one bound servicer workflow
• one bound attorney workflow, and
• one bound court workflow

normally, combination of loan number , task name enough identify unique task in qbo3. not true use case.

however, knowing source of data can uniquely identify task record:

loan + importtype (legal | servicer | court) + task

create stataement: importform/autobindmonitoring, , require parameters loan, importtype, importform

select importform.importformid loan   inner join process     on process.objectid = loan.loanid        , process.object = 'loan'       , process.sibling = 'fcmonitoring'       , process.dateclosed null   inner join decision      on decision.objectid = process.siblingid       , decision.object = process.sibling       , decision.decision @importtype + '%'   inner join decisionstep     on decisionstep.decisionid = decision.decisionid   inner join importform     on importform.decisionstepid = decisionstep.decisionstepid       , importform.importform = @importform    loan.loan = @loan 

so calling:

importform/save?loan=x&importtype=servicer&importform=foreclosure referral&actualcompletion=y 

would find matching importformid, , proceed there.

assuming have column called 'foreclosurereferral' in spreadsheet, mappings need include:

• foreclosurereferral -> tasks_0_actualcompletion
• "foreclosure referral" -> tasks_0_importform
• "servicer" -> tasks_0_importtype
• loan -> tasks_0_loan

that is, each task date column, need 4 mapping rows, 3 of "hard coded" give parameters required autobindmonitoring.