create a trigger on case table to add any prefix to a ReferenceId field as the below :

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create TRIGGER [dbo].[UpdateCaseRefNumber]

ON [dbo].[cases]

AFTER insert

AS

BEGIN

SET NOCOUNT ON;

Declare @CaseType nvarchar(5),@CaseReferenceId nvarchar(128),@caseID int

Select @CaseType=case_c_type ,@CaseReferenceId =Case_ReferenceId ,@caseID=case_caseid from inserted

if(@CaseType ='CSI')

begin

update cases set Case_ReferenceId='CSI'+@CaseReferenceId where case_caseid=@caseID

End

if(@CaseType='NCN')

begin

update cases set Case_ReferenceId='NCN'+@CaseReferenceId where case_caseid=@caseID

End

End

That sounds perfect, one question... where do i put that? Sorry for being a bit thick.

Never Mind... found it.

It applies the prefix after you save the case, but on the entry screen it shows without the prefix. This shouldn't be a problem, but is there anyway round it?

Many thanks for all your help.

Hi Kevil,

What I understood from your above post is you need to add a prefix to the Case reference id based on the Case type. JFYI, Case Reference Id is generated using stored procedure on the fly before case is created in Sage CRM. Also in order to modify case reference id, you should have the Case Type with you. Hence modification into the standard case reference id will not work in this case.

You can achieve the same by following below steps.

1. Create a new field in Case entity.
2. Write a logic to generate Case Reference ID. (Fox example, 90-12345, you can use existing stored procedure for the same)
3. On workflow rule, you need to update the above case reference id based on the case type to append CSI- or NCN- before reference id generated in step 2.

Hope this helps!

Sincerely,
Dinesh

I Kevil, I am currently working on some workflow in Brandon. Can you please tell me where did you implement that? I can't get it working!