RW - Showing the Full Item Description on Invoices Example
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:
- Some reports do not have the width available for a 100 character description on a single line.
- 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.
- 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