Get Related Data from Another Table
In creating reports, you may need to include information from multiple tables into one report. The Table Lookup calculation performs this task.
To add a Table Lookup column:
- Click the
Calculation Editor button on the main toolbar. - Type in Table Lookup under the Type of Calculation.
- On the Parameters tab enter values as follows:
| Parameter | Effect |
|---|---|
| Datasource | (Optional) Name of the data source to use when retrieving data. Note: If you defined External data sources (see External Data), you can select that data source here. Once selected, DAS will load the valid Data Names (see below) for that data source. Otherwise, DAS will use the JDE default data source and tables. |
| Data Name | Name of the table or business view to query. If you selected an External Data source, then this list will be the list of tables valid for that data source. |
| Index Name | (Optional) Name of the index to use for query. When you select an index, the calculation editor populates the fields you need to provide for Define fetch. |
| Sort Order | (Optional)Select how you want to sort the target data set. Use this option when the target data set has many values for what you want to query and you need a specific value based on the sort. |
| Summary Type | (Optional)If you query a target table with multiple rows, you can elect to summarize the values into one value. Average - returns the average value of the query Count - returns the count of the query First - returns the first result fetched from the query Maximum - returns the largest element of the query Minimum - returns the smallest element of the query Multi-row - returns all detail rows of the query None - returns the Row to Fetch number of the query (default is the first row) Sum - returns the sum of the query Summary - unused Select the summary you would like to do such as Sum or Count. |
| Row to Fetch | (Optional)Default value 1. Defines the relative row that you want. 1=First, 2=Second, etc. Useful when you need to select rows other than the first row from the target table. |
| Bulk Fetch Keys | Default is 40. Number of unique queries to batch together before actually querying the database. This reduces the number of network requests and usually speeds up reporting. |
| De-duplication | Default is no de-duplication. The Table Lookup automatically de-duplicates lookup values if you select the Fetch unique target row only once. |
Note
If you are only fetching the first record of a sorted set of records (most recent sales order of a customer for example) you will want to set Bulk Fetch Keys to one. A value of one and a Summary Type of 'First' will alter the query to the database and improve performance in this case.
Query Columns
To fetch information, you specify fields in the target table and values you want to query against those fields. The left hand side of the Specify how to query target table/view defines the target fields. The right hand side defines the values to query for. You may add or delete field names as you wish.
Result Columns
Under Specify which columns you need back from the target table/view, select columns from the target table that you want to return to your report. If you selected a Summary Type, above, then you may only select one column here (which is the column you want to summarize). Otherwise, you may select multiple target table columns.
Warning
Make sure to include the main JDE address book column alias (AN8) (if the table includes such a column) if your company has implemented address book security. If this column is not included with this type of security enabled, any columns designated to be masked will be completely masked even for address book numbers the user is allowed to view.