Creating a Lookup Table
- Lookup tables are controlled in PWA (Project Web App), which is accessible via your 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.
- Enter your username and password to log into PWA.
- 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:
- On the PWA Settings screen, locate the Enterprise Data heading, and click on Enterprise Custom Fields and Lookup Tables:
- 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:
Zoomed in, it looks like this:
- To create a new lookup table, click on New Lookup Table::
- Start by giving your new lookup table a 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.
- Next, select a data type for your lookup table:
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.
- The Code Mask grid contains a Sequence of different strings that can be placed together. Each Sequence will have a format, length, and separator:
- For example, if you want your lookup table to consist of ten-digit phone numbers in "xxx.xxx.xxxx" format, you'd do this:
- 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:
- 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.
- 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:
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:
Unfortunately, PWA doesn't tell you which lookup table value is incorrect, so you're somewhat on your own to fix the issues:
- 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:
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:
If you forget to create a two-level code mask, you will get an error message when attempting to save your lookup table.
- Once you have finished setting everything up, click Save, and you should see your new lookup table listed.
Last Updated: March 15, 2019