Suppressing Voided Documents from Customer Statements
Microsoft Dynamics GP is a feature-rich product with the ability to include and exclude a good amount of information via the user interface, but when it comes to simple practical aspects such as suppressing voided documents from a customer statement, well it kind of falls short. No worries though! This article presents detail steps on how to suppress voided documents on customer statements without involving any programming.
For this example, I will be using the RM Statement Long Form (any statement will do) and will devide the process into 2 phases. So, lets get started.
Phase I - Setup the new relationships to expose the Void Status field
Since the document void status cannot be obtained from the current tables in the relationship, it will be necessary to create a relationship to the RM Open table.
1) Print the statement you would like to modify to screen and hit CTRL + F9 on your keyboard. Close ALL opened windows once in Report Writer.
2) Click the Tables button on the toolbar and select Tables from the drop-down button menu. This will open the Tables window.
3) Locate the RM_Statements_TRX_TEMP table, click Open to open the Table Definition window.
4) Click on the Relationships button to open the Table Relationships window. Click on the New button to open the Table Relationships Definition window.
5) Click the ellipses button, next to the Secondary Table field to open the Relationships Lookup window. Locate the RM Open table, highlight and click Ok.
6) Back to the Table Relationships Definition window, click on the Secondary Table Key drop-down and select RM_OPEN_Key1
7) Match each drop-down key segment on the left with the grayed out key segment on the right. You should find and EXACT MATCH for each field (this should create a One-to-One relationship between the tables) as shown below. When finished, click Ok to close the Table Relationships Definition window.
8) Close the Table Relationships window
9) Click Ok to close and save the Table Definitions window.
10) Close the Tables window
11) In the Report Writer window, highlight the report and click Open to open the Report Definition window.
12) Click the Tables button to open the Report Table Relationships window. Highlight the RM Statements Transaction Temporary file, then click the New button.
13) In the Related Tables window, select the RM Open table added in step 5, and click Ok.
14) Close all subsequent windows to apply the changes.
Phase II - Suppress the field on the report
Now that we have created the table relationships, we will setup a calculated field to check on the Void Status field of the RM Open table to decide whether the record will be included in the body of the statement report. For this, we will use the "Suppress When Field is Empty" feature in the Report Section option, in tandem with the calculated field.
1) Click on the Reports button on the toolbar. This will open the Report Writer window.
2) Select the statement report from the Modified Reports column on the right and click the Open button to open the Report Definition window.
3) Click the Layout button to open the Report Layout window.
4) Select calculated fields from the drop-down in the Toolbar window (usually at the left of the screen)
5) Setup a Conditional calculated field called (C) Suppress Voids that returns an Integer value.
6) Setup the following 3 expressions, as shown below for the calculated field.
7) Hit Ok to save
8) Click on the Layout window again and this time select Tools from the menu. Click on Section Options
9) Click on "Suppress When Field Is Empty" checkbox beneath the Body checkbox and select the calculated field created in steps 5 and 6.
10) Close the Layout Window and click Save when prompted to. Click Ok in the Report Definition window.
Return to GP and grant security to the modified report accordingly with the release of GP you are using. This should take care of everything.
NOTE: THIS WILL ONLY WORK FOR TRANSACTIONS THAT HAVE NOT BEEN MOVED TO HISTORY WITH THE PAID TRANSACTION REMOVAL PROCESS.
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
Edits:
10/17/2013 - included steps 11 through 14 in phase I to describe the steps required to add the new table relationship to the report, per comment suggestions. Improved graphics layout and added window names.
For this example, I will be using the RM Statement Long Form (any statement will do) and will devide the process into 2 phases. So, lets get started.
Phase I - Setup the new relationships to expose the Void Status field
Since the document void status cannot be obtained from the current tables in the relationship, it will be necessary to create a relationship to the RM Open table.
1) Print the statement you would like to modify to screen and hit CTRL + F9 on your keyboard. Close ALL opened windows once in Report Writer.
2) Click the Tables button on the toolbar and select Tables from the drop-down button menu. This will open the Tables window.
3) Locate the RM_Statements_TRX_TEMP table, click Open to open the Table Definition window.
4) Click on the Relationships button to open the Table Relationships window. Click on the New button to open the Table Relationships Definition window.
5) Click the ellipses button, next to the Secondary Table field to open the Relationships Lookup window. Locate the RM Open table, highlight and click Ok.
6) Back to the Table Relationships Definition window, click on the Secondary Table Key drop-down and select RM_OPEN_Key1
7) Match each drop-down key segment on the left with the grayed out key segment on the right. You should find and EXACT MATCH for each field (this should create a One-to-One relationship between the tables) as shown below. When finished, click Ok to close the Table Relationships Definition window.
 |
| Table Relationships Definition Window |
8) Close the Table Relationships window
9) Click Ok to close and save the Table Definitions window.
10) Close the Tables window
11) In the Report Writer window, highlight the report and click Open to open the Report Definition window.
12) Click the Tables button to open the Report Table Relationships window. Highlight the RM Statements Transaction Temporary file, then click the New button.
13) In the Related Tables window, select the RM Open table added in step 5, and click Ok.
14) Close all subsequent windows to apply the changes.
Phase II - Suppress the field on the report
Now that we have created the table relationships, we will setup a calculated field to check on the Void Status field of the RM Open table to decide whether the record will be included in the body of the statement report. For this, we will use the "Suppress When Field is Empty" feature in the Report Section option, in tandem with the calculated field.
1) Click on the Reports button on the toolbar. This will open the Report Writer window.
2) Select the statement report from the Modified Reports column on the right and click the Open button to open the Report Definition window.
3) Click the Layout button to open the Report Layout window.
4) Select calculated fields from the drop-down in the Toolbar window (usually at the left of the screen)
5) Setup a Conditional calculated field called (C) Suppress Voids that returns an Integer value.
6) Setup the following 3 expressions, as shown below for the calculated field.
|
| Calculated Field Definition Window |
7) Hit Ok to save
8) Click on the Layout window again and this time select Tools from the menu. Click on Section Options
9) Click on "Suppress When Field Is Empty" checkbox beneath the Body checkbox and select the calculated field created in steps 5 and 6.
10) Close the Layout Window and click Save when prompted to. Click Ok in the Report Definition window.
Return to GP and grant security to the modified report accordingly with the release of GP you are using. This should take care of everything.
NOTE: THIS WILL ONLY WORK FOR TRANSACTIONS THAT HAVE NOT BEEN MOVED TO HISTORY WITH THE PAID TRANSACTION REMOVAL PROCESS.
Until next post!
MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/
Edits:
10/17/2013 - included steps 11 through 14 in phase I to describe the steps required to add the new table relationship to the report, per comment suggestions. Improved graphics layout and added window names.
Comments
Thanks so much for your help - this did the trick! However, you left out a step in between phase 1 and phase 2. After creating the relationship to the RM Open table, you need to add the RM Open table to the report via the Tables button on the Report Definition window (select "04. RM Statements Transactions Temporary File*", click New, then select the RM Open table. Until you do that, while the relationship between the two tables exists (per steps in phase 1), the RM Open table is not accessible in your report (so you cannot do phase 2).
You are absolutely correct. I edited the article to include the missing step.
Thanks very much and keep up the readership.
MG.-
Mariano Gomez, MVP