Adding additional voucher fields to the Payables Check Remittance report

By Steve Endow

I just had a request to add an additional voucher field to the payables Check Remittance report.  It has been a little while since I've had to do this type of modification in Report Writer, so it took me a few minutes to remember the process.

In this case, the client wanted the TRDISAMT field added to the remittance.  That field is not available by default in the Check Remittance report field list, so we have to add the voucher table to the report.  Sometimes additional tables can easily be added to the report, while in others, you have to join in a new table yourself.

First, insert the Check Remittance report as a Modified Report and click Open.

On the report Definition window, click on the Tables button.

On the Tables window, you should see 3 default tables for the report.

These three tables do not have any additional table relationships available--if you select one of the report tables and click New, the Related Tables window is empty.

To remedy this, we need to create a new Table Relationship.  Looking at the three available tables on the report, the Temp table is not an option, and the Currency Setup is not a candidate.  Our only hope is to join the voucher table to the Payment Apply To Work table.

Because the PM table names can get confusing, and because Report Writer inconsistently displays the Technical Name vs. the Display Name, I recommend using the Resources Description window in GP to confirm which table we need to work with.

The PM Payment Apply To Work File has a Technical Name of PM_Payment_Apply_WORK.  We'll need to know this name for the next step.

So then head back into Report Writer and click on the Tables button, then select Tables.

In the Tables list, locate PM_Payment_Apply_WORK and click on the Open button.

In the Table Definition window, click on Relationships.  We are basically going to tell Report Writer how to join the voucher table to the Payment Apply Work table.

Notice that there are no Table Relationships--this corresponds to what we saw in the report--there were no additional tables to select.

Click on the New button.

In the Table Relationship Definition window, you will select a Secondary Table.  We want to add the PM20000 table, which is the PM Transaction OPEN File.

You will then choose Key2 as the Secondary Table Key, and 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.  Then click on OK.

Congratulate yourself--you now have a new Table Relationship!

So now that you've done all of that work, you need to add the table to your modified report.

Open your modified report, click Tables, select the PM Payment Apply To Work File, then click New.

Select PM Transaction OPEN File and click OK.  Then close the Report Tables Relationships window and click on Layout to open your report.

You should now see the PM Transaction OPEN table in the drop down list for your report, and can then add the Discount Amount field, or any additional voucher field, to your remittance report.

While this process did have several steps to add the new Table Relationship, be aware that the Dynamics GP reports vary dramatically in terms of complexity.  Some reports are very complex, with much more involved tables and relationships that make it difficult or impossible to perform the joins required to add another table.  So just be aware that certain report modifications could get very complex.

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 on Google+ and Twitter

http://www.precipioservices.com