Find carriage return (cr) and line feed (lf) in NOTES fields of Dynamics GP

This is a Epson FX80 dot matrix printer. For those of us who remember RS232 driving these things using DOS or Turbo Pascal then you know that to make it move down a line, then a line feed ASCII character would need sending in the text stream, a char(10).

linefeedprinter

To make the printer move its print head to the start of the line (left had column) a carriage return would have to be issued, a char(13). This heralded from the mechanical typewriter origins of printers where on the typewriter, you’d have to move a line down and push the carriage to the start of the line again (return the carriage). – Yes I still own a mechanical typewriter…

Although the behaviour could be configured, often with DIP switches, electrical config switches hidden in the printer or using config ESC codes, it was common in computing to have to have a char(10)+char(13) sequence at the end of lines. However it was not uncommon (UNIX) to find just a char(10) alone used.

if it has not be sanitised before injection into the GP database, Dynamics GP notes fields, can have both or one of these characters used. However only char(13) should be used as char(10) gives  an ugly block character in the text when displayed in the user interface, for some versions of GP. We use Profad Enhanced notes in place of the default notes window, where the type of line feed character no longer seems to matter.

To see what line endings are present, the output saved from SQL can be viewed in a text (HEX enabled) editor allowing the hex of the file to be viewed, or it can be easier to translate the line feeds and carriage returns into tokens using SQL script like this and view in normal SQL server Management Studio:

SELECT 
REPLACE(
REPLACE(CAST(TXTFIELD AS VARCHAR(max)), CHAR(10), '[lf]'),
CHAR(13), '[cr]'
)
FROM sy03900
WHERE NOTEINDX=0000000

Where the zeros of NOTEINDX are replaced with the note id of interest and every carriage return will show as [cr] and every line feed as [lf].

Here is an example of the output of this SQL, a bad example with [cr][lf] sequence in the notes

image

If the fields need cleaning because of blocky characters, then I wrote about this a while back Formatting notes using GP econnect

This wiki has much more info and more detailed information for those who are interested https://en.wikipedia.org/wiki/Newline

Duplicate SOP Master number in Dynamics GP

The credit card fulfilment add in that I wrote charges cards using sage pay API, from the GP screens or our custom SOP Fulfilment software, when the goods are despatched on a sales order.

In order for the charges to be tracked as the sales document migrates from Sales order to Invoice and subsequent fulfilments I utilise the master number of the order. However it turns out that the master number can end up getting shared by more than one document.

Master Numbers – what are they?

Master numbers can be enabled in the SOP setup screen.

SOPSetup

When ever a new document is created from a source document, they are tied together with a common master number. The stored procedure:taSopGetMasterNumber in the company database serves us with the next master number from number in this setup screen when ever a new sales document is created without getting derived from another document.

You can use master numbers in SOP Document enquiry and reporting to see all related documents as the master number ties them all together.

Our Issue

Somehow we have found that some of our sales documents have ended up sharing master numbers with unrelated documents. We have seen this with the SOP number itself too but there it is more obvious.

To find the maser numbers that are causing a problem I put together the following script that will work on SQL server 2005+. Note that we don't use back order document types so only one sales order document exists per master number according to our business logic.

WHERE ORIGTYPE=0 should help identify them if you do use back order documents, but the query will not use the indexes on the tables and thus will take a long time to execute.

-- Script to identify duplicate Master Numbers 
--  on Dynamics GP SOP Documents
With WorkHistUnion (MSTRNUMB, SOPNUMBE )as
(SELECT MSTRNUMB,SOPNUMBE  FROM SOP10100 WHERE SOPTYPE=2
UNION ALL
SELECT MSTRNUMB,SOPNUMBE FROM SOP30200 WHERE SOPTYPE=2)
SELECT  MSTRNUMB, COUNT(MSTRNUMB)from WorkHistUnionGROUP 
BY MSTRNUMBHAVING COUNT(MSTRNUMB)>1ORDER BY MSTRNUM
 

Trying this on one of our GP9 companies I got (278 row(s) affected). So we have 278 instances of sales documents sharing master numbers with unrelated sales orders. Now I have introduced a check that looks for this senario and uses the customer CUSTNMBR as a key when charging cards.

Hopefully this should stop the wrong customer getting charged for a despatch of goods – bug squished!

Why do we get this situation? Dynamics GP does not lean very much on SQL server for database integrity. This is due to the heritage of the product, rooted in DBISAM, it has never used the database to ensure integrity. SQL server is quite capable through the correct transaction handling and constraint indexes of ensuring the master numbers are not shared, but sadly it has never been written into the database.There must be some holes in the application that allows this to happen in a similar way that our telesales team sometimes manage to get the same SOP number as each other.

Edit 2011

Some more web references have shown up since 2009 when I wrote this originally, the reason behind this happening can be found in these references too:

SOP Master Numbers not being assigned properly

SOP Master Numbers not being assigned properly (Dynamics community site)

Alternative Get SOP master number script (stored proc)

Dynamics GP slow to login or open forms, other users ok

Original post:14th March 2009

Updated 2016:

I notice that Ian had a similar problem that this solved too, see his post here: http://www.azurecurve.co.uk/2015/03/slow-opening-windows-in-microsoft-dynamics-gp/

Updated 2015:

GP offers a cache clear button (Remove Entries), under user preferences, I don’t think it existed/didn’t work or I didn’t know about it when this post was written, hence the drastic measures of removing the cache files. It would be wise to check the size of the cache files before and after “Removing Entries” to see if the size really does decrease. I would still advocate some audit/enforcing scheduled SQL script as mentioned later in the post to protect users from setting values for auto complete too high, especially in installations with very large numbers of items or customers/suppliers etc.


AutoComplete2

AutoComplete Setup is found from the user preference window, by clicking on AutoComplete button.

autocomplete
 
The problem report as reported:
Goods receiving, purchasing and accounts users all report some GP forms taking a very long time to open. Log in as admin or some alternative user and the problem does not occur. It was also observed that when a user’s network profile was deleted and recreated the problem went away (discovered by accident due to having to do this for other reasons and finding it solved this problem).
For information the company has over seventy four thousand items setup in inventory.

GPAutoComplete

Above: AutoComplete is a great feature that I welcomed but now is causing problems

Found the solution

The clue to the solution was this post: How to troubleshoot slow performance in Microsoft Dynamics GP [login required] 
More...

Smart Connect for Dynamics GP Webinar

I attended a webinar today hosted by Touchstone’s Dominic Houlbrooke-Bowers, about Smart Connect for Dynamics GP

Summary

There are two ways to summarise this product from this demo (i’ve not yet read more);

Replacement for integration manager using eConnect to enforce the busines rules, providing a richer and much faster experience than integration manager.
OR
A power user graphical user interface for eConnect

It has some very powerful extra features too like  push from internet placed excel or info path forms to web services and into GP.

Product description

More...