Time Load VBA Scripting Example

One of the neat things about Enterprise is that it is customizable with VBA scripting. One of the uses I've found for this is to change the default checkboxes in many of the Enterprise screens to match how we do things. For instance, when our users need to manually upload time using Time Load, they have to select Expanded Line Format 2 and Workstation from the options. Rather than the users having to manually select these (and risk errors by choosing the wrong option), we can use VBA to default to these two choices. The code is shown below for this particular change.

Private Sub eisvbaform_forminitialize()

Me.gpbInput0 = False
Me.gpbInput4 = True
Me.optDos = True
Me.optUnix = False


End Sub

When making VBA changes, I usually test at my user level first and then at the firm level. To make the changes at the firm level, when logging in, choose the Advanced option and the Select different user for form and VBA changes option. Browse to the form you want to edit and choose VBA Editor from the Design menu. Making these types of changes are simple, and can save administrators headaches caused by incorrect user input.

Check & Voucher Totals by Vendor

It is always interesting to look at year-end stats, and this query is no exception. This will show you the number and dollar amount of vouchers and checks by vendor that your Accounts Payable department processed in a given year, with the focus being on value of checks. Note, as it is currently written, this will not return any vendors with vouchers but no checks.

SELECT 
ap.apnum AS 'Vendor #',
apname AS 'Vendor Name',
csd.apaddid AS 'Address #',
apaddr.appynm AS 'Payee Name',
COUNT(cknum) AS '# Checks',
SUM(ckamt) AS '$ Checks',
nvo.numvoucher AS '# Vouchers',
nvo.dolvoucher AS '$ Vouchers'
FROM ap
INNER JOIN csd ON ap.apnum = csd.apnum
INNER JOIN apaddr ON csd.apaddid = apaddr.apaddid AND csd.apnum = apaddr.apnum
LEFT OUTER JOIN (
 SELECT
 apvo.apnum,
 apvo.apaddid,
 COUNT(vo_id) AS numvoucher,
 SUM(voamt) AS dolvoucher
 FROM apvo
 WHERE YEAR(votrdt)='2009'
 GROUP BY apvo.apnum,apvo.apaddid) AS nvo ON csd.apnum=nvo.apnum AND csd.apaddid = nvo.apaddid
WHERE YEAR(ckdate) = '2009'
AND (voiddt IS NULL OR ckstat <> 'V')
GROUP BY ap.apnum,apname,csd.apaddid,apaddr.appynm,numvoucher, dolvoucher
HAVING SUM(ckamt) > 0
ORDER BY SUM(ckamt) DESC

Matter Exception Rate Update

Occasionally we have the need to input matter exception rates for a number of matters for a client, but not all (hence matter exceptions and not client exceptions). To my knowledge, there are at least three ways to accomplish this. 1) Manually input exception rates on all matters that need them (but what if there's 50!). 2) If you're on 3.5 or higher, you can create a matter udf called CEXRATE and input a different client number to utilise that client's exception rates. You could then create a dummy client, load the exception rates once and add this client number to each matter in the CEXRATE udf. 3) You can use the method below.

Step 1 - Input exception rates for one matter. Then view the exception rates in SQL. We want to copy the last row with the effective date of 5/1/2004.

SELECT *
FROM mexrate
WHERE mrmatter='2000-001'

Results

mrmatter	mrtk	mreffdate	mrrate	mrdevper	mrdevdol	mrccode	mrtitle	mrmaximum	mrreason	mrrtnum
2000-001	!	2000-01-01 00:00:00.000	200.000	NULL	NULL	!	Senior Partner	N	NULL	NULL
2000-001	!	2003-01-01 00:00:00.000	250.000	NULL	NULL	!	Senior Partner	N	NULL	NULL
2000-001	!	2003-03-01 00:00:00.000	300.000	NULL	NULL	!	Senior Partner	N	NULL	NULL
2000-001	!	2003-05-01 00:00:00.000	350.000	NULL	NULL	!	Senior Partner	N	NULL	NULL
2000-001	!	2004-04-01 00:00:00.000	400.000	NULL	NULL	!	Senior Partner	N	NULL	NULL
2000-001	!	2004-05-01 00:00:00.000	500.000	NULL	NULL	!	Senior Partner	N	NULL	NULL

Step 2 - Create temporary table to hold matters that need the exception rates & populate.

CREATE TABLE ##mexrate1
( mmatter VARCHAR(15));

INSERT INTO ##mexrate1 VALUES ('2000-003')
INSERT INTO ##mexrate1 VALUES ('2000-010')
...
--If there is a systematic way to identify the matters then you can use:
INSERT INTO ##mexrate1
SELECT mmatter
FROM matter
WHERE ...(your criteria)...

Step 3 - Execute cursor update below to cycle through the matters and insert new exception rates

BEGIN TRANSACTION mexrateupdate

Declare c Cursor For Select Distinct mmatter From ##mexrate1
Open c

DECLARE @MatId VARCHAR (15);

Fetch next From c into @MatId

While @@Fetch_Status=0 Begin

INSERT INTO mexrate
SELECT @MatId,
 mrtk,
 mreffdate,
 mrrate,
 mrdevper,
 mrdevdol,
 mrccode,
 mrtitle,
 mrmaximum,
 mrreason,
 mrrtnum
FROM mexrate
WHERE mrmatter = '2000-001' --The original matter to copy
 AND mreffdate = '5/1/2004' --The exception rates to copy

   Fetch next From c into @MatId
End

Close c
Deallocate c

Step 4 - Use SQL to view a matter that was updated & make sure everything looks good

SELECT *
FROM mexrate
WHERE mrmatter = '2000-010'

Step 5 - Check the matter in Elite to make sure everything looks ok.
Step 6 - If it's all good, commit the transaction (if not, rollback)

ROLLBACK TRANSACTION mexrateupdate --if it's bad
COMMIT TRANSACTION mexrateupdate --if it's good

Step 7 - Explicitly drop the temporary table

DROP TABLE ##mexrate1

Step 8 - Be thankful you didn't have to manually input 25 exception rates for 75 matters...


DISCLAIMER - As always, test liberally in a test environment and deploy cautiously.

Missing Files in Whitehill

For those that use Elite Document Studio by Whitehill/Skywire/Oracle Legal (whatever it's called these days) there's several moving parts that can wind up not working properly. One of these is that the document record is created in the Document_Catalog table, but the actual file doesn't make its way to the correct folder. This results in an error in Elite when trying to access an invoice pdf. So what do you do when you have a host of files missing from your bill directory, but the records are already in the Document_Catalog table (especially if only 1 of 4 converters wasn't functioning properly)?

Well, here's a solution. If you have any ideas on how to improve this, or re-write it altogether to be more efficient or effective, please share!

The first section of code here creates a temporary table and inserts into the temporary table the list of files of the targeted directory. In this case, we're looking at the bill directory on the Elite application server. You'll need to modify this path according to your environment. One note: the collation in the CREATE TABLE needs to be the same as the collation of your Document_Catalog table otherwise you may get errors when joining the two.

USE [son_db]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--Drop temporary table if it already exists
IF OBJECT_ID(N'tempdb.dbo.#DirContents', N'U') IS NOT NULL 
DROP TABLE tempdb.dbo.#DirContents;
GO

--Create temporary table to store contents of directory
CREATE TABLE #DirContents (
 Line VARCHAR(512) COLLATE SQL_Latin1_General_CP1_CS_AS NULL)

DECLARE  @PathName       VARCHAR(256) ,
         @CMD            VARCHAR(512) 

SET @PathName = '\\eliteappserver\elite\home\bill\'
 
SET @CMD = 'DIR ' + @PathName + ' /TC'
 
PRINT @CMD

INSERT INTO #DirContents
EXEC MASTER..xp_cmdshell   @CMD

The results from above should appear like this:

Database name 'tempdb' ignored, referencing object in tempdb.
DIR \\eliteappserver\elite\home\bill\ /TC

(32568 row(s) affected)

By taking a quick look at the temporary table you should see this:

Line
Volume in drive \\eliteappserver\elite\home\bill is Data_LUN72
Volume Serial Number is ABCD-E123
NULL
Directory of \\eliteappserver\elite\home\bill
NULL
07/17/2009 07:34 PM <DIR> .
07/17/2009 07:34 PM <DIR> ..
10/06/2008 01:09 PM 41,270 000001.000001.481826.10062008.pdf
09/03/2008 01:00 PM 42,434 000567.000026.471323.06052008.pdf
09/03/2008 01:00 PM 42,680 000567.000026.473887.07072008.pdf

These next few lines of code will strip out the non-file lines from the temporary table.

DELETE
FROM   #DirContents
WHERE  Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR Line LIKE '%<DIR>%'
OR Line is null

Next we need to compare the files in the directory (temp table) to the records in the Document_Catalog.

WITH realfiles AS (
SELECT
  'DocName' = REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ),
  'CreateDate' = LEFT(Line,10)
FROM #DirContents
WHERE LEFT(Line,10) LIKE '12/%%/2009'
),
doccatfiles AS (
SELECT
EMP_ID
,REVERSE(LEFT(REVERSE(CAST(FILE_PATH AS VARCHAR(400))),CHARINDEX('\',REVERSE(CAST(FILE_PATH AS VARCHAR(400))))-1)) AS 'DocName'
FROM Document_Catalog
WHERE FILE_DATE >= '12/1/09'
AND DESC_DATE_2 >= '12/1/09'
)
SELECT DISTINCT doccatfiles.EMP_ID
FROM doccatfiles
WHERE doccatfiles.DocName NOT IN (SELECT realfiles.DocName FROM realfiles)

Note: Since we're doing so much text manipulation and comparing long strings of text, this query is rather slow. You'll see I limited the inputs here to only December 2009 to make the information more manageable. If you have a large issue, I would recommend separating it into batches like this.

You now have a list of invoices that are missing from your bill location. You can now recap these specific invoices rather than target all within a certain date range of the error.