Streamline Dynamics GP: Add Document Numbers and Due Dates for Enhanced Tracking

Table of Contents

Managing financial operations effectively is paramount for any business leveraging enterprise resource planning (ERP) systems like Microsoft Dynamics GP. A critical aspect of financial management involves meticulous tracking and reconciliation of payment transactions. This article provides a comprehensive guide on how to augment the Computer Checks Posting Journal report in Microsoft Dynamics GP by integrating vital information: the document number and the document due date directly from invoices. This enhancement significantly improves the traceability and auditability of your payable processes, ensuring a more streamlined and transparent financial overview.

Understanding the Importance of Report Customization in Dynamics GP¶

The Computer Checks Posting Journal report serves as a crucial record of all computer-generated check payments made within Microsoft Dynamics GP. By default, this report provides essential details related to the checks issued. However, for enhanced financial scrutiny and operational efficiency, it often becomes necessary to include additional data points that directly link payments to their original invoices, such as the unique document number and the invoice’s due date. This customization allows for quicker reconciliation, improved audit trails, and a clearer understanding of payment timing relative to obligations.

Customizing reports in Dynamics GP is a powerful capability that allows businesses to tailor their ERP system to specific operational needs. The Report Writer tool is the primary interface for these modifications, enabling users to adjust layouts, integrate data from various tables, and define specific restrictions to present information precisely as required. The following steps detail a practical application of this customization capability, focusing on a widely beneficial enhancement for accounts payable processes.

Step 1: Initiate Microsoft Dynamics GP Report Writer¶

The first step in modifying any report within Microsoft Dynamics GP is to access the Report Writer tool. This environment is where all report customizations are performed, from simple layout adjustments to complex data integrations. It’s crucial to ensure you have the appropriate security permissions to access and modify reports within your Dynamics GP instance.

For users on Microsoft Dynamics GP 10.0 and later versions:
Navigate to the Microsoft Dynamics GP menu, point to Tools, then Customize, and finally select Report Writer. A prompt will appear asking you to select the product; here, you must choose Microsoft Dynamics GP from the list.

For users on Microsoft Dynamics GP 9.0 and Microsoft Business Solutions - Great Plains 8.0:
Access the Tools menu, point to Customize, and then select Report Writer. When prompted to select the product, choose Great Plains from the available options. Selecting the correct product ensures that you are working within the appropriate set of reports and tables for your specific Dynamics GP installation.

Upon successful entry into Report Writer, you will be presented with a workspace that allows you to manage and modify both original and modified reports. This initial setup is critical for all subsequent steps in the customization process.

Step 2: Establish Necessary Table Relationships¶

To include the document number and due date from invoices on the Computer Checks Posting Journal report, you must first ensure that the report has access to the relevant data tables. The original report typically draws from certain temporary files and payment work tables, but often doesn’t directly link to the specific invoice details needed. Therefore, modifying table relationships is a crucial intermediate step.

1. From the Report Writer main toolbar, locate and select the Reports button. This action will open the Report Writer window, which lists all available reports.
2. Within the Original Reports list, scroll to find and select the Computer Check Posting Journal report. Once highlighted, click the Insert button to move it into the Modified Reports list. This creates a working copy of the report, preserving the original report’s integrity.
3. Now, in the Modified Reports list, select the newly inserted Computer Check Posting Journal report, and then click Open. This will bring up the Report Definition window, which provides an overview of the selected report’s properties.
4. In the Report Definition window, locate and select the Tables button. This action opens the Report Table Relationships window, where you can view and manage the data tables currently linked to your report.
5. Within the Report Table Relationships window, identify and highlight the table named Payables Journal Distributions Temporary File. Since this table may not contain the specific invoice-level detail needed, it is often necessary to remove it. Select Remove.
6. A confirmation message will appear: “Are you sure you want to remove this table and its related tables? Remove all corresponding tables.” Select OK to proceed. This step ensures that old, potentially irrelevant links are cleared before new, precise relationships are established.
7. Next, in the Report Table Relationships window, highlight the existing table PM Payment WORK*. This table is foundational for payment processing. To establish the connection to invoice details, select New.
8. The Related Tables window will open. Here, you need to find and highlight PM Payment Apply To Work File*. This table is crucial as it contains the application records that link payments to their corresponding invoices, thereby holding the document number and due date information you wish to include. Select OK to establish this new relationship and close the window.
9. Finally, with the new table relationship established, select Close in the Report Table Relationships window. This completes the critical step of linking the necessary data sources to your modified report, laying the groundwork for accessing the desired invoice details.

This intricate process of modifying table relationships is fundamental to pulling specific data into a report. By linking the PM Payment Apply To Work File to the PM Payment WORK table, we create a pathway to access detailed invoice application information that was not originally available on the standard Computer Checks Posting Journal.

Step 3: Implement the Report Restriction for Accurate Data Display¶

After establishing the necessary table relationships, the next crucial step is to create a report restriction. A restriction acts as a filter, ensuring that the data pulled from the newly linked tables is relevant and accurately displayed in the context of each payment. Without a proper restriction, the report might display incorrect or unrelated invoice information.

1. From the Report Definition window, select the Restrictions button. This opens the Report Restrictions window, which lists any existing filters applied to the report.
2. In the Report Restrictions window, you might find a dummy restriction, such as Dummy4. If present, highlight this restriction and select Delete. This is to remove any placeholders that could interfere with your new, specific restriction.
3. Confirm the deletion by selecting Yes when prompted with the message: “Do you want to delete this restriction?”
4. Now, to create your new restriction, select the New button in the Report Restrictions window. This will open the Report Restriction Definition window.
5. In the Restriction Name field, type a descriptive name for your restriction. Using a unique identifier, such as 912953 (referencing the original knowledge base article number) or ApplyToVoucherRestriction, is a good practice for future reference.
6. For the Report Table field, select PM Payment Apply To Work File. This indicates that the restriction will primarily operate on data from this table, which holds the critical invoice application details.
7. From the Table Fields list for the selected report table, choose Apply To Voucher Number. This field represents the specific voucher number of the invoice that a payment is applied to.
8. Select Add Field. This adds the Apply to Voucher Number field to the expression area.
9. In the Operators box, select the equal sign (=). This sets up a comparison operator, ensuring that we are looking for exact matches.
10. Again, in the Fields box, select Add Field. This will complete the restriction expression, which should now read as follows:
    PM_Payment_Apply_WORK.Apply to Voucher Number = PM_Payment_Apply_WORK.Apply to Voucher Number
    This restriction, while seemingly self-referential, is a common technique in Dynamics GP Report Writer when you need to ensure that the report processes each instance of the Apply to Voucher Number from the PM Payment Apply To Work File table correctly, effectively creating a join based on the application voucher itself for the purpose of the report’s processing logic. It ensures that for every payment record, the corresponding Apply to Voucher Number from the application file is considered in the context of that payment.
11. Select OK to close the Report Restriction Definition window, saving your new restriction.
12. Finally, close the Report Restrictions window. This critical restriction ensures that the report correctly associates each payment with its corresponding invoice details, preventing data discrepancies.

Step 4: Customize the Report Layout¶

With the table relationships and restrictions correctly configured, the next logical step is to modify the visual layout of the Computer Checks Posting Journal report. This involves removing placeholder fields and strategically adding the new fields that will display the invoice document number and due date. This step transforms the report to present the desired information clearly and effectively.

1. Return to the Report Definition window and select the Layout button. This action opens the Report Layout window, which is a graphical interface allowing you to design the visual presentation of your report.
2. Within the body (B) section of the report layout, identify and delete the following existing placeholder fields. These fields are typically generic data points that may not be necessary once the more specific invoice details are added, or they occupy space that can be better utilized:
   • Field9
   • Field2
   • Field3
   • Field4
   • Field5
     Deleting these fields creates valuable space to neatly arrange the new information.
3. Open the Toolbox window, which typically floats over the Report Layout window. From the dropdown list at the top of the Toolbox, select the PM Payment Apply To Work file table. This action populates the Toolbox with all the available fields from this table.

4. From the Toolbox window, drag the following essential fields from the PM Payment Apply To Work file table directly into the body (B) section of the report layout. Place them where they will best enhance the report’s readability and utility:

   • Apply To Voucher Number: This field is crucial for linking the payment to the specific invoice voucher.
   • Document Number: This is the unique identifier of the original invoice.
   • Document Due Date: This indicates when the original invoice was due for payment.
   • Amount Paid: While potentially already present in some form, explicitly adding this from the application file ensures consistency with the newly linked data.

   Note: It is highly likely that you will need to carefully rearrange, resize, or even remove some other existing fields in the body (B) section to create adequate space for these new fields. The goal is to achieve a clean and organized presentation of all information. Take time to align fields properly to maintain a professional appearance for the report.

5. Once you are satisfied with the arrangement and inclusion of the new fields, close the Report Layout window. When prompted, select Save to confirm and preserve the changes you’ve made to the report’s visual design. This step ensures that your modified layout is retained for future use.

Step 5: Save All Changes and Exit Report Writer¶

After meticulously modifying the report’s table relationships, implementing the restriction, and customizing its layout, the final step in the Report Writer environment is to save all your work and then safely exit the tool. This ensures that your modifications are permanently applied to the modified report.

1. From the File menu within Report Writer, select Microsoft Dynamics GP. This action initiates the process of returning to the main Dynamics GP application.
2. You will typically be prompted to save changes. Select Save when you are asked to save the modifications made to the report layout. This confirms that the visual adjustments you made in Step 4 are stored.
3. A second prompt may appear, asking you to save the changes to the modified report itself (referring to the overall report definition, including table relationships and restrictions). Select Save again to ensure all aspects of your modification are preserved.
4. Once all saving prompts are addressed, Report Writer will close, and you will be returned to the Microsoft Dynamics GP main interface. Your modified report is now saved and ready for the next crucial step: assigning security permissions.

Step 6: Assign Security Permissions to the Modified Report¶

Even after successfully modifying and saving a report, it will not be accessible to users until appropriate security permissions are assigned. This step is vital to control which users or user groups can view and print the customized Computer Checks Posting Journal. The method for assigning security varies slightly depending on your version of Microsoft Dynamics GP.

Method 1: Utilizing Security in Microsoft Dynamics GP 10.0 and Later¶

For users on Microsoft Dynamics GP 10.0 and subsequent versions, the Alternate/Modified Forms and Reports window is the primary tool for managing access to customized reports.

1. Navigate to the Microsoft Dynamics GP menu, point to Tools, then Setup, then System, and finally select Alternate/Modified Forms and Reports.
2. In the ID box, type or select the specific Alternate/Modified Forms and Reports ID that is associated with the user ID or user group intended to print this modified report. This ID acts as a profile for security settings.
3. In the Product list, select Microsoft Dynamics GP.
4. In the Type list, select Reports. This filters the list to show only report-related security options.
5. Expand the Purchasing folder, as the Computer Checks Posting Journal falls under this module.
6. Further expand the folder for the specific report that you modified (e.g., Computer Check Posting Journal).
7. Within that report’s folder, select Microsoft Dynamics GP (Modified). This directs the system to use your customized version instead of the original.
8. Select Save to apply these changes to the selected Alternate/Modified Forms and Reports ID.
9. Next, to link this security ID to specific users, go to the Microsoft Dynamics GP menu, point to Tools, then Setup, then System, and then select User Security.
10. In the User list, select the specific user ID that needs access to the modified report.
11. In the Company list, select the company for which the user will be accessing this report.
12. In the Alternate/Modified Forms and Reports ID list, select the ID that you configured in step 2. This links the user to the security profile containing your modified report.

Method 2: Employing the Advanced Security Tool (Pre-Microsoft Dynamics GP 10.0)¶

For earlier versions of Dynamics GP (prior to 10.0), the Advanced Security tool is used to grant permissions to modified reports.

1. From the Tools menu, point to Setup, then System, and then select Advanced Security.
2. If prompted, enter the system password in the Please Enter Password box and select OK to gain access.
3. In the Advanced Security window, select View, and then choose By Alternate, Modified and Custom. This view helps you easily locate modified components.
4. Use the appropriate step for your version:
   • In Microsoft Dynamics GP 9.0, expand Microsoft Dynamics GP.
   • In Microsoft Business Solutions - Great Plains 8.0, expand Great Plains.
5. Expand Reports, then expand Purchasing, and then expand the specific report that you modified (e.g., Computer Check Posting Journal).
6. Again, use the appropriate step for your version:
   • In Microsoft Dynamics GP 9.0, select Microsoft Dynamics GP (Modified).
   • In Microsoft Business Solutions - Great Plains 8.0, select Great Plains (Modified).

7. Select Apply, and then select OK to save the security changes.

   Note: By default, when you launch the Advanced Security tool, the current user and the current company are automatically selected. Any modifications you make will apply to this default context. However, you have the flexibility to select additional users and companies using the Company area and the User area within the Advanced Security window if you need to assign permissions more broadly.

Method 3: Utilizing the Standard Security Tool (Pre-Microsoft Dynamics GP 10.0)¶

In versions preceding Microsoft Dynamics GP 10.0, the Standard Security tool can also be used, although Advanced Security provides more granular control.

1. From the Tools menu, point to Setup, then System, and then select Security.
2. If prompted, enter the system password and click OK.
3. In the Security window, select the User ID for whom you want to grant access.
4. Select the Company ID for which the user will access the report.
5. In the Type field, select Modified Reports.
6. In the Series field, select Purchasing.
7. Find and mark the checkbox next to your modified Computer Check Posting Journal report.
8. Click OK to save the security settings. This ensures the selected user can now access and print the customized report.

The Impact of Enhanced Reporting¶

By successfully implementing these modifications, your Computer Checks Posting Journal will transform into an even more powerful tool for financial oversight. The inclusion of the document number and document due date directly on the report provides several key advantages:

• Improved Reconciliation: Easily match payments to specific invoices, streamlining the reconciliation process and reducing manual effort.
• Enhanced Audit Trails: Create a clearer and more comprehensive audit trail, as payment records are directly linked to their originating invoice details. This is invaluable for compliance and internal controls.
• Better Cash Flow Management: Gain insights into payment timing relative to invoice due dates, aiding in better cash flow forecasting and management.
• Faster Problem Resolution: Quickly identify and resolve discrepancies or payment issues by having all relevant invoice information readily available on a single report.
• Operational Efficiency: Reduce the need to cross-reference multiple reports or delve into separate inquiry windows, saving time and improving the efficiency of accounts payable staff.

These enhancements empower financial teams with greater visibility and control, transforming a standard report into a strategic asset for informed decision-making within Microsoft Dynamics GP.

---

We hope this comprehensive guide assists you in tailoring your Microsoft Dynamics GP reports to better suit your business needs. Have you implemented similar customizations in your Dynamics GP environment? What other report modifications have you found particularly beneficial? Share your experiences and insights in the comments below!