Corrupción 140 y conflictos de intereses

A qualified table is a special kind of lookup table that is extremely versatile.

It can be used to efficiently store complex relationships between a main table product record and one or more lookup table records that contain various types of additional information.

A qualified table is necessary when the number of lookup table records would otherwise be very large, because each main table record is related not just to the predefined lookup values of the lookup table records but also to one or more additional fields of information that are different for every main table record (such as quantity price breaks, multiple prices for different divisions, regions, or trading partners, or cross-reference part numbers for different distributors or contract customers). In these cases, the fields whose values are different for each main table record should be defined as qualifier fields of the qualified table; the qualified table will then contain an actual record for each of the predefined lookup values or value combinations (such as distributor, contract customer, division, region, or trading partner).

A qualified table stores a set of lookup records, and also supports qualifiers, database “subfields” that apply not to the qualified table record by itself, but rather to each association of a qualified table record.

Note: An MDM repository can have multiple qualified tables. In practice, the use of qualifiers and a qualified table instead of normal fields and a subtable keeps the number of actual records in the qualified table very small, but since every link between a main table record and an instance of a qualified table record contains additional information, the number of qualified link table records necessary to store the additional information is very large, often larger than the number of records in the main table itself.

When used for multiple prices or cross reference part numbers, qualified tables and qualifiers allow you to store a massive amount of potentially sparse data, by eliminating n fields from the main table and replacing them with a single qualified lookup field into a qualified table that has n corresponding records and one or more qualifiers. For example, n price fields, one for each distributor or quantity

price break (or worse, each distributor / quantity price break combination) can be replaced with n qualified table records, one for each distributor / quantity price combination, and a qualifier for the price.

Qualified tables offer self-configuring, out-of-the-box support for:

• Multiple prices (including quantity price breaks)

• Cross-reference part numbers

• Other distributor-, supplier-, and customer-specific information

• Product applications for application-based search

Consider a main table of product records that contains sparse quantity pricing data for each product.

Figure 53: Sparse Data on Main Table

Using a qualifier to store the quantity pricing data, the qualified table would have a single field Quantity and a single qualifier Price, and would contain these quantity records.

Figure 54: Qualified Table

A qualified lookup field in the main table would replace all of the quantity price fields, and the pricing data would be stored as qualifier values associated with main table / qualified table links.

Figure 55: Qualified Lookup on Main Table

Note: A main table / qualified table link is created only for those product/quantity combinations for which a price value actually exists.

Using qualifiers to distinguish between different uses of the same unqualified application: (1) eliminates the need to enumerate every distinct value combination of fields and qualifiers taken together; (2) in so doing, dramatically reduces the number of distinct records in the qualified table, making it more useful as a valid table of allowed lookup values; and (3) avoids a tremendous amount of data duplication, especially when rich content (such as images, text blocks, and PDFs) is added to each qualified table record.

This innovative data model has the following advantages: (1) it completely eliminates all duplication of both product data and application data typical of previous systems; (2) it efficiently enforces validation against the table of qualified table records; (3) it dramatically reduces memory and storage requirements; and (4) it is radically more efficient for maintenance and searching. For example, an automotive parts catalog that historically contained over twenty million application records is represented within an MDM repository with just over one million part records and forty thousand vehicle specification records.

Maintaining Qualified Lookups

Each record in a repository’s main table may be assigned to one or more qualified table records. Qualified lookup fields, which appear on the right side of the Record Details tab, show which qualified table records are currently linked with the selected main table record(s). You can perform the following functions on qualified lookup fields:

• Add (link) qualified table records to the selected main table record.

• Remove (unlink) qualified table records from the selected record.

• View qualified table record details (including qualifiers).

• Add new records to the qualified table.

• Edit qualifier values on a linked qualified table record.

The Qualified Lookup Field Cell

Each linked qualified table record is displayed on its own row inside the qualified lookup field’s cell. By holding the mouse over a record in the qualified lookup cell, you can quickly view the details of that qualified table record. MDM displays the details in a tooltip that contains the values for each of the linked qualified table record’s fields and qualifiers.

Figure 56: Qualified Lookup Cell

You can also view and edit qualified record details by right-clicking on the record in the qualified lookup cell and selecting View/Edit Detail… from the context menu. This opens the Qualified Lookup Detail dialog

Figure 57: Qualified Lookup Detail

The Qualified Lookup Detail dialog displays the field and qualifier values of the selected qualified table record. For easy identification, qualifier names are enclosed in square brackets ([ ]).

From the Qualified Lookup Detail dialog, you can also edit the qualifier values of the selected qualified table record.

To edit the qualifiers of a linked qualified table record:

1. From the Record Detail tab, right-click on a qualified table record in a qualified lookup field cell and choose View/Edit Detail… from the context menu.

2. From within the Qualified Lookup Detail dialog, click the Edit button to enable the edit controls, and edit the qualified table record qualifier values.

The Select Qualified Lookup Record Dialog

To add or remove qualified records linked to the main table record, double-click inside the qualified lookup field’s cell to open the Select Qualified Lookup Records dialog.

Figure 58: Qualified Lookup Record Dialog

The Select Qualified Lookup Records dialog contains a Search Parameters pane, an Available lookup records list, a Selected lookup records list, and two tabs:

Lookup Detail and Search Selections. Use the Search Parameters pane to narrow down the list of qualified table records appearing the Available lookup records list.

For convenience, all of the search selections you make on the Search Parameters pane are shown on the Search Selections tab.

The Available lookup records list contains all of the records on the qualified lookup table which match your search selections. When you select a record in the Available lookup records list, the Lookup Detail tab displays the record’s field values (both display and non-display) in read-only gray, but its qualifiers are not displayed.

The Selected lookup records list contains all of the qualified table records which have been added to the main table record(s) you are editing. When you select a record in the Selected lookup records list, the Lookup Detail tab displays both the field and the qualifier values for the record, with the field values appearing in read-only gray and the qualifier names appear in square brackets ([ ]).

In addition to adding and removing existing qualified table records, you can also create new qualified table records from the Select Qualified Lookup Records dialog. New qualified table records are added to the Available lookup records list and must be added to the Selected lookup records list before their qualifier values can be edited.

To narrow down the set of qualified table records in the Available Lookup Records pane:

1. In the qualified lookup selector dialog, add one or more search selections in the Search Parameter tabs for each lookup field of the qualified table.

2. MDM narrows down the list of available qualified table records.

Hint: To clear the search selections for all of the lookup fields, right-click on any of the search tabs and choose Reset Search from the context menu.

To clear them for just a single field, either: (1) select [ALL] in the search tab for that field, or (2) right-click on the search tab for that field and choose Reset Parameter from the context menu.

To link existing qualified table records to the main table record:

1. In the Select Qualified Lookup Records dialog, highlight a record in the Available lookup records list and click the Add button.

2. The record appears in the Selected lookup records list.

3. Click OK to save the change and close the Select Qualified Lookup Records dialog.

Hint: You can also double-click on a qualified table record (or drag-and-drop it) to add it to the Selected lookup records list.

Note: All qualified lookup fields are multi-valued, so you are always permitted to select multiple qualified table records. Unlike other object selectors, the qualified lookup selector dialog does not remove qualified table records from the Available lookup records list when you add them to the Selected lookup records list so that you can add each qualified table record more than once, if necessary, with a different set of qualifier values.

To unlink qualified table records from the main table record:

1. In the Select Qualified Lookup Records dialog, highlight a record in the Selected lookup records list and click the Remove button.

2. The record disappears from the Selected lookup records list.

3. Click OK to save the change and close the Select Qualified Lookup Records dialog.

Hint: You can also double-click on a qualified table record (or drag-and-drop it) to remove it from the Selected lookup records list.

Note: When you remove a qualified table record from the Selected lookup records pane, MDM adds it to the Available lookup records list if it is not in the current qualified table search results. To refresh the set of qualified table records in the Available lookup records list to correspond precisely to the current search selections, you must change the search selections.

Hint: To remove all of the qualified table records, click None. To unlink a qualified table record from the current record without even entering the Select Qualified Lookup Records dialog, right-click on the qualified table record directly from the Record Detail tab and choose Remove from the context menu.

To add a new qualified table record:

1. In the Select Qualified Lookup Records dialog, click the Add… button to open the Add Qualified Table Record dialog

2. For each of the lookup fields of the qualified table, select from the drop-down list to specify the value for the field.

3. For each of the non-lookup fields of the qualified table, enter a value.

4. Click OK to close the Add Qualified Table Record dialog.

5. The new record is added to the qualified table and appears in the Available lookup records list.

Figure 59: Adding a New Qualified Record

Note: You must edit the record later to add qualifier values to it