Dynamics GP–syWindowDefaults cannot find the table SY01407

A get/change operation on table ‘syWindowDefaults’ cannot find the table.

After upgrading a Dynamics GP system from GP2015 => GP2016 => GP(18.4) the following error occurred on opening some enquiry windows in GP;

A get/change operation on table ‘syWindowDefaults’ cannot find the table.

Invalid object name ‘SY01407’

Invalid object name ‘SY01407’


The above window is used for the “The Default Inquiry Sort options for Payables, Receivables and Bank Reconciliation”, a feature introduced in 18.4 version of Dynamics GP.

According to the linked documentation, this feature saves some of the field settings and window sizes for windows:

     Receivables Transaction Inquiry - Customer  (Inquiry > Sales > Transaction by Customer)

     Receivables Transaction Inquiry - Document  (Inquiry > Sales > Transaction by Document)

     Payables Transaction Inquiry - Vendor  (Inquiry  > Purchasing > Transaction by Vendor)

     Payables Transaction Inquiry - Document  (Inquiry > Purchasing > Transaction by Document)

     Checkbook Register Inquiry  (Inquiry > Financial > Checkbook Register)

     Checkbook Balance Inquiry  (Inquiry > Financial > Checkbook Balance)

     Sales Order Processing Item Inquiry  (Inquiry > Sales > Sales Items)


The information for the feature is stored in the following table:

Dynamics GP table name: SY01407
Dynamics GP Technical Name: syWindowDefaults

Investigation

Checking in the database to see if the table was in SQL server, proved it was not:

select * from DYNAMICS..SY01407

Msg 208, Level 16, State 1, Line 1
Invalid object name 'DYNAMICS..SY01407'.

So it would seem this table got missed when the GP installer/upgrade scripts were created!

There is evidence of other people having similar experience on the forums, Inquiry error message missing table SY01407


Creating building table SY01407 and its associated database objects

Being a new table it is easy to build as you don’t need to worry about pre-existing data. Do so using the Dynamics GP SQL Maintenance window as user sa.

Microsoft Dynamics GP>>Maintenance>>SQL

Select Database DYNAMICS

Product Microsoft Dynamics GP

Locate the table in the list (“System Window Defaults”) and select it (you will see 1 object selected as shown below)

Select the checkboxes to “Create Table” and “Create Auto Procedure

SQL Maintenance Window Dynamics GP

Then select Process button, the missing table will be created along with some stored procedures and now the error messages should stop occurring as the defaults feature should start to function.

Thanks to Beat Bucher for confirming the issue for me and confirming I could use the above window to build the missing database object.

He noted there is also a script you can obtain from GP support if you raise a ticket, that effectively does the same thing as the GP client does above.

Resulting database objects created

table:
[dbo].[SY01407]

stored procedures:
[dbo].[zDP_SY01407F_1]

[dbo].[zDP_SY01407L_1]

[dbo].[zDP_SY01407N_1]

[dbo].[zDP_SY01407SD]

[dbo].[zDP_SY01407SI]

[dbo].[zDP_SY01407SS_1]

Important Note:

Although this will get you running, it may be a sign of other issues with the upgrade. It is always important to run a dummy upgrade and perform User Acceptance Testing on it to ensure everything is ok before doing the upgrade of the production environment.

Dynamics GP Focus trigger registration failed: taUpdateCosts after installing Shipping Notification Tool 2013 on GP18.4

Focus trigger registration failed: taUpdateCosts

After installing the Shipping Notification Tool on Dynamics GP 18.4 the error “Focus trigger registration failed: taUpdateCosts” is shown after launching Dynamics GP.

I guess some Dexterity changes were made to the forms that the tool is plugging into, causing the Dex trigger to fail like this.

Luckily Harry Lee has already investigated this issue on the community forum post Focus trigger registration failed: taUpdateCosts after Shipment Notification Installed and Configured.

 There is a another build of the tool "Shipment Notification Tool version 2018" that fixes the issue available from Microsoft Support, under a free support ticket.

After dropping the new .cnk file into the GP application, this new build fixes the issue.

 If you already have the product installed as this is an upgrade, do not run the taShipmentnotification_Create.sql script, as it drops your table,. I did a file comparison on the previous version and this version and they are identical, so no schema changes in the tool has were made. 

Note:

There is a lack of information in the partner channel about the Shipping Notification Tool, it will often be thought to be part of the Professional Services Tool Kit (PSTL). This is not the case, the PSTL is now part of main media installer for GP and the shipping notification tool is separate cnk available only from MS support via a support case. 

Deploying Dynamics GP Addin Using Visual Studio Menu Item

I demonstrated this technique in one of the sessions I presented on GP development at the 2016 Tampa GPUG conference, but never got around to writing about it.

Often when developing Dynamics GP Addins, you may wish to deploy the current build to your local copy of Dynamics GP, perhaps for testing. This can be done manually but gets old real quick after many iterations.

I set my development environment up by creating a Visual Studio Menu item to deploy the current build of my GP Addin. This means I can simply use a hot key to deploy my code to the application and start debugging within GP or testing.

This solution copies the debug files into the application Addins folder if the build is debug, and removes them if it isn’t.

Follow this guide for what to do, the following screen shot shows the “Deploy GP Addin Locally” option we are aiming to build…

Create the Menu Item in Visual Studio

Visual Studio External Tools Menu Item

Visual Studio lets you add custom Tools to the Tools menu, select “Tools>>External Tools…”, to set this up.

Visual Studio External Tools Window

Use the “Add” Button on the form to create a new entry with the following value:

Title: Deploy GP Addin Locally
Command: powershell.exe
Arguments: -ExecutionPolicy RemoteSigned -File "$(SolutionDir)\Deployment Batch Files\DeployGPLocally.ps1" -BuildOutputFolder $(BinDir)
Use Output window: “Ticked”



Click Apply and OK on the window to preserve your changes.

The “Deploy GP Addin Locally menu option should now exist on the Tools Menu of Visual Studio but it wont work yet, not without creating the power shell script it calls.

The Menu calls the power shell interpreter (Powershell.exe),  passing the script to run and the build directory to run the script against as command arguments.

The external tools window provides some super handy environment variables you can insert into the fields. Click the little arrow boxes to the right to see the options available, useful for future reference. However in this case we take the output build location for the binary output $(BinDir) and use that folder as the basis for the script to run against. We also use the ${SolutionDir) variable to locate the powershell script, as different developers may have the source in different root directories.

Create the power shell script

Next create the script that is called from the menu item. I have a folder in the solution for this script (Deployment Batch Files), you can hack the location around to your needs changing the path as required.

Save the following into a text file with the name “DeployGPLocally.ps1" in a “Deployment Batch Files” folder located off the root folder of the solution folder for the AddIn Solution.

The script takes the build folder as a parameter, it then looks up the Dynamics GP Application location from the registry, that is then used for the destination of the copied files.

The script decides if visual studio is in a debug or release build mode, by looking for the folder “debug” in the current build path. It then copies the files appropriate to the build type, to the application Addins folder.


DeployGPLocally.ps1

param ($BuildOutputFolder)
 
### SCript to automate deployment of GP Addins to the installed application Addin folder during development
### Written by T. Wappat 2021-11-25
###
###	Todo - check folders exist before operation
### Todo - provide a list of registry locations for various version of GP  ###    https://timwappat.info/post/2016/12/02/Lookup-Dynamics-GP-install-location-from-registry-key
 
# $AddInsPath = "C:\Program Files (x86)\Microsoft Dynamics\GP2015\AddIns\"
# Fetch the location of GP application from the registry (this requires that GP installer was used to install it)
$AddInsPath = Get-ItemPropertyValue 'HKLM:\SOFTWARE\WOW6432Node\Microsoft\Business Solutions\Great Plains\v14.0\1033\DEFAULT\SETUP' 'AppPath'
$AddInsPath = "$($AddinsPath)AddIns\"
 
# Note the following is regular expression match
if($BuildOutputFolder -match '.*\Debug\.*' )
{
	Write-Output "Starting to deploy GP (Debug) to $AddInsPath"
	Write-Output "================================================================================================="
	Copy-Item -Path ($BuildOutputFolder + "\*.dll") -Destination $AddInsPath -Recurse -Force -Verbose
	Copy-Item -Path ($BuildOutputFolder + "\*.pdb") -Destination $AddInsPath -Recurse -Force -Verbose
	Write-Output "================================================================================================="
	Write-Output "Deployed (Debug) GP to $AddInsPath"
}
else
{
	Write-Output "Starting to deploy GP (Release) to $AddInsPath"  Write-Output "================================================================================================="
	Copy-Item -Path ($BuildOutputFolder + "\*.dll") -Destination $AddInsPath -Recurse -Force -Verbose
	Get-ChildItem -Path $AddInsPath *.pdb | foreach { Remove-Item -Path $_.FullName -Verbose }
	Write-Output "================================================================================================="
	Write-Output "Deployed (Release) GP to $AddInsPath"
}
 
 

MyTNT Missing Pending Shipment Menu item fixed

Pending Shipment Menu Item Missing from MyTNT web application

For some users the Pending Shipment Menu item was found to be missing when the logged into the MyTNT website. This was found to be a cross browser issue, also using another login credentials or clearing cookies and cache didn't clear the issue. It was not user rights related in the MyTNT application either. 

This was found to be linked to specific users in a particular Active Directory group policy group applied to the users. This policy was blocking internet access, except for certain domains. This policy was the immediate clue as to what might be happening. 

If you look in the image below, pending shipments does not exist in Domain User A web browser, but does for Domain User B. Also note the hamburger menu is added on B too. 

After investigating in the developer tools of the internet browser to see what page objects were failing to load, one set of requests became prime suspect -requests to https://cdn.optimizely.com/js/5435521705.js

Blocking this domain (optimizely) on a machine that was not affected, caused the pending shipments menu option to vanish after clearing caches on the previously working machine -aha! 

Thus group policy was adjusted to allow this content distribution network, and all was well again.