Enhance Dynamics GP: Add Bin Number Visibility to Inventory Transaction Edits

Table of Contents

This guide provides a comprehensive walkthrough on how to modify the Inventory Transaction Edit List report within Microsoft Dynamics GP to display bin numbers. By enabling bin number visibility, especially in environments utilizing multiple inventory bins, businesses can significantly improve their inventory management accuracy and operational efficiency. This enhancement applies to various versions of Microsoft Dynamics GP, including 10.0, 9.0, and Microsoft Business Solutions - Great Plains 8.0.

The Importance of Bin Number Visibility in Inventory Management¶

Effective inventory control is a cornerstone of efficient operations for any business handling physical goods. When multiple bins are enabled in Microsoft Dynamics GP, inventory can be stored in various designated locations within a warehouse or storage facility. Without explicit bin number visibility on transaction reports, tracking the exact movement of items can become a complex and error-prone process.

Integrating bin numbers directly into the Inventory Transaction Edit List offers substantial benefits. It provides immediate clarity on where specific items were picked from or put away, reducing manual lookup times and potential discrepancies. This level of detail is crucial for improving picking accuracy, streamlining audit processes, and ultimately making more informed decisions regarding inventory placement and movement.

Understanding Microsoft Dynamics GP Report Writer¶

Microsoft Dynamics GP’s Report Writer is a powerful, built-in customization tool that allows users to modify existing reports or create entirely new ones. It provides a flexible environment to tailor reports to meet specific business requirements that might not be covered by standard functionalities. Mastering Report Writer enables organizations to adapt their reporting to evolving operational needs.

While incredibly useful, Report Writer requires careful handling. Any modifications made should always be tested thoroughly in a non-production environment before deployment to live systems. A solid understanding of database structures and report design principles is beneficial for successful customizations.

Prerequisites and Best Practices Before Customization¶

Before embarking on any report modification, it is paramount to adhere to best practices to ensure data integrity and prevent potential disruptions to your live system. Always perform customizations in a test environment first, mirroring your production data as closely as possible. This allows for thorough testing without risking operational data.

Ensure that the user performing the modifications has the necessary security permissions within Dynamics GP and Report Writer. It is also highly recommended to document all changes made, including the purpose of the modification, the steps taken, and any custom code or fields used. This documentation will be invaluable for future reference, troubleshooting, and during system upgrades.

Step 1: Backing Up Your Reports Dictionary¶

The Reports.dic file is a critical component within Microsoft Dynamics GP as it stores all modified reports. Before making any changes, creating a backup of this file is an absolute necessity. This precaution safeguards your existing customizations and provides a recovery point should any issues arise during the modification process. Without a proper backup, unintended errors could lead to the irreversible loss of valuable custom reports.

To locate your Reports.dic file, follow these detailed steps based on your Dynamics GP version. These steps guide you through the system’s launch file settings, which specify the exact path to your dictionary files. Once located, copy the Reports.dic file to a secure, separate location.

• In Microsoft Dynamics GP 10.0: Navigate to the Microsoft Dynamics GP menu, then point to Tools, then Setup, then System, and finally click Edit Launch File.
• In Microsoft Dynamics GP 9.0 and Microsoft Business Solutions - Great Plains 8.0: Navigate to the Tools menu, then point to Setup, then System, and click Edit Launch File.
• If prompted, enter the system password to proceed.
• Once the Edit Launch File window appears, click Microsoft Dynamics GP (for GP 10.0/9.0) or Great Plains (for 8.0).
• Carefully note the path displayed in the Reports box. This is the location of your Reports.dic file.
• Click OK to close the Edit Launch File window.

After identifying the location, navigate to that directory using your file explorer and create a copy of the Reports.dic file. Store this copy in a safe place.

Step 2: Accessing and Opening the Report for Modification¶

With your Reports.dic file securely backed up, the next step involves launching Report Writer and opening the specific report that requires modification. The “Inventory Adjustments Edit List” is the target report for this enhancement because it serves as a primary review document for inventory transactions, making it an ideal candidate for displaying bin information. Accessing the report in Report Writer allows you to manipulate its underlying data structure and visual layout.

Follow these instructions to open the report within the Report Writer environment:

1. To access Report Writer:
   • In Microsoft Dynamics GP 10.0: Go to the Microsoft Dynamics GP menu, point to Tools, then Customize, and click Report Writer.
   • In Microsoft Dynamics GP 9.0 and Microsoft Business Solutions - Great Plains 8.0: Go to the Tools menu, point to Customize, and click Report Writer.
2. Once Report Writer opens, select the appropriate product from the list:
   • For Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 9.0: Click Microsoft Dynamics GP.
   • For Microsoft Business Solutions - Great Plains 8.0: Click Great Plains. Then, click OK.
3. In the main Report Writer window, click Reports.
4. In the Original Reports area, locate and click Inventory Adjustments Edit List. Then, click Insert to move this report to the Modified Reports area. This action creates a copy of the original report for customization, preserving the original version.
5. In the Modified Reports area, select Inventory Adjustments Edit List, and then click Open. This will open the Report Definition window for your copied report.

Step 3: Establishing the Necessary Table Relationship¶

For the Inventory Transaction Edit List report to display bin numbers, it must be able to retrieve this data from the relevant database tables. This requires establishing a table relationship between the core inventory transaction line table and the table containing bin quantity information. In relational databases, these links are crucial for ensuring that data from different tables can be joined and presented cohesively. The relationship effectively tells the report how to connect a specific inventory transaction line to its corresponding bin quantity details.

The primary table in this scenario is IV_TRX_WORK_LINE, which holds the details of each inventory transaction line item. The secondary table, Inventory Transaction Bin Quantities Work, contains the specific bin number and quantity information. Linking these two tables ensures that when a transaction line is processed, the report can correctly pull its associated bin data. The key fields used for this linkage – IV Document Type, IV Document Number, and Line SEQ Number – are vital for establishing an accurate one-to-many relationship, where one transaction line can have multiple bin entries.

Here’s a conceptual diagram illustrating this relationship:

mermaid graph TD A[IV_TRX_WORK_LINE] -- IV Document Type --> B A[IV_TRX_WORK_LINE] -- IV Document Number --> B A[IV_TRX_WORK_LINE] -- Line SEQ Number --> B B[Inventory Transaction Bin Quantities Work]

To create this essential table relationship, follow these steps:

1. From the main Report Definition window, click Tables on the menu bar, and then click Tables.
2. In the Tables window, select IV_TRX_WORK_LINE, and then click Open.
3. In the Table Definition window for IV_TRX_WORK_LINE, click Relationships, and then click New.
4. Click the ellipsis button (…) next to the Secondary Table field. In the window that appears, locate and select Inventory Transaction Bin Quantities Work, then click OK.
5. In the Secondary Table Key list, click ivTrxBinWorkIdx_ID. Then, in the Primary Table list, match the fields by clicking IV Document Type, followed by IV Document Number, and finally Line SEQ Number. This crucial step creates the link that ensures the correct bin data is associated with each line item.
6. Click OK to save the new relationship, and then click Close in the Report Table Relationships window.

Step 4: Structuring the Report for Bin Data Integration¶

Once the table relationship is established, the report’s internal structure needs to be adjusted to utilize the newly available bin data. This involves removing any old, irrelevant data sources and adding the new source for bin information. Specifically, the Inventory Serial and Lot Number Work table, which might have been used for serial or lot tracking, is no longer needed for bin visibility and can be removed to streamline the report. Its replacement is the Inventory Transaction Bin Quantities Work table, which directly provides the bin numbers.

Additionally, a restriction is necessary to ensure that the report accurately filters and displays the correct bin quantities. Restrictions act as filters, controlling which data records are included in the report based on specified criteria. In this case, creating a restriction on the IV Document Number helps to ensure that only bin quantities associated with the currently processed inventory document are displayed, preventing data mismatches or extraneous bin entries from appearing on the report.

Here are the detailed steps to modify the report structure:

1. From the Report Definition window, click Open, and then click Tables.
2. In the Report Table Relationships window, locate Inventory Serial and Lot Number Work. Select it and then click Remove. Click OK when prompted to confirm the deletion.
3. Now, click New to add a new table. Select Inventory Transaction Bin Quantities Work from the list, and then click OK. This makes the bin data available to your report.
4. Click Close to exit the Report Table Relationships window.
5. Back in the Report Definition window, click Restrictions. Any existing “Serial” restriction should be removed. Select Serial (if present) and click Delete. Confirm deletion by clicking Yes.
6. Click New to create a new restriction. In the Restriction Name field, type Bin.
7. In the Report Table list, click Inventory Transaction Bin Quantities Work.
8. In the Table Fields list, click IV Document Number, and then click Add Field.
9. In the Operators area, click the equal sign (=).
10. Click Add Field again. The restriction expression should now resemble: ivTrxBinWork.IV Document Number = ivTrxBinWork.IV Document Number. This restriction ensures that the bin quantities are linked correctly to the corresponding document.
11. Click OK, and then close the Report Restrictions window.

Step 5: Refining the Report Layout and Calculated Fields¶

The final stage of modification involves adjusting the report’s visual layout and configuring a calculated field to display the bin numbers. The layout determines how information is presented on the printed report, including the placement of data fields and text labels. To maintain a clean and relevant report, it’s essential to remove outdated fields and replace them with the new bin-related information.

Calculated fields in Report Writer are extremely versatile; they can be configured to display data based on an expression, a database field, or a combination of both. By modifying an existing calculated field (like ‘Serial’) to instead show the ‘Bin’ number, we can efficiently repurpose an existing slot on the report layout. This approach often simplifies layout adjustments compared to adding entirely new fields and reorganizing the entire report section. Renaming text labels ensures that the report clearly communicates the new information being displayed.

Here are the steps to modify the report layout and a calculated field:

1. From the Report Definition window, click Layout. This will open the Report Layout window.
2. In the layout, navigate to the B (Body) section. Locate and delete the following fields:
   • Serial
   • Serial/Lot Qty
3. In the RH (Report Header) section of the layout, locate the text label Serial/Lot Number. Double-click it or select it and modify the text to Bin Number. This clearly labels the new column.
4. In the toolbox on the left, click Calculated Fields. Select the calculated field named Serial, and then click Open.
5. In the Calculated Field Definition window, click inside the Calculated Expression box, and then click Remove to clear the existing expression.
6. In the Resources list, click Inventory Transaction Bin Quantities Work.
7. In the Fields list, click Bin, and then click Add. The expression should now simply be Bin. Click OK to save the calculated field.
8. Back in the toolbox, click Inventory Transaction Bin Quantities Work to show its fields.
9. Drag the Bin field and the Qty field from the toolbox to the B section of the layout. Position them appropriately beneath the Bin Number header you created earlier, ensuring they align correctly with other detail fields. You may need to resize or reposition other fields to accommodate the new bin and quantity information.

Step 6: Saving Your Modifications¶

After making all necessary changes to the report’s structure and layout, it is crucial to save your work. Saving the modified report overwrites the previous version in your Reports.dic file, ensuring that your enhancements are preserved. Failing to save will result in the loss of all your customizations. Once saved, you can exit Report Writer and proceed to assign security permissions.

Follow these instructions to save the modified report:

1. Close the Report Layout window. When prompted to save your changes to the report, click Save.
2. In the Report Definition window, click OK.
3. To exit Report Writer, go to the File menu and choose the appropriate option:
   • In Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 9.0: Click Microsoft Dynamics GP.
   • In Microsoft Business Solutions - Great Plains 8.0: Click Microsoft Business Solutions - Great Plains.

Step 7: Granting Security Permissions to the Enhanced Report¶

The final step is to grant users access to the newly modified report. Even though the report has been customized and saved, users will not see the changes until their security settings are updated to point to the modified version instead of the original. Microsoft Dynamics GP offers different methods for assigning security, depending on the version and the complexity of your security setup. It’s essential to choose the method that aligns with your organization’s Dynamics GP environment.

Method 1: Utilizing Microsoft Dynamics GP Security (GP 10.0)¶

This method provides a direct and user-friendly way to manage security for alternate and modified forms and reports in Dynamics GP 10.0. It allows you to quickly assign the modified report to specific user IDs.

1. On the Microsoft Dynamics GP menu, point to Tools, then Setup, then System, and click Alternate/Modified Forms and Reports.
2. In the ID box, type the user ID of the user who requires access to this modified report.
3. In the Product list, click Microsoft Dynamics GP.
4. In the Type list, click Reports.
5. Expand the Inventory folder by clicking the plus sign (+).
6. Expand the Inventory Adjustments Edit List folder.
7. Click Microsoft Dynamics GP (Modified) to select your customized report.
8. Click Save to apply the security changes.

Method 2: Leveraging the Advanced Security Tool (GP 9.0 and Great Plains 8.0)¶

For more granular control over security, especially in environments with numerous users or companies, the Advanced Security tool in Dynamics GP 9.0 and Great Plains 8.0 is highly effective. This tool allows administrators to manage access permissions comprehensively.

1. On the Tools menu, point to Setup, then System, and click Advanced Security.
   • Note: If prompted, type the system password.
2. Click View, and then click by Alternate, Modified and Custom.
3. Expand the appropriate product node:
   • In Microsoft Dynamics GP 9.0: Expand Microsoft Dynamics GP.
   • In Microsoft Business Solutions - Great Plains 8.0: Expand Great Plains.
4. Expand the following nodes in order:
   • Reports
   • Inventory
   • Inventory Adjustments Edit List
5. Select the modified report:
   • In Microsoft Dynamics GP 9.0: Click Microsoft Dynamics GP (Modified).
   • In Microsoft Business Solutions - Great Plains 8.0: Click Great Plains (Modified).
6. Click Apply, and then click OK.
   • Note: By default, when you start the Advanced Security tool, the current user and company are selected. You can select other users in the User area and more companies in the Company area of the Advanced Security window if needed.

Method 3: Standard Microsoft Dynamics GP Security (GP 9.0 and Great Plains 8.0)¶

This is a simpler, traditional method for assigning security permissions to a modified report for individual users in earlier versions of Dynamics GP.

1. On the Tools menu, point to Setup, then System, and click Security.
   • Note: If prompted, type the system password.
2. In the User ID list, click the user ID of the user who will access the report.
3. In the Type list, click Modified Reports.
4. In the Series list, click Inventory.
5. In the Access List box, double-click Inventory Adjustments Edit List. After you double-click, an asterisk (*) will appear next to the report name, indicating that it has been assigned.
6. Click OK to save the security changes.

Benefits of Enhanced Bin Visibility¶

Implementing this customization provides tangible benefits across various aspects of inventory management:

• Improved Accuracy: Direct visibility of bin locations on transaction lists significantly reduces errors during picking, putting away, and cycle counting processes. This leads to more reliable inventory records.
• Faster Operations: Warehouse personnel can quickly identify specific bin locations for items, which streamlines fulfillment, receiving, and internal movement tasks, ultimately increasing overall operational speed and efficiency.
• Enhanced Auditing and Compliance: A clear, comprehensive audit trail that includes bin numbers for every transaction provides better accountability and simplifies compliance with regulatory requirements or internal audit procedures.
• Better Inventory Control and Optimization: With granular insight into where every item is stored, businesses can make more strategic decisions about inventory placement, optimize storage space, and improve inventory turnover.

Troubleshooting and Further Considerations¶

While this guide aims to provide clear instructions, some common issues or considerations might arise during and after implementation:

• Report Not Appearing for Users: If users cannot see the modified report, double-check that security permissions have been correctly assigned using one of the methods described in Step 7. Ensure the correct user ID and modified report version are selected.
• Incorrect Data Display: If the bin numbers or quantities appear incorrectly, revisit Step 3 (Table Relationships) and Step 4 (Report Restrictions). A subtle error in linking tables or defining the restriction can lead to data mismatches.
• Performance Impacts: Custom reports, especially those involving complex table joins, can sometimes affect report generation performance. Monitor report generation times and, if necessary, consult with a Dynamics GP developer for optimization strategies.
• Upgrades and Future Maintenance: Be aware that modifications made directly to Reports.dic might need to be reapplied or re-verified after major Microsoft Dynamics GP upgrades. Keep detailed documentation of your changes to facilitate this process.

Concluding Thoughts¶

Enhancing the Inventory Transaction Edit List with bin number visibility is a straightforward yet impactful customization that can significantly improve your inventory operations within Microsoft Dynamics GP. By following these steps, you can provide your team with better tools for accurate tracking and management, leading to greater efficiency and data reliability. This modification empowers businesses to maintain tighter control over their inventory, which is crucial for operational success.

We hope this detailed guide helps you enhance your Dynamics GP environment. Do you have any tips or challenges you’ve faced with Report Writer customizations? Share your experiences in the comments below!