OLE Attachments and Record Notes
While working on a recent support case, I was asked again about how Notes work and in particular the OLE attachments. In the previous post, Understanding Notes and the Note Index field, we discussed where the next Note Index value comes from. In this post, we will discuss how the record notes are created and where the OLE attachments are stored.
So, starting at the beginning.... Every time a new Master record or Transaction record is created in a Microsoft Dynamics GP Company, the next Note Index value for the company is read, incremented and saved. The read value is then assigned to the new record. This means that a Note Index value is used even if a note is not yet created. If a record note is created for that Master or Transaction record, it will add a record into the SY_Record_Notes_MSTR (SY03900) table using the previously assigned Note Index value as the primary key.
If the OLEPath setting in the Dex.ini configuration file is correctly defined, the paper clip icon will be available to allow the addition of OLE attachments to the note. If the paper clip does not appear, please check the following:
- The OLEPath Dex.ini setting points to a shared folder for ALL workstations, you can use a shared drive letter (eg: G:\Dynamics\Notes\) or a UNC pathname (eg: \\Server\Dynamics\Notes\).
- The OLEPath Dex.ini settings ends with a backslash.
- The OLEPath Dex.ini setting points to a folder which exists and has read/write/create access to it and all sub folders.
- The folder pointed to by the OLEPath Dex.ini setting contains a folder for each company using the Intercompany ID (SQL Database name) as the folder name.
- Each company folder contains a folder called OLENotes.
When you click on the paper clip icon the Contain.exe program is opened. This program provides an OLE Container that is associated with the record note. OLE stands for Object Linking and Embedding, in our case it refers to how we can either create a link to an object (file) or embed the entire file into the OLE Container. Using a link saves hard disk space, but does require the linked file to remain in the same location. It also means the file can be updated and all links pointing to the file will always point to the latest version. Embedding the file uses more hard disk space and makes a static copy of the file at the time it was embedded. It is self contained and keeps the historic contents of the attachment.
Any linked or embedded attachments are stored in an OLE Container file which is named using a 8 character hexadecimal representation of the Note Index value with no extension. Whether there is an OLE attachment associated with a record note is not stored at the SQL database level. The application checks for the attachment container file when the note window is opened for a particular Note Index value. In the situation where an OLE attachment is added to a note without any characters being stored in the note's text field, the system will add the text "OLE Link present.". This is so we will show that there is a note attached when looking at the master or transaction record.
The question that I am asked often is "What is the full path for a OLE Container file for a given Note Index?". So below is how the path is generated:
{OLEPath Dex.ini} + {Intercompany ID (DB Name)} + "\OLENotes\" + {Note Index in 8 digit hexadecimal}
Below is some example Dexterity sanScript that shows how to generate the full path to the OLE Container file:
Dexterity Code Example
Note: The Utility_ConvertLongTo8CharHex() function library command is not documented in the Dexterity help file.
Below is an example T-SQL query which shows the Note Index in hexadecimal. As SQL can't read the Dex.ini setting, it is not possible to generate the full path, but this will at least give the file name.
SQL Code Example
select
convert(binary(4),cast(N.NOTEINDX as integer)) as OLEPATH, N.*
from SY03900 N
Note: You will need remove the "0x" from the OLEPATH column to leave the 8 hexadecimal digits.
For more information on OLE and the OLE Container, have a look at All about the Dexterity OLE Container which refers to Mariano Gomez's post All about the Dexterity OLE Container.
Hope this is useful.
David
