Returning Ole Notes File names in Dynamics GP

Hi folks


Here is another tip from our tame Dex developer Jon, which I have written up as an article for us mere mortals. The below article should help you if you need to bring back an image from the OLE notes folder in Dynamics GP.

To start with you need to know how OLE notes get their file names. Well the file name is the hexadecimal version of the note index held in the Dynamics GP notes master table (SY03900).

Therefore to reference the file you are going to need to convert decimals to hexidecimals. The below function will allow you to do this.

udfConvertBase10NumberToAnyBase function:

CREATE FUNCTION udfConvertBase10NumberToAnyBase

( @iNumber INT,

@iNewBase INT,

@bitUppercaseOnly BIT = 0,

@bitNoNumbers BIT = 0) --1 to use only alphas

RETURNS VARCHAR(120)

AS

BEGIN

DECLARE @vUppers VARCHAR(26)

DECLARE @vLowers VARCHAR(26)

DECLARE @vNumbers VARCHAR(10)



SELECT @vUppers = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

SELECT @vLowers = 'abcdefghijklmnopqrstuvwxyz'

SELECT @vNumbers= '0123456789'



DECLARE @vCharactersInBase VARCHAR(62) --Max possible of 10 digits, 26 upper, 26 lower

SELECT @vCharactersInBase = @vUppers --all options allow this



IF @bitNoNumbers = 0

BEGIN

SELECT @vCharactersInBase = @vNumbers + @vCharactersInBase

END



IF @bitUppercaseOnly = 0

BEGIN

SELECT @vCharactersInBase = @vCharactersInBase + @vLowers

END



--If caller requests a "base" size > number of characters in conversion-characters-set, return error

IF LEN(@vCharactersInBase) < @iNewBase

BEGIN

RETURN 'udfConvertBase10NumberToAnyBase - ERROR: Requested Base-size greater than available characters in @vCharactersInBase'

END



DECLARE @vNewNumber VARCHAR(120)

SELECT @vNewNumber = ''





-- Algorithm for generating equivalant number in the new "base":

-- 1) The orignial (base-10) number is continually divided by the new base until the product

-- of the old number divided by the base is zero (meaning the number is finally smaller than the new base).

-- 2) On each cycle (loop iteration), the remainder is added to the number, which is each digit of the new base.

WHILE @iNumber <> 0

BEGIN

SELECT @vNewNumber = SUBSTRING(@vCharactersInBase, (@iNumber % @iNewBase) + 1, 1) + @vNewNumber

SELECT @iNumber = @iNumber / @iNewBase

END --While



RETURN @vNewNumber



END –Procedure



So now we have a function that can convert the decimal to hexidecimal to obtain the file name.

To be able to use this we then need to include this function in a script

See Below:



Select noteindx, right(‘00000000’ + dbo.udfConvertBase10NumberToAnyBase(noteindx, 16, 0,0), 8) as filename from SY03900

You can now bring back the OLE note file.