Report Writer, Macros & and my attempt at saving some time

One of the common requests that I get when it comes to editing things like invoice layouts is being able to add extra comment lines. Both line comments and header comments.

David Musgrave, creator of GP Power Tools made a great post back in February 2011 about doing this using the report writer functions RW_SOPLineCommentText and RW_SOPHDRCommentText.

Since I do this fairly regularly, I decided to see if there is a way for me to streamline this a little more, as it requires you to make 8 new fields to replace the four original comment fields, once you do it a few times, you do get a bit quicker at creating them, but I’m always looking for ways to save time and make my job a little easier.

Even if you use David’s unsupported method of exporting a package file and then using copy/paste to create the extra fields and rename them, it still takes a little while to get it all done. I decided to see if I could use a Macro to create the fields.

For those of you who don’t know what a macro is, it’s a set of instructions that you record into a .mac file which you can run in Dynamics GP (other software also have variations of this, such as Microsoft Office products, etc.). These sets of instructions are pretty specific, for example, if you have a specific window open when you record the macro, it expects to have this window open when you run the macro again or the instructions will fail to run properly.

I recorded a macro of myself creating one of the fields and then edited it using Notepad++ to copy/paste the sections that created each fields and edited where required to change things like the field name and the line number. Using this method, I managed to get this part of the process, which would usually take at least 10-15 minutes, or more, depending on your ability with Report Writer, down to 32 seconds using the macro.

All in all, it took the same kind of time to make the macro as it did if I were to make the changes manually, except I can now save this time each time I need to add these comments. The macro adds the line comments but can easily be adapted to add the header comments too. The only thing left to do is to follow final part of David’s post and edit the suppression fields.

I did try and see if I could include this process in my macro, but you are unable to add the fields to the suppression calculated fields without the line comments being dragged onto the report and the macro didn’t really like this process as the screen has to be in a certain position for it to work properly.

Something to revisit one day when I have nothing else to do!

If you would like a copy of the macro, you can download it below.

GetInterfaced_CommentLines [7zip] [2kb]

For the macro to run properly, you need to have report designer open, ‘calculated fields’ selected in the Toolbox and the report you are editing in focus. Then, go to the Macro menu and click Play.

You can then select the .mac file you wish to run.

Feel free to take the macro and edit it to meet your needs!

 

Share on Social Media:

Populating Sales User Defined List via SQL

Well, it’s been a while since I last posted!

I have now moved the blog to a new host as I was having trouble gaining access to the control panel for the hosting and a few other issues here and there.

Time to get back into the swing of things!

I have a client who wishes to use the Sales User Defined – List 2 for an easy way to look up and select a Lead Time which will ultimately be used to appear on a quote word template.

Instead of having to populate the list manually, I offered to populate the information for them by loading it in via SQL.

To enter the list manually, you can go to Sales >> Setup >> Sales Order Processing and click on User Defined. Once on this screen, enter a name that will display on the Sales User Defined window.

 

Click the blue expansion arrow. You can now type in your list.

 

Items in this list are limited to 21 characters on each line.

To find which table the list is stored in, I used SQL Server Profiler to monitor the activity in SQL while I created an entry for List 2. The result was SOP40400.

Having a look at SOP40400 in SQL, it consists of three columns:

 

Column NameFriendly NameData Type
INDEX1Index
USTABVALUser Defined Table ValueChar(21)
DEX_ROW_IDDexterity Row IDInt
  • INDEX1 is the ID of the list in question – 1, 2 or 3.
  • USTABVAL is the content of the list, up to 21 characters.
  • DEX_ROW_ID will be automatically populated

 

I created a csv file, the first column a static value of 2 and the second column with the data that they want to have in the lookup for List 2.

I then loaded the data in with the following SQL


CREATE TABLE #List2
    (INDEX1 smallint
    ,USTABVAL char(21))
GO

BULK INSERT
     #List2
FROM
    'C:\Temp\LeadTimes.csv'
WITH
    (FIELDTERMINATOR = ','
    ,ROWTERMINATOR = '\n')
GO

INSERT INTO
	SOP40400
	(INDEX1
	,USTABVAL)
SELECT
	INDEX1
	,USTABVAL
FROM
	#List2
		
DROP TABLE  #List2

 

This creates a temporary table, populates it with the data from my CSV file and then inserts it into SOP40400.

Now, when you use the lookup on the Sales User Defined List 2, your values will show.

 

 

Share on Social Media:

Workflow 2.0 Email Actions Cross Domain Error

Yesterday I got to the bottom of an issue a client was having with Email Actions relating to Workflow.

The client have two domains, one domain that their staff are on and another domain that their servers are on; including the Dynamics GP server.

Firstly, to get email actions working, I needed to have a user created on Domain 2 with the users Email Address populated otherwise the workflow would fail. Once I had this working, however, we encountered another error when opening the links.

I did some research and found that this is because Dynamics GP doesn’t support enGB and requires one of the following formats to work:

  • English (US)
  • Spanish
  • French

After some troubleshooting, I found the easiest way around this is to add enUS as a secondary language in IEs Internet Options.

In Internet Explorer go to Tools > Internet Options > Languages;

Then go to Set Language Preferences;

 

Click on Add a language;

 

Choose English (United States);

 

Once this has been done, you can close these windows and try using the Approval link again.

Something to note, is that you will need to login with a user from Domain 2 to be able to access this. I believe this is something to do with the way the Trust Relationship is set up between the two domains, however this is out of my control in this case.

 

Share on Social Media: