Reference Data in Dataedo Desktop

Marcin Zlotek - Dataedo Team Marcin Zlotek 8th December, 2022

Adding Lookup and Lookup Values

Creating a new Lookup can be done in a few ways:

  1. In Ribbon: Add Object -> Lookup.
  2. In the Repository Explorer context menu:
    • from database menu: Add object -> Add Lookup.
    • from the Lookup folder menu.
  3. In columns view context menu: Create new Lookup for … column.

Add Lookup window

Click on Add button (1) to add a new value. A new value will be added at the end of the list (5). Click on the Remove button (2) to remove values selected in the list below (5). You can use the Shift and Ctrl keys to select many values at once. Select Bulk add Values (3) to add many values at once by pasting them from another source (see next picture for details). A name field is required.

NOTE: Lookup values with the same ‘Value’ are not allowed – invalid values will be marked. Review all errors manually.

Image title

Paste values copied from other sources (e.g. Excel file) by clicking on the Paste (1) button. You can use Copy template (2) first to know how your source file with values should be formatted and then paste them without further issues. All pasted values will be displayed on the list below (3).

Image title

Create a new Lookup from the column menu

From the context menu on the column select Create new Lookup for column_name column (1). In the new window, you have to type a valid name for creating Lookup. If you want to create a Lookup and add values from the selected column, check Create Lookup with current values (connect to source) (2).

Image title

Image title

It will connect to your data source and show you sample values from that column and how many unique values in general were found in that column (screen below).

WARNING: Always please verify if the selected column contains dictionary values.

NOTE: Each Lookup has maximum capacity of 100’000 values. Fetching many values may take a while.

Image title

Linking objects to Lookups and adding values

Linking column to Lookup

From the context menu on the column select Link to Lookup. In the new window, you have to select the existing Lookup. If you want to link with values from the selected column, check Link Lookup and add current values (connect to source). The mechanism is similar to creating a new Lookup for the column.

Linking Lookups to Term

Open Term from Repository Explorer (1). Go to the Linked data elements (2) tab. Click on the Edit data links button or a similar one above table (3). A modal window will be opened (see next image). After completing the process lookup will be added to the grid (4).

Image title

Select object type and optional name substring (1). From the Grid below select Lookup(s) (2) that you want to link and click Save.

Image title

Linking Terms and columns to Lookup

Open Lookup from Repository Explorer – here Currencies opened. Go to the Linked Columns and Terms (1) tab. Click on Edit linked Columns and Terms (2) or a similar button in the ribbon. A modal window will be opened. After completing the process linked columns and terms will be added to the grid (3).

Image title

Select object types that you want to link (1) and type in the search phrase for the object name (2). Searching results contains Object and Column columns. From the list select objects that you want to link. Check the Fetch unique values option if you want to import column values from your data source and insert them into Lookup (3). When you click the Save button (4), a new window with sample values for all columns will be opened. Please verify if all columns contain dictionary values.

NOTE: searching results contains only the first 1000 objects, as often as possible, please specify longer names.

Image title

Editing Lookup and Lookup values

Changing status of a lookup value

Select one or many values belonging to the same group and open the context menu. Look at the table below to see how actions work for different groups.

Initial status Action Status after action
For Review Approve Values (Approved)
Move to Rejected Rejected
Remove from repository Removed from Lookup*
Values (Approved) Move to For Review For Review
Move to Rejected Rejected
Rejected Move to For Review For Review
Remove from repository Removed from Lookup*

*Lookup value disappears from Lookup and is removed from the repository. It will be added again with the For Review status when you link another column containing this value or reimport values using the Import Lookup values option (described below).

Import data from linked columns

Import Lookup values ribbon button allows the user to import values from linked columns. You can choose from which column values should be imported. As in the linking process, a window with sample data for all selected columns will be opened.

Image title

Unlink column from Lookup

In Linked Columns and Terms click on the column you want to unlink and select Remove link from repository. You can unlink multiple columns at once.

NOTE: values imported from unlinking columns will not be deleted.

Image title

Found issue with this article? Comment below
There are no comments. Click here to write the first comment.