Microsoft Dynamics GP 2018 was released in December 2017 and new features are abound. One of the most impressive features for me is the changes to the workflow process, which defines how information flows through the system.
Workflow 1.0, released with Microsoft Dynamics GP 2013, worked the same as it did in Business Portal without the dependency on Sharepoint to function. Microsoft Dynamics GP has expanded workflow’s capabilities with each version. It had started with a workflow for Purchase order approval and has increased to six modules. In Microsoft Dynamics GP, the following workflows available in their most recent release:
- General Ledger Account Approval
- General Ledger Batch Approval
- Receivables Batch Approval
- Payables Batch Approval
- Payables Transaction Approval
- Purchase Order Approval
- Purchase Requisition Approval
- Purchase Invoice Approval
- Receivings Transaction Approval
- Vendor Approval
- Employee Profile Approval
- Employee Skills Approval
- Payroll Direct Deposit Approval
- Payroll Timecard Approval
- Payroll W4 Approval
- Expense Report Approval
- Timesheet Approval
- Smartlist Designer View Approval
Originally, Workflow 1.0 restricted its functionality into four purchasing workflows. In Workflow 2.0, it became compatible with the Web Client and integrated with Active Directory. Workflow 3.0 improved set conditions and created notifications within GP to alert users. The release of Microsoft Dynamics GP 2018 added new interesting features to expand Workflow. The two most expected are the reminders and copying of workflow steps.
Microsoft Dynamics GP 2018 added the ability to send reminder e-mails without having end users tracking down their supervisors, or the workflow administrator reach out to the approver. Reminders allow users to select intervals of when the reminders go out. The interval’s present daily or even hourly options.
2. Copy workflow steps
In previous versions of Microsoft Dynamics GP, copying an entire workflow was the only way to make changes. The new release brings the capability to copy a step within a workflow. If you have complicated workflows that require you to add multiple steps that are approximately the same, thus this will save you hours of work.
I’m certain that this has taken place in many businesses. Someone logs in for the day and when they print a report or a check, they receive this error. Or they receive a new computer with a fresh copy of Microsoft Dynamics GP and when they try to print a report, the error stops them. This error is interesting because it can happen in different circumstances. It is one of three problems, a missing reports dictionary, a missing 3rd party, or a communication issue with the reports share. Every time I see this error I take specific troubleshooting steps.
1. If the install is a new install on the PC, I check the reports dictionary by going to Tools>> Customize >> Customization Maintenance. If there are no reports stored here, my next step would be to identify the storage location of the reports. On new installs, the reports need imported or pointed to the shared location. If they are on a share, I would point the GP installation to the share.
2. If there are reports visible and they will not print, I navigate to the shared reports folder while logged in as the user. This verifies that the user has access to the share. If you cannot navigate to the shared file location, it may not be a problem with Microsoft Dynamics GP. The permissions to the folder the reports dictionary writes to could have communication issues. Anytime there is a communication issue, I recommend having the server checked over by your IT department. If you are a technical consultant, I would recommend that you take a more in-depth look at why the communication issue appeared. In past situations, I identified communication issues because disks malfunctioned or the operating system corrupted. The result was the server being decommissioned and replaced. Another example, I discovered that they had Cryptolocker on the file share and the virus encrypted the dictionary within the shared folder.
3. Check the form to verify that the correct security is set by going to Tools >> Setup >> System >> Alternatives/Modified Forms and Reports. If you had a 3rd party and removed it, this could have no selection. In one example, I had no selections in this window. By selecting the default Microsoft Dynamics GP report in this window, the report could print again.
Payroll in January is the most havoc time of year. One common problem I see on the help desk in January is the 1095C printing a form for an employee that has not worked this year or has been inactive since before the current year.
To correct this there are two scripts to run. Both of these scripts are from Terry Heley’s ACA post which can be referenced here. In both scripts, please replace XXX for field YEAR1 with the current year.
This script will remove any employee without coverage for the year:
delete from upr10111
where (MonthofCost_1=’0′ AND MonthofCost_2=’0′ AND MonthofCost_3=’0′ AND MonthofCost_4=’0’and
MonthofCost_5=’0′ AND MonthofCost_6=’0′ AND MonthofCost_7=’0′ AND MonthofCost_8=’0′ AND
MonthofCost_9=’0′ AND MonthofCost_10=’0′ and MonthofCost_11=’0′ AND MonthofCost_12=’0′ AND
MonthofCoverage_1=’0′ and MonthofCoverage_2=’0′ and MonthofCoverage_3=’0′ and MonthofCoverage_4=’0’and
MonthofCoverage_5=’0′ AND MonthofCoverage_6=’0′ AND MonthofCoverage_7=’0′ AND MonthofCoverage_8=’0′ AND
MonthofCoverage_9=’0′ AND MonthofCoverage_10=’0′ AND MonthofCoverage_11=’0′ AND MonthofCoverage_12=’0′)
This script will need to have the year replaced in two places. This will delete users who are older than the current payroll year. For example , n 2016 you would replace the XX with 2016.
DELETE UPR10111 WHERE EMPLOYID IN (SELECT EMPLOYID FROM UPR00100 WHERE INACTIVE = ‘1’
AND DEMPINAC <= ‘XXX-01-01 00:00:00.000′) and YEAR1=’XXX’
What these scripts do is update the UPR10111 table where the wage file creates data for the 1095C and 1094C transmittal form.
This information can be found on Terry Heley’s Blog, the payroll ACA information can be found here. Her blog is an amazing source of information and I highly encourage everyone to check it out.
Disclaimer: Always run a new script in your test environment and run a good backup before you run any scripts that will delete or change data.
In Microsoft Dynamics GP, users have access to nothing until security access to windows, reports, and files are granted. There are standard Roles and Tasks are the pre-defined set of windows, reports, and files based on the tasks they are granted. Microsoft built the system in a way that allows Administrators to edit the Roles and Tasks to fit our requirements or even create new ones.
There are specific building blocks that build on one another to create the security in Microsoft Dynamic GP.
- Operation: Is the base level access to windows or reports. These are assigned to a task.
- Ie. The Account Maintenance window
- Task: A Task component is the group of operations that are needed to complete a business task.
- Role: The Role component is the group of tasks that define a particular job in a company.
A great tool to lookup security Roles and Tasks for specific windows or reports is GP Window.
The roles that are assigned to each user are company-specific and you would be able to assign different roles to different companies if needed.
Setting up a Task
Click Microsoft Dynamics GP, point to Tools, point to Setup, point to System, and then click Security Tasks.
Then lookup or create your task ID. If this is a new task then you would need to fill in the Task Name and Category.
Then Select a Product, Type, and Series and this will bring up a selection to provide access. If it is checked then the task will have access to the window or report.
Setting up a Role
On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to System, and then click Security Roles.
Then lookup or create your Role ID. If this is a new task then you would need to fill in the Role Name.
You will then be able to select various security tasks to apply to the Role. If it is checked then the Role will have access to the Task.
Applying user security
On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to System, and then click User Security.
Then look up your user and select the company that you would like to apply the Role. You will need to apply this to each company. If the same permissions are for the same for each company then you can use the copy button on the ribbon to copy the setup between companies.
You will then be able to select various security roles to apply to the user’s security.
Microsoft Dynamics GP Deleting User Error “Deleting the login failed for an unknown reason. Contact your SQL Server administrator for assistance.”
￼Every time I have encountered this is when a user is set up to a specific company database. To resolve this follow these steps:
- Go to your SQL and verify that the user has a SQL login setup. If they do then follow
- Expand your company database
- Expand Security
- Expand Users
- Right-click on the user you are trying to delete and select Delete. This will remove the user’s SQL profile from the database and any schema’s that are causing the error.
- Repeat for all company databases
- Repeat in the Dynamics database
NOTE: DO NOT REMOVE THE OVERALL SQL LOGIN only under each specific database
Log back into Microsoft Dynamics GP and remove the user through the normal user setup. This will remove the user from Microsoft Dynamics GP and also SQL.
After you are comfortable with selecting information and isolating it, I would then recommend moving on to DELETE statements. You must be cautious when running a delete statement as this will remove data from your system. Because you will be making a change to your data, I always recommend getting into the practice of creating a backup before running any statement that will alter your data. You may think, I am extremely careful and this is unnecessary. I used to think this and then accidently delete an entire table with a poor query. The only way I was able to fix my mistake was by having a backup of the system.
Typically, I would create a select statement for the information that I want to delete and then replace SELECT * FROM table WHERE Condition with DELETE table WHERE Condition. In Microsoft Dynamics GP I will typically use the Column Dex_Row_ID to narrow my results when I only want to remove one line. This is because Dex_Row_ID is a unique key and does not duplicate in any of the tables.
SELECT BACHNUMB, MKDTOPST, Dex_Row_ID FROM SY00500 WHERE DEX_ROW_ID=’5′
Would then be changed into a delete statement with the following
DELETE SY00500 WHERE DEX_ROW_ID=’5′
When using a select statement and a delete statement in the same query window, it is best to also use two – in front of a statement that you do not want to run right away. This can assist with accidently deleting the wrong row or table.
Top Tips from This Post
- Always make a backup before running queries that alter data.
- Isolate the information you want to delete with a select statement.
- With Microsoft Dynamics GP, the Dex_Row_ID Column is great to isolate information.
- Use — before delete statements to keep the statement from accidently running
- In Microsoft Dynamics GP it is recommended to delete records through the system if possible and only manually remove records via SQL as a last result.
Knowing where to look to run a query is also extremely helpful to getting started. There are three locations within SQL Server that you need to be aware of to get started.
- You can select New Query and this will bring up a new window to type your SQL query.
- You will see a box appear under the new query with the name of one of the databases. Whatever is in this box is the database that the query will run against (except if specifies otherwise in your query).
- The third box is the name of the databases. If you right click on your database that you want to pull information from and select New Query.
- You can highlight part of a query and it will not run the entire script, only what is highlighted.
- You can select F5 or the execute button in box 2 to run the query.
- Adding – before a query will make it not able to run. This is useful if you have a select statement and a delete statement in same query window and don’t want the delete statement to run yet.