APS- Converting and Importing Client/Matter Billing Codes.

 

PaperCut MF can be used very effectively in Legal and Accounting Firms by modifying the Client/Matter codes so they are compatible with PaperCut Shared Accounts.

 

This example illustrates how to modify the APS Client Matter File and set up an overnight Synchronisation with PaperCut.

 

APS.

 

The APS Client Matter File is generally a CSV file in the format:

 

Client Code, Matter Code, Client Description, Matter Description

 

At the the time of writing this technical note (October 2009) PaperCut does not have an automatic way to handle this format.

 

To overcome this limitation it is quite a simple task to run a Code Conversion Program through the Windows Scheduled tasks to convert the Lawbase File to a PaperCut File.

 

Generating the APS Export

The APS data will be stored in an SQL Database. The first step is to extract the Client Matter codes and one relatively simple method is to set up an ODBC Connection to the APS Database and then use an MS Access Database to generate the appropriate query.

 

Setting up an ODBC Connection.

 

Select Data Sources (ODBC) from the [Administrative Tools] Menu

 

 

Select Add from the User DSN Tab.

 

Give the Connection a Name and select the Server. In my example here I have called the Database [aps_dsql] and it resides on [XSERVER]

 

 

 

Select SQL Server authentication and enter the Login ID and Password. In my example I am using [sa] without a password.

 

 

Next we need to connect to the database within SQL. In my example I am using [Conf2004] as this was the sample database supplied to me by APS. Yours will be different.

 

 

Click Next then Finish.

 

 

Your ODBC Connection should now be complete.

 

You can download a zip containing the following files here.

ExportPaperCutData.bat

APSExport.bat

Convert.Class

aps_dsqlPaperCut.mdb

 

Extract the files into C:\Program Files\PaperCut MF\APS\

 

Modifying the Access Database.

 

Hold down the Shift key and double click on aps_dsqlPaperCut.mdb, this will open the Database without executing the AutoExec Script.

 

Once open, you should have the following Objects. (note - this example is using Access 2007.

 

 

 

The first step is to modify the Table links to connect to your Database.

 

Note that when you hold the mouse over the dbo_cdbClient Table it shows my specific data and needs to be changed to your data. This is relatively straight forward.

 

 

Right Click on the dbo_cdbClient and select [Import] - [ODBC Database] as illustrated.

 

 

Select [Link to the data source by creating a linked table] and click OK.

 

 

Select [Machine Data Source] then select the aps_dsql] connection we created above and click OK

 

 

You will be prompted to enter the Login Details. Click [Use Trusted Source] and click OK.

 

 

Now select the dbo.cdbClient Table and click OK

 

 

 

This will create your new Linked table as dbo_cdbClient1. Remove dbo_cdbClient and rename dbo_cdbClient1 to dbo_cdbClient

 

 

Follow the same process with the dbo_ocsMatterMaster except select the dbo.ocsMatterMaster link as illustrated below.

 

 

When completed you should now have the following Linked Tables except they will now link to your data.

 

 

Now that you have linked to your data lets have a look at the other Objects.

 

QUERIES.

If you right click on the Query - gryGetList you can open in Design View.

 

 

By opening the Query in Design View you will be able to see the conditions that have been set for the exported data.

 

 

This is basically just a SELECT Statement, for example.

 

SELECT

dbo_ocsMatterMaster.mtrClientID

dbo_ocsMatterMaster.objInstID

Replace([dbo_cdbClient.cltSortName],",","") AS clientName,

Replace([dbo_ocsMatterMaster.mtrDesc],",","") AS MatterName

FROM dbo_cdbClient INNER JOIN dbo_ocsMatterMaster ON dbo_cdbClient.objInstID = dbo_ocsMatterMaster.mtrClientID

WHERE (((dbo_ocsMatterMaster.mtrAllowDisb)<>0) AND ((dbo_ocsMatterMaster.mtrDateClosed) Is Null))

ORDER BY dbo_cdbClient.cltSortName;

The Replace Statements replace any commas with spaces so that data can then be saved as a Comma Separated File.

 

You may need to modify the WHERE conditions to suit your particular requirements.

 

 

MODULES

Double Click on [Module1] and this will open in the Visual Basic Editor. Here you will be able to modify the path if required. By default it is set to strPath = "C:\Program Files\PaperCut MF\APS\"

 

MACROS

The AutoExec Macro will run Module1 and generate the output files.

 

TESTING.

 

TEST 1.

To test, right click on the gryGetList and select [Open]

 

 

You should get a list of all client matter codes and descriptions. For example:

 

 

AT this stage you need to check to see that this data is the correct list of Client Matter codes that you want to have as billable Clients.

 

TEST 2.

Next, close the Access Database, open the APS Folder and right click on the database as illustrated and select [Open]

 

 

After about 15 seconds the Database actions will complete and you should have a file called clients.csv created in the APS folder as illustrated.

 

 

This file is a comma separated file that is ready to be converted into PaperCut format.

 

Now we need to install the Conversion program that will convert the Lawbase CSV Data into a special PaperCut Tab Delimited format.

 

IMPORTANT NOTES:

 

1. By default PaperCut will synchronise at 1:15am. See more information on how to change this..

 

2. Before running an import, select the Template Account, then select the Security Tab. Now click on the drop down, select All Users then click Add. This will add the All Users group to permit access to all the accounts that you import as illustrated below.

 

 

Checking to see if Java is Installed

 

The first step is to see if you have a compatible version of Java installed on your Server.

 

To do this:

 

 - Create the following folder in the PaperCut MF Directory:

 

C:\Program Files\PaperCut MF\APS\.

 

 - Download and copy the following file into this folder.

 

Conversion Program [Convert.class]

 

 - Next, open a command Window and type:

 

java Convert > importcodes.txt

 

At this point the Convert Program should execute and a file importcodes.txt be created. However, if java is not installed you will get the following error

 

'java' is not recognised as an internal or external command, operable program or batch file..

 

In this case you will need to install java.

 

Installing Java

If you get the error message as illustrated above then you will need to download and install the java runtime. Click on the download link below, save it to a temporary location and run the executable accepting the default configuration.

Download Java

Synchronising Accounts with PaperCut

Now in PaperCut you can select Accounts - Shared Account Sync and Synchronize with the file created by the conversion process as illustrated below.

The next step is to create a  Windows Scheduled task to run the batch file and update PaperCut overnight.

Contents of APSExport.bat

REM Starting Export of Client/Matter codes from APS
"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" "C:\Program Files\PaperCut MF\APS\aps_dsqlPaperCut.mdb"
REM Now convert the csv file to a PaperCut Format
java Convert > importcodes.txt

NOTE: You may need to change the MSACCESS Path.

 

The following screen shots illustrate this process.

 

 

 

 

 

To test that everything is running as expected, manually run the task.

If the task completes correctly then in the folder C:\Program Files\PaperCut MF\APS you should have two files created as follows.

 

Clients.csv - this is the file that APS creates

ImportCodes.txt - this is the Client codes converted into a PaperCut format ready for import.

Example of Scheduled Tasks Timeline

• Time	Scheduled Task
  11:00pm	Windows Schedule runs the Lawbase Export.bat file. This creates clients.csv
  11:30pm	Windows Schedule runs "java Convert > importcodes.txt" this converts clients.csv into the importcodes.txt file for PaperCut
  1:15am	PaperCut locates the Import Codes file and imports into PaperCut.

.

 

 

More Information.

 

Changing the PaperCut Scheduled Maintenance Time.

By default PaperCut runs it scheduled tasks at 1.15am. This can be changed by selecting the Options Tab - then selecting the Advanced Config Editor menu option from the left hand menu.

 

In the Quick Find, enter Schedule.daily-maintenence

 

.

As illustrated above, the time is set at 0 15 1. This is 1.15am. You can change this if required but ensure that it is still outside of working hours as there are a number of other tasks that use this time. For example, changing to 2.30 am you would enter 0 30 2 * * ?

 

 

Modifying the Convert File

Please feel free to download the Convert Source Code and modify the file name if you require. Currently the Convert.class file has a hard coded filename of Client.asc. On line 8 you will find the following entry:

 

ProcessLineByLine("clients.csv");

 

Once modified, save the changes then compile on a command line with:

 

javac Convert.class

 

Download the Convert Source Code

 

 

Lawbase Files

Please contact james.milne@xtra.co.nz for more information on upgrading from the CMS Copy & Print Management System to PaperCut MF.

 

Checking the Paths.

 

If you get errors associated with Java Classes of Classpath then check the Environment variables. By default when you installed Java it will have installed the files required to run the Convert Program in C:\Program Files\Java\jdk1.6.0_05\bin. Make sure these paths are in the Path and Classpath

 

 

Checking the Classpath

 

 

 

About Us       |       Solutions       |       Partners       |       Services       |       Contacts