Dynamics GP Payables - A unique document number could not be found Please check setup

Payment run failed after GP upgrade

On upgrading to GP: Version 18.4.1361 (2021) everything worked great except for making EFT payments.

On printing the EFT payments, the following error popped up.

A unique document number could not be found. Please check setup.

A Unique Document Number Could Not Be Found. Please check setup.


Although it could be clicked through the first time, attempting further subsequent EFT payments resulted in another message box.

An error occurred. Contact your system administrator to verify data.

An Error Occurred. Contact your system administrator to verify data.

This was annoying as this prevented any further EFT payments, although manual payments were absolutely fine.


Diagnosing the problem

I posted the issue on the community forum and lodged a ticked with our support partner, just in case this couldn’t be resolved ourselves… then time to dig in and investigate as not paying suppliers is a big issue…

This was a familiar message to me from other modules of GP, where the number sequence has gone so far out of range that GP fails to scan for a new number, or the number sequence has not been defined – this experience helped me later narrow in on the problem. I checked the purchasing setup window and the various GP tables to find that the next numbers for both that chequebook and the payables transactions. All were all looking correct.

hmmm…

It felt like something was wrong when GP was getting the next chequebook number (or perhaps payment transaction number, though it felt less likely), you get a gut feeling on these things when working with a product long enough!

Next action was to capture a SQL debug trace and a Dexterity Script debug trace around that operation. The SQL trace had nothing interesting in it. However the script trace did. This showed the error box was being called after function “GenerateNextEFTNumber” was executed.

'GenerateNextEFTNumber of form PM_Print_Computer_Checks', table 'CM_Checkbook_MSTR', "0000000000000001012343295", 0

           'PM_Number_Inc_Dec', "", 1

'SaveCurrentFormTrigger', 2567, 0, "SY_Error_Message"

EFT Number, what is that?? – I’m not that familiar with the EFT module in GP, so I went looking around the configuration for EFT, which hangs off the chequebook form.

I found in the EFT options here: Cards->Financial->Chequebook->EFT Bank->Payables Options

EFT Setup - Payable Optoins shiowing use cheque numbers and empty field for next number


In there it states option is to use the cheque number, but it looks like it is possible rather than use up cheque numbers, to use a unique EFT sequence number for EFT payments. This was the moment that I felt that I knew what was happening.  You may see from the screen shot that there is no EFT number in the Next EFT Payment Number field. On the test environment I seeded this value with EFT000000000001, having also set the option to “Use EFT Numbers”. This time the payment processed as expected and the “Next EFT Payment Number” incremented by the number of payments.

Aha! – I set the option back to “Use Cheque Numbers” but leaving the Next EFT Payment Number populated and it still allowed payments to be processed. It looks like the blank field was the problem.

Fix Production environment

I moved to production environment and ran

UPDATE CM00101 SET EFTPMNextNumber = 'EFT000000000001'

that will seed the Next EFT Payment Number for all the chequebooks (make sure this is ok in your environment and do for each company DB).


The setup options for EFT now looked like this below, with a value in the next number field but option still to use cheque numbers…

EFT Options with corrected setup

On attempting to do the payments run, it worked just as it did pre-upgrade. –yay!


Conclusion

Conclusion is that even if the option is set to “Use Cheque Numbers”, then GP will still attempt to generate EFT numbers, and thus needs the field for Next EFT Payment Number to be populated. It is not so much “used” but is “required”.

Also out of curiosity went and created a new chequebook. On opening the EFT options window of this new chequebook, the “Next EFT Payment Number” field is pre-populated by the application with “EFT000000000001” – thus on a current versions of GP this issue would not happen. There would always be a value in that field. I guess in the past versions of GP that this field was not auto populated by the application, thus our chequebooks ended up with blank fields, that only then became an issue after upgrading to this latest version of GP.

It was a stressful few hours but outcome was good.

Task to think about when migrating SQL server for Dynamics GP to a new server

After performing SQL migration I had this top level list of things I had to think about, so this is just a private list put here of things that you might need to think about when migrating from one SQL server to another. This is for my environment, if you use clustering and other features of SQL server, then you will have some more to add to the list. However hopefully this will help me next time and may provoke some thoughts to others working on a similar project.


In this case reporting services is co-hosted on the server as is eConnect service and some IIS API end points.
They are in random order, there are many interdependencies.

  • Linked Servers, Script out any linked SQL servers and re-create on the new server.
  • SQL Jobs needs scripting out and re-creating on new server. Any reporting services jobs need removing and let reporting services rebuild the schedules when it the service starts. References to server names needs checking within the scripts.
  • Maintenance plans need moving to the new server.
  • SQL Logins needs scripting out and moving to the new server.
  • TempDB files need creating with appropriate number of data files for the server and run on appropriate disks.
  • Replication needs scripting, removing, then rebuilding on the new server. Change server names in replication as has to use the actual server names not aliases.
  • The user databases needs need backup from old server and restore to new server.
  • Reporting services, if installed needs keys applying and configuring with new host names in .config file, restore the encryption keys from the old server. 
  • Back up devices needs scripting out and recreating on the new server.
  • eConnect Service needs installing on the new server.
  • eConnect Config for service needs updating for changes to port numbers and transport protocol used (if not using defaults).
  • Any IIS web services needs migrating to new server (if any installed).
  • Script out extended events and recreate on new server.
  • SSIS packages need exporting and importing to new sever.
  • SQL Operators need scripting out and moving to new server.
  • After migration switch DNS aliases to point at new server from old (including reporting services and any iis site alias if applicable).
  • Check Database Collations as set correctly for new sever vs old.
  • Compare side by side new and old SQL server settings pages for all settings.
  • Start SQL server Jobs after migration and once old server is out.
  • Check virtual machine settings after migration to ensure full resources have been restored to the virtual machine.
  • Ensure Full text index is installed (if required).
  • Ensure SSIS is installed (if required).
  • Ensure Reporting Services installed (if required).
  • Install VMWare SCSI drivers for performance reasons.
  • Check firewall settings on machine are the same between old and new, including application settings. 
  • Duplicate any windows file shares on the new server to be the same as the old server, including share and NTFS permissions.
  • Double check any local users and user permissions.
  • Install Dynamics GP workflow server CLR objects on the server using stored procedure DYNAMICS..wfDeployClrAssemblies
  • Migrate mail profiles from old to new server.

Dynamics GP folder path incorrect when using Create Installation Package utility

GP Create Installation Package Menu

Application folder for Dynamics GP moved

The default installation package location moved for the Dynamics GP application to

C:\Program Files (x86)\Microsoft Dynamics\GP

for releases after GP2018. Previously it was in C:\Program Files (x86)\Microsoft Dynamics\GP2018, where the GP2018 was replaced with the version of GP. As the versions are no longer linked to years this was dropped as a folder naming pattern.


Create installation package utility

The Create Installation Package utility, found on the GP installation media main menu, did not get this memo about the folder change, or it introduced a bug to be more accurate. We find it broken since this change was made. After creating an installation package, and then using that installation package to install GP, the folder is not “C:\Program Files (x86)\Microsoft Dynamics\GP”, instead it is “C:\Program Files (x86)\Microsoft Dynamics\GP2018”, as it was in the previous version.

I’ve just tested this with the GP2019 fall release, and that release (V18.4) still will install the application into the “wrong folder”.

When the package is built we are prompted for the folder location:

Dynamics GP Installation Package folder C:\Program Files (x86)\Microsoft Dynamics\GP2018


As can be seen the location is correct (C:\Program Files (x86)\Microsoft Dynamics\GP\).

What makes things worse it reports that if you specify the local location for the dictionary, the resulting launch file (.SET) will then happily mix the file locations, resulting in mixture of folders in the .SET file for GP and GP2018!

Report DIC and Forms DIC locations

The fix/workaround

I stumbled on a fix or work around on Ian Grieve blog with this comment at the bottom of the hands on guide to using the Create Installation Package utility (which if you are not familiar with this, then that is a good guide to go by:

2022-02-23_15-51-42

Hands On with Microsoft Dynamics GP Fall 2021 Release: Create Installation Package


So if we try this by removing that trailing slash as shown below, then indeed the application ends up, correctly in the GP folder rather than the GP2018 folder! I have no idea how whoever it was discovered this but well done!

remove trailing slash from install path

See how the slash has been removed, it works!

Dynamics GP Install/upgrade failed SyCompanyImages

install upgrade failed syCojmpanyImages

Interesting issue with upgrading Microsoft Dynamics GP.

The upgrade stopped at the above step

Microsoft Dynamics GP Utilities install/upgrade failed
syCompanyImages


Turns out this was linked to deleting companies from this instance of GP, and the delete leaving orphaned records in the DYNAMICS..SyCompanyImages table.

The syComanyImages table holds the images used by word template reports, that are uploaded using the form below:

Reports>>Template Configuration>>[Images] button

Company Images Window Dynamics GP

There was an orphaned record from the deleted company in the table that is used to back that form. The extra record was removed using the following script:

DELETE FROM Dynamics.dbo.syCompanyImages
WHERE (RELID/65536) NOT IN
(
   SELECT  (CMPANYID) FROM  Dynamics.dbo.SY01500 )
)


Note that I don’t understand how this table works entirely, other than its holding a binary blob of the image, that is referenced back in this bizarre /65536 formula. I would check what records will be deleted before actually running this script.

Note that It is important to have ran ClearCompanys.sql - Script that will clear out all entrys in the DYNAMICS database referencing databases that no longer exist on the SQL Server, BEFORE running the above script. This is to make sure that SY01500 only has companies that have databases associated with them.