Generate Unique Reference Number in Custom Field using Workflow

By | April 9, 2018

In previous blog, we have seen that how we can call stored procedure from workflow action. Now, I will explain you, how we can use the same feature to generate Unique Reference Number for other entities like Case, Quote or Order.

New Stuff: Prevent the Non-Admin users from adding duplicate records in Sage CRM

We can achieve this by using following steps.

  1. Create a simple text field. e.g. “Orde_SONumber”. In my case, I am going use this for order entity.
  2. Add this field in workflow rule with Type Run Stored Procedure. Refer below screenshot.

3. Insert two entries in Custom_Sysparams as below so that the procedure will pick the unique value using these records.

insert into Custom_SysParams(Parm_Name,Parm_Value,Parm_CreatedBy, Parm_CreatedDate, Parm_UpdatedBy, Parm_UpdatedDate, Parm_TimeStamp)
values (‘OrdersIdFieldName’,’orde_sonumber’,1,getdate(),1,getdate(),getdate())

insert into Custom_SysParams(Parm_Name,Parm_Value,Parm_CreatedBy, Parm_CreatedDate, Parm_UpdatedBy, Parm_UpdatedDate, Parm_TimeStamp)
values (‘Ordersorde_sonumber’,’100′,1,getdate(),1,getdate(),getdate())

4. Then, create a store procedure as given below.

CREATE PROCEDURE [dbo].[SP_Ordersorde_sonumber]
@table_name nvarchar (50),
@identity_name varchar (50),
@id_no INT,
@logon_no int,

@return_value nvarchar(128) OUTPUT
AS
BEGIN
DECLARE @unique_value int

declare @default_value nvarchar(500)

declare @CurrentFY nvarchar(500)

SELECT @CurrentFY = (CASE WHEN (MONTH(GETDATE()) ) <= 3 THEN convert(varchar(2), cast((YEAR( GETDATE() ) % 100 ) as varchar(2))-1)+ ” + cast((YEAR( GETDATE() ) % 100 ) as varchar(2))
ELSE convert(varchar(2),cast((YEAR( GETDATE()) % 100) as varchar(2))) + ” + convert(varchar(2),cast((YEAR( GETDATE() ) % 100 )+1 as varchar(2)))END)

set @identity_name=’orde_sonumber’;

BEGIN
EXEC @unique_value=eware_get_reference_id @table_name, @identity_name
SELECT @return_value=’SO:’+@CurrentFY +’00’+ RTrim(CONVERT(nvarchar(60),@unique_value))
END

SELECT @return_value
RETURN (1)
END

5. To check the functionality, progress the required workflow rule to generate Unique Reference Number. Refer the below screenshot.

6. In this scenario, we have formatted the reference number as “SO:” +”Current Financial Year” +”Unique Number” as per our client’s requirement.