Tridea Partners Expanding into New England!

Tridea Partners is excited to announce our expansion into the Northeast! To better service our customers on the east coast, we have opened an office in Portsmouth, NH.  Tridea supports over 300 clients throughout the United States with a strong base of companies in the Life Sciences, Manufacturing, Food and Beverage, Healthcare, Distribution, Alternative Energy, High-Tech and Services Industries. The office is equipped to provide Dynamics AX, Dynamics GP and Dynamics CRM consulting services to help our clients maximize their growth through streamlining business processes.

Portsmouth, NH is a beautiful seacoast town settled in 1623 and is conveniently located only an hour from Boston, MA, Manchester, NH and Portland, ME. The Portsmouth team possesses a depth of knowledge in ERP & CRM Integrations, Customizations, Custom Reporting, Business Intelligence, E-Commerce, Legacy Data Migrations, Material Planning, Manufacturing, Inventory Control and Financial Management.

Since 2004, Tridea Partners has completed over 300 process improvement and business application projects in the US, and provides a complete team of functional and technical consultants who are available to meet in person to discuss your important business challenges.

Contact our New England Microsoft Dynamics Team:

Tridea Partners – New England
1 Middle Street, Suite 204

Portsmouth, NH 03801
Tel: 603.373.9027
sales@trideapartners.com

 

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.

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.

Project Tracking in Dynamics GP

Deciding what is the best way to account for project data in Dynamics GP is a widely debated topic. You can always add a segment to your chart of account structure for “project”, but if you have a high turnover rate on projects this option will result in a very large chart of accounts over time. In order to keep the general ledger from growing to large, using a sub ledger is the next best choice. Two built in options are Analytical Accounting (AA), and Multi-Dimensional Analysis (MDA). MDA is very easy to setup but is a bit limited on features. AA offers more features than MDA and is therefore a bit more complex to configure. If you have complex project billing (invoicing) requirements, then you will want to take a look at a 3rd option from the Microsoft Dynamics GP offering called Project Accounting. From a licensing point of view, you most likely own AA and MDA, but you may need to purchase Project Accounting based on when you bought Dynamics GP originally.

In addition to the 3 modules mentioned above, you might want to have a look at a module called Project Tracking from Encore Business Solutions. Project Tracking (PT) is a fully integrated project module built to work with Dynamics GP exclusively. Once installed, it looks and feels just like any other module that comes with Dynamics GP. PT adds a project sub ledger that can be accessed from all transaction entry windows in GP. PT is very easy to use and comes with many user defined fields allowing it to address a wide variety of project reporting needs.  In addition to the core functionality, PT has optional add-ins that can be purchased for project budgeting and importing data.

Here are screen shots of some of the Project Tracking windows:

Project Tracking

Project Maintenance

Project Tracking Distribution

 

Tridea Partners has implemented all of the solutions mentioned above and we are happy to speak with you to help identify the best option for addressing your project accounting needs.

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