Resolving a Payables Duplicate Key Error

One of our clients recently encountered the following error message when attempting to post a batch:

GP Payables Duplicate

Text: Unhandled database exception: A save operation on table ‘PM_Distribution_HIST’ has created a duplicate key.

A solution that is commonly suggested is to rebuild the PM Keys table (PM00400) then run Check Links on the Payables series, but this did not resolve the error the client was experiencing.  We ended up needing to go into the backend, and this is where we found our resolution.

In the SQL database, we noticed something odd.  It seemed the batch was interrupted during posting right at the beginning of the process, so records from the first voucher number in the batch were present in both the work and historical tables.  The batch was immediately throwing the save operation error whenever a user attempted to re-post it due to this duplication.

In order to resolve the error, we needed to remove the duplicate records from the following historical tables:

PM30300 – Apply To History

PM30600 – GL Distributions for Historical Transactions

 

To figure out which records were the duplicates, we ran a select statement against each voucher number present in the problematic batch.  Since the batch was not fully posted, any voucher numbers associated with the batch that appeared in these tables were data we needed to remove.

 

The tables mentioned above can hold multiple copies of the same voucher number (VCHRNMBR) from different years, so we needed to include a secondary modifier in order to remove only the problematic transaction.  In our case we used the Vendor ID (VENDORID), but values such as the posting date will work as well.   Here are the scripts we used, replacing XXX with the Voucher Number, YYY with the Vendor ID, and ZZZ with the Batch ID.

 

delete PM30600 where VCHRNMBR = ‘XXX’ and VENDORID = ‘YYY’

delete PM30300 where VCHRNMBR = ‘XXX’ and VENDORID = ‘YYY’

update SY00500 set MKDTOPST=0, BCHSTTUS=0 where BACHNUMB = ‘ZZZ’

 

The delete statements were what removed the duplicate Voucher Number data; the update statement reset the batch to the Available status so that users could successfully post it.

This article was written by Daniel Gonet, Dynamics GP Application Consultant for Tridea Partners. Tridea is a leading Microsoft Dynamics provider serving CA, AZ, UT, CO and surrounding regions.

The Dynamics GP Experience – A Project Manager’s Perspective

Microsoft Dynamics GP is a right-sized business solution designed to meet the demands of your small to medium-sized business.  In today’s fast-paced business environment, our clients find themselves wearing multiple hats, multi-tasking, and being asked to increase throughput quickly with much smaller staffs.

A successful Dynamics GP implementation can of course be measured in terms of feature-rich functionality, or sound and scalable architecture, but I believe the best measure of a successful implementation is user adoption and satisfaction.

What drives user adoption and satisfaction with their Dynamics GP system?

First, a little about my background…

I’ve been an IT Project/Program Manager since before the Year Turned 2K, responsible for building and managing client relationships, and rolling out many different technologies on almost as many platforms.  (I cut my IT teeth on VAX/VMS systems, swapping out HUGE hard drives bigger than a pizza box, and before that, one of the first IBM workstations that actually had truly “floppy” 8” disks for loading the O/S and program every time you booted up.)

Technology has improved a thousand-fold since then (or even more if you like to measure such things), but from my center court vantage-point coaching users and technical teams, here’s what I’ve learned contributes to user adoption and satisfaction, from the client’s standpoint:

  • Did the system deliver on our fundamental problem statement?  Learning our client’s underlying business objectives is the key to ensuring your Dynamics GP configuration delivers value.  This means asking clarifying questions and seeking to understand the corporate goals and drivers that prompted this investment. A client’s business objective does not prescribe the exact technology solution or design, but instead provides high-level guiding vision and touchstone for the team to ensure they are on-track throughout the course of the project. (Sample Business Objective:  Reduce Amount of Time to Perform Month-End Close.)

 

  • I don’t know exactly what I need – help me see what’s possible:  Our Design Workshops are led by skilled Functional Consultants to provide a structured tour of Dynamics GP on a development system configured per Microsoft and Tridea’s combined best practices. This forum allows the client to see the features the system provides, while the GP Functional Consultant actively interviews them about their current process, pain points, and offers suggestions about configuration parameters.  By this means, we can collect a complete and comprehensive set of configuration requirements, while also beginning to train users on the Dynamics GP experience.

 

  • Involve End-Users in Testing: Encourage your client to include members of their team in User Acceptance Testing phase.  Not only do the end-users provide valuable testing results and insight, but the testing process offers an early stage for training and helps the users gain familiarity with the end-to-end process and user interface before the go-live date.

 

  • Help with Cutover Preparations: Implementation projects can be very stressful because there are many tasks that the users need to perform in order to prepare their data and underlying processes for the new system. (For example, GL, Inventory, Item ID clean-up.)  Developing checklists, providing current system data exports and providing best-practice input are aids to busy clients to help them complete the pre-migration work to ensure clean and organized source data for the new system.  This up-front investment results in a healthier dataset in Dynamics GP, so time can be spent using the new tools, as opposed to performing data QC.

The foundation of trust in the new system is earned when we partners closely with our stakeholders from the beginning, actively soliciting and listening to their needs, and supporting them throughout the testing, training, and cutover process.

This article was written by Juanita Schoen, Dynamics GP Project Manager for Tridea Partners. Tridea is a leading Microsoft Dynamics provider serving CA, AZ, UT, CO and surrounding regions.

 

Integrating Dynamics GP with eOstar and Aloha POS

On a recent project our client a microbrewery wanted to bring data into Microsoft Dynamics GP from two different 3rd party products that they were using in their day to day business.   The customer was using eOstar Route Accounting Software to manage their deliveries and Aloha POS for their Tasting Room Sales.

From eOstar the client was looking to bring in Journal Entries for their General Ledger as well as Bank Deposits to interact with the GP Bank Reconciliation module.  We were pleased to find that eOstar had developed their own integrations to Dynamics GP.  The client worked with their eOstar representative to coordinate the upgrade of the product to bring the eOstar GL and Banking Transactions into GP through the eOstar integration modules.

The customer also wanted to bring in the daily Sales of the tasting room from the Aloha POS system.  This would allow Tender sales receipts to be recorded in GP and keep an accurate inventory of the tasting room items in Dynamics GP.   We created a custom .net application using vb.net and eConnect to bring the SOP data into GP.  The integration retrieved the Cash, Credit Card, Gift Card, Comp Tender Data items and line item Sales data from Aloha EOD files and created a SOP Invoice for each day.  One of the challenges we had was how to handle the beer inventory in the tasting room as beer was sold by various pour sizes, by flights, kegs, and cases.  We setup a unit of measure in GP for Ounces to keep track of the beer poured in the tasting room.  We were able to use categories and descriptions in the Aloha item data to map the item to the Ounce Unit of Measure and provide correct ounce value.  Another challenge was how to handle a flight of beer which is several small samples of different beers sold as one unit.  We defined flights in GP using kits where we were able to specify the ounces and beer that made up each flight.

In case you were wondering there are 1984 ounces of beer in a keg.

Check out our White Paper on Dynamics ERP for Craft Beer Production.

This article was written by Patti Vuz, Dynamics GP Technical Consultant for Tridea Partners. Tridea is a leading Microsoft Dynamics GP provider in Southern California.

Professional Services Tools Library – Microsoft Dynamics GP

A great new feature is available to Microsoft Dynamics GP users at no additional cost, the Professional Services Tools Library (PSTL).  Prior to Microsoft Dynamics GP 2010 and 2013, these tools were available by additional purchase, only.  Microsoft Dynamics GP 2010 and 2013 makes them available – absolutely free – to both new and existing users. PSTL can be used throughout most modules.

One of the most commonly used tools is the Account Modifier/Combiner tool which allows users to map old GL accounts to new GL accounts.  It can easily be accomplished through an Excel mapping file (one column for old, one column for new) and uploaded using the Software Administrator (SA) credentials when all users are out of the system.

If an account is setup incorrectly and not determined until posting to that account has occurred, the activity can be transferred to a new account number. In addition, as business needs change, the entire Chart of Accounts can be restructured.  Account numbers and segments can be increased or decreased.

There is also the Vendor Modifier/Combiner, Customer Modifier/Combiner and Item Modifier/Combiner.  It is very similar to the Account, but allows updating of Vendors, Customers and Item Numbers.  These changes will be made throughout the database, including historical year data.

Overall, these tools are easy to use and can be downloaded and installed quickly. Contact Tridea Partners to learn more!

This article was written by Kaydee Baker, Dynamics GP Application Consultant for Tridea Partners. Tridea is a leading Microsoft Dynamics GP provider in Southern California.