SQL Script to Assign All Bins to All Items in Microsoft Dynamics GP

Microsoft Dynamics GPI try to do as much through the front-end of Microsoft Dynamics GP as possible in order that the correct business logic be applied to the changes as possible. However, sometimes doing it that way takes far too much time. Recently when working with a particular client, we needed to assign all bins to all items.

Bins were being being introduced so there were no bins currently assigned, which meant a simple SQL script could be created using CROSS JOIN which produces a resultset where each item umber in the Item Master (IV00101) table was paired with the location code/bin number combination in the Site Bin Master (IV40701) table and inserted into Item Site Bin Priorities (IV00117).

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
INSERT INTO IV00117 --Item Site Bin Priorities (IV00117) ( ITEMNMBR ,LOCNCODE ,Priority ,BIN ,MINSTOCKQTY ,MAXSTOCKQTY ) --VALUES ( SELECT ['Item Number'].ITEMNMBR ,['Site Bin Master'].LOCNCODE ,ROW_NUMBER() OVER(PARTITION BY ['Item Number'].ITEMNMBR, ['Site Bin Master'].LOCNCODE ORDER BY ['Item Number'].ITEMNMBR, ['Site Bin Master'].LOCNCODE, ['Site Bin Master'].BIN) AS Priority ,['Site Bin Master'].BIN ,0 AS MINSTOCKQTY ,0 AS MAXSTOCKQTY FROM IV00101 AS ['Item Number'] --Item Master (IV00101) CROSS JOIN IV40701 AS ['Site Bin Master']--Site Bin Master (IV40701) ) GO

If any assignments exist in the IV00117 table the script will fail with a duplicate key error; the script also assigns priority based on the alphabetical order of the site/bin number combinations which may not be suitable in all contexts.

ClassicPress Plugin Development: Rename First Sublevel Menu of a Custom Top Level Menu

ClassicPress PluginsThis post is part of the ClassicPress Plugin Development series in which I am going to look at both best practice for developing plugins and how I approach some requirements as well as some of the functions I commonly use.

In the last post I showed how to create a custom top level menu and mentioned it creted both the top level menu and a sublevel menu of the same name.

As the below example, extracted from my To Twitter plugin which adds a submenu to the existing azrcrv-tt menu with a name of Settings. As the first, $parent_slug, and fifth, $menu_slug parameters match the top level menu the add_submenu_page function renames the default first submenu entry:

add_action('admin_menu', 'azrcrv_tt_create_admin_menu');

/**
 * Add to menu.
 *
 * @since 1.0.0
 *
 */
function azrcrv_tt_create_admin_menu(){
    
	add_submenu_page(
				'azrcrv-tt'
				,__('Settings', 'to-twitter')
				,__('Settings', 'to-twitter')
				,'manage_options'
				,'azrcrv-tt'
				,'azrcrv_t_display_options');
				
}

To rename a custom top level menu on a network admin dashboard, change the admin_menu tag in the add_action function call to network_admin_menu.

Click to show/hide the ClassicPress Plugin Development Series Index

azurecurve ClassicPress Plugins: Remove Revisions

ClassicPress PluginsThis is part of the azurecurve ClassicPress Plugins series which introduces the plugins I have available for ClassicPress.

The plugin I am going to cover in this post, is one written specifically for ClassicPress; Remove Revisions.

Functionality

While revisions can be disabled or limited in number with settings in the wp-config file, there is no way to allow for the deletion of revisions over a certain ago. That is what this plugin allows you to do.

In the options you can set the number of months after which revisions are to be deleted. They can then be deleted at the click of a button or via a cron job running on a daily schedule.

The options also allow you to select the post types (both standard and custom) which can have revisions removed.

Removal of revisions is done using the ClassicPress function to ensure they are done correctly.

This plugin is multisite compatible, with options set on a per site basis.

Download

The plugin can be downloaded from my GitHub.

Click to show/hide the azurecurve ClassicPress Plugins Series Index

Fixing “This share requires the obsolete SMB1 protocol…”

WindowsThis is actually an older issue, I didn’t post about as I first came across it months after Microsoft made the change. However, I’ve seen it come up a few times recently after installing Windows updates, so I am posting this as a reminder of how to fix the issue.

I have seen this on a couple of client sites which has been referred to the client’s IT department to take action, but I also get this on my home network.

The issue is when you try to access a network share, such as from a NAS (Network Attached Storage):

SM! error

You can't connect to the file share because it’s not secure. This share requires the obsolete SMB1 protocol, which is unsafe and could expose your system to attack...

Continue reading “Fixing “This share requires the obsolete SMB1 protocol…””

Trust Relationship Between This Workstation and the Primary Domain Failed

MicrosoftHaving never seen the below error message before, I have now seen it twice in the last few weeks.

The first time was when I started a VM running an old version of Microsoft Dynamics GP for the first time in a few months; the second was when I copied the VM from my laptop onto the server which had a previous copy of the domain controller VM.

The second instance was not unexpected, but at least I knew how to resolve the problem having seen it recently on a VM which hadn’t been run for a while.

The error is produced when you try to log into Windows:

Trust relationship error when logging in

The trust relationship between this workstation and the primary domain failed

The solution is to remove the computer from the domain, restart the computer and then add it back to the domain.

Microsoft Dynamics GP Workflow Navigation Lists Not Working Correctly After Upgrading

Microsoft Dynamics GPI’ve been involved with a number of upgrade projects recently where we were both upgrading the client to the latest version of Microsoft Dynamics GP, but also introducing some new functionality such as workflow for approvals.

Quite a few of these clients are long time users of Dynamics GP who started on version 9 or before. This means that in most cases, when introducing workflow, we find that the Not Submitted and Pending Approval navigation lists don’t work. By this, I mean they do not display any data.

This issue was covered back in 2017 by Derek Albaugh on the Dynamics GP Support and Services Blog.

The blog post includes scripts to clear down the List View Master (SY07220) and List View Filters (SY07230) tables whether you have custom navigation lists or not. These tables are like the Menu Master (SY07110) table which repopulate when you log back into Dynamics GP.

For most of the clients we have found that even if there are custom navigation lists, we need to delete the contents of the two tables and recreate the customisations rather than removing only the defaults, as Dynamics GP crashes whenever we tried to access a navigation list after removing the defaults.

azurecurve ClassicPress Plugins: Disable FLoC

ClassicPress PluginsThis is part of the azurecurve ClassicPress Plugins series which introduces the plugins I have available for ClassicPress.

The plugin I am going to cover in this post, is one written for ClassicPress by John Alrcon which I have now adopted; Disable FLoC.

Functionality

Have you ever heard of “Federated Learning of Cohorts” — or FLoC? It is Google’s next-generation technique for tracking users across the web. They say it’s anonymous and safe. We know better. This plugin sets a header to disable the FLoC tracking.

Download

The plugin can be downloaded from my GitHub.

Click to show/hide the azurecurve ClassicPress Plugins Series Index

azurecurve ClassicPress Plugins: Username Protection

ClassicPress PluginsThis is part of the azurecurve ClassicPress Plugins series which introduces the plugins I have available for ClassicPress.

The plugin I am going to cover in this post, is one written for ClassicPress by John Alrcon which I have now adopted; Username Protection.

Functionality

This plugin allows you to remove anonymous access to usernames in ClassicPress to help mitigate brute-force attacks. For visitors who are not logged in, usernames are removed from the REST API, feeds, author pages, and prevented from exposure through other common vectors of discovery.

Download

The plugin can be downloaded from my GitHub.

Click to show/hide the azurecurve ClassicPress Plugins Series Index

SQL Function to Return Last Workflow Final Approval Date

Microsoft Dynamics GPThe below SQL function returns the last Microsoft Dynamics GP workflow final approval date and time; it is always returned, not just when final approved is the current status.

This function was created to be used in a check of approved documents to see when they had last been approved; it was used in conjunction with the SQL function which returns the current workflow status.

IF object_id(N'uf_AZRCRV_GetLastWorkflowFinalApprovalDate', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetLastWorkflowFinalApprovalDate
GO
CREATE FUNCTION dbo.uf_AZRCRV_GetLastWorkflowFinalApprovalDate(@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(200))
	RETURNS DATETIME
AS
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
BEGIN RETURN ISNULL(( SELECT TOP 1 FORMAT(['Workflow History'].Workflow_Completion_Date, 'yyyy-MM-dd') + ' ' + FORMAT(['Workflow History'].Workflow_Completion_Time, 'HH:mm:ss.fff') FROM WF30100 AS ['Workflow History'] INNER JOIN WFI10002 AS ['Workflow Master'] ON ['Workflow Master'].WorkflowInstanceID = ['Workflow History'].WorkflowInstanceID WHERE ['Workflow Master'].Workflow_Type_Name = @WorkflowTypeName AND ['Workflow Master'].WfBusObjKey = @WfBusObjKey AND ['Workflow History'].Workflow_Action = 10 ORDER BY ['Workflow History'].DEX_ROW_ID DESC) ,'1900-01-01 00:00:00.000') END GO GRANT EXECUTE ON uf_AZRCRV_GetLastWorkflowFinalApprovalDate TO DYNGRP GO

Centralising Dictionaries in Microsoft Dynamics GP

Microsoft Dynamics GPFor as long as I’ve been working with Microsoft Dynamics GP, I’ve been setting clients up with centralised forms and reports dictionaries in order to simplify deployment. As long as you’re not using VBA which needs to be installed onto each client, centralising the forms and reports dictionaries means you can import and customisations omly once. If each client machine has its own local forms and reports dictionaries then you need to import onto every computer.

This week when I did this, I ran into problems getting the path working correctly. I’m sure I didn’t do anything differently than normal, but it didn’t work. It took me a few minutes to get sorted out, so I am posting the below path as a reminder than you can use a UNC path for accessing the remote dictionaries, but the slashes are backslashes instead:

//FILESERVER/Dynamics Central/Dictionaries/REPORTS.DIC

As long as you have a good network, centralising the forms and reports dictionaries is my recommendation to ensure that all clients have exactly the same customisations in use. You would not centralise the application dictionaries, just the ones for forms and reports.