Workflow 2.0 Adding additional tables to Workflow Conditions
From The GP Engineer:
https://community.dynamics.com/gp/b/dynamicsgpengineeringteamblog/archive/2015/07/21/joining-in-additional-tables-for-workflow-conditions
21 Jul 2015 3:00 PM
In the new Workflow system for Microsoft Dynamics GP, you are able to set conditions for workflow steps based on any field that is on the document in the workflow. One of the common questions we get is whether or not you are also able to set conditions for workflow steps based on fields that are not directly on the document. For example, can I set conditions for running the workflow step for a Payables transaction based on the vendor class of the vendor? The vendor class isn’t a field on the document itself, so it isn’t available in the list of fields for setting conditions.
We’re able to get additional fields into the list for setting conditions by joining in additional tables into the condition editor for the workflow type. We can do this by using one of the tools added to Workflow that was originally intended to help with the development of new workflow types. But we can also use it to modify existing workflow types, including extending the fields available for setting conditions.
The most common scenario where we’ve seen this requested is being able to set conditions based on a segment of the account used on the line for a purchase requisition. The account is on the document, but the conditions don’t work as expected because the condition is actually being compared against the account index (Inventory Index) in the requisition line table. To work around this, we can join the Account Index Master table into the condition list, and then set conditions based on any properties of the account.
Note: Before making any of these changes, it is highly recommended that you make a backup of your company database. The relationships for the tables and fields used for setting conditions for workflow types are stored in the database. When you change these with the condition editor, there is no undo. So you will want a backup in case you aren’t able to get the table relationships set up the way you expect.
Here are the steps:
Shut down GP.
In the Dex.ini, set the QueryDesignerAllFunctionality=TRUE switch, and restart GP.
Open Workflow Maintenance, and open the Workflow Condition Editor for one of the steps of your workflow. It should now look something like this:
4. In the Database Tables section, select Microsoft Dynamics GP >> Financial >> Account Index Master.
5. In the Selected Fields section, select the Account Number String field.
6. On the Link Tables, tab, create a left join between the Purchase Requisition Line table and the Account Index Master table, and add it to the Table Links:
Note: We use a left join in this case because the account is not required on a requisition. Using an inner join would make it so no data is returned for records where an account isn’t entered.
7. On the Conditions tab, set the condition you want on the Account Index Master.Account Number String field. For example, if you want to set a condition that the step runs only when the first segment of the account is “200”, set the condition to be “where Account Index Master.Account Number String begins with ‘200’”.
The condition is setting the SQL string that will retrieve the data during the workflow. So there is some flexibility in how you set the string, such as using wildcard characters in the constant field. For example, you can set conditions based on the second segment of your account by using wild cards for the other segments. Imagine that you want to set the condition that the step runs only if the second segment of the account is “1300”, and your first segment three characters long. You can set the condition by using four underscores (three for the length of the segment and one for the segment separator) to start the constant.
If you look at the Run Query tab, and click Execute, you can see the SQL query that is generated for the condition. Below is the SQL query generated by the condition:
SELECT [POP10200].[POPRequisitionNumber] AS [Requisition Number_1] , [POP10200].[RequisitionDescription] AS [Requisition Description_2] , [POP10200].[RequisitionStatus] AS [Requisition Status_3] , [POP10200].[COMMNTID] AS [Comment ID_4] , [POP10200].[DOCDATE] AS [Date_5] , [POP10200].[REQDATE] AS [Required Date_6] , [POP10200].[REQSTDBY] AS [Requested By_7] , [POP10200].[DOCAMNT] AS [Document Amount_8] , [POP10200].[DomainUserName] AS [Domain User Name_9] , [POP10200].[USERDEF1] AS [User Defined 1_10] , [POP10200].[USERDEF2] AS [User Defined 2_11] , [POP10210].[RequisitionLineStatus] AS [Requisition Line Status_12] , [POP10210].[ITEMNMBR] AS [Item Number_13] , [POP10210].[ITEMDESC] AS [Item Description_14] , [POP10210].[NONINVEN] AS [Non-inventoried Item_15] , [POP10210].[VENDORID] AS [Vendor ID_16] , [POP10210].[LOCNCODE] AS [Site ID_17] , [POP10210].[QTYORDER] AS [Quantity Ordered_18] , [POP10210].[EXTDCOST] AS [Extended Cost_19] , [POP10210].[UNITCOST] AS [Unit Cost_20] , [POP10210].[REQDATE] AS [Required Date_21] , [POP10210].[REQSTDBY] AS [Requested By_22] , [POP10210].[INVINDX] AS [Account Number_23] , [POP10210].[STATE] AS [Ship To State_24] , [POP10210].[CITY] AS [Ship To City_25] , [POP10210].[ZIPCODE] AS [Ship To Zip_26] , [POP10210].[CCode] AS [Ship To Country Code_27] , [POP10210].[COUNTRY] AS [Ship To Country_28] , [POP10210].[SHIPMTHD] AS [Shipping Method_29] , [POP10210].[FRTAMNT] AS [Freight Amount_30] , [POP10210].[TAXAMNT] AS [Tax Amount_31] , [POP10210].[InvalidDataFlag] AS [Invalid Data Flag_32] , [POP10210].[COMMNTID] AS [Comment ID_33] , [POP10210].[USERDEF1] AS [User Defined 1_34] , [POP10210].[USERDEF2] AS [User Defined 2_35] , [POP10210].[ADRSCODE] AS [Ship to Address ID_36] , [GL00105].[ACTNUMST] AS [Account Number String_37] , [POP10210].[POPRequisitionNumber] AS [POP Requisition Number_38] , [POP10210].[ORD] AS [Ord_39] , [POP10210].[LineNumber] AS [LineNumber_40] , [POP10210].[Item_Number_Note_Index] AS [Item Number Note Index_41] , [POP10210].[Vendor_Note_Index] AS [Vendor Note Index_42] , [POP10210].[UOFM] AS [U Of M_43] , [POP10210].[UMQTYINB] AS [U Of M QTY In Base_44] , [POP10210].[Location_Code_Note_Index] AS [Location Code Note Index_45] , [POP10210].[QTYCMTBASE] AS [QTY Committed In Base_46] , [POP10210].[QTYUNCMTBASE] AS [QTY Uncommitted In Base_47] , [POP10210].[ORUNTCST] AS [Originating Unit Cost_48] , [POP10210].[OREXTCST] AS [Originating Extended Cost_49] , [POP10210].[ACCNTNTINDX] AS [Account Note Index_50] , [POP10210].[CURNCYID] AS [Currency ID_51] , [POP10210].[Currency_Note_Index] AS [Currency Note Index_52] , [POP10210].[CURRNIDX] AS [Currency Index_53] , [POP10210].[RATETPID] AS [Rate Type ID_54] , [POP10210].[EXGTBLID] AS [Exchange Table ID_55] , [POP10210].[XCHGRATE] AS [Exchange Rate_56] , [POP10210].[EXCHDATE] AS [Exchange Date_57] , [POP10210].[TIME1] AS [Time_58] , [POP10210].[RATECALC] AS [Rate Calc Method_59] , [POP10210].[DENXRATE] AS [Denomination Exchange Rate_60] , [POP10210].[MCTRXSTT] AS [MC Transaction State_61] , [POP10210].[DECPLCUR] AS [Decimal Places Currency_62] , [POP10210].[DECPLQTY] AS [Decimal Places QTYS_63] , [POP10210].[ODECPLCU] AS [Originating Decimal Places Currency_64] , [POP10210].[ITMTRKOP] AS [Item Tracking Option_65] , [POP10210].[VCTNMTHD] AS [Valuation Method_66] , [POP10210].[CMPNYNAM] AS [Company Name_67] , [POP10210].[CONTACT] AS [Contact_68] , [POP10210].[ADDRESS1] AS [Address 1_69] , [POP10210].[ADDRESS2] AS [Address 2_70] , [POP10210].[ADDRESS3] AS [Address 3_71] , [POP10210].[PHONE1] AS [Phone 1_72] , [POP10210].[PHONE2] AS [Phone 2_73] , [POP10210].[PHONE3] AS [Phone 3_74] , [POP10210].[FAX] AS [Fax_75] , [POP10210].[Print_Phone_NumberGB] AS [Print Phone Number GB_76] , [POP10210].[ADDRSOURCE] AS [Address Source_77] , [POP10210].[Flags] AS [Flags_78] , [POP10210].[ShippingMethodNoteIndex] AS [Shipping Method Note Index_79] , [POP10210].[ORFRTAMT] AS [Originating Freight Amount_80] , [POP10210].[ORTAXAMT] AS [Originating Tax Amount_81] , [POP10210].[Comment_Note_Index] AS [Comment Note Index_82] , [POP10200].[Requisition_Note_Index] AS [Requisition Note Index_83] , [POP10200].[Comment_Note_Index] AS [Comment Note Index_84] , [POP10200].[PRSTADCD] AS [Ship to Address ID_85] , [POP10200].[CMPNYNAM] AS [Company Name_86] , [POP10200].[CONTACT] AS [Contact_87] , [POP10200].[ADDRESS1] AS [Address 1_88] , [POP10200].[ADDRESS2] AS [Address 2_89] , [POP10200].[ADDRESS3] AS [Address 3_90] , [POP10200].[CITY] AS [City_91] , [POP10200].[STATE] AS [State_92] , [POP10200].[ZIPCODE] AS [Zip Code_93] , [POP10200].[CCode] AS [Country Code_94] , [POP10200].[COUNTRY] AS [Country_95] , [POP10200].[PHONE1] AS [Phone 1_96] , [POP10200].[PHONE2] AS [Phone 2_97] , [POP10200].[PHONE3] AS [Phone 3_98] , [POP10200].[FAX] AS [Fax_99] , [POP10200].[CREATDDT] AS [Created Date_100] , [POP10200].[MODIFDT] AS [Modified Date_101] , [POP10200].[USER2ENT] AS [User To Enter_102] , [POP10200].[Flags] AS [Flags_103] , [POP10200].[Workflow_Status] AS [Workflow Status_104] , [GL00105].[ACTINDX] AS [Account Index_105] , rtrim(ltrim([GL00105].[ACTNUMBR_1])) + '-' + rtrim(ltrim([GL00105].[ACTNUMBR_2])) + '-' + rtrim(ltrim([GL00105].[ACTNUMBR_3])) + '-' + rtrim(ltrim([GL00105].[ACTNUMBR_4])) + '-' + rtrim(ltrim([GL00105].[ACTNUMBR_5])) AS [Account Number_106] FROM [POP10200] INNER JOIN [POP10210] ON [POP10200].[POPRequisitionNumber] = [POP10210].[POPRequisitionNumber] LEFT OUTER JOIN [GL00105] ON [POP10210].[INVINDX] = [GL00105].[ACTINDX] WHERE ( [GL00105].[ACTNUMST] LIKE '____1300%' )
8. Set the QueryDesignerAllFunctionality=FALSE in your Dex.ini to limit others from messing up the joins in your table conditions.
Once you have the new tables joined in and set conditions on additional fields, you can then run your workflow to make sure you’re getting the results you expect. The results returned by the query are the records for which the condition is met. The workflow step will run for records that meet this condition.