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

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) / Payroll check deductio...
Microsoft Dynamics SL (Archived)

Payroll check deduction duplicating

(0) ShareShare
ReportReport
Posted on by Community Member

Running live payroll checks. The detail that prints on the face of the check is duplicating every line. The totals all add up correctly.

I've pulled queries from PRCheckTran and nothing is duplicated there. Looked at the check preview report and no duplication. Just the front of the check.

My first assumption is that there is a form issue; but our IT manager who manages those state that it is the same form that our other entity uses where no problems exist. And no form changes have occurred recently; and this is a new problem.

Anyone run into this issue previously?

Thanks,

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    When you see something like this it is typically caused by a duplicate record in a supporting table that is joined to the main record of the report.  In this case, PRCHECKTRAN joins with the Deduction table and with the CalcChk table so I would examine both the CalcChk table and the Deduction table for duplicates.  Please be aware that the Deduction table has entries for each calendar year.  So, when looking for duplicates, you must look at the Dedid field and the CalYr field.  However, it is not likely that the Deduction table is your problem because of the unique key structure on that table so I would focus on the CalcChk table.  Note: this table should be empty after checks are kept (similar to PRCheckTran).

  • Community Member Profile Picture
    Community Member on at

    pic.png

    Thanks for the response; checked both tables; including the CalcChk detail table just in case and didn't see any duplication. Please see the screen shot of the check for reference; maybe it includes a detail I'm not describing.

  • Community Member Profile Picture
    Community Member on at

    That section of the check joins PRCheckTran, CalcChk and Deduction so the duplication has to be in one of those 3 tables and, yet, you have indicated that PRCheckTran does not have duplicates and now you are saying the other two tables do not either.  We are missing something here.

    Try running the following query and see if duplicates show:

    select * from prchecktran inner join deduction on deduction.dedid=prchecktran.dedid inner join calcchk on calcchk.empid=prchecktran.empid and calchk.chkseq=prchecktran.chkseq

    If this does not show duplicates then we know the database is fine and that the issue is, somehow, with the report definition.  Which is refuted based on you statement that other entity uses the same report (unless, of course, the other entity is not on the same system).

    The only other tables that are involved are Country and Currncy tables (other than Employee and Batch) but those tables are not joined with PRCheckTran.

    One additional question: is this happening with all employees in all payrolls?  And, what check format are you selecting?  (OK, two questions).

  • Community Member Profile Picture
    Community Member on at

    Ah you are correct, there is duplication when I do that. I was looking at the tables individually and not how they were linked. When I joined them the way you stated it did show up.

    What is the best method to remove the duplication?

  • Community Member Profile Picture
    Community Member on at

    First you need to determine which table is causing the duplicates.  The query I suggested shows all the fields from all 3 tables.  So, look at a duplicate two lines and find which table has different values between the two lines.  For example, if the deduction table is the culprit then the fields for prchecktran and calcchk will be the same on both lines but one or more fields in the deduction table will be different.  Once you determine the table and the field(s) that are different then we can create a delete query to delete one of the two lines.  This must be done with care so that it does not delete both lines so that is why we need to determine a field that is different.

  • Community Member Profile Picture
    Community Member on at

    All fields between the two duplicated lines match...let me know if I messed up the joins:

    WHERE CalcChk.ChkSeq = PRCheckTran.ChkSeq AND CalcChk.EmpID = PRCheckTran.EmpId AND PRCheckTran.Col2Id = Deduction.DedId

  • Community Member Profile Picture
    Community Member on at

    Your join looks correct (my initial query had the wrong field name in prtran for joining to deduction so good catch there).

    There has to be at least one field that is different in order for this query to duplicate records or something is wrong with the indexes on one of the tables involved (not likely but cannot rule that out).  We could use spHelp on each of the tables and look at the defined indexes on the table to see if they are correct but I would like you to look again for a different field content.  To help some, you could add some additional where conditions to select only one resident ID and one deduction ID so you have just those records in you results.

    You know, I am looking at the data source and links for the report and I just realized that the prchecktran table joins the deduction table by using the Col2Id field only but the deduction table has rows for each calendar year so now I am wondering why there would not be an issue when you have had payroll active for more than one calendar year.  It seems like that join would find records for more than 1 calendar year.  I then looked at the filter in the report and that adds where deduction.calyr=the calendar year of the check so that means, in order to emulate the report you would have to add to your where clause deduction.calyr=2017.

  • Community Member Profile Picture
    Community Member on at

    We only started using SL in 2017; so there shouldn't be prior year records in there yet. Yea, I was trying to wrap my brain around why there would be two rows with identical information...but I ran a formula to see if they matched in case I was overlooking a difference and they are identical.

  • Community Member Profile Picture
    Community Member on at

    Sorry, ignore that; you are right. It wasn't active then but there were 2016 rows in the deduction table....I added that criteria and the duplication disappeared....

    So I'm thinking it has to be the form?

    Thanks for all the support on this!

  • Community Member Profile Picture
    Community Member on at

    So, if the query is not showing duplicates then I agree the next focus is on the report definition.  Perhaps the filter in the definition is not checking the calyr field in the deduction table.  If you have Crystal reports loaded you could pull up the 02.630 report (assuming you are using laser check by employee ID format) and look at the filter definition for that report.  You can also look at the table joins using database expert and the link tab.

    You did not provide further detail on whether the working entity was using the same check format, was on the same system and was in the same database or not.  We may need to alter our approach based on that information.

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February 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