Matters with No Recent Activity

This is similar to yesterday's Client post, but for matters and with a one year qualification. It identifies "Open" matters opened more than a year ago with no activity in the past year.

SELECT
mmatter
,mdesc1
,mopendt
FROM
matter
WHERE
mstatus = 'OP'
AND mopendt < DATEADD(yy,-1,GETDATE())
--No new time
AND NOT EXISTS (SELECT * FROM timecard WHERE mmatter=tmatter AND tworkdt >= DATEADD(yy,-1,GETDATE()))
--No new costs
AND NOT EXISTS (SELECT * FROM cost WHERE mmatter=cmatter AND cdisbdt >= DATEADD(yy,-1,GETDATE()))
--No new trust
AND NOT EXISTS (SELECT * FROM trsttran WHERE mmatter=trmatter AND trdate >= DATEADD(yy,-1,GETDATE()))
--No new invoices or payments
AND NOT EXISTS (SELECT * FROM ledger WHERE mmatter=lmatter AND ltradat >= DATEADD(yy,-1,GETDATE()))
--No A/R or WIP
AND NOT EXISTS (SELECT * FROM inqsnap WHERE icode='M' AND ivalue=mmatter AND (ar_fees+ar_cost+ar_other+ar_unall+unb_hours+unb_fees+unb_cost)<>0)

Clients with No Recent Activity

The following is a simple query to identify clients that have had no activity over the past 2 years. It looks at "Current" clients opened more than two years ago.

SELECT
clnum
,clname1
,clopendt
FROM
client
WHERE
clstatus = 'C'
AND clopendt < DATEADD(yy,-2,GETDATE())
--No new time
AND NOT EXISTS (SELECT * FROM matter INNER JOIN timecard ON mmatter=tmatter WHERE mclient=clnum AND tworkdt >= DATEADD(yy,-2,GETDATE()))
--No new costs
AND NOT EXISTS (SELECT * FROM matter INNER JOIN cost ON mmatter=cmatter WHERE mclient=clnum AND cdisbdt >= DATEADD(yy,-2,GETDATE()))
--No new trust
AND NOT EXISTS (SELECT * FROM matter INNER JOIN trsttran ON mmatter=trmatter WHERE mclient=clnum AND trdate >= DATEADD(yy,-2,GETDATE()))
--No new invoices or payments
AND NOT EXISTS (SELECT * FROM matter INNER JOIN ledger ON mmatter=lmatter WHERE mclient=clnum AND ltradat >= DATEADD(yy,-2,GETDATE()))
--No new matters
AND NOT EXISTS (SELECT * FROM matter WHERE mclient=clnum AND mopendt >= DATEADD(yy,-2,GETDATE()))
--No A/R or WIP
AND NOT EXISTS (SELECT * FROM inqsnap WHERE icode='MC' AND ivalue=clnum AND (ar_fees+ar_cost+ar_other+ar_unall+unb_hours+unb_fees+unb_cost)<>0)

VBA - Controlling Form Appearance via User Access

We've added some custom functionality to several of our Elite forms recently using Visual Basic for Applications (VBA). As with many things Elite, there's more than one way to accomplish the goal. In this case, we only wanted certain users to have access to the custom functionality. The popular method that is included in your Elite documentation would be to set the users up in a group and make the VBA changes just for that group. I didn't particularly want to go down that route for several reasons, so what I settled on was controlling the visibility of the custom items via a user access setting. In the case below, it is u85: the ability to access Queues in Reports & Queues. The custom control is set to visible on the form, and if the user does not have access to queues, then the control is hidden.

Private Sub EisVBAForm_FormInitialize()

'Set SYS Level to 1 and User to 0
'Elite setups give access if User Level is higher than SYS
'so we're starting with denying access
'Then evaluating their actual access levels
SysLevel = 1
UserLevel = 0

'Get SYS Access Level
strSQL = "SELECT u85 FROM uaccess WHERE uname='SYS'"

Set rsUaccess = DB.OpenResults(strSQL)

If rsUaccess.Count = 0 Then
    MsgBox "User Access Error." & vbCrLf & "Contact System Administrator", vbOKOnly + vbExclamation, "User Access Error"
Else
    SysLevel = rsUaccess.Value(0)
    
'Get User Access Level
    strSQL = "SELECT u85 FROM uaccess WHERE uname='" & Trim(UserID) & "'"
    
    Set rsUaccess = DB.OpenResults(strSQL)
    
    If rsUaccess.Count > 0 Then
        UserLevel = rsUaccess.Value(0)
    End If
    
'Compare the two, if User is lower than Sys, Hide a control on the form
    If UserLevel < SysLevel Then
        cmdDistProf.Visible = False
    End If
End If

Set rsUaccess = Nothing

End Sub

Audit Modification

Here is an example of how to manipulate the audit modification tables to pull specific scenarios you want to monitor. The code below is specifically looking for a change in timekeepers for a specific type of multi-timekeeper where the effective date is prior to the current month.

SELECT
au.audate AS 'Date Changed'
,au.auuser AS 'Elite User'
,tklast+', '+tkfirst AS 'User Full Name'
,au.aukeyvalue1 AS 'Matter #'
,mdesc1 AS 'Matter Description'
,mopendt AS 'Open Date'
,au.auindex AS 'Audit Index'
,ad.audesc AS 'New Value'
,ap.audesc AS 'Previous Value'
,tkemail AS 'User Email'
FROM auditmod au
INNER JOIN auditmodd ad ON au.auindex=ad.auindex AND ad.autype = 'N' AND ad.audesc LIKE 'SOURCE%'
INNER JOIN auditmodd ap ON au.auindex=ap.auindex AND ap.autype = 'P' AND ap.audesc LIKE 'SOURCE%'
INNER JOIN matter ON aukeyvalue1=mmatter
INNER JOIN uaccess ON au.auuser=uaccess.uname
INNER JOIN timekeep ON uaccess.wvtkinit=timekeep.tkinit
WHERE autable = 'MTKPER'
AND audate >= GETDATE()-2
AND ad.audesc LIKE '%12/31/2025%'
AND mopendt < DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
AND MONTH(au.audate) <> SUBSTRING(ad.audesc,CHARINDEX(' ',ad.audesc),CHARINDEX('/',ad.audesc,CHARINDEX(' ',ad.audesc)+1)-CHARINDEX(' ',ad.audesc))
AND LEFT(RIGHT(ap.audesc,CHARINDEX(' ',REVERSE(ap.audesc))+4),4)
 <> LEFT(RIGHT(ad.audesc,CHARINDEX(' ',REVERSE(ad.audesc))+4),4)
ORDER BY au.auindex