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

To this:
(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.


Share this:
 
 

Comments:

Add your comment: