Blog Moved!

This blog has a new home! Visit https://blog.steveendow.com/ for all future posts. Dynamics GP Land will no longer be updated.

Thank you!

By Steve Endow

A recent GPUG Open Forum question highlighted a significant performance issue: the AP Transaction Inquiry by Document window was incredibly slow.

AP Inquiry by Document GP2018 so slow

The user had many FEDEX vendor accounts, making searching by vendor ID difficult. Searching by invoice number was preferred, but the sheer volume of transaction history caused a 3+ minute delay for the window to load.

Unacceptable!

This piqued my curiosity. Why would a basic Dynamics GP inquiry window take 3 minutes to open? What could be the cause?

To investigate, I traced the SQL activity during a search.

The results were…not good. The sheer number of SQL queries generated by the PM Transaction Inquiry Document window for each retrieved record was astounding.

Each PM transaction record triggered at least 3 additional queries. This wasn’t a simple SQL or optimization issue – the design was fundamentally flawed.

So, what would an efficient PM Transaction Inquiry window look like?

Imagine this:

A window that allows searching by date range, vendor ID, vendor name, document number, and document amount – all at the same time.

This video explains the project and showcases the PM Transaction Search window included in version 1.0:

https://www.youtube.com/watch?v=R8nvAol7mPA

As you type, this innovative search window utilizes a SQL stored procedure to update the search results in real time.

For transparency, a status bar displays the number of records retrieved, SQL retrieval time in milliseconds, and data display time in milliseconds.

Double-click a row to drill down into GP for more transaction details. Right-click a row to access the master record inquiry window.

Need to customize the displayed columns? Modify the included stored procedure to add or remove fields as needed.

Want to share your search results? Utilize the Copy button to transfer data to Excel or the CSV button to save it as a CSV file.

Installation is a breeze. Simply execute a SQL script to create a stored procedure in each GP company database, then transfer three files to the GP AddIns subdirectory.

And the best part? The Dynamics GP Transaction Search tool is entirely FREE!

Here’s a temporary OneDrive download link:

https://1drv.ms/f/s!Au567Fd0af9TogwHx8OKAarDM3Zm

UPDATE: The GP Transaction Search now has a dedicated page within the Free Products section of my website:

https://precipioservices.com/free/gp-transaction-search/

But wait, there’s more! In the spirit of collaboration, the code is open source and available on GitHub:

https://github.com/steveendow/gp-transaction-search

.NET and Dexterity developers interested in contributing to GP Transaction Search are encouraged to get in touch!

If you have any questions, feedback, or suggestions for improvement, please don’t hesitate to contact me.

https://precipioservices.com/contact-us/

Steve Endow is a Microsoft MVP in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Twitter, YouTube, and Google+

http://www.precipioservices.com