I am attaching a note to the comments field in SOP sales transaction entry window. I am writing a report where i need to display this data in notes field. Can anyone tell me what table the notes field is stored in and how is it linked to this particular order so i can insert it into my report. Any help would be appreciated, thanks

All of the comment ID's and text for the Comment on the main window are stored in the SOP10106 (link on both the SOPNUMBE and SOPTYPE).  The entire text is in the CMMTTEXT field.

Luvsql, the table you showed has the text in the comment field. I am talking of the notes field ( the notes button next to the comment id lookup button ). I am putting some data in that notes field and attaching it to the order. I need the details of that notes field, not the comments field, thanks

Notes for the company are stored by Note Index in the SY03900 table.

OK i got that. THe problem i have is now is how is this noteindex linked to the order. The order table ( sop10100) has a noteindex which is not the same as this noteindex. Please help me further, thanks

Adding to above comment, what does the noteindx field in the SOP10100 table point to? Thanks for your time and effort. Also what is the best place to learn about all these table structures and link to each other?

Hi. I think I have the solution that you are looking for. First, on the Sales Transaction Entry Window there are many notes fields that are visible. The only one that is linked to the record in the SOP10100 table is the one that follow the Document No. If you want to enter notes on a sales transaction, this is where to do it. The links would be:
SOP10100 -> SY03900 on NOTEINDX
As for the rest of the notes fields, including the comment id field. They are linked from the records that the line is associated with. Example. The Comment ID note is tied to the Comment ID master table. Let's say you have a Comment Named "Thanks". If you enter a note in this field, it will show every time you select "Thanks". The links would be:
SOP10100->SY04200(Comment Master) on COMMNTID
SY04200->SY03900 (Note Master) on NOTEINDX

Hope this helps
Scott S.

Thanks Scoot that was awesome. I have asked a lot of people but still haven't got a satisfactory answer. What's the best place and easiest method to learn the Database tables and fields and linking for Dynamics 6.0

You can either go to tools > resources > table descriptions (there it will show you all the tables and fields, then the keys on each) or install the SDK for Dynamics (shoon the second CD in the DSDK folder).

You can either go to tools > resources > table descriptions (there it will show you all the tables and fields, then the keys on each) or install the SDK for Dynamics (on the second CD in the DSDK folder).

Luvsql, I installed the SDK. No idea how to use it. I tried genview to see the view of my company database but it gave error saying genview.sql not found. Also what dictionary should i point it to and all. Pls  Help on how to use the SDK

I'm using version 7, so I hope it's the same.  When I go into the Developer's Resources, then into Erwin Models.  Pick a module and then physical files. It will show you the table links and their keys.  

luvsql, when i installed SDK, it gave me only 3 icons namely
data model documents
genview
great plains SDK readme,
I have no developer's resources and erwin models. Am i doing anything wrong in installing the SDK? I installed from DSDK directory on CD2 and ran setup.exe. I selected custom and added all the components there. Pls advise, thanks

In the 7.50 SDK is a document with a large number of Report Writer Functions that I got added into 7.00. It is not in the 7.00 SDK on the CD but can be downloaded from the web.

Included specific functions for handling Comments from SOP and POP and Note fields in general. For example

RW_SOPHDRCommentText(IN_Type, IN_Number, IN_characters, IN_line)

RW_SOPLINECommentText(IN_Type, IN_Number, IN_LineItem, IN_Component, IN_characters, IN_line)

RW_GetNoteText(IN_Index, IN_characters, IN_line)


Let me know if you want the document.

David Musgrave
Senior Development Consultant
Asia Pacific Professional Services

Microsoft Business Solutions
http://www.microsoft.com/BusinessSolutions

mailto:dmusgrav@microsoft.com

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.