On a recent project with a client to implement the Purchase Order Processing module, we needed to create a lot of vendor item numbers. I’m a big advocate of doing updates in the front-end where possible or using integration tools where they support standard functionality. This particular client didn’t have a suitable integration tool available for inserting vendor items, so instead I created a SQL script which would generate a Microsoft Dynamics GP macro which can be played back to insert the records.
The script below will link all active vendors with every active non-discontinued item; it can easily be amended to limit the selected vendors or items (which is how we used it by running it several times with different restrictions):
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=18.00.0021.000 2 2
CheckActiveWin dictionary ''default'' form ''IV_Item_Vendors'' window ''IV_Item_Vendors''
TypeTo field ''Item Number'' , ''' + RTRIM(REPLACE(ITEMNMBR, '''', '~"')) + '''
MoveTo field ''Vendor ID''
TypeTo field ''Vendor ID'' , ''' + RTRIM(REPLACE(VENDORID, '''', '~"')) + '''
MoveTo field ''Vendor Item Number''
CommandExec dictionary ''default'' form ''IV_Item_Vendors'' command ''Save Button_w_IV_Item_Vendors_f_IV_Item_Vendors''
PM00200 AS ['PM Vendor Master']
IV00101 AS ['Item Master']
VENDSTTS = 1
ITEMTYPE <> 2 -- discontinued
INACTIVE = 0
VENDORID = ['PM Vendor Master'].VENDORID
ITEMNMBR = ['Item Master'].ITEMNMBR
) = 0
While implementing Microsoft Dynamics GP for a new client a while ago, we used Integration Manager to import over 100,000 Inventory items and then a SQL script to insert the 80,000 bill of materials (BOMs).
This worked well, in that it got all of the information loaded, but found that if we inserted the BOMs in this way, they could not be viewed in the BOM Inquiry window.
I used a SQL query to change the status from Active to Pending and then looked at the best way of changing them to Active through the Dynamics GP.
The only way to bulk change the data was by using a GP Macro; the usual way we do this is to do an extract of the data and then mailmerge this into a prerecorded macro in Microsoft Excel.
However, this can be avoided, by using the SQL select to not only get the data, but to output the macro at the same time:
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
'# DEXVERSION=11.00.0364.000 2 2
CheckActiveWin dictionary ''default'' form bmBillMaintenance window bmBillMaintenance
TypeTo field ''Item Number'' , ''' + RTRIM(BMH.ITEMNMBR) + '''
MoveTo field ''Bill Status'' item 0
ClickHit field ''Bill Status'' item 2 # ''Pending''
MoveTo field ''Expansion Button 1''
ClickHit field ''Expansion Button 1''
NewActiveWin dictionary ''default'' form bmBillMaintenance window ChangeStatus
ClickHit field ''Bill Status'' item 1 # ''Active''
MoveTo field ''Process Button P''
ClickHit field ''Process Button P''
NewActiveWin dictionary ''default'' form bmBillMaintenance window bmBillMaintenance
MoveTo field ''Save Button''
ClickHit field ''Save Button''
BM00101 AS BMH
BM00111 AS BMC
ON BMC.ITEMNMBR = BMH.ITEMNMBR
BMH.Bill_Status = 2
I needed to make sure that SSMS was configured to return the data into text and that the data returned was more than the default 256 characters.
Once you have the returned macros, save the file, open Bill of Materials Maintenance and then run the macro.
I heard about this problem a short time ago, but have just had a client upgrade to Dynamics GP 2013 R2 and encounter this problem:
Microsoft Dynamics GP – The asset ID cannot contain a hyphen. Select a different asset ID.
This issue was discussed at some length on the Community Forum with Frank Hamelly supplying a SQL script to update the assets.
With my client I took a slightly different tack and used the Fixed Assets Modifier tool in the Professional Services Tools Library (PSTL). This tool works on a single asset at a time and, unlike some of the other modifiers, does not allow import of a spreadsheet.
However, PSTL is written in Dexterity and allows the use of Dynamics GP Macros; so after doing an extract of all Asset IDs I was able to use mail merge to generate a macro to update all fixed assets containing a hyphen in the Asset ID.
I have checked Microsoft Dynamics GP 2015 and a hyphen is still disallowed in the Asset ID field.
Clients who have upgraded to Microsoft Dynamics GP 2010 can use the new remember user and password
functionality, but what about those who have not yet upgraded?
Well, in Microsoft Dynamics GP 10, and before, you can use a login macro to automate the login process. A macro can be created by pressing Alt + F8 on the login screen and following the normal login process before pressing Alt + F8 again to stop recording. Continue reading “Automate Login To Microsoft Dynamics GP 10”