Okay so I just saw a question on the Sage Community forum a question about this “issue”and decided I should publish something we did internally for us and a client a while back.

The issue is that somehow, from time to time, you get duplicate case references.

You can check for this right now in your own database…

SQL to detect duplicate Case reference Id values

select Case_ReferenceId as ref, Case_CreatedDate as cd, Case_CreatedBy as cb, *
from Cases
where Case_ReferenceId in (
select case_referenceid
from cases
GROUP BY Case_ReferenceId
HAVING
COUNT(*) > 1)
order by Case_CreatedDate desc

Run this query and chances are you will be surprised to see duplicates.

If you don’t see duplicates stop reading…all is good for you..move on ūüôā

Otherwise….

Okay so you can ignore those duplicates or try fix them up… I don’t have a fix for this.

WARNING: If you implement the details below it is at your own risk. This is not something that Sage endorse or even know about.

To stop this happening again however we created a new stored-procedure (SP) to check for where a given reference exists

ct_CheckRefExists

CREATE procedure [dbo].[ct_CheckRefExists]
       @refid [nvarchar](128)
AS     
BEGIN
    SET NOCOUNT ON
       
    DECLARE @bfound int    
¬†¬†¬† SELECT¬† @bfound=0¬† –false

    IF EXISTS( select Case_ReferenceId from Cases nolock where Case_ReferenceId =@refid)
    BEGIN
      set @bfound = 1
    END
   
    SET NOCOUNT OFF
    RETURN @bfound
END

This SP checks if a ref-id already exists

DECLARE @bfound int
exec @bfound=ct_CheckRefExists @refid=’0-1009d5′
select @bfound

Then we use this within the¬†eware_default_values procedure (see the bold text below…this is what we inserted into the SP)

¬† SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+’-‘+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue¬†¬† ¬†

     DECLARE @bfound int

     exec @bfound=ct_CheckRefExists @refid=@default_value

     while (@bfound=1)

     BEGIN

EXEC @unique_value=eware_get_reference_id @table_name, @identity_name

¬†¬† ¬†¬†¬† ¬† SELECT @default_value=CONVERT(nvarchar(60),@logon_no)+’-‘+RTrim(CONVERT(nvarchar(60),@unique_value))+@extravalue¬†¬† ¬†

         exec @bfound=ct_CheckRefExists @refid=@default_value     

     END

  END  

Please note that…

Any CRM updates/upgrades will require that this is run again as the CRM update will overwrite this.

 


You may also like

Join our newsletter

Newsletter
Sending
The Business Predictability Blueprint

The Business Predictability Blueprint

Our free e-book will help you learn gain more structure within your business, better predict your pipeline, achieve consistency and start feeling more in control.

Get it now