This is another “Must Have” free customization example for you Microsoft Dynamics GP system when you have GP Power Tools – Developer Tools module.
When creating new Customer, Vendor and Item cards, it is possible to include certain characters in the ID fields which can cause issues with Microsoft Dynamics GP, its ISV products and integrating products.
GP Power Tools has the ability to capture SQL Profile Traces as one of the five different types of logs it can generate from Microsoft Dynamics GP.
To achieve this, GP Power Tools needs to change some permissions on the SQL Server so that it can both generate the Trace programmatically and also copy the resulting files to the shared Logging Folder.
This is a further update to the previous version of the change tracking project for Item Cards:
Based on the version 2 project, this update adds optional emailing of the changes made to a specified address.
This is another update to the previous versions of the change tracking projects for Customer and Vendor cards:
Based on the version 3 projects, this update adds optional emailing of the changes made to a specified address.
A few years ago I published an article showing you can use a GP Power Tools custom RW Function to execute a SQL query and return data to a report. This article used some techniques which were valid at the time and while they still work, there are better and faster methods available in the later GP Power Tools builds.
Here is the link to the original article:
Following on from the article published on Friday about why Microsoft Dynamics GP uses many 8 upper case character table and column names:
This article consolidates many of the articles written about using MS SQL Server with Dexterity and also publishes original documentation from 1999 on how SQL Server support was added to Dexterity.
Today, I was asked by a consultant how to parse specific data out of the record note associated with a transaction or master record in Microsoft Dynamics GP using a SQL Server query.
GP uses the SY_Record_Notes_MSTR (SY03900) Record Notes Master table to store a text field of up to 32767 characters linked to master or transaction record using the Note Index (NOTEINDX) field. The next available Note Index is stored in the SY_Company_MSTR (SY01500) Company Master in the system (DYNAMICS) database and is assigned to new records when they are created.
This is a further update to the previous versions of the change tracking projects for Customer and Vendor cards:
Based on the version 2 projects, this update adds tracking to the Vendor and Customer EFT Bank Maintenance windows.
This article has two purposes. The first is to highlight a very useful script published by Michael Krasivsky from The Resource Group back in 2016. The second is to explain how easy it is to use the GP Power Tools SQL Execute Setup window to execute scripts against multiple databases.
Microsoft Dynamics GP provides access to all the SQL Resources (Tables, Views, Stored Procedures, etc.) using the DYNGRP SQL role. For this technique to work correctly, all GP users need to be assigned to the DYNGRP role and all SQL objects need access “Granted” to the same DYNGRP role.
After the publishing of the GP Power Tools projects for tracking changes to Customer and Vendor Cards, there are been requests for the same functionality for Item Cards.
Starting with the framework updated in the version 2 of the Customer and Vendor tracking projects:
I have created the same functionality for Item Cards. It is labelled as Version 2 so it stays in sync with the other trackers. Even though this is actually the first release, it is based on the Version 2 code.
This is an update to the previous version of the change tracking projects for Customer and Vendor cards:
Based on the original projects, they have been enhanced to add some more fields into the default fields tracked, add support for array fields, and add support for tracking Modifier Added fields.
You might be aware of the new Security requirements for Bank Account Numbers which applies to ACH Originators and Third-Parties with more than 2 million ACH payments annually and becomes effective on 30-Jun-2022.
NACHA, the governing body for ACH transactions in the United States, is rolling out updated security requirements which are actually best practice whether you meet the transaction level targets or not.
Read on for how you handle the requirements on your Microsoft Dynamics GP system.
As promised in my previous article, this article contains awesome example GP Power Tools – Developer Tools projects for tracking changes to customer and vendor cards records.
Thanks to Eric Gernan from Amaysim Mobile whom I worked with to create this custom functionality and gave me permission to publish generic versions of the projects.
Back in 2017 I wrote an article which demonstrated how to get GP Power Tools to send an email every time the Credit Limit Field on the Customer Maintenance window was edited by a user and saved.
This article revisits this article by enhancing the functionality with the options to log changes to a text file or a SQL table instead of sending an email.
This example shows the different methods that can be used with GP Power Tools to create Self Service SQL Scripts which can be made available to all users or just specific users.
The actual SQL Script used for this demonstration is a simple select query on the Customer Master (RM00101) RM_Customer_MSTR table, but can be replaced by any SQL statements to achieve any manipulation of the data desired.
The recent Build 28.8 hotfix of GP Power Tools incorporated some Developer Tools module changes to enable so very cool customization options for updating reports based on temporary tables. The big feature enhancement is the ability to store a Table Reference as a Memory parameter.
References in Dexterity are like pointers to a particular resource, so the Table Reference allows access to the particular instance table buffer without needing to pass the table as a parameter to a script.
This article is comes about thanks to my friend, Melissa Brown, who approached me for a solution to a reporting requirement she had for a customer due to new legislation brought in by the US state of Minnesota.
The requirement was not possible to solve with the Dynamics Report Writer alone as it involved an array in a table which prevented a static relationship from working to retrieve the data needed.
When writing customizations using GP Power Tools as your development tool, there may be times where you need to return a SQL query as a data set to your Trigger Setup script or Runtime Execute Setup script.
It is possible to display a data set to the end user in a SQL Results window using the MBS_SQL_Results helper function. If you have “Goto” actions defined, the MBS_SQL_Results_Goto helper function will display the data and enable the SQL Goto functionality.
But what if we don’t want the data displayed to the user and just need to use the data in your code.
Earlier this week, a forum post by my friend Abra Gilman on the GPUG Open Forum brought up the problem where a user was getting an error when attempting to insert a new line into a SOP Transaction.
This problem can be reproduced by attempting to insert lines in the same location more than 14 times, on the 15th attempt an error will be generated.
Today I was asked about how you can use Dexterity to create SQL Stored Procedures when the stored procedure itself exceeds 32,768 characters.
This 32K limit is because the largest datatype supported by Dexterity is the Text field which is limited to 32,768 characters. This is the maximum size of a signed 16 bit integer (-32767 to 32768).
Winthrop Development Consultants Blog 