Insert Segments from Text File/CSV into Microsoft Dynamics GP

Microsoft Dynamics GPIt seems that while I’ve posted about how to update segment descriptions from CSV in Microsoft Dynamics GP and how to copy them to a new company or even all companies, I’ve never actually posted the script I use to insert them.

I needed this script the other day and ended up using the update descriptions one as the basis for a new script to insert segments into Dynamics GP; this script will pick the text file (or CSV file if you change the second highlighted section to a comma), update any existing segments and insert new segments into the Segment Description Master (GL40200) table.

/*
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). */
CREATE TABLE #Segments ( SGMTNUMB VARCHAR(100) ,SGMNTID VARCHAR(100) ,DSCRIPTN VARCHAR(100) ) GO BULK INSERT #Segments FROM 'C:\Integrations\COA\Segments.txt' WITH ( FIELDTERMINATOR = '\t' ,ROWTERMINATOR = '\n' ,FIRSTROW = 2 ) GO UPDATE Segments SET Segments.SGMTNUMB = NewSegments.SGMTNUMB ,Segments.SGMNTID = NewSegments.SGMNTID ,Segments.DSCRIPTN = Left(NewSegments.DSCRIPTN, 31) FROM GL40200 AS Segments INNER JOIN #Segments AS NewSegments ON NewSegments.SGMTNUMB = Segments.SGMTNUMB AND NewSegments.SGMNTID = Segments.SGMNTID GO INSERT INTO GL40200 ( SGMTNUMB ,SGMNTID ,DSCRIPTN ) --VALUES ( SELECT SGMTNUMB ,SGMNTID ,LEFT(DSCRIPTN, 31) FROM #Segments AS NewSegments WHERE ( SELECT COUNT(*) FROM GL40200 AS Segments WHERE Segments.SGMTNUMB = NewSegments.SGMTNUMB AND Segments.SGMNTID = NewSegments.SGMNTID ) = 0 ) GO DROP TABLE #Segments GO

As always with a SQL script which makes changes, I’d make sure you have a good backup and test the script before running it in case you encounter problems.

Management Reporter Currency Does Not Exist Error

Microsoft Dynamics GPI was doing some training on Management Reporter for a client the other week and had an issue to follow up on. The issue was around multicurrency conversion which wasn’t working correctly. When doing some testing around the issue, I was able to reproduce the same currency error the client had encountered:

Currency could not be found error message
Currency XXXX does not exist for company XXXX. No values will be returned.

The currency did exist and on most reports was working fine. It took me a little investigation and research to find that this was an issue with the Data Mart connector; the reports using the legacy connector were working correctly.

This issue is covered in Microsoft KB Article 3058400; when using the Data Mart connector, currency lookups are done using the ISO Code of the Currency rather than the Currency Code itself.

Microsoft Dynamics GP Fall 2020 Release Documentation

Microsoft Dynamics GPAs of the October 2019 release, Microsoft Dynamics GP moved onto the Modern Lifecycle which offers continuous support and servicing, including bug fixes, new features and the latest tax updates. There will be one major release per year along with at last two hotfix releases through the year.

There are a number of resource pages available or updated for the Fall 2020 Release of Microsoft Dynamics GP which has recently been released.

The Microsoft Dynamics GP Directory holds a wealth of information and links which will be useful to you, sorted by version and learning by module.

In addition to the Microsoft resources, I have also done two series on the Fall 2020 Release which you may find useful:

SmartConnect Error: “The delete permission was denied on the object…”

Microsoft Dynamics GPA while back a client using SmartConnect reported an error running one integration; this is one of a set of newly created identical integrations running against a set of companies, but only one of them was failing.

The error produced was:

SmartConnect error
Task xxxx failed. Map run will end reporting failure. The DELETE permission was denied on the object 'xxxx', database 'xxxx', schema 'dbo'

As there was a reference to permssions, I assumed there would be a SQL issue; when looking through database permissions, I found that in one database the user account used by SmartConnect had the db_denydatawriter role membership assigned:

Database User permissions

Once this permission was removed, the integration ran successfully.

SmartConnect could not find data

eOne SolutionsI recently created an integration using SmartConnect for a client as I have many times before; however, when I came to test the integration, my source file was totally ignored and the integration returned an error that no file was found.

The integration was using a folder data source with an Excel template; nothing special and something which I have configured many times. I copied the template file into the source folder and tried the integration again and got the same result.

I asked another consultant on the team to take a look with me and neither of us could see a problem; everything looked correct. Then it occurred to me that the extension of the file was .XLSX which I would not expect to be a problem; but, we couldn’t see anything else, so changed the extension to .xlsx and tried the integration again.

It worked correctly. I didn’t expect the case of the file extension to cause a problem, but is something to definitely bear in mind in the future and to make sure clients are aware.

Missing Security Roles and Tasks After Upgrading to Microsoft Dynamics GP Fall 2020 Release

Microsoft Dynamics GPWhen upgrading Microsoft Dynamics GP, the security roles and tasks are not automatically updated; this is to avoid overwriting or causing problems with any customizations to these roles and tasks which might have been made. Instead Microsoft provide a set of scripts which you can choose to run to add the missing items.

I always recommend clients not directly amend the standard tasks, but make copies and amend the copy (this provides a way to roll back to the standard versions should there be a need).

The update scripts are available from the Dynamics GP Support and Services Blog and I have updated my links on the GP Missing Security Scripts page to link to that blog and the script directly; if you’re using an additional product such as Project Accounting, Enhanced Intrastat, VAT Daybook or HR & Payroll there are additional scripts on the blog post which you can run.

Microsoft Dynamics GP Support from ISC Software

ISC Software SolutionsI’ve worked with Microsoft Dynamics GP for over 17 years and, like most people, do not work for the company with whom I first started. In fact, I am now on my third company working with Microsoft Dynamics GP (and starting to do some work with Microsoft Dynamics Business Central). 18 months after I left the first company they bowed out of supporting Dynamics GP; I now hear from a former client whom ISC Software were already on-boarding for support, that two years after I left the second company, Perfect Image Ltd., that they are also now bowing out of supporting Microsoft Dynamics GP.

A few of the clients I worked with at Perfect Image have already decided that they needed to change their support partner and moved across to ISC Software where they can continue to work with me and report being happy with the support, and other services they receive from the team and I.

With a new year about to dawn (and hopefully a better one that 2020), it might be time to review your support contract and consider moving to ISC Software.


Direct phone line to our support team

Online support portal

Dedicated account manager

Non-support related assistance, reports and modifications

Free GP Elementz Add-ons

Tailored Support Plans including 24/7/365 and unlimited

Value for money, plans start at £/€2,000

Accredited consultants with real world experience

All versions supported including older releases

3rd party products including Jet Reports & SmartConnect

Continue reading “Microsoft Dynamics GP Support from ISC Software”

Workfow Submit button on PM Transaction Entry not working in Microsoft Dynamics GP

Microsoft Dynamics GPI’ve been doing some work with a client to implement the Workflow module of Microsoft Dynamics GP for approvals of Payables Transactions and encountered an oddity; when the user creates a transactions and hits Submit, nothing happens, but was not able to reproduce this on any of my demo or test systems.

There was a customised window in use on the client system, so I removed this and tried with the standard window and got exactly the same result. Some more testing showed that when the action pane was disabled and the old style toolbar being used, the Submit button was missing.

I had tried a 2018 RTM versaion and not been able to reproduce, but this was becuase I had one setting diferent. In Payables Management Setup (Purchasing area page » Setup » Payables Management), my systems were all set to allow the voucher number to be overridden, but the client had disabled this option.

When we changed this setting in their test company, the workflow submit worked correctly.

I did some further checking and was able to confirm that this is a bug in the Dynamics GP 2018 version which was resolved in the R2 version; the workaround is to allow the voucher number to be overridden and the solution is to upgrade.

Fortunately, we are already talking to this client about upgrading Microsoft Dynamics GP to the latest version; in the short-term they’ll allow override in test while the build and test of the workflows are complete and the deploy to live should tie in with the upgrade project.

Microsoft Dynamics GP Analytical Accounting Open Transactions SQL Query

Microsoft Dynamics GPI was looking into a problem reported by a client recently and needed to check the assignment of Analytical Accounting Transaction Dimension Codes to the AA GL transactions and so wrote the below SQL which I am posting to keep it easily accessible should I need it again.

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

SELECT
*
FROM
AAG30000 AS aaGLHdr
INNER JOIN
AAG30001 AS aaGLDist
ON
aaGLDist.aaGLHdrID = aaGLHdr.aaGLHdrID
INNER JOIN
AAG30002 AS GLAssign
ON
GLAssign.aaGLHdrID = aaGLDist.aaGLHdrID
AND
GLAssign.aaGLDistID = aaGLDist.aaGLDistID
INNER JOIN
AAG30003 AS aaGLCode
ON
aaGLCode.aaGLHdrID = GL Assign.aaGLHdrID
AND
aaGLCode.aaGLDistID = GLAssign.aaGLDistID
AND
aaGLCode.aaGLAssignID = GLAssign.aaGLAssignID
INNER JOIN
AAG00401 AS aaTrxDimCodeSetp
ON
aaTrxDimCodeSetp.aaTrxDimID = aaGLCode.aaTrxDimID
AND
aaTrxDimCodeSetp.aaTrxDimCodeID = aaGLCode.aaTrxCodeID

Recent ISC Software Webinar: Year-End Best Practice in Microsoft Dynamics GP

ISC Software SolutionsIn our most recent webinar, we took a look at Year-End Best Practice in Microsoft Dynamics GP. In this webinar, we covered how some key points to action or consider before starting, the order in which the year end should be run before running through the closing process in each of the standard modules in Microsoft Dynamics GP covering not only what to do. but also what the year-end closing process does. If you want to catch up on this, or any other, webinar, you can do so here.

In this blog post, I am going to recap the webinar and include some links to documentation from Microsoft which will expand on some areas with more detail. The topics I’ll cover are:

  1. Key Points
  2. Year-End Processing Order
  3. Year-End Closing steps by module
  4. Conclusion

Key Points ^

There are five key points I always stress when discussing year-end closes with clients:

  1. Always have a good backup before starting a year-end close and take another after the year-end close has completed – a good backup is defined as one which you know will successfully restore; unfortunately I’ve worked with more than one client over the years who created a maintenance plan to take backups, but then, when the need came, found that the backups were not valid and could not be restored.
    Taking a backup before starting means you can easily revert back should there be a problem in the close; taking a backup after the close completes, means that if problems encountered later in the day that you can revert back to after the close has completed instead of having to repeat the close.
  2. Make sure all users log out of the company in which the year-end is being processed.
  3. Make sure there is sufficient space for database files to double in size; one significant cause of this is that the GL year-end close will copy data from Year-to-Date Transaction Open (GL20000) to Account Transaction History (GL30000) and during this process the data will exist in two places causing the database to grow.
  4. Perform a trial year-end close on a copy of live in test before closing the year-end on live; this will allow you to verify that there are no issues which might require a roll-back.
  5. Once one of the Year-End Closes has been started, be patient and allow it to run to completion. When processing, the close might cause Dynamics GP to white-screen, but this is just a sign that there is a lot of server-side processing underway and not that there is a problem.

Continue reading “Recent ISC Software Webinar: Year-End Best Practice in Microsoft Dynamics GP”