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

Happy New Year Everyone!!

Well, finally, we get to put the challenges of 2009 behind us and look forward to a more prosperous 2010 for everyone.  May each of you find many blessings in the New Year.

gptip42day - Reconcile

GP provides a way to 'Reconcile' summary and detail data within the application.  Each module has its own Reconcile utility.  For instance, Tools>Utilities>Financial>Reconcile opens the Reconcile Financial Information window.

* click image to enlarge

Now, this Reconcile is not for reconciling a GL account to the subledger, i.e., Accounts Receivable to the AR Trial Balance.  This Reconcile forces financial summary information, such as the monthly data in the Financial Summary Inquiry window, to equal the underlying detail.

Over time, some transactions don't post properly, data gets corrupted, etc, etc.  This Reconcile fixes those data errors.

However, there is one aspect of this utility that you need to be aware of.  Note that you can reconcile Open and History years.  Be very careful that you don't reconcile a History year that contains only GL account balances.  This might be the case where historical account balances were imported during an implementation because it was deemed unnecessary to import all the transaction detail.  If you reconcile a History year in this scenario, it will zero out your account balances.  Why?  Because the Reconcile process, by design, forces the summary data to equal the detail transaction data.  If there are no detail transactions - Reconcile will make the summary equal that!

Now, fortunately, GP presents a warning to this effect when you select a History year to reconcile.  Heed the warning!!