How to use a lookup column in dynamic reports
In a dynamic report, you can set up a column that fetches information from another table based on a field value. This is useful when your report holds a reference value, such as an item number, and you want to pull in related data stored on that record.
To set it up, go to your dynamic report and choose Detail Setup, then Dynamic Report Columns, and add the fields for the column.
Defining the lookup field and the lookup table
In the Field setting, you choose the field that you want to look up based upon. In the Lookup Table setting, you define the table that you want to look up in.
There is one important rule to keep in mind. The primary key in the table you are looking up in must be the field you define in the Field setting. The lookup only works when these match.
Example: looking up item information by item number
As an example, you can look up in the Item table based on the item number. This lets you fetch information stored in any of the fields on the item record. Once the lookup is configured, you can pick the specific field you want to display as a column in your report.
Q&A
How do I add a lookup column to a dynamic report?
Open the dynamic report, choose Detail Setup, then Dynamic Report Columns, and add the fields for the column. In the Field setting, choose the field to look up based upon, and in the Lookup Table setting, define the table to look up in.
What is the requirement for the lookup field and table?
The primary key in the table you are looking up in must be the same field you define in the Field setting. The lookup will only work when these match.
Can I choose which field is displayed from the lookup table?
Yes. After configuring the lookup, you can pick the specific field stored on the looked-up record that you want to display as a column in your report.