The final session of the day I attended, was on Custom Workflow Creation. I was interested in this session, as we need to create some new Workflow Types. One which is needed, is a Workflow Type of EFT Bank.
The Vendor Change Workflow Approval is good, but does not do anything when the EFT Bank Details are changed and this is a major requirement for many of our clients in the UK.
The session showed how the new Workflow Type is created, tables updated, buttons added to windows and I took the opportunity to discuss wider requirements such as the need to update code elsewhere to respect the new Workflow Types created.
The changes for new Workflow 2.0 Types must be done in Dexterity.
There is a lot of scope for creating new Workflow Types in addition to EFT Bank Details such as SOP Transactions, Bill of Materials, and there my mind goes blank. Been in this airport too long.
The session on custom workflow creation was one of my favourites due to my interest in Workflow 2.0 (did I mention I am doing a second edition of my Workflow 2.0 book?).
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 (http://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.
Microsoft Dynamics GP 2015 R2 saw the introduction of the Purchasing All-In-One Document View, and Dynamics GP 2016 R1 sees the introduction of an Inventory equivalent:
The main features of Inventory All-In-One Document View are:
- View quantity in and quantity out transactions for one item in one window
- Accessible from Item Maintenance, many item inquiry windows, navigation lists and the home page
As with the Sales All-In-One Document View I am prepared to be underwhelmed by this feature of the day based on the Purchasing one introduced in Dynamics GP 2015 R2.
MVP Belinda Allen has posted a suggestion to MS Connect asking Microsoft to add a bar code field to Item Maintenance, Item Transaction Inquiry and Stock Count Entry.
I think this would be a good additin, so please take a look and cast your vote.
While implementing Microsoft Dynamics GP for a client earlier this year, we used macros to load Assembly Transactions into Dynamics GP as there is no integration available in Integration Manager. The macros were created on the clients development server, tested and then migrated to the live server.
However, despite the development and live servers having exactly the same Windows and Dynamics GP configurations, the following error was generated on the live server when trying to run the macro:
Microsoft Dynamics GP
Keyword or punctuation expected, but not found. (Line #2)
# DEXVERSION=11.00.0359.000 2 2
CheckActiveWin dictionary 'default' form bmTrxEntry window bmTrxEntry
TypeTo field 'TRX ID' , '262307A'
MoveTo field 'TRX Date'
The code displayed above is from the macro file; the problem was the key words (such as dictionary) were not capitalised when the macro was recorded and tested on the development server, but needed to be capitalised for the live server. To get the capitalisation correct, I recorded a macro on live and then used the source CSV file to create a new macro by using the newly recorded one as a template for the mail merge.
It did mean that this was not tested the way the previous one was, but we’d proved the method on test and this was deemed acceptable.
On this one I am open to someone telling me that there is actually a way to do this out of the box, but I haven’t been able to find it. On a SmartList I was creating, I needed to pull the customer item number and description through to a SOP transaction SmartList, but couldn’t find any table to link with.
As a workaround, I created this view which contains the required link using the CUSTNMBR from SOP10100 (Sales Transaction Work) and the ITEMNMBR from SOP10200 (Sales Transaction Amounts Work):
CREATE VIEW uv_AZRCRV_CustomerItemLink AS
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).
SOP10200 AS SOP102 WITH (NOLOCK)
SOP10100 AS SOP101 WITH (NOLOCK)
ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPTYPE = SOP102.SOPTYPE
SOP60300 AS SOP603 WITH (NOLOCK)
ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GRANT SELECT ON uv_AZRCRV_CustomerItemLink TO DYNGRP
This one came up from a query a client had about linking a sales order transaction line to the customer item in SmartList Builder. The problem is that to join two tables together, you need all of the key fields to be on the same table; unfortunately, with the SOP Transaction table, this isn’t the case when you want to link to the customer item.
SOP10100 (Sales Transaction Work) holds the CUSTNMBR (Customer Number), but SOP10200 (Sales Transaction Amounts Work) holds the ITEMNMBR (Item Number) which are both needed to link to SOP60300 (Sales Customer Item Cross Reference) which holds the customer item number and description.
While there may be a way to do this in SmartList Builder I’ve not been able to work it out (other than using two calculated fields), it is easier, quicker and more reusable, to create a simple SQL View which returns the relevant information.
In this case the view I created works only for transactions which are at a status of work:
CREATE VIEW uv_PI_SOPCustomerItemLink
SOP10200 AS SOP102
SOP10100 AS SOP101
ON SOP101.SOPNUMBE = SOP102.SOPNUMBE
AND SOP101.SOPOwner = SOP102.SOPOwner
SOP60300 AS SOP603
ON SOP603.CUSTNMBR = SOP101.CUSTNMBR
AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GRANT SELECT ON uv_PI_SOPCustomerItemLink TO DYNGRP
The SQL above includes the Grant statement used to add select permissions for the DYNGRP.
I have had this script for quite a while and have used it a number of times for different clients when implementing the Inventory Control module in Microsoft Dynamics GP.
One client who was using Inventory was entering a lot of user categories, mistakenly entered the description into the Image field. In that case I did not know that they were populating the User Categories or I would have offered this script to them to save time.
To use the script you need a CSV file with four columns: User Category Value (the ID of the item you want to load), User Category Number (which of the user categories into which the row is to be loaded), Image URL and Description:
CREATE TABLE #UploadData
(FIELDTERMINATOR = ','
,ROWTERMINATOR = '\n')
INSERT INTO IV40600
#UploadData AS UD
IV40600 AS IV
IV.USCATVAL = UD.USCATVAL
IV.USCATNUM = UD.USCATNUM) = 0)
DROP TABLE #UploadData
You will need to change the highlighted line to the location of your CSV file. As always before running a script on live, test it in a test company first and have a good backup of your database.
We implemented Microsoft Dynamics GP for a client a while ago; they are quite heavy users of the Inventory Control module.
Unfortunately, when they configured the User Categories in Inventory Control Setup ( ) the description for the category was entered in the Image field instead of the Description field.
Rather than making them go through and manually change all of the categories that had been defined, I put together a small script to do the job.
The script checks if the length of the entry in the Image field is greater than zero and that the Description has not been set before doing the update.
UserCatLongDescr = Image_URL
,Image_URL = ''
LEN(RTRIM(Image_URL)) > 0
LEN(RTRIM(UserCatLongDescr)) = 0
As I was writing this script we encountered the same setup issue on a client installation we took over from another partner so this looks like it may be more common that I thought it would be; caused I think be the fact the window has the Image field visible by default and not the Description which people expect.
Microsoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.
The fifteenth Feature of the Day is Historical Received Not Invoiced Report.
A new SQL Reporting Services Report based off the current Received Not Invoiced report but add new functionality by adding a cutoff date based on transaction date or GL Post Date to allow customers to use the report for Historical purposes. This report will allow users to see what was received into inventory but not invoiced yet as of a specific date.
These reports are becoming increasingly popular with clients as they look far better than the “very white” ones GP has traditionally shipped with. The only downside for someone who isn’t in the US is that they ship with a default language of en_US so all values come out as USD. I have some PowerShell scripts I have been working on to download, update the language and upload the reports from SQL Server Reporting Services. I need to revisit and see if I can knock them into shape to post online; I’m not experienced with PowerShell so it is taking me awhile. If someone wants to volunteer to help, I would appreciate it.