Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Running report error
Microsoft Dynamics GP (Archived)

Running report error

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

If we currently run a pending trial balance for Payables, if we don't touch the date, things go fin. If we back-date the reporting to the end of last month, we get errors like the one attached. Attached are the errors that we are getting. I've rebooted the SQL server prior to this and tried running an aged history, but still get the same thing. The # with the error is associated with changes all the time, but it's always the same vendor.

I found some coding to run duplicate transaction searches , but while trying to run them, they don't run.

Everything I've looked at points to an issue with the table and duplicate lines.

1651.Error-1.jpgerror-2.jpg

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Running report error

    Alright, I am finding the 2 instances from what the results show., How do I delete one of them now? I can't even see which tables they are in.

  • Suggested answer
    iruser2 Profile Picture
    iruser2 2,046 on at
    RE: Running report error

    the original query was....

    — PM Duplicates

    select DOCTYPE, VCHRNMBR, COUNT(*) as [COUNT] from

    (

    select DOCTYPE, VCHNUMWK as VCHRNMBR from PM10000 W

    UNION ALL

    select DOCTYPE, VCHRNMBR from PM10300 P

    UNION ALL

    select DOCTYPE, VCHRNMBR from PM10400 M

    UNION ALL

    select DOCTYPE, VCHRNMBR from PM20000 O

    UNION ALL

    select DOCTYPE, VCHRNMBR from PM30200 H

    ) C

    group by DOCTYPE, VCHRNMBR

    having COUNT(*) > 1

    so now that you know the invoice number do

    select * from PM10300 where docnumbr = ......

    then pm 10400 then pm20000 then pm30200

    you are likely going to find it on the 20000 and 30200 given that you are trying to run a trial balance

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Running report error

    It does return an invoice number. Not sure what you're saying by "run the select scripts one by one and add where docnumbr = 'your invoice number'"

  • Suggested answer
    iruser2 Profile Picture
    iruser2 2,046 on at
    RE: Running report error

    if the script returns an invoice number then it is telling you it is on two tables.  

    run the select scripts one by one now and add

    where docnumbr = 'your invoice number'

    you should find the transaction is on two of

    1x work

    2x open

    3x history

    You have to figure out which is right, i.e. does payables balance if not does open need to change to balance or does history need to change to balance?

    Do a back up before the remove - just in case

    the remove is

    delete table name where documbr = 'your invoice number'  be careful as invoice number is unique only to the vendor, so you may need to include vendor number or dex row id, to get exactly the one you want and make sure the where is there, otherwise you delete everything

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Running report error

    Remove it even.

    It gives the vendor and document number.

    As stated in the previous post that there was a credit memo applied to an invoice. The invoice was short paid the credit memo amount, but the credit memo still exists.

    Invoice # 125815 is showing as closed (paid in full between the check and credit memo).

    Everything looks proper here.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Running report error

    I figured out how I was going wrong with the code from above that gets the "duplicate" shown. How do I remote it though?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Running report error

    correct, I'm using SQL Server Management Studio.

    The database is correct as well.

  • RogerRogerATX Profile Picture
    RogerRogerATX 1,515 on at
    RE: Running report error

    The queries get run against a database...I assume you are using SQL Server Management Studio?  The database is listed in the upper left hand corner below the upper menu (and usually on the second toolbar)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Running report error

    It is the GP table I'm running your coding against.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Running report error

    I've tried that coding already for a Query and get the following:

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '—'.

    Msg 102, Level 15, State 1, Line 25

    Incorrect syntax near '—'.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans