gptip42day - 'Add Item'

Companies that use the Inventory module and manually enter purchase orders for their items may find this tip useful. 

When entering POs for inventory items, manually typing item numbers can lead to errors in the PO because, unless you check the 'Add Item' checkbox prior to data entry in the PO, there is no validation against incorrectly entering an item number.  (Note - in GP9 and previous versions, find 'Options' under the 'Extras' menu in the main menu bar).

Checking the 'Add Item' Option causes this message to be displayed if you incorrectly type an item number in the PO -

GP validates the item number entered and if the if it isn't a valid item, the dialogue box opens asking if you want to add the item you've just typed.  Although not officially a warning, it can be used as such to advise that an incorrect item number has just been entered.  Of course, there are times when you're ordering non-inventory items so if you don't want to add the item, just click 'No' and continue processing the PO.  If it is a new item and you do want to enter it in the item master, click 'Yes' and the Item Card opens.

Unfortunately, the default setting for the 'Add Item' Option is 'unchecked'. Also, even if you have checked the Add Item Option, as soon as you close the PO Entry window, the checkmark is cleared and you have to remember to re-check the Option each time you open the window. Not cool, to say the least.

However, all is not lost.  There is a  chunk (.cnk) file available from MBS Professional Services that changes the default setting to 'checked'.  And, believe it or not, it's free of charge.  Why this isn't included in GP out-of-the-box is a mystery.

Now, if you don't know how to contact MBS Professional Services or would rather take an easier route to getting the Add Item chunk, shoot me an email at fhamelly@eastcoast-dynamics.com.  I have the file and will email it to you.  Until tomorrow . . .

gptip42day - 'Allow Account Entry'

One recurring issue, at least in my experience, is clients keeping their Accounts Payable control account reconciled to the AP Trial Balance.  A frequent contributor to this is general journal entries being posted to the control account.  We're going to look at the way to prevent this, not just in the AP account, but all control accounts in your general ledger.

Look at the Account Card screenshot below -

When the 'Allow Account Entry' box is checked, this account can be entered in distributions in any transaction.  Unchecking this box will prevent the manual entry of this account in a distribution window.  To that you might say 'Yes, but sometimes we need to enter this account into a transaction'.  To which I would respond - 'No, you don't.  Not if your system is setup correctly'.

If you need to manually enter a control account in a distribution window, your Posting Accounts setup is incorrect, whether it's Accounts Payable, Accounts Receivable, Inventory, or any other module.

Unchecking this box will prevent posting a manual journal entry to your AP control account, thereby eliminating one of the most frequent causes of the account being out of balance with the AP Trial Balance.

In fact, not only should you uncheck this box for the Accounts Payable account, the same should be done for the rest of your control accounts so they'll tie to their underlying (subsidiary ledger) -

Cash (Checkbook Register)

Accounts Receivable (AR Trial Balance)

Inventory (Stock Status Report)

Fixed Assets (Fixed Asset Depreciation Report)

Purchase Accrual (Received not Invoiced Report)

Now, if you want to take a further step to control this, take access away from the Account Maintenance window to all but one person who is responsible for maintaining accounts.  If that isn't possible and you have Field Level Security, apply a password to this field and provide it to one person, perhaps the controller or accounting manager.

Prevent manual journal entries into these accounts.  You'll be glad you did.

gptip42day - The Ubiquitous Note Field, Part 2

Today, we look at how to attach documents in our Note window.  Open a Note window -

Click on the paper clip to open the OLE (Object Linking & Embedding) container -

Click on Edit to insert an object -

Select ' Insert New Object' -

Note that in this window, you have a number of options.  You can Create New.  When you select this option, a drop-down box opens to allow you to select from a dozen or so types of objects, such as a Word document, Excel document, Excel Chart, etc.

You can also Create from File.  This is probably what you'll use most as you can just browse to find the document you want to insert.

Once you've selected the file to insert, you can either have the file show as a link in the OLE container or have the actual document contents show there.  If you choose 'Link', you can also have the link appear as an icon, like the Acrobat Reader icon in the example above and you can change the icon to any of a number of pre-defined icons by clicking on 'Change Icon'.

Once you've made your selections in this window, click OK, then in the Note window, click 'Attach'.  Your document is now attached in the Note field.  Anyone who has access to the window in which the note is attached, and has the correct viewer (such as Adobe in this case) on their workstation, can view the note and the attached document.

I'll reiterate that Notes and attached documents are not secure - anyone who can view them can modify or delete them so make sure all users in your system understand this.

gptip42day - The Ubiquitous Note Field, Part 1

I am frequently puzzled that most users, especially accounting staff, don't make better use of the Note fields that are available in every window in GP.  One would think accountants, with their need to document transactions for subsequent review and audit, would make the Note fields their best friends.  Perhaps users just don't get trained properly on the benefits of using Note fields, so here we go -

Use the Note window to create, display, or modify a note for a window or a field.  Record-level notes apply to the specific transaction, will change with each transaction, and are always found next to a field in the transaction.  Window-level notes are specific to the transaction window, do not change with each transaction, and are always found at the bottom of the window.  A window-level note is used to describe something about the use of the window itself.

Note buttons show whether a note is attached to a window or record. If a note is attached, the "page" will appear to have lines of text in it. If no note is attached, the "page" will appear to be blank.

As you can see, a note in this general journal entry would have been perfect to document this transaction.  Instead of entering 'See Wendy P. for detail' in the Reference field, the person entering this transaction could have attached a complete description of the transaction and even supporting documentation in the Note next to the Journal Entry field, eliminating the need for a subsequent reviewer to 'See Wendy P.' unless further discussion was necessary.

To view a note, just click on the Note icon

These are the fields within the Note -

Note ID

Displays the window name or record ID to which this note is attached.

Last Changed

Displays the date and time when this note was created or last modified. This field will be updated each time a user changes the note.

Attachment icon button

Opens a window where you can add an attachment. Attachments are available only for record-level notes, not window-level notes.

You can attach links to documents or images within the Note window. For example, you can attach customer payment overdue notices to a customer note, or attach an image of an inventoried item to an item note.

If an attachment is linked to the note, the "paper clip" on the button will appear to contain a note. If an attachment isn't linked to the note, the paper clip will appear empty.

Attachments are stored in the location specified in the OLEPath= line of the Dex.ini file. (Note - if the OLEPath in the Dex.ini file has not been defined, the paper clip icon will not appear in the Note window - see your IT personnel if this is the case.  The OLEPath should be a shared directory so all users can view the notes.)

Attach button

Attaches the note you have written to a window or field.

Delete button

Removes a note attached to a window or field.

A couple of things to be aware of regarding Notes -

1.  Never store sensitive or confidential information in Note fields.  There is no security on Notes.  If a user can view the window, they can view the notes and attached documents.

2.  Notes can be modified or deleted by any user who has access to the transaction or inquiry window where the Note resides.

Stayed tuned for Part 2 on Notes tomorrow.  We'll cover in detail attaching documents to the Note field.

gptip42day - Posting “To” and “Through” General Ledger

These settings can be confusing to some who have not studied what this terminology means.  Go Tools>Setup>Posting>Posting -

The main difference between these options is that when you Post To General Ledger, you have an extra opportunity to verify Batch transactions before they affect the General Ledger. When you Post Through General Ledger, you save time by not having to complete the additional verification step.

Post To General Ledger

If you choose to Post To General Ledger, batches posted in all other modules will update the module in which the transaction originated, then create a batch of General Ledger transactions. Then you must post those batches within General Ledger as well, to update the posting accounts for the transactions.

There is one exception to bear in mind – Bank Transactions – all Bank transactions are single transactions – there are no batches in Banking. However, if you have Bank Transactions set to Post To, a batch is automatically created by GP for each individual transaction. The Batch ID will begin with ‘CMTRX’.

Post Through General Ledger Files

You can choose to have General Ledger accounts updated automatically when you post from another module to General Ledger. If you mark the Post Through General Ledger Files option, batches posted in other modules automatically update the module in which the transactions originated and the appropriate posting accounts in General Ledger.

Note: Single transactions  (not in a Batch) Post To General Ledger and not Through General Ledger, even if the Posting Setting is 'Post Through'.  A Batch ID is created using the prefix of the Source Document Code for the transaction posted, i.e. 'IVTFR' for an inventory transfer transaction.

Also, at least one of the two Posting Settings must be checked or no General Ledger batch will be created.

An Overhaul of gp2themax

I've decided to start posting a Daily Tip for GP users because I've noticed that a lot of the blogs are technically oriented toward we consulting folks and advanced users.  Even the forums seem to be more technically oriented than when I first started reading them.  So, I want to appeal to those users who occasionally just need a little help with the basics.

I posted the first Tip today on 'Changing Currency Decimals on Inventory Items'. I'd like your help coming up with a name for this daily tip. I'd love to call it the Daily Dynamic but our friend Mr. Polino has his 'Weekly Dynamic' so I don't want to plagiarize him. Any ideas? The winner will receive a free one-year subscription to my blog - LOL.

Oh, and let your colleagues and clients know to tune in to gp2themax to pick up their daily tip.

Thanks!

Changing Currency Decimals on Inventory Items

Sometimes we need to change the Currency Decimals on an existing Inventory Item. This cannot be accomplished through the Item Maintenance Card – it must be done via Tools>Utilities>Inventory>Change Decimal Places. This is an easy change to accomplish but be aware that if there are any items on unposted transactions, decimal places on those items will not be changed. Also be aware that this will not change decimal places on historical posted transactions. It will only affect new transactions.

Open the Change Decimal Places window -
 

Enter an the Item or Range of Items to be changed by Item Number, Description, Generic Description, or Class ID. Click the ‘Process Button’. When processing is finished, an edit list will print showing the ‘Before’ and ‘After’ decimal settings for the item(s) changed.

Note: decreasing the Currency Decimals for Items on Price List(s) will round the amounts in the Price List(s).

Change Data Source in Multiple Crystal Reports with Ease

I needed to change the data source in a number of Crystal reports that I developed for a client last week. I had developed the reports on my own PC, emailed them to the client and then tried to change the data source from within Crystal. Unfortunately, the client was using an older version of Crystal that apparently doesn't easily accomodate data source changes. After determining that I wasn't going to be able to mod the SQL queries to point to the client databases, I went seaching the web for a tool that would help. Gladly, I found one that quite easily changes the data source on one or multiple reports in one pass. Have a look at this website if you find yourself in the same situation - http://www.softwareforces.com/Product/ri/pro/3/rptInspector.htm.

MVP for another year

The powers-that-be at Microsoft were gracious enough to include me for another year with the best of the best GP folks. Congratulations to fellow 2009 MVPs Andrew Karasev, Charles Allen, David Patrick, Leslie Vail, Mark Polino, Mariano Gomez, Mohammad Daoud, Monzer Osama, and Victoria Yudin. Here's looking forward to another fun and interesting year in GP-land!

FIFO/LIFO Perpetual Stock Status Report

Using FIFO or LIFO Perpetual to value your inventory? Want a Stock Status report that ties back to your GL balance?

Modify the Purchase Receipts report to sum the quantity fields in the Site Footer, then insert calculated fields in the Site Footer and Item Footer to subtract the ‘Sold’ from the ‘Received’ quantities to arrive at the 'On Hand' quantity. The total 'Value Remaining' is what your Inventory account balance should tie to.

Although this won’t work as well if you’re using multi-bin because the Purchase Receipts report only reports at the Site level, it makes a great inventory sub-ledger if you only need a Site level report.

Reports Shared Directory Issue

Lately I’ve been spending way too much time with Dave Musgrave’s friend – Report Writer. But, I’m learning a few things along the way. Here’s one trick I learned the other day when trying to import a package into RW.

The client has a shared Reports.dic file that each local machine points to. Even though all users were out of the system, I kept getting the 'Can't open customizations dictionary' error when trying to import the package file. I tried a number of things to resolve this issue but in the end came to the conclusion that there must be a lock in a table somewhere that indicated there were still users in the system. I also suspected this might have something to do with the shared Reports directory.

So, I changed the path in the Dynamics.set file from the shared directory to a local directory and copied the Reports.dic file into the local directory. I ran the package import and it imported with no issues. I then made the mods to the report, copied the local Reports.dic file back to the shared directory, reset the Dynamics.set file to point to the shared directory and voila! Another issue bites the dust . . .

Get Your ERP House in Order

http://msdynamicsworld.com/story/bi-reporting/put-your-dynamics-erp-house-order-prepare-now-economic-rebound

my latest article on MSDynamicsWorld.

Item Numbering Schemes

What's in a name?

Well, the answer depends on who you ask. There are myriad ways of numbering items and all kinds of factors go into deciding how a company should go about this very basic of tasks in their day to day operations. Let's take a look at what the options are, and what that means for your enterprise applications.

The following list of numbering schemes represents an ascending level of intelligence in item numbers - in other words, how much you can tell about the item by just reading the item number. It also often represents an ascending level of complexity in managing item numbers, as well as an ascending order of the length of the item number –

• Dumb numbers – plain, sequential numbers, generally with a fixed number of digits
• Alphanumerics - one level of intelligence higher than dumb numbers, these are basically composed of an alphabetical prefix (generally indicating a product type) followed by a dumb number
• Simple composite item numbers - the next level of intelligence consists of numbers in a hyphenated composite format where one segment indicates a product family and the other segment is a running sequence
• Intelligent item numbers - a multi-segment item number where each segment indicates some characteristic of the item such as item family, sourcing type, storage location, etc.

Which item numbering scheme is right for a company depends on a number of factors. For example, for a hi-tech manufacturing organization, where a lot of items are ordered via electronic purchase orders, stocked and assembled by automated bots, nothing more than a dumb number is necessary. However, in the same industry, a company that manufactures a lot of consumer products and has to stock spares for many generations of products, may find a composite item number (that can indicate a revision etc) a lot more useful.

At the other extreme is a job shop or industrial manufacturing organization (such as aircraft engine or heavy machine manufacturing), where visual identification of items on the shop floor is part of the daily picking and assembly operations. In such a case, it is essential for the item numbering scheme to be somewhat or highly intelligent so that the shop floor worker can immediately spot if he has picked a slightly different or incompatible item.

Alternatively, a combination of dumb and intelligent item numbering may be utilized. In my first job as a cost accountant, which was 30+ years ago, our finished products were identified with a dumb number but the raw materials components utilized intelligence in the numbering. In fact, I can still remember what 254-125-25-04 represented. This was flat bar, 1.25” wide x .25” thick, in 6061-T6 aluminum. In an environment that stocked many, many different types of raw stock, intelligence in the raw materials numbering scheme was extremely helpful in identifying the material on the rack. The finished goods products were identified by labels applied to a mounting flange, so intelligence in the numbering scheme was not a requirement.

As you can see, there is no right answer to what the best item numbering scheme is. It really depends on the nature of your business and how you need to identify your products. What is important is utilizing the best scheme for your business, which means gathering information from all the stakeholders in your company and arriving at the best compromise scheme that satisfies most of the stakeholder requirements.

Account Rollups - not as tasty as Fruit Rollups but just as Satisfying!

An oft-overlooked feature in GP Financial Inquiry is the Account Rollup. If you have a need to monitor the total of a group of accounts - say multiple inventory accounts – that may or may not be in consecutive account number order, then this is the tool for you.

To create an account rollup inquiry option -

1. Open Account Rollup Inquiry window. (Inquiry>Financial>Account Rollup)

2. Choose Modify to open the Account Rollup Inquiry Options window.

3. Enter a name for the option you are creating.

4. Select the segment you want to use to sort the information. The selection you make here will control how the information is sorted in the Account Rollup Detail Inquiry Zoom window.

5. Select the number of columns of information you want to display.

6. For each column of information, enter the column heading you want to use, and the type of information you want to display. If you selected Budget or Other Currency for the type, enter or select the budget or currency you want to display. If you selected the type Calculated, choose the Selection lookup button to open the Account Rollup Inquiry Calculated Column window.

Note - A calculated column must follow the columns it is performing its operations on. For example, assume you set up an inquiry option with column 1 as Actual, column 2 as Budget, column 3 as Calculated, and column 4 as Previous Year. In this case, the calculated column could not include the previous year amounts in its calculations because it is listed after the calculated column.

7. Enter restrictions to define the segment ranges you want to create this inquiry option for. You can set up multiple ranges for a single segment.

8. Choose Save to save the account rollup inquiry option.

Fixing Depreciation Data

Business Case: The client needed to present to their external auditors clear, understandable depreciation reports for the fiscal year just ended. Because of numerous changes to depreciation-sensitive settings in their Internal, Tax, and AMT books, the depreciation reports were very confusing due to all the periodic adjustments that resulted from the changes.

Solution: Clear the depreciation tables, rerun depreciation for all assets.

Here's how it's done:

1. Delete all records in FA00200 for the Book(s) in question. FA00200 is the Asset-Book Master which stores depreciation settings and some summary depreciation info. Deleting this data will remove the Book info for the assets.

2. Delete all records in FA00902 for the Book(s) in question. FA00902 is the Asset Financials Detail Master. It stores the periodic depreciation amounts for each asset. Deleting this data removes ALL depreciation amounts from the beginning of time for each asset.

3. Re-add the book info for each asset by going to the Asset General Card. Pull up the first asset, click the ‘Auto-Add Book Info’ checkbox at the lower left corner of the window, scroll to the next asset, click ‘Yes’ when it asks you if you want to Save, then click ‘OK’ when the next window tells you that certain other Book(s) already exist. This step is recreating the records in FA00200. You must do this for every one of your assets. There is no easier way to do this that I’m aware of.

4. After you’ve re-added all your Book(s) info back to the assets, run the Fixed Assets Books SmartList and verify that you have Book(s) setup for each asset. You can verify this by comparing the number of assets that are returned when you filter on each Book compared to the number of active assets in your system.

5. Once you’re satisfied that the Book(s) have been setup for all assets, run depreciation on all assets through the year and period that corresponds with your most recent depreciation posting. This will repopulate FA00902 with all periodic amounts for the Book(s).

6. Delete the FATRX batch that is generated from GL posting!! You don’t want to post years worth of depreciation again!

This process will result in depreciation reports that are clean and easy to understand. If your Accumulated Depreciation account(s) tied to your depreciation reports before, they still will. If they didn't, now's the time to make adjusting journal entries so they do!