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:
- 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],
‘Document Amount’ = CASE [SOP TYPE]
THEN [Document Amount] * -1
ELSE [Document Amount]
[Commission Applied To],
[Commission Sale Amount],
WHERE([SOP Type] <> ‘Quote’)
AND ([SOP Type] <> ‘Invoice’)
AND ([SOP Type] <> ‘Back Order’)
AND ([SOP Type] <> ‘Fulfillment Order’);
— add permissions for DYNGRP
grant select on view_SOPReturnsNegative to DYNGRP
- 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 firstname.lastname@example.org or by phone at 855.437.7202.