Dynamics SL AR Transactions Quick Query Gives Me Duplicate Results
Posted by Tiffany Bennett, Senior Consultant on March 21, 2017
The AR Transactions Quick Query that comes out of the box with Microsoft Dynamics SL 2015 has an issue. It will report duplicates or even more, depending on your data. The actual underlying view must be modified to correct this.
To do so, log into SQL Management Studio. Then expand the Databases node, and expand the database that has the issue. In this example, we are using SLDEMOAPP10:
Now expand the Views folder and find the QQ_artran view:
Right-click on the QQ_artran view, and select Script View as, Drop and Create to, New Query Window:
Scroll to the bottom, and change the Join part from this:
From Artran a with (nolock) INNER JOIN customer c with (nolock) ON a.CustID = c.CustId INNER JOIN Ardoc b with (nolock) ON a.batnbr = b.batnbr
(modifying the second join to add Refnbr and CustID, and changing to a RIGHT OUTER JOIN):
From Artran a with (nolock) INNER JOIN customer c with (nolock) ON a.CustID = CustId RIGHT OUTER JOIN Ardoc b with (nolock) ON a.batnbr = b.batnbr and a.refnbr = b.refnbr and a.custid = b.custid
Click Excecute, to recreate your view. Now, when you run your AR Transactions Quick Query it will no longer report duplicates.
If you have further questions, please email our Dynamics SL experts at Support@Crestwood.com.