sales@onepager.com +1.303.779.0344

How to create a lookup table in Project Server, add values to it, and connect it to an enterprise custom field.

Lookup tables in Project Online and Project Server allow you to create a picklist of valid values that can be chosen by users of an enterprise custom field. This article will show you how to create and configure a picklist and then associate with an enterprise custom field in PWA.

Creating a Lookup Table

  1. Lookup tables are controlled in PWA (Project Web App), which is accessible via your browser:

    Log into PWA through your web browser.

    In this example, our Project Online URL is https://mycompanysite.sharepoint.com/sites/pwa/default.aspx but you'll need to change it to your real URL.

  2. Enter your username and password to log into PWA.
  3. Once logged in, click on the gear (Settings) icon in the upper-right of the PWA window, and then choose the PWA Settings option from the dropdown:

    PWA Settings.

  4. On the PWA Settings screen, locate the Enterprise Data heading, and click on Enterprise Custom Fields and Lookup Tables:

    Enterprise Custom Fields and Lookup Tables.

  5. On the Enterprise Custom Fields and Lookup Tables p[age, scroll down past the enterprise custom fields until you find the selection titled Lookup Tables for Custom Fields. If you have a lot of enterprise custom fields, you may need to scroll down quite a bit to find it:

    Lookup Tables for Custom Fields.

    Zoomed in, it looks like this:

    Lookup Tables for Custom Fields, zoomed in.

  6. To create a new lookup table, click on New Lookup Table::

    New Lookup Table button

  7. Start by giving your new lookup table a name:

    Lookup Table Name

    Your lookup table name needs to be unique, so it should be different from any other lookup tables that you already have or are planning to create.

    It's a good idea to give your lookup table a name similar to the enterprise custom field that you're planning to use it with. This way, it's easy to match lookup tables with enterprise custom fields later.

  8. Next, select a data type for your lookup table:

    Lookup Table Data Type

    Available options include Cost, Date, Duration, Number, and Text. It's important that you match the data type of your lookup table to the data type of your enterprise custom field. So, if your enterprise custom field is a Number, your lookup table also needs to be a Number.

Depending on the type of lookup table you are creating, the remaining instructions will vary. The next sections have specific instructions based on the data type of your lookup table.

Configuring a Text Lookup Table

Text lookup tables are a little more complicated because they have something called a Code Mask that allows you to specify the format of the text that appears in the lookup table. It's a useful feature, but Microsoft has made it a little confusing for casual users. With these instructions, though, you should be in good shape.

We should also note that you can skip the Code Mask completely. If you are ok with any and all text being valid, you don't need one. Code masks are good for things like phone numbers and social security numbers that have a very precise format.

  1. The Code Mask grid contains a Sequence of different strings that can be placed together. Each Sequence will have a format, length, and separator:

    Lookup Table Code Mask

  2. For example, if you want your lookup table to consist of ten-digit phone numbers in "xxx.xxx.xxxx" format, you'd do this:

    Lookup Table Code Masks for Phone Numbers

  3. If you want your lookup table to contain two-letter abbreviations for states and provinces, you'd force everything to uppercase and limit it to two letters, like this:

    Lookup Table Code Masks for US States or Canadian Provinces

  4. It's always a good idea to check the Code Preview above the Code Mask just to make sure that whatever masks you are putting in place look reasonable before you commit to them.
  5. Now, it's time to enter the values into the lookup table itself. The most important things to enter here are the Value, which is what actually goes into the Project Server database, and the Description which is more of a tooltip in case the values aren't clear in and of themselves:

    Lookup table values

    We'll continue with the example of states and provinces. Notice that Arizona (AZ) and California (CA) are two-letter abbreviations, but I've accidentally abbreviated Colorado as "COL". Project Online won't catch this immediately, but when I try to save my work, I'll get this message:

    One or more code values in the lookup table either do not match the mask defined for the code or contain more than 255 characters.

    Unfortunately, PWA doesn't tell you which lookup table value is incorrect, so you're somewhat on your own to fix the issues:

    Lookup table with corrected values

  6. While not commonly used, the Level of the lookup table allows you to nest different values in a hierarchy. Let's assume that instead of building a lookup table for State/Province, you want to create a lookup table for Country instead. There are lots of countries worldwide, so it makes sense to collapse countries by continent to make them easier to find. To do this, we list both countries and continents, and then we use the indent and outdent buttons to make countries children of their respective continents:

    Indent and outdent different levels in your lookup table.

    When you create a multi-level text field in your lookup table, you have to make sure that your Code Mask is defined for each level. In this case, the lookup table has two levels (continent and country), so the mask needs to be defined as two levels as well:

    Two-level code mask for two-level lookup table.

    If you forget to create a two-level code mask, you will get an error message when attempting to save your lookup table.

  7. Once you have finished setting everything up, click Save, and you should see your new lookup table listed.

Last Updated: March 15, 2019