The collection module in GP has a Table - CN00100. It has one record for every note entered.
I am systematically inserting a record into the table based on the value in an external table.
Problem - the table can't have duplicate values in the NOTEINDEX column. Here's a screen shot of the Table. My insert has to specify a NOTEINDEX = '15' or get next and then '16' or get next for next record etc.....
Any thoughts?
Here's what I have so far:
INSERT INTO [dbo].[CN00100]
([CUSTNMBR]
,[CPRCSTNM]
,[DATE1]
,[Contact_Date]
,[TIME1]
,[Contact_Time]
,[NOTEINDX]
,[RevisionNumber]
,[CN_Group_Note]
,[Caller_ID_String]
,[Action_Promised]
,[ActionType]
,[Action_Date]
,[Action_Assigned_To]
,[Action_Completed]
,[ACTCMDSP]
,[Action_Completed_Date]
,[Action_Completed_Time]
,[Amount_Promised]
,[Amount_Received]
,[USERID]
,[Note_Display_String]
,[CNTCPRSN]
,[ADRSCODE]
,[USERDEF1]
,[USERDEF2]
,[USRDAT01]
,[PRIORT]
,[NOTECAT]
,[NoteStatus]
,[Action_Cancelled_By]
,[Action_Cancelled_Date]
,[MODIFDT]
,[CN_Pinned])
VALUES
('QUEEN001'
,'QUEEN001'
,CAST(getdate() as Date)
,CAST(getdate() as Date)
,CAST(getdate() as Time)
,CAST(getdate() as Time)
,'1111' ------------this is where I need to get next index number is there an equivalent to getdate type functionality?
,'0'
,'0'
,'sa'
,'DUNFINAL'
,'1'
,CAST(getdate() as Date)
,'sa'
,'0'
,'0'
,'1900-01-01 00:00:00.000'
,'1900-01-01 00:00:00.000'
,'5800.00'
,'0'
,'BRON'
,'Dunning Letter Sent'
,''
,''
,''
,''
,'1900-01-01 00:00:00.000'
,'2'
,''
,'1'
,''
,'1900-01-01 00:00:00.000'
,CAST(getdate() as Date)
,'0')
GO
Thanks in advance.......