Upload Matters

Sample Data Uploader Script for Matters

Data Uploader uses a SQL file to read data from your billing system and create and update workspaces. In order for Data Uploader to make sense of this data, you must edit the SQL file so that the client and matter information is mapped to fields recognized by CAM. Once that has been completed, you can run the Data Uploader, which performs the following actions:

The query contained in the SQL file is executed on the database specified in your Data Uploader connection string.
The Data Uploader compares the result set with the data in the configured external system
If a client/ matter number in your billing database does not exist in the configured external system, Data Uploader adds a Create Workspace job to CAM's Job queue.
If a client matter number in your billing database matches that of an existing workspace, Data Uploader compares the metadata obtained from the billing system with that of the workspace; if any discrepancies are found, Data Uploader adds a Modify Workspace job to CAM's Job queue.
CAM then processes the jobs placed in the queue and applies the changes to the configured external system.

Sample Script #1

SELECT top 500 --change this to limit the records displayed
left(m.mmatter,6) AS ClientID,left(m.mmatter,6) AS ClientNumber,
Right(m.mmatter,4) AS MatterID,
Right(m.mmatter,4) AS MatterNumber,
c.clname1 AS ClientName,
m.mdesc1 AS MatterName,
m.mstatus AS Custom6, --MatterStatus field
m.mopendt AS Custom21, --OpenDate 
m.mclosedt AS Custom22, --CloseDate
m.mloc AS Custom3, --Office or relabel column as Office
m.mprac AS Practice, --Practice
LEFT(m.mdept,4) + RIGHT(m.mdept,2) AS Custom4, --AreaLaw
msupaty AS ResponsibleLawyer,
isnull(msupaty, usb.ufullname) AS BillingLawyer,
uso.ufullname AS OriginatingLawyer,
uss.ufullname AS SupervisingLawyer,
m.mdesc1 AS WorkspaceName,
m.mdesc1 AS Description,
CASE WHEN m.mdept = '254' THEN 'template-100' WHEN m.mdept = '325' THEN 'template-1' ELSE 'template-5' END AS Template,
'30' AS DefaultSecurity --public default security 
FROM matter m 
LEFT JOIN client c ON c.clnum = left(m.mmatter,6)
LEFT JOIN uaccess uab ON m.mbillaty = uab.wvtkinit 
LEFT JOIN uaccess uao ON m.morgaty = uao.wvtkinit 
LEFT JOIN uaccess uas ON m.msupaty = uas.wvtkinit 
LEFT JOIN usmaster usb ON uab.uname = usb.userid 
LEFT JOIN usmaster uso ON uao.uname = uso.userid 
LEFT JOIN usmaster uss ON uas.uname = uss.userid
WHERE ISNumeric(left(mmatter,6)) = 1 --and ISNumeric(right(mmatter,4)) = 1 AND CHARINDEX('-',reverse(mmatter)) = 5 AND m.mmoddate >= '{0}'

Sample Script #2:

select prj_name as name,
prj_descript as [Description],
'Litigation' as Template,
'Active' as [Database],
'iManageProd' as [Server],
'iManAdmin' as Owner,
c.custom_alias as ClientID,
m.custom_alias as MatterId,
1 as CreateinImanage,
1 as CreateinCAM
FROM MHGROUP.Projects p WITH (NOLOCK)
JOIN MHGROUP.Custom1 m WITH (NOLOCK) on p.custom1=m.custom_alias
JOIN MHGROUP.Custom2 c WITH (NOLOCK) on p.custom2=c.custom_alias
where p. EditWhen> DATEADD ( dd, -1, GETDATE ( ) ) order by p.editwhen desc