Microsoft Knowledge Base Archive

INTRODUCTION

MORE INFORMATION

Consider the following scenario. You create the SOP Ship Weight table in a Dexterity program. The SOP Ship Weight table is the companion table to the SOP Line Work table that contains the Item Shipping Weight field. You want the Item Shipping Weight field to appear in the SOP Blank Invoice Form report. To link the SOP Ship Weight table to the SOP Blank Invoice Form report, follow these steps.

Note You must create a one-to-one relationship if the tables have the same key. If you try to create a direct relationship from the SOP Line Work table to the SOP Ship Weight table, the table relationship will be lost when you create the chunk file.

1.

In Dexterity, open the SOP Blank Invoice Form report. To do this, follow these steps:

a.	In the Resource Explorer pane, click Reports .
b.	In the Reports list, double-click SOP Blank Invoice Form .
c.	In the Report Definition window, click Tables .
d.	In the Report Table Relationships window, note the items in the Report Table Relations list. You will need this information later when you add the tables back to the report. You have to add the tables back to the report because you have to duplicate every table in the Report Table Relations list above the Sales Transaction Amounts Work table. This includes the Sales Transaction Amounts Work table.

2.

Duplicate the tables. To do this, follow these steps:

a.	In the Resource Explorer pane, click Tables .
b.	In the Tables list, click SOP_Document_HDR_TEMP .
c.	On the Utilities menu, click Duplicate .
d.	In the New Name field, type a name for the table, and then click OK . For example, type SOP_Document_HDR_Temp_DUP .
e.	Double-click the new duplicate table, and then add DUP to the display name in the Display Name field.
f.	Click OK to save the duplicate table.

3.

Reopen the duplicate table, and then create a new relationship from the duplicate table to the original SOP Document Header temporary table. To do this, follow these steps:

a.	Double-click the new duplicate table.
b.	Click Relationships , and then click New .
c.	In the Relationship Table Lookup window, click the duplicate table that you created in step 2.
d.	In the Secondary Table Key list, click SOP_DOCUMENT_HDR_TEMP_Key1 .
e.	In the Primary Table list, duplicate the items that are listed in the Secondary Table list.
f.	Click OK .
g.	Repeat step 3a through step 3f for each table that you noted in step 1d. Specifically, duplicate the following tables: • Sales Document Header Temp • Sales Transaction Work • Sales Transaction Amounts Work Note You do not have to duplicate the other tables because you are not linking directly to the tree in which these tables belong.

4.

In the duplicate Sales Transaction Amounts Work table, create a relationship to the SOP Ship Weight table. To do this, follow these steps:

a.	In the Resource Explorer pane, double-click the duplicate Sales Transaction Amounts Work table in the Tables list.
b.	Click Relationships , and then click New .
c.	In the Relationship Table Lookup window, click SOP Ship Weight , and then click OK .
d.	In the Secondary Table Key list, click SOP Ship Weight Key1 .
e.	In the Primary Table list, duplicate the items that are listed in the Secondary Table list.
f.	Click OK .

5.

Follow step 3a through step 3f to add the following relationships: • Add a relationship from the Sales Document Header Temp duplicate table to the Sales Transaction Work duplicate table. Use key 1 for the relationship.
• Add a relationship between the Sales Transaction Work duplicate table to the Sales Transaction Amounts duplicate table. Use key 1 for the relationship.

6.

In the Report Definition window, change the main table of the report from the current table to the Sales Document Header Temp duplicate table that you created. To do this, follow these steps:

a.	Click the tables button, and then click New . Add the original Sales Document Header Temp table to the Sales Document Header Temp duplicate table.
b.	Link the Sales Transaction Work duplicate table to the Sales Document Header Temp duplicate table.
c.	Link the original Sales Transaction Work table to the Sales Transaction Work duplicate table.
d.	Link the Customer Master Address table and the Sales User Defined Work History table to the original Sales Transaction Work table.
e.	Link the Sales Transactions Amounts Work duplicate table to the Sales Transaction Work duplicate table.
f.	Link the original Sales Transaction Amounts Work table and the SOP Ship Weight table to the Sales Transactions Amounts Work duplicate table.
g.	Link the "Sales Line Comment Work and History" table and the "Sales Serial/Lot Work and History" table to the original Sales Transaction Amounts Work table.

7.

Open the SOP Blank Invoice Form report layout. Drag the Item Shipping Weight field into the H2 section of the report.

Note The report that you created by using this method will function slower than the original report. This occurs because the tables have been duplicated and because more data is evaluated. However, by using this method, you can transfer any data from the companion table to the report. When you use this method, the report must be included as an additional report. Any changes to the report by the client must be redone in the additional report.

↑ Back to the top

REFERENCES

↑ Back to the top

Article ID	:	935380
Revision	:	2
Created on	:	11/9/2017
Published on	:	11/9/2017
Exists online	:	False
Views	:	108

Microsoft KB Archive Search

How to link third-party tables to the SOP Blank Invoice Form report by using Dexterity for Microsoft Dynamics GP

INTRODUCTION

MORE INFORMATION

REFERENCES