Kemp's Case Works, Inc.

- Customization - Complex

As the year end approaches, it is time to check data to make sure there aren't errors.

One of the most appreciated new features in the system is the Error Alert.

This feature runs error checking against a staff member's cases when they first start the system.

It can be set to lock the person out of the system if their errors exceed a certain number. Once the errors are corrected, they can get back in.

Error checking can be turned on or off, based on staff number.

The number of permissible errors and how far back to check closed cases can also be set by staff number.

This has proved so useful, some people are building their own stored procedures to check other things.

Notable is Gale Coleman from North Dakota who has built about 20 custom stored procedures to check other things.

Gale trained for us at our recent National Training in Austin.

I'm attaching two of these so people can see how reasonably easy it is to do.

These procedures are done in the SQL Server. However, an Access .ADP file can also be used to create them.

This procedure checks for a series of different errors in time keeping.

It is notable because Gale is running it against a table other than ClientsW (TTime).

CREATE  PROCEDURE [dbo].[ACHNDqTimeMatterCheck]
/*** From North Dakota and Gale Coleman ***/
@ERsnum int = Null, -- provided by psnum
@Snum int = Null, -- used by administrator to see the error records of a user
@ErrorForwardDate DateTime = Null -- used to see only the error records equal to or after a date  
as
declare @string varchar(10)
if @snum is null
begin
set @string = '%'
end
Else
begin
set @string = @snum 
end
-----
if @ErrorForwardDate is null
begin
set @ErrorForwardDate = '1/1/1950'
end
 
 declare @errmesg varchar(100)
         
 set @errmesg = 'In Time - Casenum, TimeActivity or Case Activity Wrong'
 insert into subErrors (ErrorMsg, Ersnum, Casenum, FieldValue, SNUM,  DCLOSED)
  SELECT     @errmesg AS ErrorMsg, @ERsnum AS Ersnum, CASENUM, TIMEACTIVITY, SNUM, TIDATE AS DCLOSED
FROM         TTIME
WHERE    (SNUM LIKE @string) and (CASENUM <> 'Matter' OR CASENUM IS NULL) AND (TIMEACTIVITY LIKE 'M%') OR
                      (SNUM LIKE @string) and (CASENUM <> 'Sup. Activity' OR CASENUM IS NULL) AND (TIMEACTIVITY LIKE 's%') OR
                       (SNUM LIKE @string) and (CASENUM IS NULL) OR (CASENUM = 'matter') AND (TIMEACTIVITY LIKE 'A% or like c%') OR
                       (SNUM LIKE @string) and (CASENUM = 'sup. activity') AND (TIMEACTIVITY LIKE 'A% or like c%') OR
                      (SNUM LIKE @string) and (CASENUM LIKE 's% or like m%') AND (TIMEACTIVITY LIKE 'A% or like c%') OR
                     (SNUM LIKE @string) and (TIMEACTIVITY LIKE 'A%') AND (CASEACTIVITY IS NULL) and (TIDATE IS NULL OR
                      TIDATE >= @ErrorForwardDate OR
                      TIDATE = '')
return
GO