web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / duplicate lines on inv...
Microsoft Dynamics SL (Archived)

duplicate lines on invoices

(0) ShareShare
ReportReport
Posted on by Community Member

What causes duplicate lines to print on invoices?  We have a client that has the issue every month with a certain type of invoice.  Sometimes the lines print twice and sometimes they print three times.

I run the following script to fix the error to remove the stray records:

DELETE from RPTRUNTIME on SYSTEM DB and DELETE from RPTCOMPANY & DELETE from RPTRUNTIME on APP DB.

We are trying to find out why this issue keeps occurring.

*This post is locked for comments

I have the same question (0)
  • Barry Flynn Profile Picture
    Barry Flynn 3,090 on at

    Chuck

    I have seen that happen in reports where there is a Join to RptRunTime or RptCompany.

    Because there are "no useful columns" it is usually a Cross Join - it joins to all RptRunTime records.

    And consequently, if "nothing is done about that" you will get lines printed twice, or three times, or worse.

    You can test if this is your problem by:-

    - Fire up any report "to the screen". Doesn't have to be youir invoice report.

    - Run your report. Are the lines doubled-up?

    - Close your report.

    - Run a second unrelated report to the screen.

    - Run your report. Are the lines tripleded-up?

    (Note that in this test, the "other reports" must print "to the screen" and you must leave that previiew on the creen. That ensures that RptRunTime will remain populated by "other records" while your invoice report runs.)

    The cure for this is that either the pre-process or the report Record Selection must select on RI_ID.

    That "eliminates" the Joins to unwanted RptControl or RptCompany records, and gets you back to the "single set of records" that you want.

    Hope those ramblings are ay least slightly comprehensible.

    Barry

  • Community Member Profile Picture
    Community Member on at

    Barry

    Thanks for the quick feedback.  I tested your theory and it is correct.  How do I stop this from happening again?  I don't understand the "the cure...., etc.

    Thanks,

    Chuck

  • Barry Flynn Profile Picture
    Barry Flynn 3,090 on at

    Chuck

    OK ...

    The problem is that you are (initially) joining to multiple RptRunTime and/or RptCompany records.

    That doubles-up (or worse) the rows that emerge from the Join.

    Its like, say

     select Name from Customer join ardoc on ardoc.custid = customer.custid order by customer.custid

    You get at least some names repeated several times, because their Customer record joins to multiple ARDocs.

    That's the sort of effect that you are getting, with your "prime tables" (whatever they may be) joining to multiple RptRunTime and/or RptCompany records.

    You need to, at some point, eliminate those unwanted TptRunTime/RptCompany records.

    You can do this using RI_ID - that field is unique in RptRunTime.

    And, from memory, the combination RI_ID/CpnyId is unique in RptCompany.

    One way is to use a pre-process.

    A significant number of standard SL pre-processes appear to exist solely for this reason - they append something along the lines of "and RptRunTime.RI_ID = 2" to RptRunTime.RI_Where.

    That causes the unwanted RpptRunTime records to vanish.

    And, if RptCompany is involved, a join from RptCompany to RPtRunTime on RI_ID should eliminate unwanted RptCompany records too.

    I'll pause for breath at this point...

    Barry

  • Barry Flynn Profile Picture
    Barry Flynn 3,090 on at

    Ctd...

    If you are not comfortable with an SQL Pre-Process approach, then an alternativr is to do it in the Record Selection area of the Crystal report.

    The general appoach is to:-

    - Have a formula which uses RIPARAM to uplift the RI_ID. This uplifts "your RI_ID".

    - Then in the Record Selection you specify that RptRunTime.RI_ID = the RI_ID that you obtained from RIPARAM.

    There's at least one trap there.

    You have two RI_ID's - one from RptRunTime and one from RIPARAM.

    And, from dim dim memory one is a String & one is an Integer.

    So, somewhere along the way you probably need to convert one of them to be the same datatype as the other.

    Hope those ramblings help.

    My memory is vague about the exact syntax, but hopefully my rambles can point you in the right direction.

    Barry

  • Community Member Profile Picture
    Community Member on at

    Barry

    Sorry I am just responding to your posts today.

    I found the following solution posted by Carolyn Kolpien and I think it worked for an AR Invoice print that was duplicating.

    Revised solution:

    Here are the steps:

    1) Open Crystal and select the Invoice/Memo report (08760).

    2) Click on the Database menu and select Database Expert.

    3) Click on the Links tab.

    4) Remove the link from ARDOC and RPTCOMPANY.

    5) Create a link from ARPRINTQUEUE.CPNYID = RPTCOMPANY.CPNYID

    6) Create a link from ARPRINTQUEUE.RI_ID = RPTCOMPANY.RI_ID

    If you agree with this solution how would you fix a GL Transaction custom report that is having the same issue?  

    Chuck

  • Barry Flynn Profile Picture
    Barry Flynn 3,090 on at

    Chuck

    I suspect that Carolyn's post referred specifically to the AR Invoice report.

    She refers to the ARPrintQueue table, which I think is specific to that report (and maybe a few close relations.)

    If you are using that table, then it is definitely worth checking out the Joins as she suggests.

    << how would you fix a GL Transaction custom report that is having the same issue?  

    Sounds like a bad join.

    You can check whether RptRunTime abd/or RptCompany are a likely cause by:-

    - Run your report while no other report is being run. Is it doubled up?

    - Run it while one other report is being run. Doubled up?

    If the 1st is OK, but the second is doubled-up, then it sounds like you are joining to RptRunTime abd/or RptCompany, but are not (somewhere along the way) filtering on RI_ID.

    If RI_ID does not look like the culprit, I suggest running the report as a preview (so that all temp tables remain populated as long as the preview is on the screen) and use Profiler to capture the SQL.

    Then try running that SQL youself.

    Do you get doubled-up results?

    If so, you can experiment with that SQL to try to find the reason.

    Hope that helps.

    Barry

  • Community Member Profile Picture
    Community Member on at

    Barry

    Thanks for the detailed response.

    I will let you know if that solves our issue.

    Chuck

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans