Top Categories:
Dynamics GP
ClassicPress

Microsoft Dynamics GP SmartList window won’t open for one user

Microsoft Dynamics GPI was doing some consultancy for a client recently when they took the opportunity to ask me about a problem encountered by one of their users, whereby they were unable to open and use SmartList. it showed in the Windows task bar as open, but they couldn’t see it or bring it to the front. I tried the usual of trying to right click the window in the task bar and hitting Maximize, but this had no effect.

As this rang a bell, my next port of call was to search for similar problems and found a thread on the Community forum with an answer from Jonathan Fear of Microsoft suggesting that the System User Defaults (SY01402) table which holds window positions on a per user basis may be corrupt and need fixing.

I ran a select on both the user with the problem and one who could open SmartList successfully:

SELECT * FROM DYNAMICS..SY01402 WHERE syDefaultType = '96' AND (USERID = 'sarah' OR USERID = 'jane')

The user with a problem had a SYUSERDFSTR value of FALSE~681~1536~-25632~-117~327~ with the highlighted section throwing the window far off the visible screen area, whereas the user who could see SmartList had a value of 289.

i got the user to log out of Microsoft Dynamics GP and one simple update statement later, and the user had SmartList opening correctly on screen for them:

UPDATE SY01402 SET SYUSERDFSTR = 'FALSE~681~1536~-289~-117~327~' WHERE syDefaultType = '96' AND USERID = 'sarah'

Integration Manager error when running a Sales Order Processing integration

Microsoft Dynamics GPI created an integration recently using Integration Manager for a client to import sales invoices into Microsoft Dynamics GP. It worked fine when I was developing it, logged in as DYNSA, and for one of the finance teams managers, but produced an error for the user:

Integration manager error log

Opening source query...
Establishing source record count...
Beginning integration...
DOC 1 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 2 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 3 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 4 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 5 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 6 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
DOC 7 ERROR: Unknown Great Plains field subtype '10016'. - Field 'Deposit Received' of window 'SOP_Entry' of form 'SOP_Entry'
Integration Failed
Integration Results
    7 documents were read from the source query.
    7 documents were attempted:
        0 integrated without warnings.
        0 integrated with warnings.
        7 failed to integrate.

I toggled the Integration Manager ini file parameters so I could see what was happening with the windows to get an indication of the problem. The first window opened was Sales Order Processing Setup; why tis opened I’m not sure, but the user didn’t have permissions for this window. I did a quick test by copying the users security to a test account and added access to that window and the integration ran through without a problem.

This seems to be an operating as designed thing, but it seems odd that the SOP Setup window needs access.

Duplicate last exchange rate in Microsoft Dynamics GP for every day to future date

Microsoft Dynamics GPI’ve been involved in a project which has included a lot of development and therefore a lot of QA and UAT over a period of time. The client’s system is configured with daily exhcnage rates which has caused some issues with testing as not everyone doing testing has access to insert new rates.

To provide some consistency in the exchange rate for testing and to ensure that testing is not held up by missing exchange rates, I created the below SQL script to copy the last exchange rate and replicate forward for every day until a specific date (set in the highlighted parameter at the top); the rate tables which are updated are listed out qithin the query (second set of highlighted text).

This script was ***NOT*** created for use on a live system, which would include a test company on the live system, as the exchange rate tables are system setup data. It was created only for use on a standalone test system with no connection to live.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
DECLARE @StartDate DATETIME = FORMAT(GETDATE(), 'yyyy-MM-dd') ,@EndDate DATETIME = '2020-12-31' ; WITH NewDatesToInsert AS ( SELECT @StartDate AS EXCHDATE UNION ALL SELECT DATEADD(day, 1, EXCHDATE) FROM NewDatesToInsert WHERE DATEADD(day, 1, EXCHDATE) <= @EndDate ) INSERT INTO MC00100 -- Multicurrency Exchange Rate Maintenance (MC00100) ( EXGTBLID,CURNCYID,EXCHDATE,TIME1,XCHGRATE,EXPNDATE ) --VALUES ( SELECT ['Multicurrency Exchange Table Dates To Insert'].EXGTBLID ,['Multicurrency Exchange Table Dates To Insert'].CURNCYID ,['Multicurrency Exchange Table Dates To Insert'].EXCHDATE ,'1900-01-01 00:00:00000' AS TIME1 ,ISNULL(( SELECT TOP 1 XCHGRATE FROM MC00100 WHERE EXGTBLID = ['Multicurrency Exchange Table Dates To Insert'].EXGTBLID ORDER BY EXCHDATE DESC ), 1) AS XCHGRATE ,DATEADD(day, 30, ['Multicurrency Exchange Table Dates To Insert'].EXCHDATE) AS EXPNDATE FROM ( SELECT ['Multicurrency Exchange Table Setup'].EXGTBLID ,['Multicurrency Exchange Table Setup'].CURNCYID ,['New Dates To Insert'].EXCHDATE FROM NewDatesToInsert AS ['New Dates To Insert'] CROSS JOIN MC40300 AS ['Multicurrency Exchange Table Setup'] -- Multicurrency Exchange Table Setup (MC40300) WHERE ['Multicurrency Exchange Table Setup'].EXGTBLID IN ('Z-UK-BUY','Z-UK-SELL','Z-UK-AVG') ) AS ['Multicurrency Exchange Table Dates To Insert'] INNER JOIN MC40200 AS [Currency Setup'] -- Currency Setup (MC40200) ON [Currency Setup'].CURNCYID = ['Multicurrency Exchange Table Dates To Insert'].CURNCYID WHERE ( SELECT COUNT(*) FROM MC00100 WHERE EXGTBLID = ['Multicurrency Exchange Table Dates To Insert'].EXGTBLID AND EXCHDATE = ['Multicurrency Exchange Table Dates To Insert'].EXCHDATE AND XCHGRATE IS NOT NULL ) = 0 ) OPTION (MAXRECURSION 0)

Jet Reports with Microsoft Dynamics GP book available

Microsoft Dynamics GPI have a new book available now called Jet Reports with Microsoft Dynamics GP; this book is aimed at end-users or consultants looking to use Jet Reports with Microsoft Dynamics GP for financial and/or operational reporting.

The book covers the basics of installing and configuring Jet Reports as well as how to design and build reports using the Excel Add-in, before moving on to step-by-step guides on creating six different types of report and closing with a look at the report building tools and Jet Hub.

Jet Reports with Microsoft Dynamics GP by Ian Grieve

The book is available as an eBook direct from my azurecurve Publishing site or from Amazon (for $5 more):

If you buy the ebook from me directly, the coupon code JetWithGPLaunch will get you 20% off the price of this book (offer available until the end of July 2020).

Microsoft Dynamics GP running at an appalling slow speed

Microsoft Dynamics GPOn a recent project we encountered an issue with the test ssytem running remarkably slowly in many areas. While logging in took longer than it should have, even opening the SmartList window was taking 45 seconds; when a SmartList favourite was selected, each column header and field was drawn slowly enough that it could be read before the next one appeared.

Posting a Payables batch with 50 invoices would take more than 50 minutes (I’m not sure how long it took as that was when I disconnected my session and let it run to completion by itself at the end of the day).

We installed a client directly onto the SQL Server for testing purposes and found that the speed was what we would expect to see, rather than the snails pace the users were seeing.

One of the network team noticed that the traffic speed going over the network was very ;pw compared to what should have been possible and lower than we’d expect to see. The entire environment was a virtualised one and all machines were configured with virtual 10Gbit network cards; the solution was to remove the virtual 10Gbit ethernet cards and replace with 1Gbit.

After this change was made on both the SQL Server and terminal servers, users saw the performance of Microsoft Dynamics GP increase across the board. SmartList would open in three seconds, a favourite would return rows of data far faster than could be read and batch posting was rapid.

I’m not sure if there was a peculiarity elsewhere in the system contributing to the slowness, but the only change made at that time was the replacement of the virtual ethernet cards.

Switch off SOP/Invoicing warning in Microsoft Dynamics GP

Microsoft Dynamics GPI often end up creating test, demo or upgrade requirements and have a number of scripts I run against them to make changes. The script below can be used to change the settings for the message displayed when both SOP and Invoicing are both installed.

The script is set up to leave the message enabled for Invoicing, but switch it off for Sales Order Processing (the comments include the setting for switching the other way).

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
UPDATE IVC40100 -- Invoicing Setup (IVC40100) SET IVCDSTCK = 0x01000040 -- set to 0x00000040 to switch off warning GO UPDATE SOP40100 -- Sales Setup (SOP40100) SET IVCDSTCK = 0x00000000 -- set to 0x01000000 to switch on warning GO

How to Set the Tab Sequence Order in Microsoft Dynamics GP Modifier

Microsoft Dynamics GPWhile created a new version of a customised window for a client (the old customised version was causing problems for submission to Workflow) I needed to change the tab order of the fields on the window; I’d removed quite a few fields and changed the positions of the remaining.

I couldn’t remember how to do this (I last remember doing this in Visual Studio where you can select a field and change the tab order directly) so a quick online search found me a blog from David Musgrave, originally on the old Developing for Dynamics GP blog and now on his Winthrop DC blog.

The short version is to click Layout » Set Tab Sequence and then tab through the fields, double-clicking a field to change the order:

Set Modifier tab order

When doing this make sure you tab through fields, even after double-clicking to change the order, or odd tab ordering will result.

David’s blog post has a lot more detail available on preparatory steps, steps to accomplish the reordering as well as cautions on what to be careful.

Run Task Manager as Administrator

WindowsI was deploying a new version of a Microsoft Dynamics GP Dexterity modification for a client recently and ran into a problem caused by a user who had disconnected their RDP session to the server instead of logging off; this had left a lock on the dictionary file as they had also not closed Microsoft Dynamics GP.

The server logon requires individual logons with MFA enabled, but my user is not an administrator on the server. As I was doing the deployment out of hours, I was not able to call the user and ask them to log off, but instead needed to kill their session and close the client. With my user account being an ordinary one, I didn’t have permissions to kill the session, but I did have access to a domain admin account which did have th permissions.

The following command will let you specify the user account with which to start Task Manager allowing you to run it under an admin account when yours isn’t:

runas /user:{username} taskmgr

After entering the command, you need to enter the password for the admin account you used.

New book on Jet Reports with Microsoft Dynamics GP available

Microsoft Dynamics GPI have a new book available now called Jet Reports with Microsoft Dynamics GP; this book is aimed at end-users or consultants looking to use Jet Reports with Microsoft Dynamics GP for financial and/or operational reporting.

The book covers the basics of installing and configuring Jet Reports as well as how to design and build reports using the Excel Add-in, before moving on to step-by-step guides on creating six different types of report and closing with a look at the report building tools and Jet Hub.

Jet Reports with Microsoft Dynamics GP by Ian Grieve

The book is available as an eBook direct from my azurecurve Publishing site or from Amazon (for $5 more):

The book is structured into these chapters:

  • Chapter 1, Introduction to Jet Reports, introduces Jet Reports and explains what type of reporting for which it can be used.
  • Chapter 2, Implementing Jet Reports, covers the installation of Jet Reports including the Jet Service Tier and Jet Hub.
  • Chapter 3, Configuring Jet Reports, shows how to configure Jet Reports once it has been installed.
  • Chapter 4, Introducing the Excel Add-in, introduces the Excel Add-in and the features available.
  • Chapter 5, Report Structure, shows how reports can be structured to make maintaining them easier.
  • Chapter 6, Using the functions in the Excel Add-in, shows how each of the four Jet Functions can be used.
  • Chapter 7, Creating and using an Options page, covers the creation and use of an options page in reports.
  • Chapter 8, Create a Summary Trial Balance, steps through the creation of a Summary Trial Balance report.
  • Chapter 9, Create a Detailed Trial Balance, shows how to make the Summary TB from the previous chapter into a Detailed report.
  • Chapter 10, Create a Balance Sheet, covers the creation of a formatted Balance Sheet.
  • Chapter 11, Create an Income Statement, covers the creation of an Income Statement and how to make it an intercompany/consolidated version.
  • Chapter 12, Create a Summary Payables Aged Trial Balance, shows how to create a Summary Payables Trial Balance report.
  • Chapter 13, Create a Purchase Order by Vendor Report, covers the creation of an operational report on purchase orders.
  • Chapter 14, Report Creation Tools, introduces the tools which can be used to make creating reports easier.
  • Chapter 15, Using Jet Hub, shows how users can access Jet Reports via web access and without a local install of Jet Reports.

If you buy the ebook from me directly, the coupon code JetWithGPLaunch will get you 20% off the price of this book (offer available until the end of July 2020).