You use this program to define how Tables are logically linked. By linking a table, the columns available in the linked table can be used when creating a report.
When you use the Data Dictionary Import program to import the standard SYSPRO data dictionary into the Report Writer data dictionary, the standard SYSPRO table links are automatically created. The Browse on Data Dictionary Linkages program enables you to define your own links between the standard SYSPRO tables as well as for tables you may have created using the Browse on Data Dictionary Tables program.
Linked tables have a common column in both the Source and the Target Tables. Only links that already exist in the data dictionary can be used in a report.
You use the Data Dictionary Linkages List program to print a list of table linkages.
Before you can define the target table(s) you want to link to a source table, you must indicate the source table.
Once you have selected a source table, you can use the Change Table option from the Change menu to select a different source table.
| Field | Description |
|---|---|
| Source table |
This indicates the relevant Source Table that you would like to link other tables to. These other linked tables are referred to as Target Tables. Linked tables must have a common column in both the Source and the Target Tables. |
| OK |
Select this to accept the source table you entered. The Data Dictionary Linkages screen is displayed enabling you to maintain the links to the required target tables for this source table. Once you have defined a valid table, all the Target Tables already linked to this source table are displayed in a listview. |
| Close | Select this to exit the program without maintaining any links. |
| Field | Description |
|---|---|
| Change | |
| Change Table | Select this to indicate a different table for which you want to maintain links. |
This screen is displayed when you select the Add or Change option form the Edit menu.
![[Note]](images/note.png)
|
|
|
When you create links using a C-ISAM based system, all links from one file to another must link to the Primary key fields of the target file. When you create links on SQL Server based systems, you can link to any column(s) in the target file. |
|
| Field | Description |
|---|---|
| Target table |
This indicates the name of the target table you want to add to the source table or for which you want to maintain the links. It is not possible to link to a target table that has been defined as a sequential file. It is however, possible to create a link from a sequential file. |
| Description | This indicates the description for the target table selected in the previous field. |
| Link type | This field enables you to define the type of link required between the source and target tables. |
| 1-1 |
Select this to create a one-to-one linkage between the tables. A one-to-one linkage is when, for each row in the source table, only one row is read from the target table. The important thing to remember is that the key of the target table is built using the linkage definition. The next row with a key equal to (or greater than) the key built is then read. Once the required row type(s) have been read and the line on the report printed, the next row from the source table is read. In some cases, more than 1 row in the linked table is required to be printed. For example: Where the source table is the Inventory Warehouse, the target table is the Inventory Master table and the link is the Stock Code column. The Stock Code column may have various rows in the Inventory Warehouse table if it is stocked in multiple warehouses, but there is only one row in the Inventory Master table. |
| 1-Many |
Select this to create a one-to-many linkage between the tables. A one-to-many linkage is when for each row in the source table, there can be more than one row read from the target table. By defining the link as a one-to-many, the report writer prints a row from the source table and continues printing rows from the target table until the link changes. The important thing to remember about one-to-many linkages is that if the source and target columns differ, the report writer returns to process the next source row. For example: Where the source table is the Inventory Master, the target table is the Inventory Warehouse and the link is the Stock Code column. The Stock Code column has only one row in Inventory Master table, but may have various rows in the Inventory Warehouse table if it is stocked in multiple warehouses. |
| Link definition |
When adding a new table linkage, the program attempts to determine the columns that must be used for the link and inserts them automatically. The target table column is scanned in 'sequence number' sequence. (i.e. The first columns in the line are processed before the later ones). For each column found to lie within the key, the source table is checked to see if a column of the same name exists. If one does exist, the link is added automatically. The link definition validates the columns, and returns the column name in the correct case. |
| Source column, String |
This indicates the column from the source table. When defining a link, the source column name can be a column from the source table, or a string (in quotes), or the special system variable $Company. This column must be the common link between the source and target tables. |
| Target column |
This indicates the column from the target table to which you want to link the source table column. The column or string selected from the source table is moved to this field to build the key of the target table. |
| Save | Select this to save the defined linkage. |
| Close | Select to exit the program without saving the linkage. |
Add a report writer data dictionary link
-
From the Browse on Data Dictionary Linkages program, enter the source table to which you want to link a target table or tables.
-
Select OK to accept the source table you entered.
The target tables already linked to this source table are displayed in a listview.
-
From the listview pane, select Add from the Edit menu.
Alternatively select the Add icon from the toolbar.
-
Enter the target table name you want to link to the source table.
-
Select the required link type and define the links required.
-
Select the Save function to save the defined linkage.
-
Select Close to return to the listview.
Change a report writer data dictionary link
-
From the Browse on Data Dictionary Linkages program, enter the source table for which you want to change the link to the target table or tables.
-
Select OK to accept the source table you entered.
The target tables already linked to this source table are displayed in a listview.
-
From the listview pane, highlight the target table for which you want to change the linkage information.
-
Select the Change option from the Edit menu.
Alternatively select the Change icon from the toolbar.
-
Enter the new information for the link.
-
Select the Save function to save the changes to the defined linkage.
-
Select Close to return to the listview.
Delete a report writer data dictionary link
-
From the Browse on Data Dictionary Linkages program, enter the source table from which you want to delete the link to the target table or tables.
-
Select OK to accept the source table you entered.
The target tables already linked to this source table are displayed in a listview.
-
From the listview pane, highlight the target table for which you want to delete the linkage information.
-
Select the Delete option from the Edit menu.
Alternatively select the Delete icon from the toolbar.
-
From the Confirm Deletion screen, select OK to delete the selected link or Cancel to return to the listview without deleting the link.
This step is only required if the Confirm Deletions command is selected from the Options menu.
If the Confirm Deletions command is deselected, then the highlighted link is deleted immediately.