Workflow 'Not Submitted' and 'Pending Approval' navigation lists not working correctly in Microsoft Dynamics GP after upgrading (Updated)

***Updated May 21st, 2019***

We've seen some instances where, after upgrading from a version of Microsoft Dynamics GP that was before Dynamics GP 2013 R2 (12.00.1745), which was the first build that Workflow functionality was introduced, the 'Not Submitted' and 'Pending Approval' navigation lists related to Workflow types are not working correctly.

What we've seen with these lists is the following issues:

1. The 'Not Submitted' and 'Pending Approval' lists won't show at all.

2. The 'Not Submitted' and 'Pending Approval' filters are incorrect, so they both pull the wrong data.

3. The 'Pending Approval' lists never show the workflow documents/batches that are pending approval through Workflow.

We have not seen this issue with brand new installs of Dynamics GP 2013 R2 or above.

The issue with the lists is that the filters are looking at the 'Workflow_Approval_Status' column, which will always have a value of 0 (zero) and because of this, it isn't able to identify any objects that are pending approval, like it can when it is corrected to use the actual 'Workflow_Status' column.

The lists that seem to be impacted by this issue are related to the following Workflow types:

--Purchase Order Approval

--Payables Batch Approval

--Vendor Approval

--General Ledger Batch Approval

--Receivables Batch Approval

Each of these should have a default list, pending approval list and not submitted list. For example, for the Purchase Order Approval workflow, under the Purchasing area page, it'll have three lists:

   1. Purchase Order Transactions - this is a list of all purchase orders, regardless of status.

   2. Purchase Orders Not Submitted - this list shows any purchase orders that are not pending any user action in Workflow

   3. Purchase Orders Pending Approval - this list shows any purchase orders that are currently pending user action through Workflow

The fix for these issues was to update the SY07220 and SY07230 tables in the DYNAMICS/system database to use the Workflow_Status column which has the accurate status for Workflow objects as well as to verify that all three of these lists are present for these document/batch types, whether they are active in Workflow or not.

Because these tables are system tables in the DYNAMICS/system database, fixing them will fix the issue in all company databases.

>>If you do not have any custom navigation lists that you want to maintain, just defaults, you can use these steps:

  1. Make sure you have a recent, valid backup of your DYNAMICS or GP system database.

  2. Also, make backups of these two tables, using a script like this against the system database:

           Select * into SY07220BAK from SY07220

           Select * into SY07230BAK from SY07230

  3. Next, run these scripts against the system database:

           Delete SY07220

           Delete SY07230

  4. As the ‘sa’ login, launch and login to Dynamics GP. Verify that the SY07220 and SY07230 tables are now populated again. On my GP version, there are records for the core Microsoft Dynamics GP module, as well as Project Accounting and HRM Solution Series, if you happen to have either or both of those modules installed on your environment. 

  5. Verify the default navigation lists work correctly including any Not Submitted and Pending Approval lists.

>>If you do have custom navigation lists that you want to maintain, use the following to only reset the default lists:

  1. Make sure you have a recent, valid backup of your DYNAMICS or GP system database.

  2. Also, make backups of these two tables, using a script like this against the system database:

        Select * into SY07220BAK from SY07220

        Select * into SY07230BAK from SY07230

  3. Run the corresponding attached scripts for your version of Dynamics GP against the GP system database, which will remove only the default navigation lists and re-create them.

      GP 2018 >>NEW-GP-2018-Navigation-List-Fix-Scripts.txt

     GP 2016 and 2015 >>NEW-GP-2016-GP-2015-Navigation-List-Fix-Scripts.txt

      GP 2013 >>NEW-GP-2013-Navigation-List-Fix-Scripts.txt    

    4. Login to Dynamics GP as ‘sa’ and test the default navigation lists to verify they work correctly now.

These scripts update the default SY07220 and SY07230 records for the navigation lists, but shouldn't affect any custom lists that have been created. 

Dynamics GP 2015 and Dynamics GP 2016 have these navigation lists added to them:

-- Payables Transactions (Not Submitted / Pending Approval)

-- Paystubs

-- HRP Workflow Pending Approval

-- My Team

-- SmartList Designer View Pending Approval

-- W2 - Wage and Tax Statement

-- (Project Accounting) PTE Expenses (Default / Not Submitted / Pending Approval)

-- (HRM Solution Series) Benefit Enrollments (Default / All)

-- (HRM Solution Series) Administer Benefit Enrollments (Default/All)

Dynamics GP 2018 has these navigation lists added to it:

-- Accounts (Default, Not Submitted, Pending Approval)

-- Receivings Transaction Not Submitted

-- Receivings Transaction Pending Approval

-- Purchasing Invoice Not Submitted

-- Purchasing Invoice Pending Approval

**NOTE: We have seen cases where, when running one of the 'Pending Approval' or 'Not Submitted' navigation lists in Microsoft Dynamics GP related to the new Workflow, we'd see a 'Microsoft Dynamics GP has stopped working' message, which then asks if you want to send information or not. Clicking on the Debug button, it makes a mention to an Unhandled Microsoft .NET Framework exception occurring in Dynamics.exe. 

   To resolve this, we've usually had users delete the contents of the SY07220 and SY07230 tables as mentioned above, and then login to Dynamics GP and access the navigation lists again, which re-populate these two tables. 

   If you have custom lists created, I'd recommend trying the scripts first, before removing everything from these two tables, when trying to resolve this issue.

Hopefully this will save you all from having to open a support case and to be able to resolve this issue should it appear. If the customer doesn't yet use Workflow functionality and/or the navigation lists to work with workflows, they may not have encountered this issue, but it's something to be aware.

Thank you!!!