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.
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.
Password security is something everyone always hears about. Make sure that you have a strong password. Weak passwords are hackable. What I find that is not mentioned enough is what constitutes as a strong password is constantly increasing as hackers get better. 15 years ago they recommended adding uppercases to lowercases. 10 year ago they recommended adding numbers. 5-10 years ago they also recommended adding in symbols. The definition of a strong password keeps shifting.
This is typically not a problem if you keep up with the latest requirements/recommendations for passwords. Additionally, humans seem to be psychologically wired to make passwords easier to remember. This is typically by using words that have some meaning to them.
My favorite exercise is to make a sentence that means something to you. With the sentence, you would then take the first and/or second letters of the words. Capitalize some, replace some with numbers or symbols.
If you are not that creative and don’t want to remember the password. You are in luck! There are solutions for these two issues. First, there are password generators that will create a password for you. The two I like to use the most are LastPass Password Generator and Norton Password Generator.
The next problem comes from remembering the passwords. It is not recommended to reuse passwords, because once they crack one then they will crack all of your accounts. Creative companies have created an automatic solution for this. Dashlane and Last Pass 4.0 Premium are two password vault solutions that store your passwords in an encrypted vault. These two options are nice as they both offer a cloud solution that you can access your passwords anywhere on any device.