Error In SmartList Excluding One Category

Microsoft Dynamics GPA client reported an error to our service desk recently when they were working with an Account Transactions SmartList which they were trying to filter to exclude a particular Account Category. When they entered the search criteria they received a string of error messages.

First:

Microsoft Dynamics GP - GPS Error: 58

Microsoft Dynamics GP

GPS Error: 58

Second:

Microsoft Dynamics GP - SQL Error: 102 [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ')'.

"Microsoft Dynamics GP

SQL Error: 102 [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ')'."

Third:

Microsoft Dynamics GP - OSBC Error: 37000

Microsoft Dynamics GP

OSBC Error: 37000

We re-ran the SmartList while running a trace on SQL so we could see the commands being run. The following is the SQL statement being run:

SELECT
	LIVE.dbo.GL00100.ACTINDX AS '1'
FROM
	LIVE.dbo.GL00100 (nolock)
WHERE
	((LIVE.dbo.GL00100.ACCATNUM IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 
28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66,)) 
AND (LIVE.dbo.GL00100.ACTIVE IN (1)))
ORDER BY
	'1' ASC

The problem, as you can see in the highlighted section, is that SmartList is truncating the query; the client had more than 66 Account Categories. As a workaround for the client, we were able to create a SmartList Builder for them which pre-filtered out the Category they wanted excluded and then did a little more testing on different versions.

The client who reported the error was on Dynamics GP 2010 R2, and Dynamics GP 2013 R2 does not exhibit the same problem so it looks like Microsoft found and fixed this between versions. So, in the short term, the client can use the SmartList Builder we put together for them and in future when they upgrade the standard SmartList Object will function correctly.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.

2 thoughts on “Error In SmartList Excluding One Category

Leave a Reply

Your email address will not be published. Required fields are marked *