Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Management reporter an...
Microsoft Dynamics GP (Archived)

Management reporter and unusual accounting accounts

(0) ShareShare
ReportReport
Posted on by Alvaro Rodríguez Ochoa 2,395

Hi all, we are trying to strt using MR for GP 2013, but our accounting catalog is quite "unique" our accounts are 4-4-2 format, but sometimes we dont use the last 2 digits, but we could use the last 2 digits for another account that isn't a subaccount of the one with out the last 2 digits.

IE-
5400-1900 - profesional service
5400-1900-03 - products service

If I go into MR and type my account 5400-1900 it automatically brings also 5400-1900-03 account and adds it, is there a way I can prevent this from happening?


Regards.

*This post is locked for comments

  • Alvaro Rodríguez Ochoa Profile Picture
    Alvaro Rodríguez Ochoa 2,395 on at
    RE: Management reporter and unusual accounting accounts

    If I do it as you say as a string I get a duplicated key error with unique index AK4GL00100

  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Management reporter and unusual accounting accounts

    Alvaro

    Amend your update statement to be as below;

    update GL00100 set ACTNUMBR_3 = '00' where ACTNUMBR_3 = ''

    It is a string, not a number

    Additionally, when you run such an update on a critical table like GL00100 and GL00105, consider a unique parameter in the "Where" statement such as the Account Index or Dex row id.

  • Alvaro Rodríguez Ochoa Profile Picture
    Alvaro Rodríguez Ochoa 2,395 on at
    RE: Management reporter and unusual accounting accounts

    Hi, I did an update via sql server with this query:

    update GL00100 set ACTNUMBR_3 = 00 where ACTNUMBR_3 = ''

    also on table GL00105 just as mahmoud suggested, but now if I look in my account intead of having -00 as the last segment they only have -0, any ideas?

    Also I tried PSTL but couldn't figure out how it works, shoud I select range -> subaccount 2 current value '' New value 00?

  • Verified answer
    sueconrod Profile Picture
    sueconrod 335 on at
    RE: Management reporter and unusual accounting accounts

    I totally agree with Mahmoud, when I was a consultant I was constantly going in and cleaning up the COA's from other consultants who had decided you could put blanks in the COA's and even at the end.  And then lo and behold you went to do financials and they didn't come up with the right results.  I would recommend that you go with best practices as Mahmoud is suggesting.  This is your financial reporting and if it is seeing a blank you may not get information for that dimension.

  • Alvaro Rodríguez Ochoa Profile Picture
    Alvaro Rodríguez Ochoa 2,395 on at
    RE: Management reporter and unusual accounting accounts

    Thanks iruser2 thos helped!, will have to do it with other accounts, thanks again

  • Verified answer
    iruser2 Profile Picture
    iruser2 2,046 on at
    RE: Management reporter and unusual accounting accounts

    I have worked with 3-6-6-6 where segment 4 has been blank for all accounts except sales and cost of sales (segment 4 was the item number).

    MR had no issue with this.

    a blank segment is actually filled with spaces.  MR for the most part can recognize spaces.  You can put them on trees in row definitions.  

    I do try to avoid blanks in the middle of account strings but at the end is fine.

  • Alvaro Rodríguez Ochoa Profile Picture
    Alvaro Rodríguez Ochoa 2,395 on at
    RE: Management reporter and unusual accounting accounts

    Thanks once again Mahmoud, will tell the accounting guys this.

    Regards

  • Verified answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: Management reporter and unusual accounting accounts

    The best practice is to use a general segment for each of the three sections of your account structure, which could be accordingly

    • Segment One | 0000
    • Segment Two | 0000
    • Segment Three | 00

    Since your problem is primarily represented with the last account segment, I believe your best scenario is to go through the accounts included in your COA to add a general segment (00) for all the accounts that doesn't have a third segment. That could be simply achieved through the "Account Modifier" tools included in the PSTL library. It allows you to modify the account

    Another option is to add the account segment on the SQL level to both GL00100 and GL00105 and then run the financial check links, this is quite safe and I have previously applied such a work around.

    Furthermore, in case you don't want to go through the COA and change account numbers, you can consider another criteria in your MR reports, which is the account category. Amend the account categories according to your business needs and proceed with the MR reports (row definition) to be built based on the account category.

    I would personally consider the best practice is to adjust the account number to have a general segments (00) in case the segment is not used. This will provide further more flexibility in building your reports.

    Please never hesitate to share any further inquiries,

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