TABLE LOOKUP

Description

Will perform a table fetch off another table and that table's fields, returning that data to an existing report. A table lookup is similar to a left outer Table Join, but offers much more flexibility such as joining columns with differing data types. For example, this calculation is the only method to join in data from external data sources such as SalesForce or NetSuite.

Calculation Editor

Options

Input Parameter Values	Input Values
Datasource (optional)	Name of the data source to use when retrieving data. If left blank, DAS will use the JDE default data source and tables. Note: If you defined External data sources (see External Data), you can select that data source here. Once selected, DAS will load the valid Target Table/ View list (see below) for that data source.
Target table/view	Name of the table or business view from JDE or a defined data source or external connection to query. You also have the ability to define a join by clicking the special DASVIEW object name listed first in the visual assist.
Index	(Optional) Name of the index to use for the query. When you select an index, the calculation editor populates the key fields from that index in the Specify how to query target table/view.
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. Can be sorted ascending or descending.
Row to Fetch	(Optional) Default value is set to 1. This defines the relative row that you want. 1=First, 2=Second, etc. This is useful when you need to select rows other than the first row from the target table, or when specifying a Sort Order.
SummaryType	(Optional) If you query a target table with multiple rows, you can elect to summarize the values into one value. Average - the average value of all the records returned by the query Count - count of the records returned by the query Count distinct - count the distinct number of records returned by the query (NULL counts as 1) Filter: list of values - creates a list of distinct values in a filter format (separated by a semicolon) Filter: not in list - creates a list of distinct values to be excluded in a filter format (separated by a colon and uses an exclamation to signify "not") 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
Bulk Fetch Keys	This is the number of unique queries to batch together before actually querying the database. This reduces the number of network requests and usually speeds up reporting. We generally recommend leaving this setting as is. Occasionally design notes will recommend a change to the bulk keys setting. Default is 40.
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. This only works in conjunction with the the multi-row summary type.
Authentication timing	(Only applies for certain External Data Connections On Report open - Indicates to authenticate the connection when the report is opened. This is the default option. When first used - Indicates to authenticate when the connection is first used.
Only Rollup If	Just like any other calculation, this criteria determines if the table lookup should even query the target table or view for a given row. Use this mainly to optimize report performance by skipping unnecessary queries to the database.

Input

Specify how to query target table/view

This is how you build the relationship between the data on your report and the target table/view. You can use a combination of data columns, calculated columns, and literal values (including parameters and variables) from your report to build the relationship. Depending on your target table/view, you may only need to use one column to build the relationship or you may need more than one. See samples below.

Column to column
Address Number to Address Number

Column to literal
In this example, the Target Table/View has multiple Cost Methods (LEDG), and we only want to bring back 07. This is the method that will be used to filter on any fields in the Target Table/View.

Column as filter

Column as filter will interpret the contents of an input column as a filter expression.

In this example, the Range Filter calculation is used to construct a date range filter to be used when filtering the order date column of the table lookup.

Assignment Behavior

Use the Assignment Behavior input option to ignore a filter on a column if the incoming value is blank. Normally, a blank value from a incoming parameter or column will be interpreted as a literal blank by a table lookup input. In other words, the table lookup will only return rows where the filtered column is blank.

In this example, the order number will be used as filter on the table lookup only if the user entered a value in the parameter.

Output

Specify which columns you need back from the target table/view	Input Values
Define column caption	Columns you want to bring back

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 the output columns will show the summary values for all the selected rows. The system will attempt to convert numeric strings to numbers before attempting a summary function.

Remarks

A Table Lookup requires three design elements:

• Table name – The database source and table where data will be retrieved.
• The matching criteria – The matching filtering criteria between the target table and the original table to retrieve correct data based off “like” index/ key fields.
• The columns that will be returned – Selecting the column(s) to return from the lookup table.

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.

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.

Examples

Example 1: Adding a Table Lookup to a Table Join

Current Table Join: Accounts Payable Ledger (F0411) simple join via Address Number to Supplier Master (F0401)
This is a Table Lookup to the Address Book - Phone Numbers (F0115) where we could have multiple phone numbers for the same address number. We can also have multiple voucher records for the same address number. With traditional joins, this could lead to duplication. Using the Table Lookup as shown below will allow you to avoid that.
Go into the Design Menu → Calculations → add a new calculation → Table Lookup.
After building the relationship between the two tables (Address Number to Address Number), we're going to change SummaryType to "Multi row" and De-duplication to "Fetch unique targets only once". Doing this will ensure that you are not getting duplication like you would if you did a Table Join to the F0115, with no additional calculations. The calculation editor should look as follows:

The columns you chose to bring back from the F0115 will show up on the report. You now have the option to use them in calculations and move them around as needed. Because they are from the Table Lookup calculation, they will also have the calculator icon in the top right of each column caption. The calculation will only show data from the target table in new rows beneath the information from the initial report. The report will look as follows, assuming you have hidden columns from the previous Table Join:

Example 2: Ignore a Blank Value in a Parameter used by a Table Lookup

Using the same report and Table Lookup from Example 1, we're going to add the Phone Number Type as a parameter.
In the Table Lookup, we're going to add an additional "Specify how to query" field between Phone Number Type and this new parameter. Because Table Lookups will always query with blank values when fetching, we need to take an extra step and tell the Table Lookup to ignore the value. We do this by clicking on the blue arrow square between the target table/view and filter criteria columns and changing it from the default to "no filter".

Example 3: Adding a Table Lookup to bring back a sum

Starting with the Item Branch File (F4102) we're going to do a Table Lookup to the Item Location File (F41021). When we do this Table Lookup, we want to join by more than one field and return the sum. We will be connecting the tables via Short Item Number (ITM) and the Business Unit/Branch Plant (MCU).
The key to this one is changing the SummaryType to "Sum".
We're going to bring back the Quantity on Hand (PQOH) column. The calculation editor should look as follows:

You will see the sums for the Quantity on Hand being returned from the F41021. The output for this looks as follows: