March 26, 2010

GPTip42day - SA vs. DYNSA

What are the primary differences between SA and DYNSA users in GP? 

1.  SA is the SQL database system administrator and has full  access to all databases on the SQL Server, including non-GP databases.  DYNSA is the GP database administrator and has access to all GP databases only.

2.  SA and DYNSA are both granted Power User roles automatically in GP Security. 

3.  SA is required for certain tasks such as initial system installation, payroll updates, and to administer some 3rd party applications.  Generally speaking though, DYNSA can perform most of the security and maintenance tasks in GP that SA can.

4.  From a SQL database security perspective, DYNSA should be used by your GP administrator for day-to-day administrative duties.  SA use should be reserved for SQL administrators where possible to reduce the possibility of tampering in non-GP databases.

March 25, 2010

GPTip42day - FRx - How do I setup my Balance Sheet to reflect current YTD balances before I've closed the previous fiscal year?

This is a common issue when you leave the previous fiscal year open into the new fiscal year.  Because the new BS balances haven't been rolled forward yet, the balance sheet is inaccurate.  Here's how to resolve it.

1.  In the Row Format, use 3 rows to calculate Retained Earnings.  The first row reflects the prior year RE account balance.  The second row reflects the total of all P&L accounts.  Make these two rows non-printing.  The third row is the total of the first two.  Make this a printing row.  In conjunction with what we're going to do in the Column Layout, Retained Earnings for the current year will be correct.

* click image to enlarge

2.  In the Column Layout, use 3 columns to calculate the current year balances.  The first column reflects the prior fiscal year account balances.  The second column reflects current year activity.  Make these two columns non-printing.  The third column is the total of the first two.  Make this a printing column.

* click image to enlarge

Voila.  A current year balance sheet before the prior year is closed.

March 24, 2010

GPTip42day - Activity Tracking

If you need to track certain user activities in GP such as master data creation or transaction posting activity, use Activity Tracking to do so.

Administration>Setup>System>Activity Tracking

* click image to enlarge

To turn on Activity Tracking, select the Activity Type, place a checkmark in the Activity, select User(s) and place a checkmark in the appropriate Company(s).

You can track multiple activities but beware that the Activity Tracking table can grow very large in a short period of time if many activities are tracked.  Therefore, turn  tracking on for only those activities that are essential and clear the table on a regular basis via Administration>Utilities>System>Activity Detail.

March 18, 2010

GPTip42day - Transferring Inventory Items between Projects

How do you transfer an inventory item from one project to another in Project Accounting?  Use the Inventory Transfer transaction.

Project>Transactions>Inventory Transfer

* click image to enlarge

Use the Return Transaction Type to return the item to inventory from Project A, then use the Standard Transaction Type to transfer the item from inventory to Project B.

March 16, 2010

ERP Implementation Strategies

Our friends over at are taking an informal survey about ERP implementation strategies at They want to determine which implementation strategies are the most popular and most successful. Big bang, phased rollout or parallel adoption.

If you have a chance, click on the link and give them your input.  Thanks!

March 15, 2010

GPTip42day - Miscellaneous Recurring Batch

Need to do a recurring batch but none of the standard recurrence frequencies suit your needs?  Use the Miscellaneous frequency.  The Miscellaneous frequency allows you to specify the number of recurrences and the number of days between each recurrence.  For instance, let's say you need to post the same transaction daily for 30 days.  Here's what it would look like -

* click image to enlarge

March 4, 2010

GPTip42day - Resolve Batch 'Stuck' in Posting

Occasionally, a batch will 'hang' during the posting process and not show as available to be recovered in Batch Recovery. When this happens, follow these steps to 'unstick' the batch. Note: to be performed by GP Administrators only.

1. Run the following script against the company database. Replace XXX with the batch number or the name of the batch that you are trying to post.


Note: The value of BACHNUMB is the same as the Batch ID in GP.

2. View the contents of the SY00800 table located in the DYNAMICS database to determine the value of the DEX_ROW_ID field for the batch that you are trying to recover. You can view the contents of this table by running the following script:


Note: If no results are returned, go to step 5.

3. Run the following delete script, based on the results received in step 3. Replace XXX with the DEX_ROW_ID value of the batch that you are trying to recover:


The preceding steps will free up the batch for edit and posting. Once the batch is again available:

4. Verify the accuracy of the transactions.
5. Verify that you can edit and post the batches.

March 3, 2010

Sharpen the Axe - Part 1

Have a look at my latest article on MSDynamicsWorld.  This is the first of three articles leading up to my presentation at MSDynamicsWorld's virtual conference - Decisions2010 in May.