I will no longer be posting to Dynamics GP Land, and all new posts will be at https://blog.steveendow.com
Thanks!
By Steve Endow
A few weeks ago, a question was posted to the GPUG Open Forum asking why the AP Transaction Inquiry by Document was so slow.
The post explained that the company has so many different vendor accounts for FEDEX that they can't easily search based on vendor ID. So they find it easier to search by vendor Invoice Number.
Unfortunately, because they have so much transaction history, and because the window pre-loads some data, it takes over 3 minutes for the inquiry window to even be displayed.
That. Is. Crazy.
I was...intrigued.
What would cause a seemingly simple Dynamics GP inquiry window to take 3 minutes just to open? That's just weird.
So I decided to test the window and trace the SQL activity that occurs when the window performs a search.
It...was...bad. The PM Transaction Inquiry Document window throws out SQL queries like they're going out of style.
Every single PM transaction record that is retrieved by the inquiry window results in at least 3 subsequent queries. FOR. EACH. RECORD.
After seeing that, I gave up on attempting to "fix" or optimize the window. It wasn't a SQL performance issue or a question of optimization. It's just a terrible design.
So, what would an ideal PM Transaction Inquiry window look like?
How about this:
A window that allows you to search by date range AND vendor ID AND vendor name AND document number AND document amount. All simultaneously.
Here is a video explaining the project and demonstrating the PM Transaction Search window that is included in version 1.0:
As you type each character, this magical search window calls a SQL stored procedure and updates the search results. In real time.
To keep the window honest, a status bar at the bottom of the window shows how many records were retrieved, how many milliseconds it took to retrieve the data from SQL, as well as how many milliseconds it took to display the data in the window.
We're talking real-world accountability here, folks.
Want to see more info about a transaction? Just double click on the row to drill into GP! Or right click on a row to view the master record inquiry window.
Don't like the columns that are displayed in the search window data grid? No problem. You can modify the included stored procedure. Want to add a column or two to the data? No problem. Just add a few fields to the procedure.
Are you so thrilled by the results of your search that you want to share them with your coworkers? No problem. You can click on the Copy button to copy all of the data in the grid and paste it into Excel. Or you can click on the CSV button and save the data to a CSV text file.
And the GP Transaction Search tool is extremely simple to install. Just run a SQL script to create a stored procedure in each of your GP company databases, then copy 3 files into the GP AddIns subdirectory. It really is that easy.
You must be thinking: Wow, with all of those features, that fancy search window must cost a fortune!
But that's where you're wrong! The Dynamics GP Transaction Search tool is available for FREE!
Here is the temporary link to download the search tool from OneDrive:
https://1drv.ms/f/s!Au567Fd0af9TogwHx8OKAarDM3Zm
UPDATE: I now have a dedicated page for the GP Transaction Search in the Free Products sections of my web site at:
https://precipioservices.com/free/gp-transaction-search/
But that's not all! In addition to offering the tool for the rock bottom price of Free, I have published the code on GitHub as an open source project so that others can contribute to the project:
https://github.com/steveendow/gp-transaction-search
If you are a .NET developer or a Dexterity developer and are interested in helping enhance GP Transaction Search, please contact me!
If you have any questions about the search tool, or if you have used it in a real GP environment and have any suggestions for improving it, please 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.
Thanks for sharing!
ReplyDeleteGreat idea, Steve! Love the open source plan, too
ReplyDeleteOne though I have is regarding security: can your window be restricted against unauthorized use? E.G. only certain users can open it.
Thank you,
EmilyH
Hi Emily,
ReplyDeleteThe GP Transaction Search windows are currently restricted based on the user's access to the related GP windows. For instance, if a user has access to the PM Inquiry windows, then the user will have access to the search window. The drill down (zoom) windows in GP should also be controlled by GP security.
The PM Search window is currently accessible via the Vendor Maintenance window, which is the only avenue I could imagine a highly restrictive company wanting to limit. If that question comes up, I can ask around and see if there is consensus on removing that navigation path.
And a company (or partner) interested in different navigation or security options can now download the source code and build their own version to suit their needs.
Steve
Hi Steve,
ReplyDeleteGreat tool - but we do have the issue where access to the Vendor Maintenance window is restricted. Has any other method been developed where users who don't access Vendor Maintenance can access this very helpful window?
Thanks
Jerry