Happy New Year Everyone!!

Wishing everyone a happy, safe and prosperous New Year!!  May we all see great things happen for Dynamics GP in 2011.  Hope to see you all at Convergence this year.

GPTip42day - Victoria Yudin's new book - Microsoft Dynamics GP2010 Implementation

What a way to start the New Year!  Our good friend and fellow MVP has a new book out called Microsoft Dynamics GP2010 Implementation.  Victoria is extremely knowledgeable about Dynamics GP and has been a GP MVP for longer than I've even known what an MVP is.  She has taken her vast knowledge about GP and used it to create a step-by-step guide to implementing GP2010.  I'm going to read the book in the next couple of weeks and post a review here on the blog but I wanted to give you all advance notice that the book is available here - https://www.packtpub.com/microsoft-dynamics-gp-2010-implementation/book.

I'm really looking forward to reading the guide and whether you're getting ready to implement GP2010 or just want to learn more about it, I'm sure this will be a must-have for your GP library.

Merry Christmas, Happy New Year and Happy Holidays to Everyone!

May your holidays be safe, serene and restful and may your New Year bring you health, wealth and wisdom.  My best to each and everyone!

GPTip42day - Why can't I see the Batch ID in SmartList after I post a GL batch?

This little quirk has to do with GP allowing you to use the same Single Use (as opposed to Recurring) Batch ID multiple times.  If GP stored the Batch ID (BACHNMBR) in the Open or History tables after the batch is posted, as it does in the Work table prior to posting, this could prevent use of the same Batch ID again.  Some (like me) might say this is a good thing because it would provide a solid audit trail between the batch and its transactions.  However, the current behavior does offer more flexibility in batch naming.

At any rate, if you need to see the Batch ID for a posted batch in a Financial Transactions SmartList, add the 'Originating Source' column to your SmartList.  That is where the Batch ID is stored permanently.

GPTip42day - Year End Close

It's hard to believe we're already at that time of year again.  If you need some assistance with Year End Close processes in GP, have a look at my series of posts here - http://gp2themax.blogspot.com/search/label/Year%20End%20Close.

GPTip42day - General Ledger enhancements in GP2010

Exclude Inactive Accounts in Accounts Lookup

You can now exclude all inactive account records from the Accounts lookup window.

Clear recurring batch amounts

You can now clear the distribution amounts for all transactions on recurring batches after posting a batch. You can navigate through the distribution lines and enter new amounts for each period.

Combine multiple General Ledger Budgets into a single, Master Budget

You can combine multiple General Ledger budgets into a single, Master Budget, one budget at a time. The Master Budget can be completely new and have its own Budget ID, or it can be one of the original budgets. This feature is helpful when multiple departments in your organization create their own budgets, which must then be consolidated. The new Combine Budgets window simplifies this process.

Create budget transactions
You can now create a transaction directly against a budget. You can view a list of budget transactions, make adjustments, and then update the budget from the list.

Comply with International Financial Reporting Standards (IFRS) requirements

GP2010 now includes features that meet compliance standards for IFRS. You can identify transactions for local GAAP, IFRS, or other accounting purposes, and you can define ledgers for each purpose. You can select one or more ledgers for financial reports and inquiries. Also, you can generate distributions automatically as part of the year-end closing or currency revaluation process.

GPTip42day - Emailing Documents in GP2010

In GP2010, you can embed any of a number of documents into the body of an e-mail message or send documents as attachments.

You can send a single document, batches of documents, or send multiple documents using the Lists functionality in GP. When setting up e-mail functionality, you can select which documents can be sent and which customers and vendors should receive their documents via e-mail.  If you are using Word templates for Microsoft Dynamics GP, you can send predefined or customized forms.

The following documents can be sent via e-mail from within GP –

 * click image to enlarge

A document can be sent in HTML, XPS, PDF, or DOCX file format type.  You can also combine multiple documents to be sent in a single e-mail.

GPTip42day - New Features in GP2010

Over the next few days (weeks?) we're going to focus on some of the cool new features that GP2010 provides that weren't available in any previous versions of GP.  There's a document available from Microsoft that lists all the new features, but for those who don't like reading long documents, we'll present the interesting stuff here in bite-size pieces and instructions on how to do the setup, if necessary.

Viewing Reminders as Cues or Text

You can specify to view predefined reminders and customized reminders as text or as Cues in the Home Page. A Cue is an icon that increases or decreases in height to indicate the number of records in a particular reminder category.

*click image to enlarge

To change from Text to Cues, or vice versa, on the Home Page, above the Reminders box, there's a pencil icon.  Click the pencil to open the To Do Details window, then click on the blue arrow to the right of Reminders to open the Reminder Preferences window.

* click image to enlarge

Place check marks in the Display as Cues boxes as desired.

GPTip42day - Using the Miscellaneous field on SOP transactions to recapture Credit Card fees

Business case  - the user needed a way to invoice customer for fees the user incurred as a result of receiving a credit card payment from the customer, without including the fee as a line item on the invoice.

Solution - use the Miscellaneous field on the transaction (this works regardless of whether you're using Sales Order Transaction Entry, Invoice Entry, or Transaction Entry) to invoice the fee to the customer.

Sales>Sales Transaction Entry

* click on image to enlarge

In Posting Account Setup, Series = Sales, set the account on the Miscellaneous line to be a revenue account, or whatever account you find appropriate, so the distribution occurs automatically.



GPTip42day - Multiple Project WIP Accounts

From the GP Forums comes the question - 'Can I setup multiple WIP accounts in Project Accounting?'  The answer is Yes.  Because the WIP account is specified in Cost Categories, you can setup multiple WIP accounts as long as you setup Cost Categories that are specific to the  projects you want to track per each WIP account.

GPTip42day - Leslie Vail shows how to get rid of that pesky 'Customer Experience' task

Thanks Leslie - great stuff!!

http://dynamicsconfessions.blogspot.com/2010/07/goodbye-ceip-program.html

GPTip42day - Locking users out of GP

I don't generally suggest or endorse products in this blog but when I run across something that really stands out in terms of 'bang for the buck', I like to share it.

Need to lock users out to perform upgrades or maintenance?  Check out Rockton Software's Dynamics GP Toolbox.  http://www.rocktonsoftware.com/products.aspx?p=12

For a very modest price, the Toolbox provides the ability to lock users out and much more.

GPTip42day - Checklists

Here's a great little feature in GP that I rarely, if ever, see clients use.

Company Checklists can be used to create customized checklists of company routines. You can specify the frequency with which routines should be completed: daily, weekly, on payday, at the end of a period, month, quarter, fiscal year or calendar year, during setup, or at a frequency you choose. Using the Checklist, you can launch a GP window, a GP macro or an external task, such as SmartConnect or Integration Manager - really anything application that is an executable.  Heck, with the right executable on your desktop, you could launch a Delta IV rocket.

When you run a routine from within the Checklist, the date, time, and User ID are captured and stored in the SY02500 table in the Company database so if you want to report on or expose the record to SharePoint to incorporate into a workflow, the data is there to do so.

In the screenshot below, I setup a few routines that might be followed in a typical Financial month-end close.

Financial>Routines>Checklist

* click image to enlarge

Checklists are provided in almost all GP modules, not just Financial, and could be used for ensuring processes are carried out at any time, not just period end.

I was going to provide instructions on how to set each type of routine up but it's so intuitive with GP Help, I decided to let you have some fun with it on your own.

GPTip42day - Taking the 'Pane' out of Grouping SmartLists

I've never been a big fan of the Navigation Pane in GP9 and prior versions. I just don't like anything taking up my precious desktop space. However, I've found a great use for it.

As you probably know, there is no easy way (perhaps no way at all - I've tried but gave up) to group unrelated SmartLists. Here's the business case -

Client has many branches, with branch ID's ranging from 901 to 935. Each of these branches has 8-10 SmartList Favorites that are identical across branches except they are each defined to be specific to a branch ID. Most are Field Service SmartLists, with a couple Inventory Item SmartLists and a Project SmartList in the mix. Needless to say, multiplying 8-10 SmartList Favorites by 35 branches makes for one large list of Favorites to have to wade through for each branch to find their own.

The solution I came up with was to use the Navigation Pane Shortcuts. Using this tool, you can setup a folder or folders to contain the unrelated SL Favorites for each branch and group them all in the folder. This way, all each user has to do is open that Shortcut folder and voila! there are all their Favorites grouped in one spot.

All you have to do is right click on the Shortcuts Folder and select Add>Folder.  Then rename the folder to suit your needs.  Next, right click on your new folder and select Add>SmartList.  The Add SmartList Favorite Shortcut window opens.  You select the SmartList you want added to the folder and click Done.

* click image to enlarge

That's all there is to it.

* click image to enlarge

I'd love someone to tell me there's a way to do this in SmartList or SL Builder but until they do, I'll be recommending this method of SmartList grouping.

GPTip42day - SmartList with Cash Receipts and Invoices Applied

Here's a tip from the forums compliments of our friend and my fellow MVP, Victoria Yudin.

Victoria offers a SQL view to form the basis of the aforementioned SmartList using SmartList Builder.  You can take advantage of her work by clicking on this link - http://victoriayudin.com/2010/02/15/sql-view-with-ar-apply-detail/

Thanks Victoria.  You're the best!

GPTip42day - Microsoft Dynamics GP 2010 Cookbook

Okay folks, here is the best tip you'll get all year for wringing the most out of GP.  Our friend and all-around GP expert, Mark Polino, has a new book coming out this summer.  It's called the Microsoft Dynamics GP 2010 Cookbook and is chock full of great tips to help you extract maximum value from GP.  Don't let the '2010' designation in the title scare you away though.  Most of the tips in this book also apply to GP 8.0 thru 10.0.  Here's a sample of the book cover and a link to get your order in.

https://www.packtpub.com/microsoft-dynamics-gp-2010-cookbook/book

Happy Reading!

GPTip42day - Price not found in Price List

Tired of getting this message when adding an item to a sales document?  Open the Sales Order Processing Setup window and check the Prices Not Required in Price List checkbox.  You can even password protect this option if you'd like.

Sales>Setup>Sales Order Processing

* click image to enlarge

GPTip42day - Can I post transactions to a year prior to the most recent closed year?

The short answer is no - unless you want to spend some $$ and hire MBS Professional Services to open the year in question for you.  There are those who've tried it on their own and they report it as a very ugly process, resulting in the need to fix a lot of transactions.

If you need to do it - hire the experts!

GPTip42day - Can I transfer non-inventory items between Projects?

Yes, you can but you need to make sure the Do not allow Non-Inventory Items box in Inventory Transfer Setup in PA Setup is unchecked.

Project>Setup>Inventory Transfer

* click image to enlarge

Then, perform your transfer transactions out of the incorrect project and into the correct project.

Project>Transactions>Inventory Transfer

* click image to enlarge

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.

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.

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.

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.

ERP Implementation Strategies

Our friends over at http://www.softwareadvice.com/manufacturing are taking an informal survey about ERP implementation strategies at http://www.softwareadvice.com/articles/manufacturing/erp-implementation-strategies-1031101/. 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!

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

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.

UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB='XXX'

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:

SELECT * FROM DYNAMICS..SY00800

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:

DELETE DYNAMICS..SY00800 where DEX_ROW_ID= 'XXX'

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.

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.

http://msdynamicsworld.com/story/accounting/sharpen-axe-and-increase-your-microsoft-dynamics-gp-productivity

GPTip42day - In which order should I run the Reconcile Utilities in GP?

The is the prescribed order for running Reconcile

(Tools>Utilities>xxxmodule>Reconcile) -

1.  Sales Order Processing

2.  Purchase Order Processing

3.  Inventory

4.  Project Accounting (if used)

5.  Field Service (if used)

Running Reconcile in this order is important because of the interaction between the modules  affecting Item Quantities.  Running Reconcile in a different order can result in incorrect Item Allocation Quantities.

GPTip42day - Adding a non-depreciating asset

From the GP forums this morning comes the question 'How do I capitalize an asset without depreciating it?'  The poster had received some fixed assets as gifts and didn't want to depreciate them.  Here's how you do it - very simple.

In the Asset Book window, select No Depreciation as the Depreciation Method.

* click image to enlarge

GPtip42day - Track User Inactivity

I generally don't get into the technical side of GP in the Tip42day but this is just too good not to share. 

A user on the https://community.dynamics.com/forums/32.aspx forum posed the following question -

Is there any column that will show the last activity time for a user? I would like to be able to send email alerts to users who are logged in but haven't been active for 1 hour.

This is a great idea but I'm not aware of any field in any table in GP that stores this info.  However, I didn't think about pulling this directly from SQL.  Fortunately, there are people much smarter than I am out there who did.  Here's the solution - 

SELECT CASE WHEN S.session_id IS NULL THEN 'Missing DEX_SESSION'

ELSE ''


END MISSING_SESSION,


CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1


THEN 'Idle for ' + LTRIM(RTRIM(STR(DATEDIFF(mi, P.last_batch, GETDATE())))) + ' minutes.'


ELSE ''


END AS IDLE_TIME_DESC,


CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1


THEN DATEDIFF(mi, P.last_batch, GETDATE())


ELSE 0


END AS IDLE_TIME,


A.USERID,


A.CMPNYNAM COMPANY_NAME,


INTERID COMPANY_ID,


LOGINDAT + LOGINTIM LOGIN_DATE_TIME,


SQLSESID SQL_SESSIONID,


P.login_time SQL_LOGINTIME,


P.last_batch SQL_LAST_BATCH,


DATEDIFF(mi, P.last_batch, GETDATE()) TIME_SINCE_LAST_ACTION,


S.session_id SQLSERVER_SESSIONID,


S.sqlsvr_spid SQLSERVER_PROCESSID,


P.spid PROCESSID,


P.status PROCESS_STATUS,


P.net_address NET_ADDRESS,


P.dbid DATABASE_ID,


P.hostname HOSTNAME


FROM DYNAMICS..ACTIVITY A


LEFT JOIN DYNAMICS..SY01400 U ON A.USERID = U.USERID


LEFT JOIN DYNAMICS..SY01500 C ON A.CMPNYNAM = C.CMPNYNAM


LEFT JOIN tempdb..DEX_SESSION S ON A.SQLSESID = S.session_id


LEFT JOIN master..sysprocesses P ON S.sqlsvr_spid = P.spid


AND ecid = 0


LEFT JOIN master..sysdatabases D ON P.dbid = D.dbid

This script creates a column called 'IDLE_TIME' and stores the idle time in minutes, which can be used to determine which users to send the email to via SQL Mail.

Thanks to Ron Wilson and Sivakumar Venkataraman for this great tip!

GPtip42day - Why does one Item show different costs on the same receipt?

Occasionally, or maybe more often, you process a receiving transaction for a multiple of an Item at an Extended Cost that isn't evenly divisible by the quantity.  Take a look at the screenshot below, for example.

* click image to enlarge

This is a snapshot of a modified Purchase Receipts report.  Note that for RCT1607, there are two receipt lines, the first with a quantity of 4 @ $6,314.60 and the second a quantity of 1 @ $6,314.59.  Why is this?  Because the receipt was entered for a total quantity of 5 and an Extended Cost of $31,572.99.  GP can't divide the cost evenly across 5 pieces because the Item's Currency Decimal setting is 2 decimals.  Therefore, it posts the receipt in two lines as seen above.

This same scenario can occur in a multicurrency environment when the Originating and Functional Currency rates result in an exchange difference than can't be apportioned evenly across the quantity received.

GPtip42day - Size of Note field

I'll bet everyone has been wondering what the maximum note size is that can be entered into a note field.  From our friend and GP developer extraordinaire, Mr. David Musgrave, the maximum number of characters a note field will hold is 32,000. 

Now, if we just had a character counter on that note field David!

GPtip42day - Are hotfixes cumulative?

This question was posed on one of the forums yesterday.  The answer is yes - hotfixes are cumulative - meaning they include all previous hotfix and service pack modifications to-date.

From Microsoft PartnerSource - 'All service packs, hotfixes, tax updates and year end updates are cumulative and the version information is incremented with each patch release.'

GPtip42day - Printing Multiple Sales Invoices

In GP10, you can print multiple sales invoices in one step.  Select the Sales tile in the Navigation Pane, then select Receivables Transactions.  Filter the resulting list for the type(s) of documents you want to print, date range, etc.  When you have the list filtered, place checkmarks next to the documents you want to print and click Print Documents.  All documents will be printed to the destination you select in the Print Destination window.

* click image to enlarge

GPtip42day - How to place a watermark in a Report Writer document

Debi Carlton of D1 Technologies posted this solution on one of the GP forums recently -

1.  Insert the image in a Word document

2.  Use the Custom watermark option in format to create the watermark image

3.  Copy the image and paste in Paint

4.  Save the image as a .jpg file

5.  Insert the .jpg into the Report Writer document

Thanks Debi!  Great tip!

gptip42day - Resetting the GP system password

Note, this tip is for GP Administrators only.  Attempts by non-administrators to change the system password could compromise data security!

Occasionally, the GP system password is forgotten or lost.  This password is required to make changes at the system level, such as adding a new user, changing user security, etc.  Here's how to recover when this happens.

1.  In SQL Management Studio, run this script to remove the existing password -

           UPDATE DYNAMICS.SY02400
           SET PASSWORD = 0x0202020202020202020202020202020
 
2.  In GP, reset the system password.
 

Tools>Setup>System Password

 

* click image to enlarge

Now, store the password in a safe, accessible place!!

gptip42day - What printer should I use with GP?

I get this question on a fairly regular basis.  My response always is - what do you want to use the printer for?  Invariably, the use is printing checks, invoices, etc, etc, etc.  My recommendation is always - HP LaserJet.  You just can't go wrong with a LaserJet.  They work well with GP and supplies are readily available for them.

If you're printing MICR checks, a LaserJet with a standard toner cartridge works well in most cases.  The banking industry has moved away from requiring magnetic ink because character recognition software has improved so dramatically.  Even so, it would be best to check with your bank to determine if they require magnetic ink, just to be on the safe side.

gptip42day - Audit Trail Code

Need to reference the transaction(s) associated with a particular Audit Trail Code but can't seem to find a report that gives that to you?

Let's use Account Transactions in SmartList as an example.  Open the SmartList, then add this column - TRX Source.  There's your Audit Trail Code!

gptip42day - Account Lookup

Somewhere along the line in GP10, the prior behavior of account lookups (where you type in the first few digits of the account, click the lookup button and it takes you to the beginning of the range) went wacky such that the lookup takes you to the very beginning of the chart of accounts. 

If you're experiencing this, here's how to fix it.

1.  Open any window that has an account lookup in it.

2.  Type a few digits of an account

3.  Click on the looking glass

4.  Change the default lookup to 'by Account'

5.  Select an account

* click image to enlarge

The next time you use account lookup, it will take you to the beginning of the range based on the digits you enter.  You need do this only in one lookup window.  The change in lookups applies to all account lookups system-wide.

'The Closer'

Now's the time of year when many of us are reconciling our GL accounts to get ready for external auditors and closing the year.  I personally spend a lot of time with my clients helping them reconcile Cash, AR, AP, and Inventory.  Until now, it's been a matter of hunt & peck and a whole bunch of analysis to find the reconciling items.

The Closer (http://www.reporting-central.com/) changes all that.  Microsoft has taken a stab at providing a reconciliation tool (Reconcile to GL) that helps.  The Closer goes beyond that by providing a tool that not only tells you which transactions are responsible for the differences, it also recommends the fixes.  I've used it and it's easy to install and even easier to setup and use.

If you spend too much time beating your head against the wall trying to reconcile your accounts, I highly recommend having a look at The Closer.  It will save you time and money.

** Full disclosure - my company is a reseller for Reporting Central