Localization for Dynamics GP

Over at ComputerationDynamics, Gloria has a great account of some of her localization trials with Dynamics GP. If you are looking at multi-language implementations, it's worth a read to understand potential issues beforehand.

The Ins and Outs of Dynamics GP on SQL Server

David Musgrave has a great new post up on Dynamics GP and SQL Server. In it he explains how GP works with SQL Server and why some of the concepts (like dex_row_id) exist.

Weekly Dynamic: SQL Snapshots and Dynamics GP

Sometimes when implementing Dynamics GP you do a lot of backing up and restoring. Large implementations can require multiple test environments, development environments, etc. Sometimes you need to push a large number of transactions through for testing over and over again, restoring the environment each time.

Well all of those backups and restores take time. A faster alternative is to use functionality in SQL Server to take a snapshot of a company. Snapshots have lots of limitations that make them unsuitable for typical backup/restore needs but for activities that require multiple reloads they are a lifesaver. Why? Snapshot restores are fast. Smoking fast. 4 second restores fast.

In a test on an old, single processor machine, the default TWO company and the Dynamics DB took 45 seconds to backup and another 45 seconds to restore. A snapshot took 6 seconds to backup and 4 seconds to restore. What's more, the snapshots don't slow down much as the db size increases. We've seen GP databases that take an hour for a full backup and restore still create a snapshot in under 10 seconds and restore in under 10 seconds.

Unfortunately Snapshots only work on the Enterprise Edition of SQL Server but if you want to know more about both the power and the limitations of snapshots, here is a great overview.

Here is the SQL Code I used to create and restore my test snapshots:

CREATE DATABASE DYNAMICS_dbss ON
( NAME = 'GPSDYNAMICSDat.mdf', FILENAME =
'c:\DYNAMICS_dbss.ss' )
AS SNAPSHOT OF DYNAMICS;
GO

CREATE DATABASE TWO_dbss ON
( NAME = 'GPSTWODat.mdf', FILENAME =
'C:\TWO_dbss.ss' )
AS SNAPSHOT OF TWO;
GO

-------------------------------------------------

RESTORE DATABASE DYNAMICS FROM DATABASE_SNAPSHOT = 'DYNAMICS_dbss'
go
RESTORE DATABASE TWO FROM DATABASE_SNAPSHOT = 'TWO_dbss'
go


[H.T. to Ross Carlson for this]

Dynamics GP Product Feedback

If you've ever wondered where your Dynamics GP feedback goes, the Inside Microsoft Dynamics GP blog has more info including some tips on how to make your voice heard.

Business Ready Flexible Pay

We've briefly discussed the new Business Ready Flexible Pay here before but now the US Microsoft Dynamics GP Field Team has lots of details for you to decide if this is right for you.

Dynamics GP CTRL+Q: Save and Print SOP Docs in One Step

Mariano Gomez has uncovered a great tip on using the CTRL+Q key combination to allow saving and printing SOP documents in a single step. This may make into the next version of 50 tips. Thanks for highlighting this Mariano!

Flattening Related Data for Report Writer

David Musgrave takes a look at making one to many table relationships behave like one to one relationships in Report Writer. Essentially he is flattening the data for reporting. This common technique for data warehouse and business intelligence use and Dave shows how to apply it to Report Writer.

SQL View with All Dynamics GP SOP Lines

Victoria Yudin has a new SQL View up to show all of the Sales Order Processing transaction lines, posted and unposted.

Dynamics GP SOP Table Posting Statuses

Over at the Dynamics GP Blogster, Mariano takes a look at the various posting statuses present in the Sales Order Processing Tables.

Weekly Dynamic: SQL Data Mining Add In

Despite the Performance Point setbacks, Microsoft BI marches forward. The latest cool tool is also free. Microsoft has released its Data Mining Add In for SQL Server. While not necessarily GP specific it should work just fine against GP databases and cubes. I could spend a whole bunch of bits here telling you about it but why don't you just go here and see for your self how cool this is.

One caveat, this requires Office 2007 and SQL Server 2008 so some of you may not be ready for it yet.

Importing the Vendor 1099 Box with Integration Manager

Dynamics GP's Integration Manager is missing the 1099 box. The 1099 Type box is there but not the 1099 check box. Mariano Gomez has a great post on how he solved the problem using SQL. Make sure to check out the comments too for some other solutions.

Activity Tracking and Security in Dynamics GP

Merit Matters takes a look at using Activity Tracking to figure out what task a user needs security permission to.

Improve Cash Flow with Bonus Depreciation and Dynamics GP

Congress has extended bonus depreciation for another year and CFO magazine has highlighted a study showing that bonus depreciation does indeed boost cash flow. So for my newest article on MSDynamicsWorld.com I tackled how to use bonus depreciation in Dynamics GP to give your firm a tax benefit.

Dynamics GP and Landed Cost

Christina Phillips has posted part 1 in a series on using Landed Cost in Dynamics GP over at Dynamics GP Land. Part 1 looks great with lots of detailed explanations and screen shots. Nice job Christina!

Moving from MSDE to SQL with Dynamics GP

Doug Pitcher of Rose Business Solutions has nice write up of his process to move a customer that had outgrown Dynamics GP on MSDE to Dynamics GP on SQL Server Express. Both databases are free but SQL Server Express has double the size limit. As Doug points out, the process is essentially the same as moving from MSDE to SQL Server and you will need new reg keys.

DynamicAccounting.net Coming to a Kindle Near You

I have added DynamicAccounting.net to the Amazon Kindle publishing site. It should be available for folks to sign up and get automatic downloads to their Kindles within 72 hours.

I will now sit back and wait for the royalties to roll in...not.

Dynamics GP Web Services Installation

The Development Matters blog as a new post up on their pain and solutions when installing Dynamics GP Web Services.

Database Mail and SQL Agent Mail Script

MSSQL Tips has a SQL script to simplify the setup of SQL Database Mail and SQL Agent Mail. You'll need to enter a few parameters but at the end you've got a consistent, repeatable process for the setup.

More on the Support Debugging Tool for Dynamics GP

David Musgrave has more info on effectively using the Support Debugging Tool for Dynamics GP.

Weekly Dynamic: PA Cost Category Naming Best Practice

In the Dynamics GP Project Accounting module, Cost Category names can't be duplicated across transaction types. This means that you can't have a Timesheet category called "Labor" for employees and Miscellaneous Log cost category also called "Labor" for contractors. A common best practice is to use a short abbreviation in the naming of the cost category to identify the type of transaction. In our example this would result in cost categories of TS_Labor and ML_Labor.

A second advantage is that if you use the GP Project Accounting terminology as part of the naming convention, then everyone gets used to the terminology quickly. This means that EL for equipment log is better than EQ since Equipment Log is the actual GP term used. We commonly use:

• TS - Timesheet
• EE - Employee Expense
• EL - Equipment Log
• ML - Miscellaneous Log
• PM - Purchase Material

There are other benefits to this like easy recognition of the transaction type in Smartlists and other reports. Cost Categories are powerful, they get more powerful when you put just a little bit of intelligence into the naming convention.

Preventing Backdated Transactions with VBA

Sweeeet! David Musgrave shows how to prevent entry of a backdated transaction using VBA. There are few folks that I could have made a living off of just fixing their improperly dated transactions. Guess I'll have to do real work now. Thanks Dave! (Does this mean that I have to take back that dinosaur crack about Report Writer from a previous post?)

Smartlists and Related Scanned Document

Mariano Gomez has a new article up where he looks at a company that has scanned documents related to GP transactions and needs Smartlist to find transactions that don't have a related document. Pretty cool stuff Mariano!

Email Business Portal Users with SQL Server

Steve Endow at Dynamics GP Land has a great new article up covering emailing business portal users from SQL by looking up their address in Active Directory. Lots of code and screen shots.

All Microsoft Dynamics Solutions Lead to the Future

Kiril Tatarinov, Microsoft Corporate VP of Microsoft Business Solutions and Mr. Dynamics to the rest of us has a new artcile up this week. It rah-rah but we all need a little rah-rah now and then. Sometime these types of communication worry me. I'm not feeling concerned about Microsoft's commitment to business solutions. If anything, what I see on twitter and blogs is that MS has moved solidly into the number 3 slot behind SAP and Oracle. Not a bad place to be considering that while there is some overlap, it's not the same market. But when Kiril feels the need to reassure me, I get nervous.

Personally, I'd rather see Kiril twitter than release well scrubbed reassurances. Authenticity and vulnerability provide an opportunity to get past the cyncism often directed at corporations as a whole. It's harder to hate a person than a company and it's really hard to hate a person once you really get to know them. If Kiril wants to break down walls and reach partners and customers we're going to need more frequent, smaller communications. Actually, that's not a bad idea for any of us.

Dynamics GP Report Writer - Convert Words to Numbers with Multi-Currency

David Musgrave at Developing for Dynamics GP has info on getting the RW_ConverttoWordsandNumbers() function in report writer to work with multi-currency. Always nice to see a report writer article even if RW is a dinosaur. (That should pretty much guarantee a response from Dave.)

Find the Line Number on a Dynamics GP Document

I'm horribly behind due to technical constraints and other factors this week so it's catchup day. Buckle up. First up is Mariano with not one but two posts on finding the line number of an item on Dynamics GP document via SQL.

Upcoming Microsoft Dynamics GP Workshops

Developing for Dynamics GP has info on upcoming workshops on Integration Manager and eConnect.

Scheduling Jobs in SQL Server

MichaelJ2 finishes his earlier thought on Automatic Aging for GP with info on how to schedule automatic aging in SQL Server 2005.

Paul Randall on SQL Backups

Paul Randall, one of the smartest guys in the SQL Server world, did a brain dump on SQL Server backups. He promises that much of this will later get incorporated into a real article but even his brain dump provides a fantastic look at backing up SQL Server.

Dynamic GP Goes Green

From the Microsoft Dynamics GP UK blog I see that Microsoft has released Environmental Management Accounting Implementation Guidance for GP on Partnersource.

I mention this not just because it's cool but because I had a hand in creating some of this content along with some colleagues from I.B.I.S.

Adding AutoComplete with Dexterity

Dave Dusek provides information on how to populate auto complete entries for a field using Dexterity over at Developing For Dynamics GP. Nice info Dave!

Weekly Dynamic: SQL to Show Transactions for a Certain Account

Let's say that you are analyzing an account and want to see all the transactions that hit a particular account to see the related and offsetting accounts. Sure you can do that transaction by transaction via Detail Inquiry but it's tough to do analysis that way. Smartlists won't really give you what you want. Since you are looking for the full transaction detail whenever part of that transaction hits a certain account. You can't really limit the account and get what you want. You need a SQL statement that can be used in Smartlist Builder, Excel Builder, SSRS or someplace else.

Well here you go. This statement gets the transaction detail from the open year when that transaction contains a certain account number in segment 2. You'll of course need to modify it for your segments and accounts but it's a good starting framework.


SELECT *

FROM GL20000

Where GL20000.jrnentry in (Select distinct(jrnentry) FROM GL20000 INNER JOIN

GL00100 ON GL20000.ACTINDX = GL00100.ACTINDX

Where GL00100.ACTNUMBR_2='1100')

More Excel Reporting

Christina Phillips has more on Excel Reporting in Dynamics GP over at Dynamics GP Lan

Better CustomerSource search?!?

From the Dynamics Community Team comes this announcement that we've been heard and starting May 4, phase 1 of improved search for Partnersource and Customersource will be rolled out. Perhaps my repeatedly threatening to kidnap a member of the Windows Live team and hold them hostage while they fix search has finally worked!

Unfortunately the announcement is behind the Partnersource wall so here are the basics:

Phase 1

• Improved relevance
  
• Search by language
• Search preferences
• Search across multiple areas
• Editor’s Choice
• Related searches

Phase 2 of search is scheduled for 8/29/09.

I'm reluctant to reprint the whole thing since it's behind Partnersource. Maybe someone at MS will shake the whole thing loose for me.

Dynamics GP 10 Public Sector HR/Payroll

Inside Microsoft Dynamics lets us know that the new Dynamics GP 10 Public Sector Industry Fact Sheet for Human Resources and Payroll is available now on CustomerSource.