Steve Endow

A client recently asked to have an extra voucher field added to their payables Check Remittance report. I haven’t modified this report in a while, so it was a good refresher.

Specifically, the client wanted the TRDISAMT field on the remittance. Since this field isn’t included in the Check Remittance report by default, we need to add the voucher table to the report. Sometimes, adding tables is simple, but other times you have to manually join them.

First, open a modified copy of the Check Remittance report.

In the Report Definition window, click the “Tables” button.

You’ll see three tables already associated with the report.

These tables don’t have any related tables available. If you try to add a related table, you’ll see an empty window.

To fix this, we need to create a new table relationship. We can’t use the Temp table or the Currency Setup table, leaving us with the Payment Apply to Work table. We need to join the voucher table to this table.

Because PM table names can be confusing, it’s best to use the Resources Description window in GP to verify the correct table name. Report Writer isn’t always consistent in how it displays table names.

The technical name for the PM Payment Apply to Work File is PM_Payment_Apply_WORK. We’ll use this in the next step.

Go back to Report Writer, click “Tables,” and then select “Tables.”

Find PM_Payment_Apply_WORK in the list and click the “Open” button.

In the Table Definition window, click “Relationships.” This is where we tell Report Writer how to join the voucher table to the Payment Apply to Work table.

You’ll notice that there are no table relationships, just like we saw in the report.

Click the “New” button.

In the Table Relationship Definition window, choose PM20000 (the PM Transaction Open File) as the Secondary Table.

Set Key2 as the Secondary Table Key. Link the “Apply To Document Type” and “Apply To Voucher Number” fields from the Apply To Work file. Make sure to use “Apply To” and not “Apply From.” Click “OK.”

You’ve created a new table relationship!

Now, let’s add the table to your modified report. Open the report, go to “Tables,” select “PM Payment Apply to Work File,” and click “New.”

Select “PM Transaction Open File” and click “OK.” Close the Report Tables Relationships window and click “Layout” to view your report.

The PM Transaction Open table should now be in your report’s drop-down list. You can add the “Discount Amount” field or any other voucher field to your remittance report.

Although we had several steps to add the table relationship, remember that Dynamics GP reports can be much more complex. Some reports have complicated table structures that can make joining new tables quite challenging or even impossible. Be prepared to handle more intricate report changes if necessary.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him onGoogle+andTwitter

http://www.precipioservices.com