Financial Elementz export to Excel crashing Dynamics GP

Receivables Transaction Analysis window

User reported that Microsoft GP crashed with “Microsoft Dynamics GP has stopped working”. This occurred when attempting to export the Financial Elementz window Receivables Transaction Analysis to Excel.

GPCrash

By closing all instances of Excel on the machine and then examining the user’s Windows Task Manager, it was revealed that there was a hidden (Crashed?) copy of Excel sitting in process. The orphan process was “end tasked” using Task Manager.

After removing all instances of Excel the export to Excel started working again. It turned out to be a quick and easy fix.

Drop me a comment if this turned out helpful!

Extender–you do not have access to open this window

You might have this problem where Dynamics GP claims “you do not have access to open this window”. The extender manual tells the basics of setting up security for extender to allow access to windows, it does not mention that the inquiry windows have their own security section or my stupidity.

Extender - You do not have access to open this window

I will not entirely repeat how to set security in extender, other blogs and the user guide helps on the detail, but in summary:

Set up a Security Task, name it something useful, then be aware if this is an enquiry (inquiry) window you need to find the check box under Extender>>Extender Resources>>Inquiries for the window in question and then check it.

Also note to ensure that you are not working in a test company because while you are debugging an issue, it is all too easy to find yourself in another company, thinking you are changing the permissions for the production company, when you are not.(I am not admitting to anything there…but we’ve all done it all too often right?!)

Below is the inquiry version of the window security.

Extender>Extender Resources>Inquiries Showing extender security

Here is normal version.

Extender>Extender Resources>Windows

I hope this helps someone out, let me know with a comment if it did!

Beware diacritic characters where integrating with SQL

I’m certain you all know the above and practice it regularly. First a little background…

In Dynamics GP we wrote a very basic “CRM like” system using a .NET GP Addin, that lays over the top of the SOP module. It introduces the concept of contact records, with many-many relationship to customers/debtors in GP.  The list of contacts associated with an account can be viewed from a sales order and debtor card. The contacts are syncronised to MailChimp (saas email marketing). Marketing click through and email opens are also synced back to be shown next to the contact record. The contacts are also synchronised with the various ecommerce websites that feed GP, contacts being soft linked to website users.

The website integration means there is a merge required to accommodate new and updated records when users update details on the websites. This is where my oversight came to light. Duplicate records were being created, it turned out to be due to diacritics. Below is an example of a duplicate record.

FirstName
Kristján
Kristjan

The example shows what we know they are the same person, but SQL MERGE statement, due to the default collation on the database, sees these as the same. Instead it sees two distinctly different names and thus creates a new contact record for the second instance, where it should (in our case) be merging changes into the first instance. This is an over simplified version of what happened as there are other keys involved and lot of business rules. Obviously SQL is not doing anything wrong but it is not our desired behaviour for this particular task.

It is easy to resolve, when comparing records, for our purpose, we override the default collation and use a Accent Insensitive (AI) version instead, for example:

COLLATE Latin1_general_CI_AI

where “AI” at the end of the collation name is the key to the insensitive comparison.

WHERE
t2.FirstName = t1.FirstName COLLATE Latin1_general_CI_AI

The implementation depends on your own needs, my point for this post is to not forget about this issue if merging data from different sources where there may be a mixture of diacritic and non-diacritic text entered. Integration of data continues to have its challenges…

Enforcing field level security in Dynamics GP

Field level security is a useful Dynamics GP feature. It used for easily securing a field or form, however I have seen the admin user interface form confuse IT admins who are not used to the way GP does things, or simply mistakes being made setting up security, leading some users not having the correct security applied.

fls

The problem I am solving in this post is where some of the field level security policies need to be always enforced/applied to (almost) all users  in GP. To solve this I wrote a quick SQL script to enforce the field level security policies for one of the GP companies. The script ensures that all users who are not in the SYSADMIN class, have the supplied list of field level security policies applied to them, for a particular company. This can be put in a stored procedure and ran within a loop to apply to a list of GP companies, should that be required. It could also be set to run at frequent intervals via a SQL scheduled job, for peace of mind.

If automating the script I would recommend introducing a step in the script to check all the field security IDs hard coded in the script still exist, perhaps raise an exception if they don't so the SQL job will report a fail to admins. For simplicity I have omitted that stage in the example below.


-- Script will apply LISTED field level security ids
-- to ALL users, that are not of class of SYSADMIN
-- for the GP company id specified
--
-- Intention of script is to prevent human error in setting field level security
-- allowing users to slip though and not having it applied to them.
-- It will do this enforcing the security on selected company for all those users.
-- T.Wappat 2016-11-09
-- Check the validity of this script on a test instance before running in production
-- Author assume no responsibility for errors and omissions, or for damages resulting
-- from the use of the information contained herein.

DECLARE @CMPANYID as smallint
SET @CMPANYID=2
MERGE WDC41500 AS target
USING (
SELECT ut.USERID
,ua.CMPANYID
,secid.Field_Security_ID
FROM SY01400 ut
JOIN SY60100 ua ON ut.USERID = ua.USERID
CROSS JOIN [WDC41300] secid
WHERE ua.CMPANYID = @CMPANYID
AND ut.UserStatus = 1
AND secid.Field_Security_ID IN (
'IV_INACTIVE'
,'PRICELEVEL'
,'SOPSHIPTO'
,'SOPSHIPTO2'
)
AND USRCLASS != 'SYSADMIN'
) AS Source(UserID, CompanyID, Field_Security_ID)
ON source.USERID = target.USERID
AND source.CompanyID = target.CMPANYID
AND target.Field_Security_ID = source.Field_Security_ID
WHEN NOT MATCHED BY target
THEN
INSERT (
CMPANYID
,USERID
,Field_Security_ID
)
VALUES (
2
,source.USERID
,source.Field_Security_ID
);

Even if you don’t have the same example problem to solve, the above script may be a leg up and help orientate the reader so they can solve the problem that they do have. Apologies that it is in a MERGE statement, when I started writing it, I was expecting it to get more complicated than it ended up being, an insert on a join would have done this too.