Project Accounting: Update Budget Lines - the good and the ugly

The other day a client asked me if there was a way to mass-add a new cost category to all projects at once.  I actually didn’t know there was a new function to do this starting with GP 10, but a quick google search resulted in a post from Mark Polino describing this exact thing.

Wow.  That doesn’t happen every day… I was thinking of the alternatives I would explain the client, but was really happy to see this added functionality.

Until… today, after the client used the new window, and rolled down a new cost category to every project they have, they found a problem. Uh oh!

This particular article relates to the Project Accounting module in Dynamics GP, and the Update Budget Lines window under the Routines menu.

What’s great about this feature

There are a number of things that are great about this, and for most clients, it will work exactly as expected.  Keep reading for the situation my client found, before you attempt using it in case this affects you as well.

• The tool is simple. Type in or select a cost category, it shows all the projects that don’t yet have that on it (yes, it filters the list… I tested earlier today and the projects in this screen shot are the projects I DIDN’T add the cost category too this morning).

•  You can update both projects and templates, if you use them.
• It’s quick. The whole point of this is to quickly update project budgets with new cost categories, not to have to go through the trouble of manually updating a lot of things one at a time.

What’s not so great about this feature

The reason my client can’t use it anymore - or not without my help to run some scripts in SQL afterwards - is they have at least one default account type Source on every transaction type set up as “Specific” with a default account that should roll down to every and any project they create.  When using this tool, for any account sources that are set to specific, the related cost categories get added to the project but the default account does not.  Here is a screen shot of the Project Setup default posting account sources window: (notice one type on Misc Logs is set to Specific, and a default of 999-9999-99).

Here is a screen shot of one of the projects I updated to write this article: (specific is there, but no default account).  This window by the way, is all the way buried in the Budget, Budget Detail Entry, Accounts.

What does that mean? Well, it means you need to get a script to insert some records into SQL directly (never recommended) or you have to go to each project manually to update the account.  Aaaaaand, this is far more work than going to each project manually to just add the cost category in the first place because that, at least, is less clicks to perform the task.

End Result

I believe this is a bug and am trying to determine that with Microsoft right now. In the meantime, I wrote a script to add the records to the PA43001 table which stores the accounts for sources that are “specific”.  The tables are not very easy to navigate due to the complexity of scenarios that Project Accounting can handle. Trust me when I say, I wouldn’t want to have to do that very often. I’m not a fan of inserting records into tables via script, but in this case there were over 650 projects to update and they simply did not have the time to do this manually.