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.

Microsoft Dynamics Evolution – Reasons to Grow from GP to AX

Our customers really do ask the best questions.

I was participating in a pre-sales call with a customer who implemented Dynamics GP in the past two years and was considering their short-term and long-term strategic direction.  They asked:

What drives a client’s decision to upgrade from Dynamics GP to AX?

Each customer’s business model and corporate objectives are unique. Certainly budget considerations, staff experience, and technological preference will influence a company’s decision.

However, there are a few key factors that may be indicators you have outgrown your current GP solution and are ready for the depth of functionality that Dynamics AX delivers.

  • Inventory Management has grown more complex. For example, your company has grown from 1-2 physical locations into a multi-site or global operation with needs for tracking, transferring, and managing inventory across multiple locations and company entities.
  • Warehouse would like to use hand-held barcode scanners. Still related to Inventory Management, but also relates to an additional layer of technology integration between AX and the wireless mobile devices.
  • Point of Sale functionality for front-end retail transactions. The latest version of Dynamics AX 2012 includes POS functionality for your retail locations, without need for separate ISV product purchase.
  • Workflows. Dynamics AX enables development and management of workflows for routing Requisitions, POs, Journals, etc.
  • Supports Robust Manufacturing Process. Much broader and deeper capability for performing Management Resource Planning (MRP). AX is a valuable tool for the Operations and Quality staff, in addition to performing core financials.

Our team at Tridea is trained to carefully assess your ERP implementation against your current challenges and future needs and objectives, whether it is Dynamics GP or another product.  We can help you decide if Dynamics AX is the next step in your evolution to a more carefully managed world.

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

Adding Extended Weight Field to Picking Ticket in Dynamics GP

First, the Item Master table needed to be added to the Report. In report writer, open up the SOP Blank Picking Ticket Order Entered form. Click on Tables to open up the Report Table Relationship window. Click on sopIndividualPickTicketTemp table, and then click New. Select Item Master and click OK.

Report Table Relationships

The Item Master will now be added to your list in the Report Table Relationships window, then click Close.

Now that you have added the Item Master you can create the Extended Weight field on the report. Click Layout on the Report Definition window. In the Toolbar, click on the drop down and select Calculated Fields. Then click New.

Calculated Fields

Follow these steps to create the Extended Weight field within the Calculated Field Definition window:

  1. Name the field
  2. Set Result Type to Integer
  3. Set Expression Type to Calculated
  4. Insert QTY * Shipping Weight into the Expressions Calculated formula bar by:
    1. Go to the Fields Tab, select sopIndividualPickTicketTemp for Resources, and QTY for Field, then click Add.
    2. Next click on the * under Operators
    3. Go to Field Tab, select Item Master for Resource and Item Shipping Weight for Field, then click Add
    4. Click OK to create the new field.

Calculater Field Definition

The field can now be added to the Picking Ticket report. I added it to the Body section of the report, set the Display Type to Data and Format to DLR6_S2 so it would display with normal formatting showing decimals.

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

Modifying Dynamics GP Sales Order Processing

Sure, Microsoft Dynamics GP forms are great, but maybe your business has some unique way of handling things. Perhaps you long for a Sales Transaction Entry window that can somehow incorporate some of your unique business practices. Well, good news – Dynamics GP forms are customizable. Take, for example, the case of how we responded when a client of ours that works in the wine business asked if we could customize Dynamics GP to integrate the logic of their inventory-on-hold shipments. To give a little more background information, our client sells wine and offers customers the option of placing a hold on their orders and having them delivered at a specified later date. At the time, the winery would have a manager enter each on-hold order’s shipment date into an Outlook calendar, check that calendar on a regular basis, and then write and pass around notes with details of each on-hold shipment to the warehouse manager when it was the right time. Our clients asked us to help make this inventory on-hold process less tedious and more reliable.

Tridea was able to improve the on-hold order process by utilizing GP’s modify window tool. Using the tool, we added customized buttons and text fields to various Sales Transactions forms and expanded these forms’ functionality to include processing on-hold ship date information. We added checkboxes to mark whether orders were on-hold and, if so, whether these on-hold orders had been shipped. This allowed managers and users to instantly recognize the status of an order and to manage their on-hold orders with ease. Saving this information to customized SQL tables allowed for sharing of this new data between various sales forms and states of an order; all relevant on-hold shipment information was available from orders to invoices to posted and historical invoices. Furthermore, with this information, we were able to do away with the passed around notes and replace those with a SSRS report that provided a list of on-hold orders according to a user specified date. A custom SSRS report provided an easy to access reminder of when to send out on-hold orders. Rather than go through the redundant act of copying an order’s information over to a Outlook calendar in order to keep track of on-hold orders, our client could now enter on-hold information when they entered order information into GP and then pull a summarized form of that data with a SSRS report. With the help of GP form customization, our client was able to integrate their inventory on-hold logic into GP and make this practice of theirs more efficient.

Check out Tridea’s  7 Signs Your Winery Is Ready For Business Management Software.

This article was written by Kevin Huynh, Dynamics GP Technical Consultant for Tridea Partners. Tridea is a leading Microsoft Dynamics GP provider serving CA, UT, CO and surrounding regions.

Using Calculated Fields to Create Sequential Fields

Recently, a client of ours came to us with a customization request. They wanted all of their checks address fields together as a one single block, regardless of weather the fields contained data.  Many businesses don’t have a full address set, and this can leave a gap between the end of a business’ address and the City/State/Zip Code. Solving this is possible in Dynamics GP2013, and it takes the form of Calculated Fields.

Calculated Fields are great for allowing very specific things to happen within Report Writer.  In this example, we will be creating four calculated fields; Address Line 1, Address Line 2, Address Line 3, and City/State/Zip Code.  These fields will all interact with each other, and Dynamics GP will ignore lines without data rather than leaving blank space.

1.  In Report Writer, open the Report you would like to edit (in this case, Check with Stub on Top and Bottom – Text).  In the Tools window, find Calculated Fields then click New.

Report Writer

2.  Set the Result Type to String, then click the Functions tab.

Calculated Field Definition

3.  Select the radio button next to User-Defined, then select System and rw_SelectAddrLine.  Click Add, then click on the Constants tab.

GP 2013

 

4.  Select Integer, then type the appropriate line number.  In this example we are creating four lines; Address Line 1, 2, 3, and City/State/Zip.  Address Line 1 will have an integer value of 1, Address Line 2 will have an integer value of 2, and so on.  Once this is selected, click Add.

Calculated Field

5.  Click on the Fields tab.  Select the PM Payment WORK resource, then select the fields that are desired in the calculated fields.  In this case we will be using Address 1, Address 2, Address 3, and City-State-Zip Code.  Select each of these, clicking Add between each one.

Constants Tab

6.  Click on the Constants tab and select String.  Leave the field blank, and click Add five times.  The reason we add these five blank constants is that calculated fields require a total of ten calculated expressions.  We have the initial rw_SelectAddrLine and four address lines, so adding five blank strings brings our total to ten.  This should add five sets of quotation marks in the Expressions Calculated field.  Click OK.

Report Layout

7. Repeat this process for Address Line 2, Address Line 3, and City/State/Zip Code.  The only differences will be the name of the field and the integer value chosen in step 4.

8. Once all four fields are complete, put them on top of each other within a given report.  These calculated fields will always show together on a report, regardless of whether every field is populated within GP!

This article was written by Daniel Gonet, Dynamics GP Application Consultant for Tridea Partners. Tridea is a leading Microsoft Dynamics GP provider in Southern California. www.trideapartners.com