Share via

RW - Showing the Full Item Description on Invoices Example

  • Article

David MeegoWhen the inventory module of Microsoft Dynamics GP was first written the Item Description field was 60 characters long.  A few versions ago it was extended to 100 characters.  While this allows for a longer description to be entered, it has created three issues:

  1. Some reports do not have the width available for a 100 character description on a single line.
  2. Some reports use a calculated field to print either the Item Description or the Customer Item Description. As string calculated fields are limited to 80 characters, the remaining 20 characters are truncated.
  3. Some reports use temporary tables where the generic field used for storing the Item Description is less than 100 characters and so the description is truncated.

Below are two methods of getting the full description displayed on a report, using two or more calculated fields.

The first method uses Visual Basic for Applications and can be used when it is not possible to link the Item Master table to the report.  The SOP Blank Options Invoice Form uses the Sales Document Temp (SOP_Document_TEMP) temporary table and does not have any fields which can be used to link to the Item Master (IV_Item_MSTR) table or the Sales Transaction Amounts Work (SOP_LINE_WORK) table.

The code for this example uses ActiveX Data Objects (ADO) to access the SQL data.  Because there are no fields in the temporary table which can allow use to link to the Sales Transaction Amounts Work, we are going to use a special type of query.  This query does not link directly to a line in the SOP document, but instead obtains the first line of the document which has a higher Line Item Sequence than the previously retrieved line.  This allows us to move through the lines of the document in SQL at the same time as we move through the lines in the report, even though we can't create a one-to-one relationship.

Once we have the correct line, we can retrieve the Item Description.  However, as calculated fields are limited to 80 characters we cannot put the entire 100 character Item Description into a single field.  So the example also includes code to break the description into two fields, looking for a space so that it does not split a word in half.

Example code for v8.0, v9.0 & v10.0 is attached at the bottom of the article.

Please see the "Installation Instructions.txt" file in each version's archive for more information.

For more information on the techniques used, please see these posts:

Using VBA with Report Writer

Using ADO with VBA with Report Writer

If you are able to create the relationship to the Sales Transaction Amounts Work (SOP_LINE_WORK) table or Item Master (IV_Item_MSTR) table, then you can use Report Writer user defined functions to take the Item Number Field and break it up into two or more fields. Note that you would want to use the description from the transaction lines, so that manually edited descriptions and non-inventory items work correctly.

Just create a set of calculated fields using the RW_ParseString() function:

  • RW_ParseString(SOP_LINE_WORK.Item Description  60  1)
  • RW_ParseString(SOP_LINE_WORK.Item Description  60  2)
  • RW_ParseString(SOP_LINE_WORK.Item Description  60  3)

If you can't get to the transaction level description, you can always use the one from the Item Master table:

  • RW_ParseString(IV_Item_MSTR.Item Description  60  1)
  • RW_ParseString(IV_Item_MSTR.Item Description  60  2)
  • RW_ParseString(IV_Item_MSTR.Item Description  60  3)

Note: I have created 3 fields to be sure we get all of the description.  The RW_ParseString() function will work backwards from the length specified to find a space to break on.  If there are long words and not many spaces, it might require the third field to show the entire description. If two fields work for you system, then only create two.

For more information on using Report Writer Functions and what functions are available please see the following post:

Using the built-in Report Writer Functions

I hope you find this helpful.

David

SOP Blank Options Invoice with Full Item Description.zip

Comments

  • Anonymous
    June 02, 2009
    I'm catching up on a bunch of posts after a terrific, disconnected weekend. First up is David Musgrave's

  • Anonymous
    June 03, 2009
    Dear David, I found one bug in GP 10.0 VBA. In VBA, SHIFT + F11 is the short cut key to add fields in to VBA. But when I press the same key combination it won't add. All GP forms is having menu, so we can simply add the same by selecting menu option. But consider the Login Screen or Company Information window, we won't able to do the same. For example if I need to add OK button of Login Screen how can I add the same field into VBA ? Can you please provide a good solution ? With Kind Regards, Sajeesh K A

  • Anonymous
    June 09, 2009
    Hi Sajeesh This is a known issue caused by having the Dexterity Debug menu active at runtime mode.  It has a shortcut which uses the same key combination and takes precedence. Use the Support Debugging Tool Dex.ini Settings window to turn this off or edit the ScriptDebugger setting in the dex.ini manually. It is not going to be fixed as the Debug menu should not be active in an end user's system and so the issue would not normally occur. David

  • Anonymous
    December 28, 2011
    Thanks for the guide.  When we print the SOP options invoice (either from the standard report or word templates) using method 1, we get blank lines printing for each field (Item Description 1, Item Description 2, Field 12).  Is there a way to suppress these blank lines - particularly in word templates?

  • Anonymous
    December 28, 2011
    Hi Jason No, there is no way to supress the blank lines on the Report Writer report if the additional lines are not used. You might be able to something with the Word Template, but I am not sure. David

  • Anonymous
    June 11, 2014
    Hi David, I my scenario,In the customization report i get the string value from the extender table (Table Name: EXT00101 and field name: STRGA255) but there is no relationship between report and extender table. How can i use the RW_ParseString(). My string value is approximately more than 100. Thanks, Pragadees

  • Anonymous
    January 17, 2017
    The comment has been removed

    • Anonymous
      January 17, 2017
      Hi AdamPlease check your Result Type in the calculated fields and make sure it is set to String.David
      • Anonymous
        January 24, 2017
        Yes that worked. Thank you!