Box 6 values incorrect in Dynamics GP VAT Daybook VAT Return

VAT-100

There have been many annoying 'features' recently in the VAT Daybook VAT Return - in particular with the VAT Return Summary sheet.

At one point, the figures in box 6 on the summary report were simply showing zeros, even though the values were clearly showing on the sales and purchasing detail print outs.

The latest is an issue regarding Sales Order Processing (SOP) or Purchase Order Processing (POP) transactions where there are multiple lines on the transaction, and some lines with different tax treatments to others.

Obviously, if you don't use SOP or POP, or don't have the need for differing VAT treatments on the various lines on those transactions, then this won't affect you, but if you do, then this is an issue for you to know about.

So what is currently happening?

In Box 6 (Total Net Sales) of the summary report, if you have SOP or POP transactions with multiple lines, the summary report is only using the transaction amount from the first line and ignores the rest.

As far as we can tell, it believes that they are multiple VAT treatments on the same amount, rather than individual tax treatments on different amounts.

There is a work around for this that I would like to share, but only in a specific Dynamics GP Version 18.03.1200.

In this version, the audit reprint does not ignore the subsequent lines and is therefore correct. This means that you need to run the audit report first, and then run the reprint to get the correct values.

FYI: In subsequent releases of Dynamics GP, versions 18.03.1245 and later, Dynamics GP appears to have made the audit reprint report behave exactly the same as the Audit report, so this wrokaround no longer works.

So, for the time being, the version we would recommend is Dynamics GP 18.03.1200.

As a quick update, we recently tested the latest (December 2021) Dynamics GP version 18.4.1361, but it still has the same issue on box 6 of the VAT Return, where it ignores the additional VAT lines.

 

Making Tax Digital in Dynamics GP

Making Tax Digital in Dynamics GP

Most companies in the UK would have already switched onto electronic VAT submission directly from their system, but some are still using a bridge to do their submission.

As we all know, use of a bridging software (an intermediary software that submits to HMRC electronically) is supposed to come to an end soon, and if you are still stuck using it because you can't get the correct values out of Dynamics GP, then I have written this article for you based on the last few years experience we have had implementing this for our clients.

The first thing to note, is that in Dynamics GP, there are two main ways (modules) to produce a VAT Return (excluding the manual effort of doing it via the GL...):

  1. The VAT 100 Report
  2. The VAT Daybook

They are both accessed in almost identical locations in the menus.

Within the Administration area, under routines, you will find one (or both) of these menu items:

Dynamics GP VAT Return Menus

VAT 100 Report in Dynamics GP

If you are using the window below, you are using the VAT 100 module to produce your VAT Returns in Dynamics GP:

VAT 100 VAT Return in Dynamics GP

VAT Daybook Report in Dynamics GP

If you are using the window below, you are using the VAT Daybook module to produce your VAT Returns in Dynamics GP:

VAT Daybook VAT Report in Dynamics GP

Which VAT Return module is best in Dynamics GP for Making Tax Digital?

In our experience over the last few years, there is a clear winner on this question: the VAT Daybook module.

The reason for this are quite simple when you compare how the two work.

The VAT 100 Return is a rigid module that works on inbuilt rules which is pretty much a black-box. This is essentially Microsoft Dynamics GP telling you what it thinks your VAT Return should look like based on a number of factors related to, among other things, country codes on customer and supplier addresses.

If you do not have a country code on one of your customers or suppliers, transactions from those customers or suppliers will simply be ignored, regardless of whether they have a tax code assigned to the transaction or not.

If you are a company with customers only in the UK, then it is likely that adding a country code onto every address is not a high priority, as you know which country your customers are in. Similarly for suppliers.

This makes it very difficult (I would say almost impossible) to reconcile to any report you can get out of GP relating to what it should be.

Then, to make up for it, before you actually submit your return, you can just change the figures to what you think it should be, and then submit. What is the point of that?

It means that you have to do all the hard work and figure out what your VAT Return should be manually, before using a module that should do that work for you!

If you are using the VAT 100 report, then you definitely need to look at changing to the VAT Daybook report.

The reason why so many prefer using the VAT Daybook report is that you are in control of exactly what tax details should be included into each box in the VAT Return, and it works on transactions that have a tax detail assigned, and doesn't care about whether the customer or supplier has a country code on the address.

What is really good about the VAT Daybook, is that the reports provided give you complete breakdown of the transactions that are included in each box, whether they are the Tax amounts or the Net amounts, and you get the whole report summarised into the standard VAT return summary, which is what you submit to HMRC.

All you need to do on the setup side, is to specify which of your tax details you want assigned to each VAT return box. This will be different for every setup, but essentially, you will end up with something like this (which is a report print out showing the VAT Daybook Report setup):

VAT Daybook Setup Report

As you can see, you assign the tax details, and then specify whether you want to include the TAX, or the NET. Obviously, in the boxes 1-4, you will include only the TAX values, and from boxes 6 onwards, you will include only NET values, as you can see from the above report.

Once this report has been setup, you just run the VAT Daybook report and the complete set of reports are printed giving you a thoroughly complete set of auditable reports that will very easily define the contents of your VAT Return, should it ever be queried.

Furthermore, if you use separate tax codes for internal transactions, or for foreign transactions that are out of scope and are not to be included on the VAT return, then you will leave those tax codes off of the VAT Daybook Report setup. All transactions that are assigned to those tax detail codes are then displayed to your on an Exceptions report, for you to check and make sure that no entry mistakes have been made.

All in all, the VAT Daybook report is accurate, reconcilable, and gives you full control over the VAT Return, where the VAT 100 report just doesn't.

Both modules have the ability to electronically submit the returns to HMRC, although that is sometimes a difficult task too. We have spent many hours trying to get Dynamics GP to 'speak' with HMRC to submit the returns, but often it is just a difficult task that we cannot fix.

For almost all of our clients, we have implemented the Nolans Making Tax Digital module which works with the VAT Daybook module, and just works every time when submitting to HMRC.

You can check it out their VAT Submission for Making Tax Digital here...

If you need guidance on how to setup the VAT Daybook, then it is available as one of the lessons in our Dynamics GP Administration training courses or contact us directly if you would like assistance setting this up - we would be delighted to help.

If you have any questions about this post, leave us a comment below.

Dynamics GP crashes without warning or error when opening a window

If you are opening a window in Dynamics GP and it is just causing Dynamics GP to instantly crach, then it is likely that VBA is the cause.

This is usually experienced immediately after an upgrade, either of the server, or the Dynamics GP client, where it used to work fine just before the upgrade.

There are pretty much two different options to resolve this, but first, let me take you through the steps necessary to find out whether this window actually does have VBA attached.

Go to the Customisation Maintenance Window via the following menu:

Microsoft Dynamics GP >> Tools >> Cusomise >> Customisation Maintenance

 

Cusomisation Maintenance Menu

When it opens, it will display all of the customisations that are currently active in your client setup.

It will look something like this:

 

Cusomisation Maintenance Window

To find the cause, take a look in your list of customisations, and see if you are able to spot any where the type says Form with VBA.

If you can find a match in one of those 'with VBA' entries to the window that you are trying to open, just before Dynamics GP crashes, then you have most likely confirmed that VBA is the cause.

 

How to fix the issue of Dynamics GP crashing when VBA is the cause

You basically have three options to deal with this, and I recommend starting with step 1, but if that is not possible or doesn't work, then either step 2 or step 3 will be needed.

Step 1 - Turn of Data Execution Prevention for Dynamics.exe

What is sometimes the issue is the that the Data Execution Prevention (DEP). It is understood that the DEP this is designed to try and stop viruses running on the machine. Because of that, it seems to think that the VBA running in DYNAMICS.EXE is a virus, and Dynamics GP crashes.

Here is how to prevent the DEP from getting involved by turning off DEP for Dynamics.exe:

  • In File Explorer, right click on This PC and select properties.
  • Click on Advanced System settings
  • Click on Advanced tab
  • Click on Performance > Settings
  • Click on Data Execution Prevention
  • Click Add and pick the Dynamics.exe file from your client install folder (found somewhere like this C:Program Files (x86)Microsoft DynamicsGP).
  • Make sure the checkbox is ticked and then click Apply.

Once selected, it will show up as Microsoft Dynamics GP:

 

Add Dynamics exe to DEP

Now, open Dynamics GP again, and try opening that same window or report and see if it works, or if Dynamics GP still crashes.

If Dynamics GP still crashes, then you will need to decide on one of the following two options, which depend entirely on whether you need the functionality performed by the VBA, or if you can do without it:

  • Remove the VBA - this is only an option if you don't need the functionality of the VBA
  • Changing the VBA to Deterity - this option is if you need the functionality

 

Option 1 - Remove VBA from the Dynamics GP window

If you are considering this option, then you will first need to determine whether you need the functionality that the VBA was designed for. Unless it is obvious and you already know what the VBA was supposed to do, you may need to take a look at the VBA to see. If you are not sure and are not familiar with VBA, then you will need to get an expert to take a look.

To get to the VBA, you need to do to the following Dynamics GP menu (if you have access to it):

Microsoft Dynamics GP >> Customise >> Visual Basic Editor

This will open the VBA editor. (But, if VBA is an issue, then the Visual Basic Editor may not open either...)

Now, you will need to find and expand the Microsoft_Dynamics_GP section, and then also the 'Microsoft Dynamics GP Objects'. Within that section, double click on the window name, which will open the VBA code for inspection.

Note: if the window has a grid or table within it, then there may be two Objects showing in that list, the one will be for the grid and will have (grid) as the suffix to the name, and the other will have (Window) as the suffix. Try opening both, as functionality could have been added to either.

Once you have determined what the functionality does, and that you are happy that you do not want it, then you need to remove it from your setup.

Remove the VBA customisation from your client

Go back the Customisation Maintenance window:

Microsoft Dynamics GP >> Tools >> Customise >> Customisation Maintenance

Now follow these steps:

  1. To create a backup of all of the customisations, select ALL the customisations (select any record in the customisation maintenance window, then press ctrl + A)
  2. Now click on the Export button in the menu.
  3. Find a suitable export path for this file, and give it a name of something like AllCustomisations.package. The name is not important for anything other than identifications later, but the extension MUST be .package.
  4. Now, repeat step 1 above, selecting all the customisations, and then, while holding down the ctrl key, click on the window with the type 'Form with VBA' that you want to remove, this will de-select it from the list and make it the only one that is NOT selected.
  5. Again, click on Export, but this time, change the name to something else, such as AllCustomisationsExcludingXXX.package. This file will contain all the other customisations on your client.
  6. Ensure that Dynamics GP is closed before the next step, and if you are using shared client or shared customisation dictionaries (i.e. all users are using the same modifications), then ensure that ALL users have also logged out of Dynamics GP when you do the next few steps. This will prevent locks on the modification files when you try to import the customisations again...
  7. Remove the customisations files from your client: using windows exporer, go to the installation directory (e.g. C:Program Files (x86)Microsoft DynamicsGP).
  8. Make a copy of this entire 'GP' folder (or whatever yours is called) as a backup. It is the simplest way to restore your client back to it's original status.
  9. Within the GP folder, delete the Dynamics.vba file. (Hint: You will know that there is VBA in that file if it is larger than 4Kb)
  10. Now, log back into Dynamics GP
  11. Go back to the Customisation Maintenance winow (Microsoft Dynamics GP >> Tools >> Customise >> Customisation Maintenance)
  12. Now, take a quick browse down the list, and you should no longer see any of those records showing 'with VBA'.
  13. Click on Import, and select the second file you exported earlier (the one excluding the window).
  14. It may tell you that there are various customisations that will be updated, but that is OK. Confirm the selection to complete the import. You should see a message saying that all the customisations have been successfully imported. If the import was unsuccessful, then this is usually because other users are locking the modification files - make sure that they are all logged out properly, and try this again.
  15. Now, once the Dynamics GP modifications (excluding the one you wanted to remove) have been successfully imported, then try to open that window again.

Option 2 - upgrade the VBA from the Dynamics GP window to Dexterity

If you really need the functionality that was added to the window using VBA, and the DEP exception did not resolve the issue for you, then I recommend getting the functionality duplicated in Dexterity.

For those who don't know, Deterity is a programming language that Microsoft Dynamics GP was written in.

Many organisations have functionality added using Dexterity, for a variety of reasons, and these are included as additional modules.

If you need to go down this route, then contact your reseller, or if they are not able to assist you with this (i.e. they don't have the expertise), then feel free to get in touch with us, and we can work with you to get this functionality transferred from VBA into Deterity, and make your Dynamics GP setup more future-proof.

 

Categories