07 March, 2009

Get the Next NOTEINDX

This came up for me 2x this week alone on 2 different projects. Just about every time you have to insert a record into a GP Table you have to get the next note index. While it's a simple thing, it may not be obvious exactly how to do it.

Run this against your COMPANY database to increment and return the next note index from the DYNAMICS.dbo.SY01500 table:

--Declare some variables
DECLARE @I_sCompanyID smallint,
@O_mNoteIndex numeric(19,5),
@O_iErrorState int

--Get the CompanyID
select @I_sCompanyID = CMPANYID
from DYNAMICS..SY01500
where INTERID = DB_Name()

--Get and increment the next note index
exec DYNAMICS.[dbo].[smGetNextNoteIndex] @I_sCompanyID, 1, @O_mNoteIndex output, @O_iErrorState output

--Print the Next Note Index
print(@O_mNoteIndex)
--Print the Error State
print(@O_iErrorState)

9 comments:

Steve Endow said...

Thanks a lot for this excellent post--your timing could not have been better. I just had to create a mod to insert a new batch record, which of course requires a valid note index. Saved me a fair amount of time trying to remember / track down the stored proc and figure out it's proper usage.

Devo said...

This appears to be for a pure-SQL insertion, am I right?

Are you familiar with how this would be done in Integration Manager? Would a "Before Document" script be used? What would the scripting syntax look like and how would the returned NOTEINDX be mapped to the destination adapter? Thanks!

mbsguru said...

Devo,

You could use this in DIM on the Before Document script. You'd have to massage the syntax of course but technically, you could do it.

In what scenario does DIM not automatically handle the NOTEINDX for you? I'll be happy to help with that problem.

Thanks

Devo said...

You're right, we double checked and DIM did handle it appropriately. Thanks.

Joanne said...

Thank you for this post. Perfect time for me as well... had a situation this morning where data being integrated through sql script into gp was not actually filling in the NoteIndx field. The script worked great!!

smitha said...

really amazing blog and I was thankful to you for sharing such a useful information.
- Microsoft Dynamics CRM

Janakhiram said...

Hi Michael,

How can I use this to insert multiple records into a table in SQL or table import

mbsguru said...

Janakiram,

I use this often in cursors and loops. You could loop through your records and get the next note index before inserting each one. Just paste the code in this post inside your fetch.

Here's a template for a cursor you might find helpful. http://mbsguru.blogspot.com/2010/08/row-by-row-analysis-and-execution-with.html

Let me know if this doesn't help.

Michael

Janakhiram said...

Thanks Michael for answering my question and your help on this.