btamulis 1/4/2022 2:55:49 PM

SQL Insert a record into a Table with a NOTEINDEX Column

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

Version: All
Section: Dynamics GP, SQL Scripts


Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3