Developing for Dynamics GP

by David Musgrave (Perth, WA, Australia) and the Microsoft Dynamics GP Developer Support Team (Fargo, ND, USA)

How to filter Dynamics GP Notes - Not!

How to filter Dynamics GP Notes - Not!

  • Comments 1

Emily Halvorson - Click for blog homepageA month or three ago, I had a case where special characters entered into a Note on a Sales Invoice were causing the eConnect Requester to fail.  Since the Requester utilizes XML to pass the data, characters like Control-B can cause problems. 

If you want to see the Note entry screen in Dynamics GP you could go to Cards and select Sales and then Customer.  Use the First Record button at the bottom, left corner to pull up the first Customer in the table.  The Note icon is right of the Customer ID label; it looks like a piece of paper with a corner folded over.  When you click on that icon you open Form_Note_1 where you can enter information regarding your Customer.  In this case the users were accidentally holding down the Ctrl key while hitting the letter B while in this form.

(Quick Tip: Next I will meander into some coding that I found interesting; if you are in a hurry please skip down to the VSTools Solution towards the end of this post.)

Thinking that a SQL trigger on SY03900 (Notes Master) table would be the simplest way to update new Notes and that .Net StringBuilder would be helpful to eliminate any control characters, I created this file named FilterSpecial.cs:

At a command prompt, the following creates the dll:

csc /target:library FilterSpecial.cs

Then, into SQL Management Studio and select the Company database.  I was using the sample company so I selected TWO.  Execute the following:

create ASSEMBLY fspec from 'c:\MyPath\FilterSpecial.dll' WITH PERMISSION_SET = SAFE

Create the stored procedure to hit the external library assembly like this:

Here is the trigger on the SY03900 table, which executes the filterspecial proc:

Note: This post is for demonstration purposes and so does not include things like update triggers, but if your users would want to update the field you would probably want to add that as well.

I also needed to enable CLR use in my SQL instance by executing the following two lines: 

sp_configure @configname = 'clr', @configvalue = 1

reconfigure

I tested this with some brief data entry and it works fine, but you may have noticed that it handles up to 4000 characters while the Notes text fields are actually 32000 characters. I then had to remove the insert trigger from my SY03900 table, so it would not cause problems with future attempts at a solution.

Workaround

We received an update about this time and learned that the Customer did not actually need the data from the Notes passed back to the eConnect Requester calls.  So we changed the Sales Transaction row for Notes in the eConnect_Out_setup table:

  

This means that the Requester calls for Sales_Transaction data would not include SY03900 data.  Changing this resolved the issue for this particular scenario.  It will not cause any problems having the XYZ row out there, and that can be changed back if needed.  Subsequent Dynamics GP upgrades would possibly require this statement to be executed again.

VSTools Solution

This includes creating a Visual Studio Tools for Microsoft Dynamics GP (VSTools) project with a ClickBeforeOriginal event handler on the Attach button of FormNote1.  See the Events chapter in the VSTools Programmers Guide for details. 

My event handler method calls a slightly modified StripControlChars. Since it was now to function against window fields, I removed the SQL embellishments:

If your users only open one form note at a time, this works fine.  However Dynamics GP uses up to 5 windows: Form_Note_1, Form_Note_2, Form_Note_3, Form_Note_4, and Form_Note_5.  VSTools uses the same name, but without underscores so FormNote1, and so on. Events would need to be registered and handled against the other windows as well if multiple notes are needed.

I should mention that Customers need to obtain licensing before using the Visual Studio Tools for Microsoft Dynamics GP.  Here is the link from Customer Source with more information - https://mbs.microsoft.com/customersource/downloads/servicepacks/visualstudiotoolssdkformicrosoftdynamicsgp10.htm?printpage=false&stext=visual%20studio%20tools%20GP.

Enjoy!

Emily
Escalation Engineer
Dynamics GP Developer Support

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)

Page 1 of 1 (1 items)
Comments Information

PLEASE READ BEFORE POSTING

Please only post comments relating to the topic of this page.

If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.

Leave a Comment
  • Please add 1 and 4 and type the answer here:
  • Post