Microsoft Dynamics GP – SmartList Displays SOP Returns as Negative Numbers

Recently I have had a few inquiries about how SmartList displays and exports SOP return documents.  Have you ever noticed that when you export SOP transactions from SmartList to Excel, return documents appear as positive numbers, requiring additional manipulation in Excel to provide accurate calculations? Fortunately, there are a few workarounds available using both SmartList Designer and SmartList Builder that will return information to display as a negative number:

If using SmartList Designer, you can create a view and then attach the view to a custom SmartList.  See the steps below:

1. In SQL Server Management Studio, run the following script against the company database(s) to create the SQL view. Note that the last command line grants permission to all members of the DYNGRP.

create view view_SOPReturnsNegative as

SELECT [SOP Type],

[SOP Number],

[Document Date],

[Customer Number],

[Customer Name],

‘Document Amount’ = CASE [SOP TYPE]

WHEN ‘Return’

THEN [Document Amount] * -1

ELSE [Document Amount]

END,

[Commission Amount],

[Commission Applied To],

[Commission Sale Amount],

[Salesperson ID]

FROM dbo.SalesTransactions

WHERE([SOP Type] <> ‘Quote’)

AND ([SOP Type] <> ‘Invoice’)

AND ([SOP Type] <> ‘Back Order’)

AND ([SOP Type] <> ‘Fulfillment Order’);

— add permissions for DYNGRP

go

grant select on view_SOPReturnsNegative to DYNGRP

2. In Dynamics GP, open SmartList. Highlight the Sales folder and click New.  Give your SmartList a name.  Select Microsoft Dynamics as the Product and the Sales Series.  Under database views, scroll down to the bottom and expand Views and Companies.  Mark the checkbox for view_SOPReturnsNegative.  Click OK to save your changes.

The result is the query will return negative amounts for documents with a SOP Type of Return.

RSM offers access to Certified Microsoft Professionals, help desk and phone support, knowledge and experience with third-party products and dedicated account management.  To learn more about who we are, go to:  http://rsmus.com/who-we-are.html or contact our professionals at erp@rsmus.com or by phone at 855.437.7202.

Nancy Hogan