[Dianne Butterworth]

?Hi all,

Using NAV 2013R2

I do a lot of work with SQL queries, but haven’t been able to figure out how to get the content of a note. I know they are stored in the [Record Link] table, but the [Note] field is an image field and the contents are displayed in hex (I think).

Googling yielded the suggestion of

cast ( cast( [Note] as varbinary(max) ) as nvarchar(max)) as NoteField

but the result is not intelligible.

Any ideas?

Thanks.

——————————
Dianne Butterworth
Business System Manager
The King’s Fund
London
United Kingdom
——————————

[Paul Turner]

Note in Record Link is a BLOB field and by default is set as Compressed (parameter Compressed = Yes).

To read this in SQL you need to change this to an uncompressed field.

——————————
Paul Turner
Liberty Mountain
Sandy UT
——————————
——————————————-

[Dianne Butterworth]

?Thanks, Paul.

This setting would be in the NAV development environment? (not sure about the terminology)

——————————
Dianne Butterworth
Business System Manager
The King’s Fund
London
United Kingdom
——————————
——————————————-

[Paul Turner]

Correct.  Design the Record Link table in NAV, select the Note field then choose View > Properties.

Once you change the Compressed property to No, save the table.  I am not sure if this is enough of a table definition change to require restarting any service tiers you have servicing client connections but I would restart them just in case.

——————————
Paul Turner
Liberty Mountain
Sandy UT
——————————
——————————————-

[Dianne Butterworth]

?Thanks, Paul.

We don’t do any development work ourselves on NAV, so I’ll talk to our partner.

Are we unusual in that, I wonder? Do many organisations manage/develop their own systems, or do most work through a partner? Maybe another forum question there…

——————————
Dianne Butterworth
Business System Manager
The King’s Fund
London
United Kingdom
——————————
——————————————-

[Robert Buccigrossi]

Dianne,

We have been working with NAV since 2002 and we do not have a staff developer. We have always used our partner for development. However, this requires we have an outstanding working relationship and fortunately we do! There are many companies that have their partner do their development work as well as many that have their own developer on staff. There’s no right or wrong answer, whichever is the best business decision.

——————————
Robert Buccigrossi
PT Tech, LLC
Wadsworth OH
——————————
——————————————-

[Brad Brown]

It might be different in 2013, but if you disable the compressed flag in 2009 R2 and you have notes that are only 2 or 3 chars long you will have issues. SQL doesn’t care much, but NAV clients do and you will get errors when the Note is read back.

Our accounting department likes to put an “OK” note on sales orders, so I had to put a trigger on the Record Link table that queried the text value of any new note. If it sees an “OK” note, then it replaces the BLOB data with a space padded “OK” BLOB data.   

——————————
Brad Brown
IS/IT Manager
Elitechgroup Inc
Logan UT
——————————
——————————————-

[Dianne Butterworth]

?Thanks, Brad.

I don’t think that will be a problem for us, as we tend to use notes for instructions and information, but it’s worth knowing about in case we run into this issue.

——————————
Dianne Butterworth
Business System Manager
The King’s Fund
London
United Kingdom
——————————
——————————————-