A Diary of reIMAGINE 2016: Part 15 – Conference Day 2/Custom Workflow Creation

reIMAGINE2016The 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?).

Click to show/hide the A Diary of reIMAGINE 2016 Series Index

SQL Script To Create macro To Activate BOMs

Microsoft Dynamics GPWhile 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).
*/
SELECT DISTINCT
	'# 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'' 
	'
FROM
	BM00101 AS BMH
INNER JOIN
	BM00111 AS BMC
		ON BMC.ITEMNMBR = BMH.ITEMNMBR
WHERE
	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.