TechKnowledge Content
Issue
How to add the Inventory transaction item number to the Detail Trial Balance Report?
Resolution
To add the item number to the Detail Trial Balance Report, follow the steps below:
Go into Report Writer (Tools | Customize | Report Writer)
1. Opening the Report
Click on the Reports icon from the top menu bar.
From the Original Reports side, highlight the Trial Balance Detail and choose insert. Then on the Modified Reports side, highlight the same report and choose the Open button.
2. Linking
Choose the Tables icon from the top menu bar.
In the Tables window, highlight GL_YTD_TRX_OPEN and choose the Open button. In the Table Definition window choose the Relationships button. Then choose the New button.
New:
Click on the Ellipse button to the left of the Secondary Table line. (The button with three periods) Select the Inventory Transaction History and click OK. For the Secondary Table Key select: Key 1 Match the appropriate fields:
Primary Table: Secondary Table:
Originating TRX Source TRX Source
Originating Type IV Document Type
Originating Document Number Document Number
Choose OK.
Close the Table Relationships window by clicking on the X in the upper right corner of that window. Click OK. Close the Tables window by clicking on the X in the upper right corner of that window.
3. Tables
In the Report Table Relationships window, highlight the Year To Date Transaction Open and choose the New button. Highlight the Inventory Transaction History and choose the OK button.
Highlight the Inventory Transaction History and choose the New button. Highlight the Inventory Transaction Amounts History and choose the OK button.
Select the Close button.
4. Restrictions
A dummy restriction must be made to accommodate the new table linking. We will set a field from the most nested table equal to itself. Select the Restrictions button.
Select New.
Name the restriction Dummy.
In the fields section, select the Inventory Transaction Amounts History as the Report Table and Customer Number as the field. Add this to the Expression.
Select = from the Operators. Again add Customer Number from the Inventory Transaction Amounts History to the expression. The expression should read:
IV_TRX_HIST_LINE.CustomerNumber=IV_TRX_HIST_LINE.CustomerNumber
Select OK to close the Restrictions window.
5. Sort
From the Table Definition window, select the Sort button. Select Year-to-Date Transaction Open as the Report Table. Highlight Journal Entry in the Table Field list and Insert. Select OK to close the Sorting Definition window.
6. Additional Header
In the report layout, select Tools, Section Options to open the Report Section Options window. Under Additional Headers, highlight Account Number. Select New.
Name the Header 'Journal Entry.' Select Year-to-date Transaction Open as the Print When Field Changes Report Table. Highlight Journal Entry in the Field List.
Select OK to close the Header Options and Report Section Options windows.
7. Layout
Drag all of the fields from the Body section of the report into your newly created H1 section.
Drag the Item Number from the Inventory Transaction Amounts History into the body section of the report.
8. Close the Report
Close the report and save changes to the report. Remember to grant security to the modified report (Setup | System | Security).
This article was TechKnowledge Document ID: 27655
DISCLAIMER
MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, THE RELIABILITY OR THE ACCURACY OF THE INFORMATION THAT IS CONTAINED IN THE DOCUMENTS AND THE RELATED GRAPHICS PUBLISHED ON THIS WEB SITE (THE “MATERIALS”) FOR ANY PURPOSE.
THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON-INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIA
