Adding more comment lines to POP Purchase Orders

Just recently, I was asked by a customer to address an issue with their line item comments truncating at 4 lines. In essence, the customer wanted the ability to print more than 4 lines of comments at the line item level on their purchase orders.

My customer happens to be in the Nuclear Waste Management industry, and as it turns out, they have strict requirements to provide contract information, start/end dates, and a number of regulatory and compliance information on purchase orders. Some of this information is very critical to the transportation of certain products on United States highways. So as you can imagine, the request for additional comment lines was critical.

The following shows an example of what the customer was facing:

Purchasing Comment Entry window and POP Purchase Order Blank form


As you can see, the comments are printing only 4 lines and truncating the rest of the text.

Before going into the details, they are a few articles that guided me through the solution:

How to Display More Than Four Line Item Comments in the "SOP Blank Invoice Form" Report in Sales Order Processing in Microsoft Dynamics GP

Adding more comments to SOP Documents (Developing for Dynamics GP)

Of course, these articles refer to SOP, but the underlying techniques used in them will allow us to make the necessary changes to the report. This blog post should be used in conjunction with the How To article above to provide some additional steps to get the best result for making the changes to the POP Purchase Order report.

Background

Comments for Purchase Order Processing (POP) transactions are entered using a text field which allows for a maximum of 500 characters, as shown above. When the comments are saved, the text field is divided into 4 string fields of 50 characters each, which is printed on the report. The code that parses the text field into the 4 string fields is smart enough to avoid splitting a word oddly.  The code will fit as many words as it can up to the last space before the 50 character mark or up to a carriage return (new line). When the POP documents are printed, it is these 4 comment string fields which are printed on the report. This means that if you use carriage returns or type a comment longer than 180-200 characters, you are unlikely to see all of your comment text shown on the report.

If you look at a POP document report, such as the POP Purchase Order Blank Form, you will see that the 4 comment lines are printed using 4 additional footer sections.

POP Purchase Order Blank Form Report Writer Layout showing 4 footers (F3 - F6)


Looking at each of these additional sections (Tools > Section Options), you will see that each one is configured to be Suppressed when the appropriate 'sSuppressCommentX' calculated field is empty. The 'sSuppressCommentX' calculated field is defined to look at the comment string array report field and return an empty string or the actual comment to be printed on that footer. The reason for all this complexity is so that the space for the comment line strings is only taken up when there is actually a comment line to display. If this technique with additional footers with conditional suppression was not used, there would be 4 lines between every POP line item whether it was used or not - not an efficient use of already cramped space.

Solution

The solution is to use the RW_POPLINECommentText user-defined Report Writer function in the system series in a set of calculated fields.  The same recommendations apply as with the SOP article above, this is, making each line the maximum 80 characters supported by Report Writer strings and creating 8 lines. This gives the potential for 640 characters and so should easily be able to display the 500 characters of comment text (even without breaking words in half or additional carriage returns).

Why 8 lines? This is so we can place two lines on each of the 4 additional footer sections. If we update the suppression logic correctly, it will mean there will only be the possibility of one blank line between SOP line items. With only 4 section breaks, we can only print 0, 2, 4, 6 or 8 lines at a time, so if there are actually an odd number of lines to be printed, there will be one blank line printed. If there are an even number of lines, then there will be no blank line.

For an explanation on why not using additional footers instead, see Adding more comments to SOP Documents over at Developing for Dynamics GP.

The parameters for the RW_POPLINECommentText function are as follows:

RW_POPLINECommentText()
function returns string OUT_string;
in integer IN_Type;   { POP Type of Document }
in string IN_Number;   { POP Number of Document }
in long IN_Ord;    { Ord Number of Line }
in integer IN_characters;  { Number of Characters per Line }
in integer IN_line;   { Line Number to Return }

Implementation

The steps below assume some knowledge of Report Writer. If you need detailed step by step instructions please look at the How to document.

Line Comments

1. Create 8 calculated fields of result type string for the 8 Line level comment lines:

(C) Line 1 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number popPOLineRollupTemp.Ord 80 1 )

(C) Line 2 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 2 )

(C) Line 3 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 3 )

(C) Line 4 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 4 )

(C) Line 5 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 5 )

(C) Line 6 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 6 )

(C) Line 7 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 7 )

(C) Line 8 Comments = FUNCTION_SCRIPT( RW_POPLINECommentText POP_PO.PO Type POP_PO.PO Number  popPOLineRollupTemp.Ord 80 8 )


Note: The only difference for each calculated field is the line number in the field name and the line number constant as the last parameter.

2. Expand each of the comment additional footers to 2 lines height.

3. Move the 4 existing Comment fields to the right of the report.

4. Double Click on each of the 4 old Comment array fields and set the Visibility to Invisible.

Comment Footers and Invisible Fields
5. From Calculated Fields drag out the 8 new calculated fields placing two fields in each section. Drag out the field width as desired (at least 370).

Calculated fields on footer sections

6. The 4 sSuppressCommentX calculated fields will need to be changed to conditional fields and the return type changed to Integer, as shown:

Calculated Field definition

sSuppressComment1 = F6_LAST (C) Line 1 Comments = "" AND  F6_LAST (C) Line 2 Comments  = ""

sSuppressComment2 = F5_LAST (C) Line 3 Comments = "" AND  F5_LAST (C) Line 4 Comments = ""

sSuppressComment3 = F4_LAST (C) Line 5 Comments = "" AND  F4_LAST (C) Line 6 Comments = ""

sSuppressComment4 = F3_LAST (C) Line 7 Comments = "" AND  F3_LAST (C) Line 8 Comments = ""

After all these changes, it's time to save the report and test:

Purchase Order Blank Form
Now we can see the full line item comment displays nicely on the report.

Downloads

You can download the package file for this customization here.


POPBlankFormExtendedNotes.zip


Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Comments

Ian Grieve said…
Hi Mariano,

Great article. I've always looked to VBA for this sort of thing but it's nice to get a guide on how to do it with Report Writer functions.

One thing; your download link 404s.

Ian
Mariano Gomez said…
@Ian:

Much thanks for the kind words. If you click on the image you will get 404. If you click on the caption under the image, it will allow you to download the file... go figure.

MG.-
Mariano Gomez, MVP
Anonymous said…
Mariano:

Great Article - One question. I was doing some testing on this and using both "Standard" and "Drop-Ship" type PO Documents and this was only working for "Standard" types. After digging into the data I realized that the DOCTYPE field in the POP10550 table was not matching the DOCTPYE field in the POP10110 table. The POP10550 always had a DOCTYPE of "1". I edited the various Line Comment calculation fields and hard-coded the "POP_PO.PO Type" to an integer of 1 and that made it work for both Standard and Drop-Ship type documents. Anyone else see this happening?
jdinflatables said…
I was looking for the same info about virtually identical & finally i got my answer from your post thanks for sharing this useful info.!
point of purchase displays
Anonymous said…
Awesome. Thanks a bunch
Anonymous said…
Does this work for GP 2013, thx
Mariano Gomez said…
I'm pretty confident it does.

MG.-
Unknown said…
Mariano,

Thanks for posting this, it helped me solve an issue for a client that required a larger comment area per line.

Scott
Kelli Uphaus said…
This worked great for us in 2013. I also had the same problem with drop ship POs, but changed POP_PO.PO Type to 1 and that solved our issue.
Unknown said…
This worked great for us until we attempted to move the PO to a template. Now we are getting all kinds of extra white space that we have been unable to resolve. Does anyone have any hints for getting rid of the extra white space while leaving the extra line comments in the template??
Unknown said…
Hi Mariano,
How does this set up translate over into a Word Template? I've tried it but what happens is, for example, when there are 8 comment lines only Line 1, 3, 5 and 7 display on the Word Template. Any thoughts?

Alex Smyth
FMT Consultants, LLC
arun said…
hi

if i am typing 4 lines in the comment id .its takes first two line (3 rd and 4 th lines are not displaying).when we type 5 line in the comment id .all the lines are displaying .any one come across this . pls help.
Paul said…
I'm trying to fix truncated comments in a Word template Purchase Order. This is the kind of article that makes me dread each and every time I'm asked to make a change to the GP reports/templates.

Never mind that I can't make any changes while the system is in use for fear of corrupting the reports database, even minor changes require incredibly arcane tomfoolery such as described in this page. A fine article with very useful information, don't get me wrong, but yikes, what a nightmare of a system.

Every time I do something with GP it's like I'm transported back to 1998. I still can't believe this is a real product.
CSMEUTAH said…
Awesome. Could I have hacked it on my own from the SOP example? Yes. Did I have an extra 3 days to mess with it? No.
Thanks!
Anonymous said…
Has anyone been able to get this to work with the Word Templates? I am only getting line 1, 3, 5 and 7 displaying in the template.
Unknown said…
Hello Mariano,
This is a great post. Both you and David have thoroughly covered this topic. I have already been able to deploy this at several customers and internally as well to help with the invoicing.
I am currently working on a Project and we are creating an SSRS report that we would like to be able to pull these extra Calculated fields onto. I am trying to find where SQL stores these newly created Calculated fields from Report Writer. I have been able to find the big Text field CMMTTEXT in the SY04200 and the SOP10202 tables and am wondering IF SQL actaully stores these new fields in separate fields within a table.
Do you have any suggestions on where I may find these or grab them to appear on an SSRS report?

Thank you for your time.
David Sirois
Mariano Gomez said…
David,

Thanks for the kind words. Unfortunately, these fields are only Report Writer calculated fields and are not stored in a table. Technically (and literally) speaking, the fields are stored in the Reports.dic dictionary file, but not the data. In SSRS, you supposed to be able to pull the actual comment field and set the field property to word wrap.

Here's a good example on how to do that:

http://www.sqlrelease.com/text-wrapping-in-ssrs-reports-in-reportviewer-control
Unknown said…
Mariano,

This article has been extremely helpful. I'm trying to increase the character count in the Purchasing Line Comments. I'm a newbie to Report Writer. Your "How to" Link sends me to SOP which is not relevant for me. So I've a few questions as follows:

1) Am I supposed to be using the Report Table: Purchasing Comment?
2) How do I create a calculated field?
3) Do I click New in the Header or the Footer of the Report Section Options Window
Unknown said…
Mariano,

I forgot to mention that I'm using GP2016R2.
I have a third question, I'm assuming that I need to check the checkbox, "suppress when field is empty" But when I do so, I'm prompted to select a calculated field. And there's where I nee d your assistance to create a calculated field.
Mariano Gomez said…
Maria,
I must first direct you to the Report Writer manual, Chapter 7: Creating Calculated Fields for more information as I cannot possibly provide all the steps here.

Also, I am not sure what you mean by "increasing he character count". Report Writer is limited to 80 characters for string and string calculated fields.

I believe your "Suppress when field is empty" will depend on the calculated field first. Please read the chapter on creating calculated fields and if you have further questions I will be happy to assist.

Popular posts from this blog

Power Apps - Application Monitoring with Azure Application Insights

DBMS: 12 Microsoft Dynamics GP: 0 error when updating to Microsoft Dynamics GP 2013 R2

eConnect Integration Service for Microsoft Dynamics GP 2010