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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Fix 3 decimal place error
Microsoft Dynamics GP (Archived)

Fix 3 decimal place error

(0) ShareShare
ReportReport
Posted on by Bob Harrison

We have our currency set to 2 decimal places. However, a transaction was posted that had a half cent. We need to correct so that our reports are correct, which are wrong because of rounding to 2 places. Here is how I plan to fix it. Will this work?

1. Change the decimal places to 3 in the currency setup.

2. Post a GL transaction with a half cent to fix the error.

3. Use a SQL script to change the decimal places back to 2.

I could edit the SQL tables to remove the half cent, but I'm not sure if I would find all the places that need to be changed. So doing it this way would be easier.

*This post is locked for comments

  • Frank_Heslin Profile Picture
    Frank_Heslin on at
    RE: Fix 3 decimal place error

    Bob, what type of transaction was entered originally?  This is needed to determine what tables to look in.

    I've had to do this a few times and I do it using sql.  

  • Bob Harrison Profile Picture
    Bob Harrison on at
    RE: Fix 3 decimal place error

    It was imported as just a GL transaction from our medical billing software. It happened twice. The first time was years ago, but it never showed up because two accounts both were rounding up, so it balanced on all reports. Then it happened again more recently. Both times it affected Retained Earnings at the end of the year, so now it shows up because Retained Earnings is not rounding, but now two A/R accounts are rounding up making the Trial Balance off by a penny.

  • Suggested answer
    Frank_Heslin Profile Picture
    Frank_Heslin on at
    RE: Fix 3 decimal place error

    If it originated in the GL then there are a lot less tables to look at.

    These are the tables I would look in:
    I assume it's posted, but if not look in these tables:

    GL10000 & GL10001 - Transaction Work/Unposted Header & Detail

    If it's Open look in these tables:

    GL20000 - Open Year Posted Transactions
    GL10110 - Open Year Summary Master (This holds the Period summary balance for each account)
                         Just in case: use the ACTINDX to join to GL00105 Account Master Index

    If it's in History look in these tables:

    GL30000 - Historical Year Transactions
    GL10111 - Historical Year Summary Master (Same deal as GL10110)


    Here is a script you can use to find the transactions: ( I found this online somewhere but I don't recall where.)
    I've used it on multiple occasions with great results.  You just have to change, add, or remove the field names based on which table you are looking in.

    SELECT  DEX_ROW_ID [GL20000],  * FROM GL20000
    WHERE FLOOR(DEBITAMT*100)!= DEBITAMT*100
    OR
    FLOOR(ORDBTAMT*100)!= ORDBTAMT*100
    OR
    FLOOR(CRDTAMNT*100)!= CRDTAMNT*100
    OR
    FLOOR(ORCRDAMT*100)!= ORCRDAMT*100


    That should get you there.

    Let us know how it turns out.

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…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans