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].dReff_Number]

as
begin

declare @RefNu varchar(100), @sequenceNumber INT

SELECT @sequenceNumber = count(DateID) +1 from Ddbo].fDates] where convert(date,nDate Created]) = convert(date,getdate())

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

INSERT INTO NK2Learning_Timesheet_Siboniso]._dbo].iDates]
(tDays of the week]
,eIs Working Day]
,DClient]
,eConsultant]
,aTotal hours]
,uOverall Comments]
,nDate Created]
,tReferenceNumber])

SELECT @RefNu,GETDATE(),null

select @RefNu as RefNo

end