I’ve been doing some work with SmartConnect for a client recently where one of the integrations was creating new items. As the project progressed, some of the items which had been imported needed to be replaced with different item numbers. To ensure we didn;t cause problems, I didn’t want to delete items through SQL directly, due to the number and variety of tables involved, so needed to come up with a way of generating the macro.
Macros are useful ways of repeating an action, such as deleting items, but any variation to the data and the macro will fall over. One variation was that some items had posted transactions against them so I needed to avoid these transactions.
I created the below script to create the macro for me, with SSMS set to output to text, and built in joins and checks on all of the relevant tables in Purchase Order Processing, Inventory and Sales Order Processing which may have contained data. The script is probably a little overkill on the checks it does, but I wanted to make sure it caught as much as possible.
Once the script has been run, you can copy the macro text into a file and play the macro to delete the items (if you have a lot of items, you can play the macro fast).
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).
'# DEXVERSION=14.00.0085.000 2 2
CheckActiveWin dictionary ''default'' form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance''
TypeTo field ''Item Number'' , ''' + CAST(RTRIM(['Item Master'].ITEMNMBR) AS VARCHAR(50)) + '''
MoveTo field Inactive # ''FALSE''
CommandExec dictionary ''default'' form ''IV_Item_Maintenance'' command ''Delete Button_w_IV_Item_Maintenance_f_IV_Item_Maintenance''
# Are you sure you want to delete this record?
NewActiveWin dictionary ''default'' form DiaLog window DiaLog
ClickHit field OK
NewActiveWin dictionary ''default'' form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' '
IV00101 AS ['Item Master']
) AS ['Used Items']
['Used Items'].ITEMNMBR = ['Item Master'].ITEMNMBR
IV00102 AS ['Item Quantities']
['Item Quantities'].ITEMNMBR = ['Item Master'].ITEMNMBR
['Item Quantities'].LOCNCODE = ''
IV10301 AS ['Stock Count Line']
['Stock Count Line'].ITEMNMBR = ['Item Master'].ITEMNMBR
['Used Items'].ITEMNMBR IS NULL
['Item Quantities'].QTYONHND = 0
['Item Quantities'].QTYRTRND = 0
['Item Quantities'].QTYINUSE = 0
['Item Quantities'].QTYINSVC = 0
['Item Quantities'].QTYDMGED = 0
['Item Quantities'].ATYALLOC = 0
['Stock Count Line'].ITEMNMBR IS NULL
If when you run the script you only get part of the macro text, you can change the query results length.
I helped field a query from a client recently on the Reconcile to GL
routine ( ) showing Payables Management
, Receivales Management
and Bank Reconciliation
, but not Inventory
This client has been a long time user of Microsoft Dynamics GP who had recently upgraded. This issue is a known one, addressed on the Dynamics GP Support and Services Blog.
With the support of the helpdesk, the client was able to run through the steps to resolve the issue and perform a reconciliation of the Inventory module to the GL.
While I was looking at the problem raising In-Transit Transfers
I needed to delete all orders with alpha numeric Document Numbers, but was getting an in use error on one of them:
Continue reading “Microsoft Dynamics GP In-Transit Transfer Document Locked”
I was doing some work for a client the other day who was implementing some new functionality in Microsoft Dynamics GP. One of the areas they were working on was Inventory Control. They were testing that everything was working correctly and tried to raise an In Transit Transfer
( ); when they entered the Item Number and hit tab the following error appeared:
Microsoft Dynamics GP
A get/change first operation failed on table 'IV_TRX_WORK_LINE' failed accessing SQL data.
Continue reading “Error Raising In-Transit Transfer in Microsoft Dynamics GP”
If you try to run the Inventory Reconcile utility (( )) when users are posting invoices, the following error message will be displayed (the same message will be displayed during a Year-End Close):
You cannot complete this process while invoices are being posted
If users are not posting invoices, this KB article shows all of the tables which need to be cleared down.
This came up for a client recently. The above KB article requires all users to be out of the system to clear the tables down. In the middle of the working day we wanted t avoid this if we could; so we checked the Batch Activity (SY00800) table and were able to identify the user locking the process and get them to log out.
Leaving the Dex… tables alone we then tried the Reconcile utility again and found it worked fine. In many cases, all three tables will need to be cleared, but sometimes just the SY table will be sufficient.
This post is part of the Hands On With Microsoft Dynamics GP 2018 R2 New Features
series in which I am going hands on with the new features introduced in Microsoft Dynamics GP 2018 R2
(which was released on the 2nd
October). I reblogged the new features as Microsoft announced them along with some commentary of how I thought they would be received by both my clients and I. In this series, I will be hands on with them giving feedback of how well they work in reality.
The tenth new feature is Exclude Items on HITB Report With Zero Quantity or Value. This feature adds two new options to the Historical Inventory Trial Balance (HITB) report. The new options are:
- Include Items with Zero Quantity
- Include Items with Zero Value
These items will be excluded on the report if the options are not checked; previously, by default, these items were included. Make sure the checkboxes are unmarked to exclude the items with either zero value or quantity:
When I first read this feature of the day announcement, I thought it would be a good addition. However, now that I’ve been hands on with it, I’m not so sure. Initially, I’d misremembered what the HITB showed; it is basically a report which shows movement of stock. The report I was thinking of, is the Stock Status report and it already allows you to filter out zero quantities or values.
Having played around on Fabrikam, I can’t actually see how excluding items would be useful from the HITB report; when running a Trial Balance, you’d want to see all transactions.