1. Preventing date errors with DocDateVerify
Microsoft provides a small add-on for Dynamics GP
called DocDateVerify. This little application does one thing and it does
it well. DocDateVerify prevents users from entering dates in subledgers
for fiscal periods that have not been set up.
Without DocDateVerify it is possible to accidently
date a payables transaction with the year 2100 instead of 2010. As
Payables Document Dates are often different from general ledger posting
dates, the transaction could post just fine but would never be selected
for payment because of the odd year. It's also hard to fix because other
parts of Dynamics GP do validate against the fiscal period and prevent
users from voiding some of these incorrectly dated transactions.
DocDateVerify rectifies this by preventing the entry of dates that don't
exist in fiscal periods that have been set up.
DocDateVerify works in core financial and
distribution modules to validate date entry. This Microsoft module is
available through an authorized Dynamics GP partner on request. It is
free, but it's not available for download. In this recipe, let's look at
how it works.
Getting ready
DocDateVerify is a file with a CNK extension,
commonly called a 'chunk file'. Chunk files are installed by dropping
them in the location where Dynamics GP is installed and restarting the
application. Dynamics GP may need to be launched with Administrator
privileges on some versions of Windows to allow the installation to
proceed. Dynamics GP may ask if it's okay to install new code. Users
must answer 'yes' for DocDateVerify to be installed. After installation
the 'sa' user may need to log in first to allow the installation to
complete.
How to do it...
To see how DocDateVerify protects dates in Dynamics GP:
1. After DocDateVerify has been installed select Purchasing from the Navigation Pane. Select Transaction Entry under the Transactions section on the Purchasing Area Page.
2. In the Doc. Date field enter 4/12/2100.
3. Dynamics GP responds with a message that A fiscal period for this date has not been set up. Users must change the date to continue with the transaction.
How it works...
DocDateVerify provides some peace of mind for core
financial and distribution transactions. Other modules, such as the
Contract portion of Field Service, actually need to put dates out beyond
the existing fiscal periods because of the nature of the transaction.
For financial and distribution modules DocDateVerify does not work with
Quotes and Orders, as it is reasonable that those transaction types
could affect fiscal periods not yet set up. DocDateVerify does need to
be installed on each workstation. It cannot be rolled down via Client
Update.
2. Executing SQL from the Support Administrator Console
There are times when the best way to confirm that
information in Dynamics GP is correct is to simply look at the records
in the SQL Server. Typically, this is done by executing an SQL command
in SQL Server Management Studio. However, SQL Server Management Studio
is not typically installed on an average user's machine. Also, the
user's machine can be a long way from a computer with Management Studio
installed. Finally, Management Studio contains a lot more features than
is necessary to simply execute a query.
Microsoft again provides a free tool, this time in
the form of the Support Administrator Console. Originally, this tool was
designed for versions of Dynamics GP that ran on a light version of SQL
Server that didn't include SQL Server Management Studio. Nevertheless,
the Support Administrator Console works just fine with Dynamics GP 2010
and provides a lighter option in place of using SQL Server Management
Studio for quick queries.
The Support Administrator Console is a free download
for customers with access to Microsoft's CustomerSource site. The
version for Dynamics GP 10 also works in Dynamics GP 2010. It's
available at https://mbs.microsoft.com/downloads/customer/SptCon.zip.
In this recipe, we will take a look at how the Support Administrator Console works.
Getting ready
The Support Administrator Console needs to be downloaded and installed on a user's machine prior to using.
To get the Support Administrator Console:
2. Right-click on the SptCon.zip file, select Extract All, and follow the prompts to unzip the file.
3. In the new SptCon folder double-click on Setup and follow the installation prompts.
How to do it...
To use the Support Administrator Console:
1. On the Windows Start menu find the Microsoft Support Administrator Console folder added in All Programs. Select Support Administrator Console in that folder to start the tool.
2.
Select the appropriate server for Dynamics GP and log in with an SQL
username and password. The GP username and password will not work but
the 'sa' password will.
3. In the window at the top select the sample Dynamics GP database named TWO.
4. Type Select * from GL00105 and click on the arrow-like Play icon:
5. The basic Chart of Accounts information is returned to the screen in the tab marked Result at the bottom:
6. To modify the query select the SQL tab.
7. Select File | Export to export the data to a comma-delimited file.
8. Click on File | Save to save the query to use at a later time.
How it works...
The Support Administrator Console is a great, quick
way to check data in Dynamics GP. It loads faster and with less overhead
than SQL Server Management Studio. However, it is a powerful tool and
should still be controlled with care.