Viewing Missing Transactions in Apply Sales Documents with National Accounts

One of our clients recently encountered an issue where they were unable to see posted invoices for a customer in the Dynamics GP Apply Sales Documents window.  These documents were correctly created and processed, and were visible in inquiry, but were not available to have payments applied when viewing the customer in Apply Sales Documents.

After some troubleshooting, we ascertained that the cause of the issue was National Accounts.  By default, when a user enters a customer in Apply Sales Documents, the National Account radio button is automatically selected next to View/Apply:

Dynamics GP National Account radio button

This is normally not a problem, but occasionally the parent account is in a different status or handled differently than the children.  In this case, we simply needed to select the Specific Customer option and choose the child from the dropdown; this allowed users to view and apply payments to the invoices that were previously missing!

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, NH, MA, NY and surrounding regions.

 

Microsoft Dynamics GP2015 R2 Feature of the Day

Combine General Ledger Inquiry

In Microsoft Dynamics GP 2015 R2 in General Ledger, the inquiry windows have been consolidated.  Now, you can view open and historical information in the same window for Summary Inquiry, Detail Inquiry and for Account Summary.  Any GoTos or drill backs will now go to the single window instead of the user picking open or history.

 GP 2015 R2 Combine General Ledger Inquiry

 GP 2015 R2 Combine General Ledger Inquiry

Contact Tridea Partners if you are looking to find out more information on GP2015 R2!

 

How To Find The Hiding Notes Window In Dynamics GP

Have you ever clicked on the Notes icon in a GP window, saw it flash, but the window never popped up? Well here is a trick to help get it back up on your screen instead of hiding in the background.

When you click on the note icon in the GP window and nothing pops up, hold down ALT and SPACEBAR, this will bring up the following menu.

Dynamics GP Note Window

Click on Maximize. This will open the note window to fit your computer screen. Then just simply resize the window by dragging in the corners and/or sides of the maximized Note window. Once you close out of the resized note window, it will open to that newly resize window you just created. This issue can come up a lot with users who use dual monitors. Hope this helps!

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, New England and surrounding regions.

Tracking/Exporting Changes in Dynamics GP

You might be facing the dilemma of figuring out where to start with tracking the changes to Dynamics GP tables and then exporting those changes to some file. You might be out of ideas on what to do, and by some chance, you end up at this blog post. Well, you’re in luck; this post is on how you can approach tracking changes to Microsoft Dynamics GP tables and then exporting them.

MSSQL (from here onward, referred to as SQL) includes a tool named triggers that allows one to run custom query code in conjunction with inserts, updates, or deletes done to a table. Triggers have access to two special temporary tables – the inserted table and the deleted table – that hold information on what was changed when the change occurs. The inserted table includes information on inserted and updated records, while the deleted table has data on what was deleted. In order to track the changes to a table, a trigger can be programmed to pull information from the inserted and deleted tables and then log them to a custom log table.   If there was a need to track newly created records to the Vendor card in GP, for example, one would add triggers to at least the PM00200 (Vendor Master File – Purchasing) and PM00300 (Address Master – Purchasing) tables and use information from the inserted table to create entries to the custom Vendor card log table. Some words of caution: before deploying any triggers to production, be sure to test the trigger extensively. Having a bad trigger that fails can interrupt GP functionality. If, for example, the insert trigger on a PM00200 fails while creating a Vendor card, the Vendor card will fail to be created as well.

There are two main options for exporting changes from a logging table.

  1. The first would be having someone with some SQL know-how to go into SQL Server Management Studios and then query the logging table. If the table contains a logged datetime field, the user can add a date filter to the where-clause of the query and look at log entries from a particular interval. The query results can then either be saved to a file by choosing to execute the results to file or they can be viewed within SQL Server Management Studios by executing the results to a grid.
  2. The second option involves building a custom application that exports the changes. The application would have to connect to the custom log table in SQL and execute a query that pulls the appropriate data and then write this data to some file.

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

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.