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 - Dude, where's my Batch ID??

When looking for the Batch ID in a Financial Transactions SmartList, one would think that if you add the Batch Number column, you'd see the Batch ID in one of those fields.  But noooooooooo . . . that's the case only if the batch isn't posted. 

To see the Batch ID for a posted batch, you have to add the Originating Source field -

* click image to enlarge

Mystery solved!

GPTip42day - Uh oh, we closed the GL with an incorrect posting type on an account

If this happens in your company, here's the fix -

https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb$en-us$864913&wa=wsignin1.0

Note:  You must have access to either CustomerSource or PartnerSource to access this document.  If you don't, contact your Partner.  If you don't have a Partner, let me know.  I'll help you out.

GPTip42day - Horizontal Scroll Arrows

Ever wonder what the setting in User Preferences called Horizontal Scroll Arrows does?

* click image to enlarge

For companies that have very long account numbers, checking this box enables scrolling arrows in account lookup windows to make it possible to view the entire account number if it's too long to view in the field provided.

* click image to enlarge

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 - Item History Card

Here's a rather obscure window in GP that I don't see used often.  Go Cards>Inventory>Item History to view.

* click image to enlarge

As you can see, this window provides a wealth of historical information about the sales and usage of your items, by period, by year.

Additionally, if you're running MRP or using the PO Generator in GP, this window can be used to enter forecast usage of items for planning purposes.  Access this window and click on the Help icon for more infomation.

GPtip42day - Reopening a closed Fixed Asset fiscal year

I'm sure this has never happened to anyone, but if by chance you ever inadvertently close the Current FA Fiscal Year, it will prevent you from posting depreciation into any period in the current fiscal year. How do you get around that? It's pretty easy.

Go Tools>Setup>Fixed Assets>Book and set the Current Fiscal Year back to the prior year.

* click image to enlarge

For instance, let's say you're closing 2009 and you accidentally close 2010 also. At that point, the Current Fiscal Year field will reflect 2011. Just change the Current Fiscal Year back to 2010. You'll now be able to post depreciation into your 2010 periods. The Current Fiscal Year determines whether depreciation is to posted to the current year (depreciation expense account), or a prior fiscal year (prior-year depreciation account).

Be careful though that you don't change the year back to 2009. You don't want to post depreciation into a year that is truly closed!

GPtip42day - Using SQL Wildcards to search & filter in SmartList

Did you know that you can perform searches and filter using wildcards in SmartList?  It's true.  Because SmartList is a query tool that uses SQL-like search functions, SQL wildcards can be used successfully in SmartList.  So you ask, what are the SQL wildcard characters?  Here they are -

* click image to enlarge

For instance, suppose you want to search your Vendor list for all Vendor IDs that begin with A, D, F, N and U.  You can't enter these search criteria separately in the Search Definition fields because there aren't enough of them.  You also can't use the 'between' filter because these characters aren't sequential.  However, you can use the [charlist] wildcard to perform the search.  Here's what that search would look like -

* click image to enlarge

This search criteria will return all Vendor IDs that begin with A, D, F, N and U.  Likewise, you could use [^charlist] or [!charlist] to search for Vendor IDs that do not begin with certain characters.

There may also be some use for the '%' and '_' wildcards but the 'contains' and 'begins with' filters cover most of the uses for those wildcards.

The cool thing about the [charlist] wildcard is it really expands the range of searches that can be done in SmartList.

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 - How to handle a check voided in error

You issue a check to a supplier and then someone erroneously voids that check.  How do you recover from this without having to contact the vendor, issue a new check, and stop payment on the old one?

First, go Cards>Financial>Checkbook and select the checkbook from which the check was issued.  If the Duplicate Check Numbers checkbox isn't checked, check it.  Click Save.  Leave the Checkbook Maintenance window open.  We'll be coming back to it.

* click image to enlarge

Go Transactions>Purchasing>Manual Checks and create a manual check to replace the check (in the system) that was voided.  Change the Document Number to the check that was inadvertently voided.  Post the transaction.

You now have a replacement check in the system for the one that was voided that can be cleared in Bank Rec when the supplier deposits the original check.

Two more steps before we're done -

1.  Document the heck out of this because you'll now have two checks in the system with the same check number, one voided and one not.  And sure as can be, this check will be selected during audit.

2.  Go back to the Checkbook Maintenance window and uncheck Duplicate Check Numbers.

GPtip42day - Duplicate Journal Entry Numbers

Have you ever noted duplicate JE numbers in your system?  There are at least three reasons why you might see this.

1.  Reversing journal entries use the same JE number

2.  Recurring journal entries (actually, recurring batches) repeat journal entry numbers with each recurrence

3.  If you post a journal entry to a closed year, GP uses the same JE number in the current year to close the activity to Retained Earnings.

GPtip42day - How do I reconcile a cash account with no transactions for the period?

At times, you may have a non-interest bearing cash account with no activity for the period.  When you try to reconcile the checkbook, you get the following error -

*click image to enlarge

Here's how to deal with this issue - enter a $.01 dummy Increase Adjustment and a $.01 dummy Decrease Adjustment in your checkbook. Debit and credit the cash account for both transactions. Run your reconcile for the period and clear the two dummy transactions. Your reconcile reports will then print.

GPtip42day - SO/PO Generation for Service/Misc/Flat Fee Items

Normally, when entering a sales order for an inventory item, GP checks to see if there's enough stock on hand to fulfill the order. If there is not, then the Quantity Shortage window opens to allow you to decide how to handle the shortage, from which you can tell GP to backorder the items. This allows you to then generate a PO directly from the SO.

Because quantities aren't tracked on Service, Misc Charge and Flat Fee items, there is no shortage checking involved and no opportunity to automatically back order the item. Therefore, you have to manually tell GP that you want to backorder the item. You do this in the expanded scrolling window area of the sales order.

* click image to enlarge

When you make the 'Qty to Backorder' greater than zero, you can then generate a PO from the line item. (You also have to change the 'Qty to Invoice' to zero).

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 - Job Costing

If you have a need for tracking project or job-type information but don't want to implement full-blown Project Accounting in GP, you might want to consider the Job Costing feature in GP Manufacturing.

For example, suppose your business is constructing modular homes. If you build the homes to customer specifications - they visit your offices to choose a model or floor plan - you might create a job when the sales order is created. As work on the construction of the home progresses, you can link various kinds of information to the job, from inventory transactions for lumber used to build the house frame to service charges for delivery and setup of the home. Manufacturing helps you track several kinds of information and transactions. For the modular-home builder, those different kinds of information might be:

• Component transactions, such as those to take framing lumber from current stock

• Purchase orders for concrete work, siding, and paint

• Receiving transactions for the items purchased to complete the job

• Payables Management transactions for purchased items

• Manufacturing orders for the construction of walls, windows, and ceilings

• Additional sales orders; for example, if the home buyers want to an optional deck or garage

• Data collection records for tracking machine and labor costs

• Receivables Management transactions for the sale of items such as setup and delivery fees

You can’t create transactions in Job Costing, but you can use Job Costing to organize transactions throughout your system for a job or project. Using Job Costing won’t affect your transactions or posting processes. Think of each job you define as a sort of subledger. Job Costing captures information from accounting transactions, but has no effect on General Ledger or any subsidiary ledgers.

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 - Non-consecutive accounts Trial Balance

If you're using GP10 and need to print a year-to-date GL Trial Balance that includes non-consecutive account numbers, here, courtesy of the Dynamics GP Blogster himself - Mariano Gomez - is the way to do it. 

Click on the Financial tile in the Navigation Pane, then click on the Accounts list.  In the results pane, place checkmarks in the boxes next to the accounts you want to include in the trial balance.

* click image to enlarge

Click the Reports button and select Print Trial Balance.  In the Print Trial Balance window, select the year and whether or not to include details.

* click image to enlarge

When you click the Print button, the standard Print Destination window opens.  Click OK to print to your desired printer.

* click image to enlarge

Note that while you can print a GL Trial Balance from within the Lists view, you can't select a date range as you can in the normal GL Trial Balance Report Definition.  You are limited to a year-to-date trial balance for a particular year.