RW – Showing the Full Item Description on Invoices Example


David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

When 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:


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:

I hope you find this helpful.

David

SOP Blank Options Invoice with Full Item Description.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

10 thoughts on “RW – Showing the Full Item Description on Invoices Example

  1. 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

    Like

  2. 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

    Like

  3. 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?

    Like

  4. 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

    Like

  5. 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

    Like

  6. Hello,

    I am attempting to modify our SOP Blank Invoice to properly display the Item Descrption fields in full. Currently it truncates even though the data entry field accepts more. Our current work around is to just add new line items to the invoice at $0 value and breaking up the text so it all displays. This is not ideal.

    I replaced the “sItemDescription” fields on the invoice in report writer with two new calculated fields with;

    Item Descr Line 1 – calculation = “RW_ParseString(SOP_LINE_WORK.Item Description 60 1)”
    Item Descr Line 2 – calculation = “RW_ParseString(SOP_LINE_WORK.Item Description 60 2)”

    I am immediately getting an error when trying to print the report to check it – “Type Mismatch symbol Item Descr Line 1”

    What could be the problem here in the formula?

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.