Tuesday, September 28, 2010

Dynamics Database. Company Information.

So, first, I have to say that Steve has been putting me to shame with his prolific blog posts the past few weeks. Believe it or not, I am alive and working. Busy but not crazy, just enjoying the changing of the seasons here. We have had some lovely fall weather the past week, a nice little break before the reality of winter sets in.

In an attempt to challenge Steve's dominance of the blog lately, I thought I would share a reminder of sorts as it relates to the Dynamics database. I know I am guilty of sometimes minimizing the significance of the Dynamics database, particularly when it comes to what version of the database is restored if there is a system failure. Of course, I remind the client that if security settings, yadda, yadda, yadda have changed since the backup then they will lose those changes. But, generally, security settings are not changed all that often and the difference between restoring a Dynamics database from this week versus last week is often minimal if a difference exists at all.

But today I received a reminder of the importance of the Dynamics database when it comes to company setup. Yes, I said company setup. Of course, the vast majority of company setup information is stored in each company's database. But there are a few critical pieces that are actually stored in the Dynamics database. So, if your restore point is prior to when those setup pieces were completed for a company, then you will indeed have issues with that company.

  • Company Master (Setup>>Company>>Company): If you restore the the Dynamics database to a point prior to when the company was actually created, it will not be part of the Company Master table and therefore will not be available for selection when logging in or defining security. The best route here (in my humble opinion) is to run through GP utilities to create the company, and then just restore a backup of the company database over the new empty company created. This ensures that all system tables are properly populated with the company information. However, this approach does not mean that your original company settings stored in the Dynamics database would be recovered. You would still need to return to Setup>>Company>Company to complete the setup, and don't forget the critical Options window. The company setup options can have significant impact on how the company functions with regards to taxes, distributions, and other items.
  • Multicurrency Access (Setup>>System>>Multicurrency Acccess): This stores the company access to specific currencies and exchange tables. You might have all of your multicurrency setup completed, but if this piece does not exist in the Dynamics database you will run in to multiple errors trying to use multicurrency. As this is a pretty simple access window, setting it up manually if the data is missing is usually the simplest solution.
  • Intercompany (Setup>>System>>Intercompany): If used, this defines the intercompany relationships for Dynamics GP. Again, this is a pretty simple setup window, so the quickest solution is to re-enter the configuration manually if the data is missing for the company.
  • Security (Setup>>System>>Security Roles, Security Tasks, Alt/Mod Forms and Reports, User Security): As I mention above, any security defined for the new company will be missing, if you restore the Dynamics database to a point prior to the set up being completed.

Well, that's my list of items that are sometimes forgotten when restoring a Dynamics database to a point earlier than the creation/setup of a new company. Of course, let me know if I missed anything or if you have any tips/tricks to share.


Anonymous said...

Hi Christina:

Great post! I would add one field that is often overlooked, and is updated VERY frequently: the NOTEINDX field on the SY01500 table.

This field is incremented with every transaction and master record created that includes a "Sticky Note" on a field. It is used to prevent duplicate references to notes to be stored on these records.

I've seen a few clients where, for some reason or another, they have notes on fields and windows which are for something completely unrelated to that field - In most cases it is because the note index on that field is a duplicate.

It's a very good idea (I'd say important, especially if your client uses sticky notes a lot) to save the SY01500 table to a spreadsheet prior to restoring from backup, and then updating the NOTEINDX field to its previous value for each company.

Christina Phillips said...

Excellent point! I have indeed been burned by that one as well. And it becomes even more important if you added a company in one environment, add master records (and notes), and the restore to another environment without updating the Dynamics database. Thanks for the great reminder!

Mariano Gomez said...

Don't forget SmartList and SmartList Builder lists, user settings as it relates to preferences, navigation bar changes, toolbar changes, home page changes, and so forth.

Mariano Gomez, MVP

David Musgrave said...

Hi Christina

Looks like I was beaten to adding the Cross Linked Notes warning.

Please see this post for the scripts to run to update the next note index to avoid cross linked notes.



Steve Endow said...

Although I wouldn't necessarily consider it part of the "Company setup", Analytical Accounting has some critical tables in the Dynamics database.

Without having a matching Dynamics database, it is a nightmare to try and restore company databases if you are using AA.

Ask me how I know! ;-)

Christina Phillips said...

Thanks, Mariano, great point. I think the lesson here is that more and more I can't take the Dynamics database lightly :)

And, Steve, I have NO idea where you would have learned the lesson about Analytical Accounting ;)

And, as always, thanks David for the post regarding the notes issue. I have run in to that before, and it is much better to just avoid the issue in the first place.