Copying Report Formats between Reports… and a warning about Word Templates

David Meego - Click for blog homepageThere is an unsupported method of copying report formats between reports that is very commonly used (and even recommended by me), but we recently had a case that highlighted a problem with the technique. This blog post will discuss the method and the issue we have seen.

 

The Situation

The situation is very common and can have two variants. I will use the Sales Order Processing Reports for my example, here are the scenarios:

  1. You have modified the SOP Blank Invoice Form to meet the requirements of your customer (either as the final document or for use with Word Templates). You now want to duplicate the format changes to other document types (Blank, Long, Other, Short, with options, etc.).
     
  2. You have modified the SOP Blank Invoice Form to meet the requirements of your customer (either as the final document or for use with Word Templates). You now want to duplicate the format changes to the historical version of the report: SOP Blank History Invoice Form.

Next, we will look at how the report formats can be duplicated. 

 

The Method

Before we go into details, I must emphasize that this method is not officially supported as it required direct editing of Customization Maintenance packages.

Below are the steps to copy a report format from one report (Source Report) to another report (Target Report):

  1. Ensure that both the Source Report and the Target Report have both been inserted into the Modified Reports list (right hand pane) in the Report Writer. The Source Report should already be there, inserting the Target Report makes one of the following steps easier.
     
  2. From the Microsoft Dynamics GP application, select Microsoft Dynamics GP >> Tools >> Customize >> Customization Maintenance and export both reports separately as packages. You can locate the files on your Desktop for ease of access.
     
  3. Open Notepad.exe and drag and drop the Target Report’s package into the Notepad window.
     
  4. Then copy the report naming line(s) to your clipboard.  For example:
     

    Report “SOP Blank History Invoice Form”
     
  5. Drag and drop the Source Report’s package into the Notepad window.
     
  6. Select the report naming lines(s) and paste from your clipboard to replace the information.
     
  7. Optional Step when changing report from Work to History: Perform the appropriate Find and Replace steps to update the tables used on the report:
     
    Find: SOP_HDR_WORK    Replace with: SOP_HDR_HIST
    Find: SOP_LINE_WORK    Replace with: SOP_LINE_HIST
     
    Tables which are used for Work and History do not need changing, such as SOP_LINE_CMT_WORK_HIST, SOP_Serial_Lot_WORK_HIST, sopUsrDefWorkHist.
     
  8. Select File >> Save As and save the updated package file as a new file with the name of the Target Report plus “Updated”. Keep the original Target Report’s package file as a backup in case you need to restore it.
     
  9. Back in Microsoft Dynamics GP, import the updated Target Report package via Customization Maintenance.
     
  10. Adjust Alternate Modified Forms and Reports security settings to use the now modified version of the report. 

Note: You must also ensure that the Main Table (as shown on the Report Definition window in Report Writer) is the same for the two reports involved. If the main tables do not match, it is very unlikely that the target report will print successfully.

 

The Problem 

While the method above works extremely well for the Report Writer reports, we have found that reports updated using this method do not always work with Word Templates. If you go back to the original report, the Word Templates work, but with the modified report the Word Template does not always work. This is often noticed when the Historical versions of the reports fail to email.

Our case had the issue that the SOP Blank Invoice Form would print and email via Word Templates, but the SOP Blank History Invoice Form would print but not email. Research found a blog post by Andrew Hall from Touchstone in the UK (see below) that highlighted the issue.

To understand what is happening you need to understand a little about how Word Templates work. Word Templates are tied into the Section Breaks of a report. When the different sections print, events fire in Dynamics which are picked up by the Word Templates handling code and are used to capture details about the key fields upon which the section breaks are based. The code in Dynamics and the section breaks in the reports match so they both refer to the same field in the same table.

If you start changing the sections, or the fields upon which section breaks are based, the code and section breaks no longer match and Word Template functionality will start to fail.

The problem as highlighted in Andrew’s blog post is that the Section Breaks in the Work SOP reports are subtly different from the matching Section Breaks in the History SOP reports. When we used the method above to copy the report format, we unintentionally broke the Word Template Email functionality by changing the Section Breaks.

Below are screenshots of the Dummy1 Header and the Back Order Footer from the Work Invoices that show that the SOP Number field is being captured from the Sales Transaction Work (SOP_HDR_WORK) table.


Dummy1 Header from SOP Work Invoice (SOP Number from SOP_HDR_WORK)


Back Order Footer from SOP Work Invoice (SOP Number from SOP_HDR_WORK)

Below are screenshots of the Dummy1 Header and the Back Order Footer from the original (unmodified) History Invoices that show that the SOP Number field is being captured from the Sales Transaction Amounts History (SOP_LINE_HIST) table, which is not the Sales Transaction History (SOP_HDR_HIST) that came across when we copied the format.


Dummy1 Header from SOP History Invoice (SOP Number from SOP_LINE_HIST)


Back Order Footer from SOP History Invoice (SOP Number from SOP_LINE_HIST)

As the report is not breaking on the correct field in the wrong table, the data passed to the Word Template code is incorrect (blank) and the Email functionality fails to work.

 

The Solution

The fix for this issue is to edit the Section Breaks for the History Report so that the Dummy1 Header and Back Order Footer use the SOP Number field in the Sales Transaction Amounts History (SOP_LINE_HIST) table again. 

The exact steps are listed in Andrew’s Blog:

 

The Warning 

The lesson learned here is to avoid changing section break tables and fields for reports that are used with Word Templates. If the Word Templates fail to work in any way, test with the original Report Writer report and see if that works. If it does, compare the table and field for each section break with the original report and make sure they match.

A good method would be to never remove fields or change section breaks. You can always hide fields or supress section breaks if you want to make changes. Also keep testing (and saving packages) as you modify the reports to make sure you have not broken anything. If you test as you go along, it will be easier to remember the last changes made so they can be backed out.

 

More Information

Below are some blog articles which discuss aspects related to this post:

 

Thanks to Andrew Hall for his troubleshooting on this issue, hopefully I have clarified the reasons and a possible cause in duplicated reports.

David

See more here:
Copying Report Formats between Reports… and a warning about Word Templates

February 26, 2013 · David Musgrave · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 315

Using Problem Steps Recorder (PSR) To Capture Replication Steps

Allan Cahill - Click for blog homepageHi

I recently had a support request where, due to time differences, I was not able to immediately stream / screen share.  It was also difficult to understand the exact set of steps taken leading up to the issue. Due to these difficulties, a viable option was to record the exact set of steps taken leading up to the problem.  This is where Windows Problem Steps Recorder (PSR) came into play.  The following is an overview as to steps to run PSR. 

 

1.  Overview

Problem Steps Recorder can be used to automatically capture the steps performed by a user on a computer running Windows 7, 2008 R2, 8, and 2012, including a text description of where they clicked and a picture of the screen during each click. This capture is then saved to a file that can be used by a support professional to help the user troubleshoot the issue or understand what steps were taken by the user.

The output describes what the user did as well as screen shots of what was going on at the time.  In addition, the screenshots included are clickable to view full size.  PSR has an additional button titled Add Comment.  When you click this button, it allows you to highlight an area of the screen and type in a comment.  This allows you to specify additional comments to the repro steps to make it easier to follow during a complex recording. 

PSR is more than just a screenshot capturing tool. Besides automating the capturing of what’s going on the screen, it will also highlight the user’s mouse clicks, and, most importantly, will provide a detailed textual metadata with a description of what the user is doing.

There is a short video on TechNet that gives a demonstration of the Problem Steps Recorder.

 

2.  How Do I Use Problem Steps Recorder?

Step 1: Firstly, open the application by writing “steps” and pressing enter in the search bar.

You can also open Control Panel and type in problem in the search box, then click on the “Record steps to reproduce a problem” link:

By default, PSR will record only 25 screenshots, yet this can be changed to any number up to 100. Click on the small down-arrow next to the help icon, and then select Settings:

If you have more than 100 actions, which is possible, screenshot #1 would be removed in order to capture screenshot #101. The text detail for Step #1 would still remain.

Step 2: To start recording, click Start Record. On your computer, go through the steps on your computer to reproduce the problem. You can pause the recording at any time, and then resume it later.

NOTE: As you click screenshots are being recorded.  You will see a red dot while you click to indicate this.

During the recording session you can hit the Add Comment button and highlight an area of the screen and leave a comment or question.

Step 3: When you’ve completed reproducing the problem, click Stop Record.

Step 4: In order to save or email the file Support, Save or Email and give the file a descriptive name.

 

NOTE:  In Windows 7/2008 R2, once the recording is stopped, the Save As window is automatically opened.

Step 5:  To view the record of the steps you recorded, open the .zip file you just saved, and then double-click the file. The document will open in your browser.

When the .ZIP file is extracted, the .MHT file can be double-clicked and viewed inside Internet Explorer. Scroll through the steps, or choose to watch them as a slide show. Each step provides text details of the action taken and clearly defines in the screenshot where
the action should be taking place.

The “Additional Details” section at the end of the .MHT file provides a text only version of all the steps taken as well as specific version information. This information can be especially helpful in a Technical Support situation.

 

3.  Notes

  • There are differences between the Windows 7 version of the application and Windows 8 version. Firstly, in Windows 8, Steps Recorder runs as administrator by default but in Windows 7/2008 R2, it does not. Secondly, the Windows 8 version allows you to view the recording before saving or sharing it but the previous version save the recording directly as a zip folder. 
  • The same functionality is available in Windows 7, where it is called the “Problem Steps Recorder”, but no shortcut is automatically created for the Problem Steps Recorder during Windows installation.  From Start >> type PSR. 
  • PSR runs as a standard user mode process. 
  • To record steps in an app that’s not on your desktop, you’ll need to start Steps Recorder from your desktop, go to the app, record the steps you want, and then return to the desktop to stop Steps Recorder.
  • To view the steps you recorded, open the file you saved, and then double-tap or double-click the file.
  • In some cases, tapping or clicking Email in Steps Recorder won’t open an email message. If this happens, try closing your email app before you tap or click Email in Steps Recorder or open a new email message and attach the .zip file.

 

Video Demonstration

(Please visit the site to view this video)

 

Additional Resources

 

Thanks for taking the time to have a read through this blog.

Allan

More:
Using Problem Steps Recorder (PSR) To Capture Replication Steps

February 22, 2013 · Allan Cahill · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 649

Quick Tip: Using Third Party Tables to fill a Scrolling Window

David Meego - Click for blog homepageMy good friend, Mariano Gomez (The Dynamics GP Blogster) raised this issue recently. He wanted to use the method I discussed in my previous Quick Tip post: Quick Tip: Accessing Third Party Table Buffers using anonymous tables to gain access to the third party table so he could display it in a Scrolling Window on his form.

The reason why this will not work in its simplest form is that the anonymous table buffer that we have which points to the third party table only has the scope of the callback global procedure. Once that procedure completes access to the third party table buffer is lost.

So what are the alternative solutions?

Going back the Cross Dictionary Dexterity Development techniques, I previously would have suggested a duplicate table definition, however as discussed in point 3 of the previous article, this is not a recommended method anymore.

Note: I am assuming here that the data to be displayed is a small subset of the entire third party table and not containing thousands of records. If you want access to the entire table, the duplicate table definition method might be for you.

Now I would suggest the use of a temporary table to contain the data you want to display in your Scrolling Window. To make this work all you need to do is populate the temporary table before you issue the fill window command and if you are allowing edits and or adds, you must write back the data from the temporary table to the actual table when you have finished.

If the third party table is stored in SQL Server (database type: SQL or default), then make sure that your temporary table is also created in SQL (database type: SQL or default) and not a local ctree table. This means that we can leverage SQL optimization to transfer the data between the actual third party table and the temporary table.

Note: I would suggest adding extra boolean fields to the temporary table to allow you to track what records have been edited or added. You might also want to add keys to the temporary table with these boolean fields as the first segment in the index.This will help later when you need to write the data back to the original third party table.

Below are some suggested methods for transferring the data between the original third party table and your temporary table:

  1. Use the method described in the post: Quick Tip: Accessing Third Party Table Buffers using anonymous tables to capture the table buffer. You can then set a range and use range copy to move the data into the temporary table. The range copy command will use a single SQL insert statement if both tables are SQL tables (and there are no Dexterity triggers on the tables). To copy the data back, you can remove all records in the range from the original table and use range copy to replace the updated data (as long as your temporary table contained every column in the original so no data is lost). Otherwise you could use Dexterity to range on updated or added records and write back the data record by record.
     
  2. Use pass through SQL to create the appropriate statements to transfer data to and from the temporary table. Use the Table_GetOSName(table temp_table_name) command to obtain the physical SQL name for the temporary table. The boolean flags added to the temporary table can be used to control what records are inserted and what records are updated at the SQL level.
     
  3. You can also use a call to a SQL Stored procedure, but this will require the creation of the stored procedure at the SQL level and the creation of a Dexterity prototype procedure to call it. The advantage of stored procedures is that the code can be precompiled. However, in this case as the temporary table’s name is not known, the code will have to be dynamically generated and then executed with the EXEC (@variable) command. Thus losing any precompiled benefits…. Forget it this method is not worth the effort.

I hope this post has provided some insight into how you can access third party data in your own scrolling windows. 

Enjoy

David

Excerpt from:
Quick Tip: Using Third Party Tables to fill a Scrolling Window

February 18, 2013 · David Musgrave · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 244

How to Track unused Journal Numbers

I got an interesting question from one of the GP users requesting a list of unused Journal Number, therefore I decided to scratch my head and write this query, below will help:

SELECT DISTINCT NUMBER FROM MASTER..spt_values WHERE NUMBER BETWEEN
(SELECT MIN(JRNENTRY) AS FROMNUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
(SELECT MAX(JRNENTRY) AS TONUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
number NOT IN (SELECT DISTINCT JRNENTRY FROM GL20000)

Happy tracking…!

Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

See original article:
How to Track unused Journal Numbers

February 17, 2013 ·  · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 198

Surface Pro has been released

David Meego - Click for blog homepageOn 26th October 2012, the Surface RT was released (see post: Pre-order your Surface today) and following on from the success of the first tablet offering from Microsoft….

On 9th February 2013, the Surface Pro was released and is available for purchase. If you are not sure which model suits your needs, then download the Specifications Sheet (which has a comparison) and check out the link below:

For more details or to start your order go to www.surface.com or click on the image below.

Check out the new videos for the Surface Pro below:

Surface Pro Commercial “The Vibe”

(Please visit the site to view this video)

  

Surface Windows 8 Pro Overview

(Please visit the site to view this video)

 

I can’t wait for the Surface Pro to be available in Australia, so I can order a 128GB version with a Type Cover to compliment my Surface RT with Touch Cover.

David

PS: If you enjoyed The Surface Movement video, check out the Behind the Scenes video and the Extended Footage Video.

Read more here:
Surface Pro has been released

February 15, 2013 · David Musgrave · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 279

Document Attachment in Microsoft Dynamics GP 2013

I am a bit late on this topic. We already have many posts (including Feature of the Day – Inside Microsoft Dynamics GP) on this written by several community members. So instead of repeating it again, I am just adding pointers here to those posts.

To just brief what it is: Document Attachment is a new feature in Microsoft Dynamics GP 2013, which would enhance the way we currently deal with document attachments on a record. If you had sensed it right, yes it’s almost like OLE Notes, but much more than that.

This scores above OLE Notes, purely on grounds of three main factors:

  1. It doesn’t restrict itself to 32 bit architecture, like OLE notes container does.
  2. It is compatible with Web Client, but OLE notes is not.
  3. It stores the attachments on company specific SQL tables. You heard it right. It stores on database by harnessing SQL Server’s file stream, if I am not wrong.

More information about this (and how to use) could be read on below posts:

VAIDY

See original article:
Document Attachment in Microsoft Dynamics GP 2013

February 14, 2013 · Vaidyanathan Mohan · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 568

My LinkedIn Profile is in the top 5% most viewed for 2012

David Meego - Click for blog homepageYesterday I received an email from LinkedIn (the professional networking site) explaining that they were celebrating reaching over 200 million members.

The email congratulated me on having one of the top 5% most viewed profiles for 2012.

See the screenshot of the email below (click on the image to see the full letter):

I know that this is LinkedIn promoting itself, but to be in the top 5% of 200 million is pretty cool.

Only me and 9,999,999* other people received this email, it makes you feel special and unique.

 

Here is the link to my profile if you are a LinkedIn user and don’t yet have me added as a contact (click on the logo below):

David Musgrave on LinkedIn

Thanks for your support.

David

PS: Congrats to all those who got a Top 1%, 5% or 10% email message.  It means you and only 1,999,999 (for 1%), 9,999,999 (for 5%) or 19,999,999 (for 10%) other people also received the same email.

PPS: * Or if we want to get more technical: the figures should be you and only 1,999,999 (for 1%), 8,000,000 (for 2-5%) and 10,000,000 (for 6-10%) other people as the others received different percentage emails.

PPPS: Happy Valentines Day. :-)

View original post here:
My LinkedIn Profile is in the top 5% most viewed for 2012

February 14, 2013 · David Musgrave · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 214