wrenchguy

Change GP fiscal period names using SQL Server


Every time I create a new fiscal year in Dynamics GP or show customers how to do it, I get a little frustrated that I have to manually rename all the periods. Some users may not care that instead of January GP displays Period 1, but I don’t think that is very user friendly. So, for more years than I sometimes care to admit, I have been manually changing all the period names on the Fiscal Period Setup window. Well, no more.

The SQL code below can be used to rename the fiscal periods for a calendar fiscal year. If you have something other than a calendar fiscal year, you can easily modify this to suit your needs. And if you have different fiscal periods each year, you can add a filter specifying the year to the WHERE clauses, although at that point, unless you are updating many companies, it might not be worth it.

update SY40100
set PERNAME = 'January'
where PERNAME = 'Period 1'

update SY40100
set PERNAME = 'February'
where PERNAME = 'Period 2' 

update SY40100
set PERNAME = 'March'
where PERNAME = 'Period 3'

update SY40100
set PERNAME = 'April'
where PERNAME = 'Period 4'

update SY40100
set PERNAME = 'May'
where PERNAME = 'Period 5'

update SY40100
set PERNAME = 'June'
where PERNAME = 'Period 6'

update SY40100
set PERNAME = 'July'
where PERNAME = 'Period 7'

update SY40100
set PERNAME = 'August'
where PERNAME = 'Period 8'

update SY40100
set PERNAME = 'September'
where PERNAME = 'Period 9'

update SY40100
set PERNAME = 'October'
where PERNAME = 'Period 10'

update SY40100
set PERNAME = 'November'
where PERNAME = 'Period 11'

update SY40100
set PERNAME = 'December'
where PERNAME = 'Period 12'

As with any code, please use this carefully. If you’re not sure, make backups and test somewhere other than your live company.

15 Responses to “Change GP fiscal period names using SQL Server”

  1. Hi Victoria,
    I have 70+ companies in my instance of GP and am wondering if there is a way to mass create fiscal periods for all my companies. Could I just create the 2013 periods in one of my companies then copy the table SY40100 to the other 69 companies? Is there a better way?

    Like

    • Hi John,

      I have never done this in SQL, so I don’t know if there would be any issues with this or a better way. At the very least you would also need to update the SY40101 table. I would maybe recommend posting this on the GP Community Forum to see if anyone has done this in the past.

      -Victoria

      Like

  2. Hi Victoria,

    Just curious if you’ve ever used two different kinds of fiscal periods in one year before (such as monthly for six months and bi-weekly for six months)? I’m looking into this for a client who has a seasonal business. I think GP will let us do it, but I’m not sure how smart it is. They need bi-weekly financial reporting during their season. However, it always has to be Sunday-Sunday, for example, so even if we do the whole year with bi-weekly periods, there’s going to be an awkward cut-off any time their year doesn’t also end on a Sunday (6 out of every 7 years). The good news is that the year-end is in the off-season, so those reports are less consequential.

    I’d be really interested to hear if you have any thoughts in this regard.

    Kimberley

    Like

    • Hi Kimberly,

      I have never done this before, however this should work with no issue in GP, at least on the technical side. I would think this can get pretty confusing for the users, though, so I would be careful with a change like this – maybe do a little test in a test/sample company first?

      -Victoria

      Like

      • Hi Victoria:
        I am trying to perform a 2nd year end close for 2012. My current setup as follows:
        Historical: 2012 from 7/1/2011 to 6/20/2012
        Open 2013 from 7/1/2012 to 6/30/2013
        Open 2014 from 7/1/2013 to 6/30/2014

        We need to end up with:
        Historical: 2012 from 7/1/2011 to 6/20/2012
        Open old2013 from 7/1/2012 to 12/31/2012
        Open 2013 from 1/1/2013 to 12/31/2013

        I tried using the Fiscal Modifier tool with no success. I do not have the option of extending year 2012, or extending 2013.
        I have also closed months 7,8,9 &10

        I am not sure what fiscal year definition I can use for old2013

        Your input would be greatly appreciated.
        Alan

        Like

        • Alan,

          GP does not allow anything other than year numbers in the Fiscal Year Name. So there is no way to have ‘old2013’ or anything like that. They also have to be unique, so you cannot have 2 2013’s. The only option is to ‘rename’ all your years going back so that 7/1/2012 – 12/31/2012 becomes 2012 and 7/1/2011 – 6/30/2012 becomes 2011, and so forth using the Fiscal Year Modifier.

          -Victoria

          Like

  3. Another option is to create a macro you save in your shortcut bar and each time you create a new calendar, and while still in the Fiscal/Financial Periods Setup form, simply execute the macro. You can also use the same process for Tax Calendars.

    Like

    • Kristie,

      I have to say, I am not a big fan of Macros…sometimes they work, sometimes they don’t…I often see people spending just as much time troubleshooting and creating Macros as they actually save by using them.

      You do make a good point about the tax calendars – many people do not use them, but those that do can update the SY40102 table the same way as I show updating the SY40100 table.

      -Victoria

      Like

      • I agree and I only use macros for the simplest of updates, calendars and exchange tables and use SQL for pretty much anything else. But the calendar update macro has proven to be, for me, a one time setup that I can run against any of our 50+ databases – it’s been a life saver for sure, but now I also have this script and it’s always good to have choices. Thanks for all you do Victoria, your site is a GREAT resource.

        Like

  4. Stephanie Burkhard Reply January 27, 2011 at 8:11 am

    Good morning Victoria,

    This is awesome information! I never considered changing the period names before this post, so thanks for the advice. I need a little more help though since I’m nowhere near being an SQL expert. We were recently acquired by another company and our fiscal periods changed. We have multiple companies setup in GP and I was able to change the others because the periods stayed the same. I’m now trying to just change the periods for 2011 in our main company, i.e., November = Period 1 for 2011, December = Period 2 for 2011, etc. You said you can add a fiter specifyng the year to the where clauses, but I received an error message stating Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword ‘where’.

    Here’s what I entered:

    update SY40100
    set PERNAME = ‘December’
    where PERNAME = ‘Period 2’
    where YEAR1 = ‘2011’

    Am I missing something? I don’t want to change all of my periods prior to 2011, so adding this filter is absolutely necessary.

    Thanks!

    Like

  5. Hi Victoria,

    That would be very handy! Although we have 13 periods a year on occasion so I guess I’d have 2 Decembers…

    Lindsay

    Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | Change GP fiscal period names using SQL Server « Victoria YudinChange GP fiscal period names using SQL Server « Victoria Yudin - DynamicAccounting.net - May 2, 2016

    […] I was travelling on Friday so I’m behind. Let’s catch up together! Victoria Yudin starts things off with a script to Change GP fiscal period names using SQL Server . […]

    Like

  2. DynamicAccounting.net - January 31, 2011

    Change GP fiscal period names using SQL Server « Victoria Yudin…

    I was travelling on Friday so I’m behind. Let’s catch up together! Victoria Yudin starts things off with…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: