How to filter Dynamics GP Notes - Not!

Article
03/04/2010

Emily Halvorson - Click for blog homepage A 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:

 using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class FilterSpecial
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static string StripControlChars(SqlString source)
    {
        int l = source.Value.Length;
        System.Text.StringBuilder sb = new System.Text.StringBuilder(source.Value.Length);
        for (int i = 0; i < source.Value.Length; i++)
        {
            if (!Char.IsControl(source.Value, i))
                sb.Append(source.Value.Substring(i, 1));
            else
            {
                if (Char.IsWhiteSpace(source.Value, i))
                {
                    sb.Append(source.Value.Substring(i, 1));
                }
            }
        }
        return sb.ToString();
    }
}

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:

 CREATE FUNCTION [filterspecial] (@source nvarchar(4000))
RETURNS nvarchar(4000)
AS
EXTERNAL NAME [fspec].[FilterSpecial].[StripControlChars]

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

 create trigger [dbo].[SY03900_InsertTrigger_FilterSpecialChars] on [dbo].[SY03900] for  insert  as 

    declare @noteindex numeric(19,5)
    declare @sourcestring nvarchar(4000)
    declare @filteredstring nvarchar(4000)

  select @sourcestring = SY03900.TXTFIELD
  from SY03900, inserted
  where SY03900.NOTEINDX = inserted.NOTEINDX

 execute @filteredstring = filterspecial @sourcestring

   update SY03900 set TXTFIELD = @filteredstring
    from inserted
   where SY03900.NOTEINDX = inserted.NOTEINDX

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:

 update econnect_out_setup
set doctype = 'XYZ'
where doctype = 'Sales_Transaction' and tablename = 'SY03900'

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:

 public String StripControlChars(String source)
{
    int l = source.Length;
    System.Text.StringBuilder sb = new System.Text.StringBuilder(source.Length);
    for (int i = 0; i < source.Length; i++)
    {
        if (!Char.IsControl(source, i))

            sb.Append(source.Substring(i, 1));

        else
        {
            if (Char.IsWhiteSpace(source, i))
            {
                sb.Append(source.Substring(i, 1));
            }
        }
    }

    source = sb.ToString();
    return source;
}

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

How to filter Dynamics GP Notes - Not!

Additional resources