I would like to create a unique reference number column in one of my tables in the following format – ITR/OB/{date}/{sequencenumber} – where {date} is the current date in format YYYYMMDD e.g. 20220721 and {sequencenumber} – number of records created for the day – 2 if there are two records.

Is there a way of doing this in k2 or would i need to create a stored procedure?

This is my stored procedure not sure if its ok.

Create Procedure [dbo].[Reff_Number]

as
begin

declare @RefNu varchar(100), @sequenceNumber INT

SELECT @sequenceNumber = count(DateID) +1 from [dbo].[Dates] where convert(date,[Date Created]) = convert(date,getdate())

SET @RefNu = 'ITR/OB/'+convert(varchar, getdate(),112)+'/'+ convert(varchar, @sequenceNumber)

INSERT INTO [K2Learning_Timesheet_Siboniso].[dbo].[Dates]
([Days of the week]
,[Is Working Day]
,[Client]
,[Consultant]
,[Total hours]
,[Overall Comments]
,[Date Created]
,[ReferenceNumber])

SELECT @RefNu,GETDATE(),null

select @RefNu as RefNo

end