Picklists and Lookups

Owned by Laura Hounshell (Unlicensed)
Last updated: Nov 18, 2019 by Alex Burns (Unlicensed)
8 min read

Picklists and Lookups are useful features within MarketWide that allow descriptions to be assigned to codes within a campaign.

For example, a common Picklist is one that defines the states. In such a Picklist, "New York" is displayed instead of "NY." Using Picklists and Lookups for a campaign makes it much easier to construct campaigns, particularly in fields with a large number of choices, such as Campaign ID, or fields with a number of regular options, such as State.

Picklists and lookups serve many of the same functions but there are certain differences. Lookups are separate tables that contain codes and their corresponding descriptions. Because Lookups are tables, you can run filters on Lookups just as you would any other table. This is particularly useful for larger lookup tables. Picklists only exist within the MarketWide catalog. Because of this, you cannot run filters on them as you would on Lookups.

Topics:

Picklists

Creating Picklists

Adding picklists allows you to provide descriptions for codes. For example, Picklists allow users to select from a list of state names when constructing queries instead of trying to remember two-character state abbreviations.

To Add a Picklists

  1. Click the Picklists ( ) icon.
  2. Right click and select Add Picklist ( ).
  3. Enter in a Name for the Picklist.
  4. Select the level of data enforcement you would like applied to this Picklist under Data Entry.
    • No Enforcement - Enter your own description regardless if it is on a pre-existing Picklist.
    • Confirmation Prompt - Enter your own description, but you will be prompted if something does not match.
    • Strict Enforcement - Description must be on a pre-existing Picklist.
  5. In the Code column, enter a code in the first cell. A new row will automatically appear.
  6. Enter a corresponding description in the Description column.
  7. Repeat steps 3-4 until your list is complete.
  8. Click OK to save your changes.
  9. After making changes to the catalog, click Save ( ).
  10. Once you have saved a working catalog, click Commit ( ) and Close.

Note

To add values to a Picklist that begins with zero, use a single apostrophe before the first zero. Otherwise, the grid eliminates the first zero, as in Microsoft Excel. For example, 02 becomes 2 when you leave of the cell. Entering '02 ensures that the value remains 02 in the grid.

To Assign a Picklist to a Field

  1. Click on the All Fields ( ) icon.
  2. Right click and select Manage All Fields.
  3. To assign a picklist to a field, click in the Picklist column next to the field and use the drop-down list to select a picklist.
  4. Click OK to save changes and exit the dialog.
  5. After making changes to the catalog, click Save ( ).
  6. Once you have saved a working catalog, click Commit ( ) and Close.

Note

You can assign the same picklist to multiple fields.

To Edit a Picklist

  1. Click the Picklists () icon.
  2. Highlight the Picklist you would like to edit.
  3. Right click and select Edit ().
  4. Make any necessary changes or additions.
  5. Click OK to save your changes.
  6. After making changes to the catalog, click Save ().
  7. Once you have saved a working catalog, click Commit () and Close.

Note

Be careful when updating a Picklist, since more than one field may use it. Make sure that the codes and descriptions used in the Picklist are valid for all fields that use it.

To Delete a Picklists

  1. Click the Picklists () icon.
  2. Highlight the Picklist you would like to edit.
  3. Right click and select Delete ().
  4. After making changes to the catalog, click Save ().
  5. Once you have saved a working catalog, click Commit () and Close.

Note

Verify that the Picklist you are deleting is not assigned to fields in your database. Otherwise, those Picklist assignments become invalid.

To Remove a Picklist from a Field

  1. Click on the All Fields () icon.
  2. Right click and select Manage All Fields.
  3. To remove a picklist from a field, click in the Picklist column next to the field and hit the <Delete> key, or right-click and select Clear Cell.
  4. Click OK to save changes and exit the dialog.
  5. After making changes to the catalog, click Save ().
  6. Once you have saved a working catalog, click Commit () and Close.

To Use a Picklist to Enter a Value

  1. In the Enter Value dialog, click Picklist.
  2. In the Select Value window, highlight a Picklist entry. You may select more than one by holding <Control> and clicking multiple items from the list.
  3. Click OK. MarketWide enters the value associated with the description you selected in the Enter Value dialog.

Tool Tip

You can click on multiple values in the Select Value window when your statement uses a condition that allows multiple values, such as IN or BETWEEN.

Lookups

Lookups give the ability to attach a lookup table to any field. When a Lookup is attached, the desired lookup table must be selected. In addition, the fields in the Lookup table that contain the valid codes and descriptions need to be selected.

Since Lookups are separate tables, filters can be run on them to narrow the results. Lookups are found when entering values in filter and TouchPoint, as well as when selecting campaigns to generate reports in Analyzer.

The Lookup Menu and Toolbar

Icon

Description

Menu Equivalent

Pulls all records that meet entered conditions into the lookup window. If no conditions are set (i.e., the top half of the screen is blank), it returns every row in the lookup table.

File > Apply

Clears all the selections in the Lookup dialog.

Edit > Clear Selections

Copies the selected text to the Windows Clipboard.

Edit > Copy

Sorts the selected column in ascending order (to select a column, click the column header).

Tools > Sort Ascending

Sorts the selected column in descending order (to select a column, click the column header).

Tools > Sort Descending

Displays descriptions for codes that have picklists (available only when assigned to them by a MarketWide administrator).

Tools > Descriptions

Adds a filter.

Tools > Filter

--

Opens the Help dialog.

Help > Lookup Help

Using Lookups to Enter Values 

Like Picklists, Lookups are lists of codes and descriptions where values can be selected manually. Lookups are used when building a filter or other query in MarketWide. The primary difference between Lookups and Picklists is that Lookups are much longer lists, may be filtered, and are stored in a database table.

The Lookup dialog gives the ability to "look up" values in a database table. When activated, the Lookup dialog displays the fields in a lookup table. Because the length of a lookup table can be quite long, the Lookup dialog also allows a filter to be performed on the Lookup table. This makes the task of finding specific values easier.

For example, you might want to view all the customers in a table who have bought a toaster from your company. Assuming your table has a field called PRODUCT_NUMBER, you need to set PRODUCT_NUMBER equal to the toaster's product number in your filter. If your company sells thousands of products, you may not remember individual product numbers. In this case, you can use Lookup to find the specific toaster product number from a table containing product numbers and their corresponding descriptions.

The Lookup Dialog Filter 

Because the length of a lookup table can be quite long, the Lookup dialog also allows a filter to be performed on the Lookup table. This makes the task of finding specific values easier.

Note

As mentioned earlier, the Lookup dialog displays fields in a particular Lookup or reference table. Consequently, your MarketWide administrator must attach reference tables to fields to utilize the Lookup function. Contact your MarketWide administrator if you are unsure what fields offer the Lookup function.

To Add a Lookup

  1. Click the Lookups () icon.
  2. Right click and select Add Lookup ().
  3. Enter in a Title for the Lookup.
  4. Select the Lookup Table.
  5. Select the Code and/or Description.
  6. Chose what option On output, translate codes into: you would like for this Lookup
  7. Click OK to save your changes.
  8. After making changes to the catalog, click Save ().
  9. Once you have saved a working catalog, click Commit () and Close.

To Use a Filter in the Lookup Dialog

  1. From the Enter Value dialog in the original filter, click the Lookup button to bring up the Lookup dialog. The fields from the table are listed as columns.
  2. In the Lookup dialog, click on the Filter icon to open the Filter dialog. For the desired field, build the criteria by double-clicking a field and selecting a condition.
  3. Select the Value cell to bring up an Enter Value dialog.
  4. In the Enter Value dialog, type the desired value and click Add. Click OK to confirm your value. See Filter for more information. 
  5. Click the Apply button on the toolbar to view the results of your filter in the Lookup dialog.

To Select a Value from the Lookup Dialog

  1. In the Lookup dialog, enter a Filter to help constrain your search, if desired. 
  2. Click the Apply button on the toolbar to execute your lookup and fetch all records that match your specifications. If you do not have a filter, click on the Apply button to display all values.
  3. Within the results, click to select your values. If you want to choose more than one value (for conditions such as IN), click multiple check boxes. 
  4. The value(s) selected will be displayed in the bottom of the dialog. Click OK to add the selected value(s) to the Enter Value dialog in your original filter.

Time Saver

  • If you click Apply without entering any filter information, all records in the lookup table will appear.
  • By default, the maximum number of rows that appear in the Lookup dialog is 200. You can increase or decrease the number of rows displayed using the up and down arrows in the text box found at the top of the dialog, or by typing a new value in the text box.
  • Each value you select in the Lookup dialog appears on the status bar on the bottom of the dialog.