December 31, 2009

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!!

December 30, 2009

gptip42day - Login to GP Faster

Suffering from long login times?  Here's a little trick that might help.  Delete all the records in the Menu Master table - SY07110.  When this table has accumulated too many records in it, it not only slows down login, it slows down logout also.  I just did this on my personal system and both the login and logout take a fraction of the time they did before.

You don't have to worry about deleting these records because GP recreates the table the next time you log in.

December 29, 2009

gptip42day - BOM Routing Sequence

I have a client that uses GP Manufacturing.  They had an issue with the Machine Costs and Machine Overhead not rolling up when they performed cost rollups.  After much checking, double-checking and triple-checking, we found the problem was with a setting in the Routing.  If you don't mark the Routing as the Primary Routing, GP assumes the Routing is an Alternate Routing. 

Cards>Manufacturing>Routings>Routing Entry

* click image to enlarge

To set the Routing as Primary, open the Routing Sequence Entry window, select the Routing Name, then click on the Routing Name link to open the Routing Header Edit window.  Check the Primary Routing box.  If this box is checked, GP will rollup the Labor & Machine Costs associated with the Routing.  If you don't check it, GP assumes it's an Alternate Routing, ignores it during cost rollup, and Labor & Machine Costs will not rollup.

December 28, 2009

gptip42day - How Sales Taxes are Calculated in GP

As a follow-on to last week's article on Shipping Methods and Types, here, explained in excruciating detail, is GP sales tax calculation logic.

When the system calculates tax on a sale or purchase transaction, it compares the tax schedule assigned to the customer or vendor, as well as the tax schedule assigned to the sale or purchase transaction and any freight and miscellaneous charges. The system checks for details that match between the customer or vendor and the sale or purchase amounts. Taxes are calculated only for the details that match.

If you change the Shipping Method for a transaction, taxes are automatically recalculated. When the Shipping Method is Pickup, the tax schedule for the vendor is compared to the tax schedules for the items. When the Shipping Method is Delivery, the tax schedule for the customer is compared to the tax schedules for the items.

Example - Different Tax Rates for Different Items

A retail store is selling three items to a customer -

 - A shirt at US$20.00
 - A portable radio at US$50.00
 - A liter of milk at US$1.00

The store is located in a town that is subject to 1% city tax and a 5% state tax. However, clothing and food are not taxable in either jurisdiction. Because the customer is purchasing the items at a retail store, the exchange of goods happens in the store's jurisdiction, not the customer's. To calculate the taxes, GP compares the tax schedules that are assigned to each item with the tax schedules assigned to the store and calculates taxes only for the details that match for both.

Example - Different Tax Rates for Different Customers

A mail-order distributor is selling three items to a customer -

 - A shirt at US$20.00
 - A portable radio at US$50.00
 - A box of chocolates at US$10.00

The distributor is located in a town that is subject to 1% city tax and a 5% state tax. The customer is located in another town, where no city taxes apply. Clothing and food are not taxable in either jurisdiction. Because the items are being shipped to the customer, the exchange of goods happens in the customer’s jurisdiction, not the distributor’s. To calculate the taxes, GP compares the tax schedules that are assigned to each item with the tax schedule assigned to the customer and calculates taxes only for the details that match for both.

If you’ve marked the 'Use Shipping Method when Selecting Default Tax Schedule' option in the Company Setup Options window, GP uses the Shipping Method assigned to the transaction to determine where the exchange of goods takes place, and provides the appropriate tax schedule as a default schedule for the transaction.

December 25, 2009

Merry Christmas and Happy New Year

Wishing you and your loved ones very safe and happy holidays.  May you prosper greatly in the New Year.

December 23, 2009

gptip42day - Clarifying 'Shipping Method' in Sales Tax Calculations

When calculating sales tax on either sales transactions or purchase transactions, GP relies on the Shipping Method to determine the sales tax rate applied to the transaction.  Key to determining the tax rate used is the Shipping Type selected in Shipping Methods Setup. 

Tools>Setup>Company>Shipping Methods

* click image to enlarge

Note the Shipping Type.  If Delivery is selected as the Shipping Type, then the tax rate used is that of the destination.  If Pickup is selected as the Shipping Type, then the rate used is that of the shipping point.  The logic behind this is based on the UCC (Uniform Commercial Code) which says that the point at which legal title to goods transfers, is the point at which taxation is determined.  So, if the goods are shipped to your customer, they pay sales tax based on their local rates.  If your customer picks up the goods at your facility, they pay sales tax based on your local rates.  The same applies for purchases you make.

There's a lot more detail behind how GP matches the Shipping Method and Tax Schedules in the Customer or Vendor card with the Tax Schedule in the Item card.  Hopefully though, this provides some clarification as to why it's so important, from a sales tax perspective, to make sure the correct Shipping Methods and Tax Schedules are setup in your Customer and Vendor cards.

December 22, 2009

gptip42day - Internet Information Button

Have you ever noticed the little i button on various cards within GP and wondered what it's for?  This is called the 'Internet Info' button and is used to access the Internet Info window, where email addresses, websites, images, and other electronic information specific to the particular master record can be stored. 

* click image to enlarge

This button appears in the following windows -

-  Address card
-  Company Setup
-  Customer card
-  Employee card
-  Item card
-  Salesperson card
-  Vendor card

Clicking the Internet Info button opens the Internet Info window -

* click image to enlarge

Note the various fields available to record electronic information.  The 'Login' and 'Password' fields are for storing user credentials specific to a secure website or ftp site.  The labels for each of the fields in this window can be changed via the Internet User Defined Setup window in Company Setup.


* click image to enlarge

Users tend to use other fields in master record cards to record web and email addresses because they're not aware this window exists.  Don't you be one of those users!

December 21, 2009

gptip42day - Recording an Unused Check

Occasionally, you have a payables check or checks that for one reason or another gets lost or destroyed.  How do you log that check in the system so you have a complete audit trail of all checks?  Here's one way, compliments of fellow MVP Leslie Vail -

Transactions>Financial>Bank Transactions

* click image to enlarge

1. Enter a Check transaction. 
2. Change the Check Number to reflect the check you're recording.
3. Enter whatever description you want in the 'Paid To' and 'Description' fields.
4. Leave the Amount $0.00.  Ignore the distributions.  They're not required. 
5. Post the transaction.

You now have a $0.00 check recorded in the Checkbook Register with 'Destroyed Check' as the description.

Thanks Leslie!

December 19, 2009

gptip42day - When can I apply the 2009 Year End Payroll Update?

This question has been asked numerous times over the last few weeks on multiple forums. 

The answer is - you can apply the YE update anytime, regardless of whether or not you still have 2009 payroll(s) to process.  The YE update does not include tax table updates for 2010.  Those are part of the 2010 US Payroll Tax Update.

The 2009 YE update does include all previous YE updates, hotfixes, and service packs.

December 17, 2009

gptip42day - Backing out a Journal Entry

One of the frequently overlooked features in GP is the ability to back out a posted GL transaction.  This can be useful in those cases where you posted a transaction with an incorrect posting date or perhaps left some detail out of the description, reference, etc.  Using the 'Correct' button in the Transaction Entry window allows you to reverse the original transaction, and if you wish, re-create the original transaction automatically, make the corrections, and repost it.  This process works for Standard, Reversing, Clearing, and Quick journal entries.


* click image to enlarge

Open the Transaction Entry window and click the 'Correct' button.  The 'Correct Journal Entry' window opens.  Select either 'Back Out a Journal Entry' or 'Back Out and Create a Correcting Journal Entry'.  The difference is in the second option, the correcting entry is created automatically.

Select the Original JE Year and the JE number to backout.  Click 'OK'. 

* click image to enlarge

The original entry debited the accounts.  As you can see, the backout entry is crediting the accounts.  Once you're happy with the entry, post it or save it to a batch.

This example illustrates a simple financial entry.  Correcting entries can be made to transactions originating in other series also.  In fact, you can make a correcting entry to an entry originating in Payables, for instance, without affecting the Payables transaction itself.  All you have to do is go Tools>Setup>Financial>General Ledger and check the 'Allow Voiding/Correcting of SubsidiaryTransactions' box.

December 16, 2009

gptip42day - Changing PURCH account in Receiving Transactions

Beginning with GP9, Service Pack 3 and GP10, Service Pack 2, you can no longer change the PURCH account at the summary distributions level.  It must be changed at the line detail level.  This was a design change made to help with matching the value in the inventory account in the General Ledger to the value of inventory in the Inventory module.

This new behavior causes issues when you want to allocate the receipt of a quantity of the same item to multiple accounts.  The work-around to this involves entering multiple lines of the same item on the receiving transaction, opening the Item Detail window for each line, and changing the account distribution at the line level.

Transactions>Purchasing>Receivings Transaction Entry

* click image to enlarge

Note the 3 lines, all for the same item.  Next, click on the blue expansion arrow to the right of the Item heading.

* click image to enlarge

Change the Purchases account here.  Do the same for the remaining lines and you're good to go.

Note that the purchase order does not have to be setup with multiple lines.  You can add the additional lines in the receiving transaction.

December 15, 2009

gptip42day - Print to Screen and Process Monitor

GP only allows one report or document to be printed to screen at a time.  If you try printing a second document before the first has completed and the screen has been closed, you won't be able to view the second or subsequent reports.  You must close all previous screen prints before the most recent one will appear.

On a related note, if you're printing a report that takes a long time to process, and you're wondering if you actually hit the print button, you can see if the report is in the Process queue and view the progress of the report using the Process Monitor.

File>Process Monitor

* click image to enlarge

All processes that are currently being run in GP will appear in the 'Process' window.  Any process preceded by a '>' (greater than) sign can be removed (canceled).

December 14, 2009

gptip42day - Resource Descriptions

Ever wonder what tables and/or fields are associated with a particular window in GP?  Did you know there's an easy way to find out?  Resource Descriptions provides the answers.  For instance, let's say you want to find the tables associated with the Account Maintenance window.

Tools>Resource Descriptions>Windows

* click image to enlarge

Select the Product, Series, and 'View By' then scroll down to the Account Maintenance line and click on it.  The associated tables appear in the scrolling window in the bottom right corner and the fields appear in the scrolling window in the bottom left corner.

December 11, 2009

gptip42day - Installing Service Pack Selectively on Workstation with Multiple GP Instances

Okay, the title is a mouthful but the solution is really pretty simple. 

I have a client for whom I'm setting up a new instance of SQL 2005 and GP9 because the consultant who originally set their system up didn't allow for expansion in the account framework (grrrrr).  So, for this and other reasons, we've setup a new instance of SQL Server and GP9 on the same server as the existing GP9 instance.  I wanted to apply SP4 to this new instance but not have it affect the existing instance because I had previously applied SP4 to it and it caused some issues with modified reports and a few user security issues.

I did some research in the KBs and found that you can, in fact, selectively apply a service pack to a GP instance on the same workstation as other GP instances and pretty easily at that.

It basically involves copying the GP product code from the registry and inserting it in a command to launch the .msp file for the service pack.  Instead of risking running afoul of my Partner agreement with Microsoft by providing the instructions here, I'll provide the link to the KB article that explains it and leave it at that.

Scroll down to Q13 to find the explanation.

Have a great weekend everyone!

December 10, 2009

gptip42day - GL Posting - Summary or Detail?

Did you know you can choose to post transactions in the GL coming from other series in either Summary or Detail?  Take payroll entries, for instance.  You may want to have them post in summary so you don't have 100's of individual check amounts in your payroll payable account. Of course, which method you choose depends on your specific business needs.

There are two places in GP to set Summary or Detail Posting, in Posting Setup and in Account Maintenance.


* click image to enlarge

Here, you can set the posting method globally, by transaction Series and Origin, by selecting 'Transaction' or 'Batch' in 'Create a Journal Entry Per'.  Or, you can set the posting method by account by changing the 'Level of Posting from Series' from Detail to Summary or vice versa in the Account card.


* click image to enlarge

Note that if you want to make the selection in the Account card, you must choose 'By Batch' in the Posting Setup window and check the 'Use Account Settings' window.

December 9, 2009

gptip42day - Item Receipts & Currency Decimals

Let's say you have an item 'SHOCK ABSORBER' with a Currency Decimal of '0' and a Current Cost of $8.00.  You place a purchase order for a quantity of 15 SHOCK ABSORBER for a total of $125.00, or $8.33 each.  You receive the order, post the receiving transaction, and then check your Purchase Receipts report.  GP shows 14 received @ $8.00 and 1 received at $13.00.  Why is that?

When GP encounters the scenario where the calculation of Unit Cost in a receipt results in a value with a greater number of decimal places than that set in the Item card, GP posts the receipt in two quantities.  The first quantity will be ((Total receipt quantity - 1) x Current Cost).  The second quantity will be ((Total receipt value - value from previous calculation) x 1).  In this example,

14 x  $  8.00
  1 x  $13.00

as shown in the Purchase Receipts report -

* click image to enlarge

This behavior occurs whether the item is lot/serial tracked or not.

December 8, 2009

gptip42day - Modify Acquisition Cost of a Fixed Asset

Let's say someone added an asset and transposed some of the numbers in the Acquisition Cost field.  Is there a simple way to correct it?  Of course!

* click image to enlarge

Open your Fixed Asset General card.  See the blue expansion arrow next to the Acquisition Cost field?  Click on it to open the 'Asset Purchase' window.

*click image to enlarge

Note that you can place your cursor in the Acquisition Cost field and change it.  In this case, the original Acquisition Cost was entered as $2,618.58 but it should have been $2,816.58.  All we have to do is change the value in this field and click 'OK'.  The Acquisition Cost field in the General card is updated to reflect the change.  Save the asset.

If depreciation was run before the cost correction was made, you'll need to go into the Asset Book window and correct the 'Cost Basis' field and recalculate depreciation.

You can change the Acquisition Cost whether the asset was entered manually or added via the FA Purchase interface.

December 7, 2009

gp2themax - Excel Reports

Did you know that, beginning with GP10, GP ships with approximately 200 pre-defined Excel Reports that can be accessed by GP users and non-GP users alike?  The reports are basically the same as the canned SmartLists, such as Account Transactions, Account Summary, Inventory Transactions, Item Quantities, etc.  However, the major differences between these reports and SmartLists is that Excel Reports open directly in Excel, without having to run a SmartList and then export it, and, these reports can be run by anyone who has an ODC (Office Data Connection) connection which means that anyone who has MS Excel 2007 or newer on their computer, and the necessary SQL security rights, can run these reports without logging in to GP!  For instance, this is perfect for setting up auditors with access to GP transaction data without the need for using a GP login.

Here's an example of the Accounts Transaction SmartList.

* click image to enlarge

If you haven't seen these reports yet, ask your GP Administrator to set them up for you.  GP includes a very simple-to-use Reporting Tools Setup facility that allows the reports to be setup in a matter of minutes.

Admininstration>Reporting Tools Setup
Tools>Setup>System>Reporting Tools Setup

* click image to enlarge

December 4, 2009

Here's a great article by Mariano Gomez

gptip42day - Printing Multiple Documents Easily

This applies to GP10 only but if you need to print, let's say, a list of invoices for a customer, here's how to do that without having to open each document, one-at-a-time and printing them.

First, select the Sales series button at the bottom of the Navigation Pane.  Then, further up on the Navigation Pane, select 'Sales Order Transactions'.  A list of posted transactions appears in the results pane.

* click image to enlarge

Next, sort and filter the list to get the desired list results.  Then, place a checkmark next to each of the documents you want to print.  Click the 'Print Documents' button at the top center of the results pane ribbon.  Each of the selected documents will print in sequence.

The Lists panes can also be used to print documents in any of the other series using the same process.

Thanks to my buddy Mariano Gomez (The Dynamics GP Blogster) for the suggestion for this tip!

December 3, 2009

gptip42day - Importing Budgets from Excel

If you receive errors when trying to import budgets from Excel into GP, try this little trick.  Instead of using your own Excel sheet, export an existing budget from GP into Excel, delete the budget detail from that budget and copy your budget detail into the template. 


* click image to enlarge

Select an existing budget to export and click the Excel button.

* click image to enlarge

Select the 'New workbook' radio button and click 'OK'.  Save the file. 

* click image to enlarge

Now, delete the old budget details from the file then copy and paste the new budget detail from your budget file.  VERY IMPORTANT - do not change any of the column headings or reformat the exported file in any manner.  You can copy and paste row data, but do not copy and paste anything from the column headings and above. GP is very particular about the column formatting in the import file.  That's why we're using the exported version rather than the worksheet you created your new budget in.

Save the file and import it into GP.

December 2, 2009

gptip42day - Vendor Items

If your vendors require their item numbers on purchase orders, GP provides a facility to make it easy to cross reference and enter the vendor item number instead of your item number.  As you're probably aware, GP forces you to assign a vendor item number to your item the first time you enter the item on a PO.  Many companies just enter their item number as the vendor item.  However, if your company enters and uses the actual vendor item, here's how to speed data entry during PO creation.

With the PO Entry window open, in GP9, go Extras>Options>Display Vendor Item.  In GP10, go Options>Display Vendor Item directly in the PO Entry window.

* click image to enlarge

Making this selection changes the 'Item' heading in the entry window to 'Vendor Item'.  You then enter the vendor item number, which will be reflected in the printed PO you send the vendor.

When you perform the receiving transaction(s) against the PO, GP converts the vendor item to your item number, based on the vendor item to item relationship established in the Item Vendors Maintenance window and increases the on hand inventory of your item.

December 1, 2009

gptip42day - Print Previously Applied Documents on Remittance

When creating a payables check batch, you have the option to print Previously Applied Documents on the check remittance.

Transactions>Purchasing>Select Checks

* click image to enlarge

Checking this box causes any payment or credit documents applied to an invoice prior to the check selection process to print on the check remittance.  However, you have to remember to check the box prior to building the check batch to include the applied documents.

If you typically do include applied documents on your remittances, you can make this the default setting in Payables Setup.


* click image to enlarge

Check this box and you won't have to remember to check it in the Select Checks window.  If you have a batch on which you do want to exclude the applied documents, you can uncheck the box in the Select Checks window.