Mail Merge Macro with Non-Scrolling Detail Window

(Mail Merge with scrolling detail is seen on the second part of this blog)

1. Begin with Template (Excel Document)

• See example file: Step 1 PriceList_DetailMaintenance.xlsx

2. Record one iteration of the Steps to create a record within the applicable module

• See example file: Step 2 Item Price Detail Maintenance.mac

Hint: Do a Save as on this file and call it something indicating it is the Final version.

       3. Copy and Paste the Macro body into a word document

See example file: Step 3 MAC_Word Document for Merge.docx

When pasting in the data this message will pop…click on No

4. The word document will open with the recorded Macro data
5. Click on Mailings
6. Select Start Mail Merge
7. Select Letters

8. Click on Select Recipients
9. Browse to the location on your network where you stored the file and select
10. Throughout the recorded macro you will see the points where input was required this is evident by the words “Type To”

11. Highlight the typed text i.e 841200 being careful to not highlight the single quotes around the text
12. In the header click on Insert Merge Field
13. Select the column name in the excel document that relates to this set of data e.g. Item Number
14. Once you have selected to insert all the relevant column data from the spreadsheet, it is best to preview the result.
15. You should expect to see the appropriate column name replace the actual typed information originally typed.

16. Select Finish and Merge and Edit Individual Letters

17. Select to Merge ALL when the option box appears
18. Click OK
19. MS Word will run the Merge Routine
20. Once complete Save As <xxxxx.mac> and Select the format as .txt
21. Select OK at the file conversion to text window

22. Allow time for the conversion and then close the merged document and you will be prompted to save the original Word Document. Save As <xxxxxWORKING.docx.
23. Open the .txt document and Select All then Copy
24. Paste the data into the Document you created in step 2.
25. Save and Close the final version and you are ready to launch the window in Dynamics GP that you wish to populate
26. Ensure that you place the cursor in the window at the exact same place that you began your macro with.
27. Select Tools > Macro > Play

28. Select the Macro to Play that you created in step 2

Hint: Do Not minimize the window…this will interrupt the Macro and it will stop running.

Mail Merge Macro with Scrolling Detail Window

1. Begin with Template (Excel Document)

• See example file: Step 1 Price List Maintenance.xlsx

2. Record a complete iteration of the Steps to create as many lines that will fit into the detail window and stop recording at one line past the scroll. Ensure that your Recording captures up to the point of completing the line and advancing to the next line to continue typing.

• See example file: Step 2 Price Sheet Maintenance.mac  

Hint: Do a Save as on this file and call it something indicating it is the Final version.

3. Open the Recorded Macro using notepad.
4. Take note that there are fields indicating the line No. Also note that after the 8th record (in my example) the 9th line actually still says 8.
Note: In a scrolling window Dynamics GP does not recognize the subsequent lines once the window advances beyond the scroll.

5. Ensure that you add a column into the spreadsheet to accommodate populating the line sequences. Once you have 8 then repeat 8 until the end of the document.

6. Copy and Paste the Macro body into a word document See example file: Step 3 MAC_Word Document for Merge.docx When pasting in the data this message will pop…click on No

7. The word document will open with the recorded Macro data
8. Click on Mailings
9. Select Start Mail Merge
10. Select Letters

11. Click on Select Recipients
12. Browse to the location on your network where you stored the file and select
13. Throughout the recorded macro you will see the points where input was required this is evident by the words “Type To”

14. Highlight the typed text i.e 841200 being careful to not highlight the single quotes around the text
15. In the header click on Insert Merge Field
16. Select the column name in the excel document that relates to this set of data e.g. Item Number
17. Once you have selected to insert all the relevant column data from the spreadsheet, it is best to preview the result.
18. You should expect to see the appropriate column name replace the actual typed information originally typed.

19. Select Finish and Merge and Edit Individual Letters

20. Select to Merge ALL when the option box appears
21. Click OK
22. MS Word will run the Merge Routine
23. Once complete Save As <xxxxx.mac> and Select the format as .txt
24. Select OK at the file conversion to text window

25. Allow time for the conversion and then close the merged document and you will be prompted to save the original Word Document. Save As <xxxxxWORKING.docx.
26. Open the .txt document and Select All then Copy
27. Paste the data into the Document you created in step 2.
28. Save and Close the final version and you are ready to launch the window in Dynamics GP that you wish to populate
29. Ensure that you place the cursor in the window at the exact same place that you began your macro with.
30. Select Tools > Macro > Play

31. Select the Macro to Play that you created in step 2

Hint: Do Not minimize the window…this will interrupt the Macro and it will stop running.

TOP 8 REASONS TO UPGRADE TO VERSION Microsoft Dynamics GP 2013

Upgrading an ERP system is often looked at as time-consuming and disruptive to operations. So it’s no wonder that many customers choose to update their ERP system only when there’s a compelling reason. Every once in a while there comes a new release that’s worth upgrading to, and that’s never been more true than with the 2013 version of Microsoft Dynamics GP.

Here are the top 8 reasons you should consider a move on up to Microsoft Dynamics GP 2013.

1. Plays Well With Others

Dynamics GP is now more easy to use than ever. Improved interoperability between other familiar Microsoft products – such as Office or SharePoint – drives productivity and gives your employees faster access to the information they need to get the job done.

2. The Power of Choice

With the innovative Web Client, your employees can work from a coffee shop, home, or even on the road using a PC, tablet, or the mobile device of their choice. Access the information you need anywhere, anytime, and any way you choose.

3. 150+ Feature Enhancements

Building upon its powerful foundation, GP 2013 added over 150 new feature and capability enhancementsto help you streamline all your business processes and get a handle on costs. Optimize purchasing productivity, inventory tracking, speed up shipping and so much more.

4. Better Insight, Better Decisions

Business Intelligencethat’s tailored to you. The new web-based Business Analyzer delivers role-specific data so that you can delegate tasks, anticipate opportunities, and make more informed business decisions.

5. There’s a Template for That

Easily create any report with out-of-the-box SQL Reporting Services, Excel reports, and dozens of new Word templates– including templates for professional invoices, purchase orders, statements and more.

6. Test Out the Cloud

With many deployment options to choose from – on-premise, hosted, or a combination of both – you can mix and match cloud servicesto fit your business and budget. Add new services as your company grows, or move it entirely to the cloud. GP 2013 is designed to sustain your business momentum.

7. Easy Updates

Version 2013 integrates with other Microsoft Services to lower IT costs and free up resourcesby providing centralized maintenance, security and administration. What’s more, you’ll never have to worry about individual desktop upgrades which are handled automatically.

8. Never Fall Behind

The Business Ready Enhancement Plan(BREP) keeps you up-to-date with the latest and most advanced release of your software.

Get All the Details! Download a free copy of the “What’s New” Guide for Microsoft Dynamics GP Version 2013, or contact us to discuss an upgrade.

Still true for GP 2105R2 Security and GP 2016 Security.  Hands up who loves Dynamics GP / Great Plains security?

Hmm, ok, a couple hands up there. Well, you have to admit it’s much better since the change to the role based security with GP 2010. That relatively small change has allowed for managing security in a much easier fashion. You can now selectively assign or remove blocks of functionality with just a checkbox. Saves so much time and hassle. However, even though this has eased the burden of maintaining security, it still doesn’t help in the case that a user has been granted more access than they necessarily should have.

Granting users more access than required is a scenario we have all seen happen in many organizations, often for what are good (or at least expedient) reasons at the time. Assigning a user to the POWERUSER role for example. Given the granularity of GP security, it can be time consuming to determine what window, report or element the user needs to have added to their security to allow them to perform a particular function. This is doubly problematic as there is almost always a rush for these access requests, it’s month-end and a new user is needed to do some critical function, after trying a few roles and not finding the correct combination, the user gets assigned POWERUSER ‘temporarily’ to complete month-end, and then someone will ‘look at it later’.

Typically of course, later never comes along and the over assignment is left until it ( hopefully ) gets found at auditing time.

Now in many cases, this can be an issue tied into the necessary separation of duties between, payables, receivables etc for fraud management and just general good business practice. Sometimes though, it can be seen as a serious breach of what is privileged information. The payroll and HR modules come to mind immediately.

So, how can we improve GP security to what is likely sensitive information and still allow the users who are supposed to access it to be able to do so.

Well, one suggestion is to add another SQL security group and manage the data for the special module(s) with that as a second tier security check.

So what we will do in our example which will be for payroll, is we will remove the payroll table access from the DYNGRP role, and create a new role in the database called DYNPAYROLL, add the access to the relevant objects to that role then assign our nominated users to that role. This has the effect of ensuring that the users in the list have to pass both being assigned appropriate rights in SQL server as well as in GP. This being handled by two ( hopefully ) separate support staff ensures that there is a double check on users having access to the sensitive data.

Step 1. Create a new role in the company database called DYNPAYROLL. We will do this in the test company Fabrikam for our example, the database name is TWO.

Step 2: We will add access to the SQL server tables and stored procedures to our new role. There are also functions, views etc that can be also managed by access through the role, but for our purposes just controlling access to the base data should be sufficient to accomplish our goal. We need to isolate the tables and procedures we want to add access to our role and also later remove access from for the generic DYNGRP role. To do this, we will start with the tools in GP itself. We can narrow down the relevant tables by looking at the resource descriptions.

Here I have set the Series to Payroll, set the view by to Table physical name and the tables are listed in the scroll box.

Now, for our purposes we need to do some educated examination of the table names displayed, you will see there are a number that start DDXXXXXX which relate to direct deposit. These we will skip for our example. Scrolling down the list we find that there are others marked temp, but the core payroll tables all start with the prefix UPR.

Armed with the knowledge that our core payroll tables all start with UPR, now we can use some SQL tricks to gather them up. I could get clever and write a loop with some dynamic SQL to do this, but I am going for the KISS approach and will show you how to write a script that will build the script you need to remove and assign the permissions we need. The standard permissions assignment statement goes something like this. Grant Select, update, insert, delete on [Tablename] to [Username/Rolename].  For the stored procedures, the naming convention is zDP_UPR , ie the table names plus zDP_ as a prefix and then assorted suffixes.

That’s fine but if there are a lot of tables and procedures, who wants to key all that up ?  So let’s make the server do the work for us.

First we will make use of the system objects that holds the names of the objects in SQL server.  We will restrict our [type] to ‘U’ which is UserTable for the table and [type] =P for the procedures . You can dig further on this material in numerous SQL resources but I leave that to the reader as advanced SQL is out of scope of this blog.

Looking at our list, our names are matching the list we saw in the GP table resource view, so looks like we have the right set.

Next we will leverage this list to create the permissions statements we need to run. In our query tool, in my case SQL Server Management Studio, I am going to set the query results to be text.

After that we write a query to build a query that will do all the permissions assignments for us. If you look you can see I am combining the changing list of names from the query above and including a literal string to represent the permissions assignments. It quickly builds 90 ( there were 90 tables ) executable statements in the lower query results pane. Now we can copy the results of that lower pane, put it in a query window and execute all 90 permissions statements in one go.

And then for the procs, do the same.

After running this our DYNPAYROLL role has the insert, update, delete and select permissions it will require to the 90 Payroll tables.

Then we do the same but remove the permissions for the same tables from DYNGRP using the REVOKE command. There is also a DENY command but we will not use that as it trumps all other permissions and effectively locks the user or role from accessing the table until removed. Also I am avoiding discussion of schemas as the current GP implementation doesn’t really use them so for our purposes we will ignore them for now.

You can see the command has changed to REVOKE and the role was changed to DYNGRP.

Then we copy that result set into a new query and run it.

Looking at our list, our names are matching the list we saw in the GP table resource view, so looks like we have the right set.

Next we will leverage this list to create the permissions statements we need to run. In our query tool, in my case SQL Server Management Studio, I am going to set the query results to be text.

Then we write a query to build a query that will do all the permissions assignments for us. If you look you can see I am combining the changing list of names from the query above and including a literal string to represent the permissions assignments. It quickly builds 90 ( there were 90 tables ) executable statements in the lower query results pane. Now we can copy the results of that lower pane, put it in a query window and execute all 90 permissions statements in one go.

And then for the procs, do the same.

After running this our DYNPAYROLL role has the insert, update, delete and select permissions it will require to the 90 Payroll tables.

Then we do the same but remove the permissions for the same tables from DYNGRP using the REVOKE command. There is also a DENY command but we will not use that as it trumps all other permissions and effectively locks the user or role from accessing the table until removed. Also I am avoiding discussion of schemas as the current GP implementation doesn’t really use them so for our purposes we will ignore them for now.

You can see the command has changed to REVOKE and the role was changed to DYNGRP.

Once all that is done, our new DYNPAYROLL should have the access for payroll tables, and regular DYNGRP will not.

Now let’s test our theory. To grant access to payroll data successfully we will add our test user to the POWERUSER role just to show that they should have access to basically everything in GP.

At this point you can see the user is a member of the dyngrp in the TWO company, POWERUSER role so thus should be able to open payroll windows with no trouble.

When I open a payroll card I am presented with this.

Once that is done, let’s try opening our payroll user card.

Success !

Things to be aware of:

• If the user is logged in, you may need them to logout and in again for everything to take effect correctly.
• This should be done by someone experienced enough in SQL that they can correct what they have done if they make a mistake.
• Take a backup before doing any of this.
• Error messages will not be friendly so this technique is more for when access needs to be enforced regardless of what GP security settings may be changed to.
• While you can do this with other modules, the maintenance overhead starts increasing as you create more groups, and I would suggest this type of strategy really only fits well for payroll and HR which are a bit of a special case.
• You need to be aware that you will need to reset the security elements after performing a service pack or upgrade as typically the DYNGRP gets reset back to default after those types of system maintenance operations are completed.
• If you want to experiment with other modules, try to stick to just key core tables and related procedures as that will usually be sufficient to accomplish your goal. The biggest risk is removing access to a table or procedure from DYNGRP that is needed by other processes or modules eg SY system tables and the like.