#GPPT Running SQL Scripts for All Companies, such as a smarter GRANT script

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.

#GPPT Protecting Mission Critical Custom Code

GP Power Tools is a subscription based product. This means it needs current, valid Registration Keys to stay operational.

If you have written Mission Critical Custom Code using GP Power Tools – Developer Tools module, you will want to ensure that those triggers and scripts stay active at all times and if the registration fails for any reason that you are notified immediately.

#GPPT Tracking changes to Item Cards Version 2

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:

• #GPPT Tracking changes to Customer and Vendor Cards Version 2

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.

#GPPT Tracking changes to Customer and Vendor Cards Version 2

This is an update to the previous version of the change tracking projects for Customer and Vendor cards:

• #GPPT Tracking changes to 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.

#MSDynGP New NACHA Data Security Requirements coming up on 30-Jun-2022

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.

#GPPT Obscuring Bank Account Numbers in Microsoft Dynamics GP

This article covers the latest “Must Have” free sample project for the GP Power Tools – Developer Tools module (requires Build 28 or later).

Back in 2012, I created code to obscure customer credit card details (see more information below). Using the same concepts, I have now created a project to obscure Bank Account Numbers for Checkbooks, Customers, Vendors and Employees.

Whether you have a requirement (see NACHA Security Requirements article) or not, best practice would be to make sure that Bank Account Numbers in your system are not human readable while stored in the database.

#GPPT Tracking changes to Customer and Vendor Cards

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.

#GPPT Using GP Power Tools to track specific changes Revisited

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.

• #GPPT Using GP Power Tools to track specific changes

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.

#GPPT Building Self Service SQL Scripts

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.

#GPPT Modifying Reports based on Temporary Tables

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.

#GPPT Reporting for Minnesota’s Wage Theft Law

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.

#GPPT Parsing Returned SQL Data into Rows and Columns

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.

#GPPT Renumbering SOP Transaction Lines

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.

• SOP Line Items – Running out of Line Item Sequence (cannot add line items)

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.

#Dexterity Creating Large SQL Store Procedures

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).

GPD Pocket 2 7″ Mini Laptop Windows 10 PC

You might have seen my posts on Twitter and Facebook recently about my new toy mini laptop PC, the crowd funded GPD Pocket 2.

I have been burnt before with crowd funded projects (I supported 3 smartwatches and only one make it to production and it under-delivered 😦 ), so I was wary of getting involved again. However, this was the second generation of the GPD Pocket and demo devices had already been delivered to a number of YouTube reviewers. I felt fairly safe to get involved with crowd funding again, as this would give me a discount and get the device earlier.

#GPPT SOP Batch Maintenance Self Service Scripts

Recently Steve Endow MVP asked on Twitter if there was a simple solution for moving SOP Transactions from one batch to another batch.

This is something that is easily solved as a Self Service script using the GP Power Tools – Developer Tools module. In fact, I had already created a solution for the same request for a customer in Australia using GP Power Tools. I spoke to the customer, Anand, and he said he was happy for me to post the code on the blog.

#SQL Collation – SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CI_AS

Today, we have an article from guest author Craig Verster, Senior Microsoft Dynamics GP Consultant at Microchannel Services, who identified an issue that can affect Australian (and potentially other non-US) SQL Server installations.

When installing SQL Server Database Engine the default collation is dependent on the Locale of the operating system.

i.e. If Australia, then Latin1_General_CI_AS is the default collation method.

If USA (which is the default locale), then SQL_Latin1_General_CP1_CI_AS is the default collation method.

Although both these collation methods use code page 1252 they actually work very differently.

#GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 5

Adding to this week’s series on customising the Payables Transaction Entry window using GP Power Tools, I thought it would be interesting to show some variants of the trigger script using less and less of the built in GP Power Tools features.

If you missed the previous articles please check them out below:

• #GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 1
• #GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 2
• #GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 3
• #GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 4

The idea is to show how the helper functions simplify your code.

#GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 4

Finishing this week’s series on customising the Payables Transaction Entry window using GP Power Tools, today we update the Trigger script and make everything work.

If you missed the previous articles please check them out below:

• #GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 1
• #GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 2
• #GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 3

The final step is to update the Trigger script to perform the desired actions to check the last transaction date and open the Vendor Address Inquiry window.

#GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 3

Continuing with this week’s series on customising the Payables Transaction Entry window using GP Power Tools, today we create the Trigger and test it is working.

If you missed the previous articles please check them out below:

• #GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 1
• #GPPT How to improve Dynamics GP with a little bit of GP Power Tools – Part 2