Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / PSTL Account format ch...
Microsoft Dynamics GP (Archived)

PSTL Account format change

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

Hi, I am posting it again because last time I could not get any satisfactory answer. My question is very simple. If I have an account format like XX-XXX, how can I change it to XXX-XXXX using PSTL. I would like to know the baby steps (i.e. each steps required to be done). Please note that I have a company account format setup as max length as 2 for the segment 1 and length of 3 for the segment 2. Can anyone please guide me the steps to be followed for this process? I have gone through many of the online postings about it and they are not working for me, which basically is telling me that something is missing from the steps I am following.

*This post is locked for comments

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: PSTL Account format change

    Sat,

    If you run this script, does everything come out alright?

    /*  This script will find incorrect account framework in all company databases in the

    GL10110, GL10111, GL70500, GL70501, GL00100, and GL00201 tables. */

    /*Script Revised: 4/21/2010*/

    /*Script Revised: 3/5/2013 - Added GL00100 and GL00201 tables.*/

    --COPY AND REPLACE DYNAMICS WITH THE NAME OF YOUR SYSTEM DATABASE

    --drop table ##Results

    create table ##Results (Results char(100))

    declare @dbname char(5), @TableName char(64), @ColumnName char(64), @statement char(1400)

    declare FindBadAccountFrameWork cursor for

    select b.INTERID,a.name,'ACTNUMBR%' from sysobjects a, DYNAMICS..SY01500 b

     where a.type='U' and a.name = 'GL10110' and b.INTERID in (select name from master..sysdatabases)

    union select b.INTERID,a.name,'ACTNUMBR%' from sysobjects a, DYNAMICS..SY01500 b

     where a.type='U' and a.name = 'GL10111' and b.INTERID in (select name from master..sysdatabases)

    union select b.INTERID,a.name,'STACCNUM%' from sysobjects a, DYNAMICS..SY01500 b

     where a.type='U' and a.name = 'GL70500' and b.INTERID in (select name from master..sysdatabases)

    union select b.INTERID,a.name,'EDGACNUM%' from sysobjects a, DYNAMICS..SY01500 b

     where a.type='U' and a.name = 'GL70500' and b.INTERID in (select name from master..sysdatabases)

    union select b.INTERID,a.name,'STACCNUM%' from sysobjects a, DYNAMICS..SY01500 b

     where a.type='U' and a.name = 'GL70501' and b.INTERID in (select name from master..sysdatabases)

    union select b.INTERID,a.name,'EDGACNUM%' from sysobjects a, DYNAMICS..SY01500 b

     where a.type='U' and a.name = 'GL70501' and b.INTERID in (select name from master..sysdatabases)

    union select b.INTERID,a.name,'ACTNUMBR%' from sysobjects a, DYNAMICS..SY01500 b

     where a.type='U' and a.name = 'GL00100' and b.INTERID in (select name from master..sysdatabases)

    union select b.INTERID,a.name,'ACTNUMBR%' from sysobjects a, DYNAMICS..SY01500 b

     where a.type='U' and a.name = 'GL00201' and b.INTERID in (select name from master..sysdatabases)

    union select b.INTERID,a.name,'STTACNUM%' from sysobjects a, DYNAMICS..SY01500 b

     where a.type='U' and a.name = 'IV70500' and b.INTERID in (select name from master..sysdatabases)

    union select b.INTERID,a.name,'EACCNBR%' from sysobjects a, DYNAMICS..SY01500 b

     where a.type='U' and a.name = 'IV70500' and b.INTERID in (select name from master..sysdatabases)

    order by b.INTERID,a.name

    set nocount on

    open FindBadAccountFrameWork

    fetch next from FindBadAccountFrameWork into @dbname, @TableName, @ColumnName

    while (@@fetch_status <> -1) begin

     set @statement = '

    declare @ActLenTable table (Segment char(15),SegLength smallint,tablename char(16), DEX_ROW_ID int identity(1,1))

    declare @numseg smallint, @x smallint, @rcount smallint

    select @rcount=count(a.name) from ' + rtrim(@dbname) + '..syscolumns a join ' + rtrim(@dbname) + '..sysobjects b on a.id=b.id

    where b.name=''' + rtrim(@TableName) + ''' and b.type=''U'' and a.name like ''' + rtrim(@ColumnName) + '''

    if (@rcount <> (select MXNUMSEG from DYNAMICS..SY003001))begin

     insert into ##Results (Results) select ''Database ' + rtrim(@dbname) + ' has incorrect number of Segments in table '

      + rtrim(@TableName) + ' with the ' + substring(@ColumnName,1,8) + ' columns.''

     end

    insert into @ActLenTable (Segment, SegLength, tablename)

    select a.name,a.length,b.name from ' + rtrim(@dbname) + '..syscolumns a join ' + rtrim(@dbname) + '..sysobjects b on a.id=b.id

    where b.name=''' + rtrim(@TableName) + ''' and b.type=''U'' and a.name like ''' + rtrim(@ColumnName) + ''' order by colid

    select @numseg=count(*) from @ActLenTable

    set @x=1

    while (@x <= @numseg) begin

     if (select SegLength from @ActLenTable where DEX_ROW_ID=@x) = (select SGMNTLTH from DYNAMICS..SY00302 where SGMTNUMB = @x) or

        (select SegLength from @ActLenTable where DEX_ROW_ID=@x) = ((select SGMNTLTH from DYNAMICS..SY00302 where SGMTNUMB = @x)+1) begin

       set @x=@x

       end

     else begin

       insert into ##Results (Results)

       select ''Database ' + rtrim(@dbname) + ', Segment '' + rtrim(cast(@x as char(2))) + '' has the incorrect length in table '

      + rtrim(@TableName) + ' with the ' + substring(@ColumnName,1,8) + ' columns.''

       end

     set @x=@x+1

     end

    '

     exec (@statement)

     fetch next from FindBadAccountFrameWork into @dbname, @TableName, @ColumnName

     end

    deallocate FindBadAccountFrameWork

    select * from ##Results

    drop table ##Results

    Kind regards,

    Leslie

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: PSTL Account format change

    Hi

    The GL10110 is the Open Year Summary Master

    The GL10111 is the Historical Year Summary Master

    The GL70500 is the GL Report Options

    The GL70501 is the GL Report Options Temp

    You might need to delete the tables and use SQL utilities to create new tables that will match the new account framework. None of these tables are critical, the data is easily replaceable. If the tables are empty, delete them and recreate them with GP's SQL Utilities.

    Kind regards,

    Leslie

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PSTL Account format change

    Hi Leslie,

    I would like to come back to you. We created a test server, migrated the Dynamics and Co. database there and changed the account framework (had to run the GP utility after changing the Dex.ini file structure to synchronise the new framework) and printed the trial balance and everything looks fine. Our structure looks good and we are happy.

    However, I would like to understand couple of things from you and this is from a risk assessment perspective - why does the account number in GL10110, GL10111 and GL00210 matter, once we have the proper account index in there? What is the use of GL70500 and GL70501 in Dynamics GP? We do not have anything in the IV table.

    Thanks,

    Sat

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PSTL Account format change

    Thanks Leslie for the valuable insight.

  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at
    RE: PSTL Account format change

    Hi everyone,

    Something I want to mention regarding changing the account framework using SQL. Something that supports NOT using it. It's not as easy as you think.  There are more tables in the company databases that contain the structure of the account framework. It's not just the sy00300. At a minimum the following tables also include the account framework:

    GL10110

    GL10111

    GL70500

    GL70501

    GL00100

    GL00201

    IV70500

    Each of those tables would also need to be modified.

    Kind regards,

    Leslie

  • Suggested answer
    drummerboy_10 Profile Picture
    drummerboy_10 15 on at
    RE: PSTL Account format change

    I concur with what iruser2 has noted.

    The solution to your issue is really three-fold:

    1.  If you're just implementing Dynamics GP, then I would scrap the initial install, start over, and add padding to your account framework to add growth to  your chart of accounts.  The decisions you make during the initial setup is crucial because, as it's already been pointed out, it affects every company you create in Dynamics GP.
    2.  You could go to your VAR and ask for their assistance, but having worked for a VAR before, they're going to tell you to go with CRG's Re-Formatter.   I don't think they would touch this if you're already several weeks or months or years into using Dynamics GP.
    3.  Purchase CRG's Re-Formatter and accomplish your goals with minimal impact to users and systems. 

    You're asking solid questions, but sometimes the answer simply is to buy the tool to do the job.   

  • iruser2 Profile Picture
    iruser2 2,046 on at
    RE: PSTL Account format change

    You can't have segment 1 and 2 have a field size of zero.

    You can have blanks but.....  Think about how stupid computers sort and filter.  They really don't like blanks.  Your best bet would be to have zero's in segment 1 and 2.

    How far into GP are you?  How much data would have to be converted / re imported? Can your VAR assist you in exporting and reimporting things in an economic manner?  Should you consider reinstalling GP to better answer the account framework question?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: PSTL Account format change

    Thanks Sean and Ir. It really helps me.

    I am just working with the test data right now which is a copy of the production company. I have just one more thing to clarify - cannot I use the last 2 segments for my new structure where the length of the segment is enough to accommodate my new structure and get rid of the first 2 segments completely? Can this be done?

    Thanks once again,

    Kumar

  • drummerboy_10 Profile Picture
    drummerboy_10 15 on at
    RE: PSTL Account format change

    Ha ha!  I agree given the similarities in our responses!  

  • iruser2 Profile Picture
    iruser2 2,046 on at
    RE: PSTL Account format change

    I think we were both typing essentially the same reply at the same time Sean.

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,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans