Walkthrough: Creating a Hierarchical Lookup Table

05/05/2014

25 minutes to read

In this article

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

You can use Project Web Access or a custom application to create lookup tables and enterprise custom fields. Custom fields that use hierarchical text lookup tables act as outline codes in Microsoft Office Project Server 2007. If you need to create a lookup table with a large number of values, or use external data for the lookup table values, you can create an application that uses the LookupTable Web services of the Project Server Interface (PSI).

Enterprise custom fields in Project Server 2007 can share lookup tables. This article shows how to design hierarchical lookup table data structures and then create methods that use the LookupTable Web service of the PSI to return a text lookup table with code masks. The CreateLookupTable method can optionally return the GUID of a default value.

To programmatically create a hierarchical text lookup table, you can use the procedures in the following sections:

Designing Lookup Table Data Structures: Determine the data structures you will use for the lookup table.

Creating a Class for Lookup Table Utilities: Create a class with lookup table methods that you can call from other applications. Add method variables and instantiate a LookupTableDataSet.

Adding Methods to Create Code Mask and Lookup Table Tree Rows: Create utility methods that you can call multiple times to add rows for a code mask row and a lookup table tree.

Creating the LookupTableDataSet Row and Code Masks: Create a LookupTableDataSet row and add the code masks.

Adding Values and Creating the Lookup Table: Add values to the lookup table and create the lookup table in the Project Server Published database by using the CreateLookupTables method in the PSI.

Calling CreateLookupTable and Checking the Results: Use sample data to call the custom CreateLookupTable method and then check the results with Project Web Access.

The procedures in this article use Microsoft Visual C# and Microsoft Visual Studio 2005.

Note

If you use CreateLookupTable to create a non-text lookup table such as type Cost, Date, Duration, or Number, you still need to programmatically create a code mask. Project Web Access hides the code mask for non-text lookup tables in the New Lookup Table page, but creates a code mask for the call to CreateLookupTables. For an example that creates a Duration lookup table, see CreateLookupTablesMultiLang.

Creating a Lookup Table

There are many ways to approach designing methods that create lookup tables for Project Server. The procedures in this article show one way to create text lookup tables that can help when you design custom methods for your organization.

Designing Lookup Table Data Structures

You can organize a hierarchical text lookup table into three main data structures, as follows:

Text values and descriptions that are organized in levels

A code mask that specifies the sequence or type of data for each level

The number of elements and separator character for each level in the code mask

The following procedures show the steps to create a class and methods that use the three data structures. To view the entire code sample, see Complete Code Example.

Note

The WebSvcLookupTable namespace in the code examples is an arbitrary name for a reference to the LookupTable Web service in the PSI. For more information, see Namespaces in the PSI.

Procedure 1. To determine the lookup table data structures:

Write a short sample of the lookup table that you want. For example, the following lookup table can act as a location outline code that contains three levels of text for state, county, and city.

CA

CA.Santa Cruz

CA.Santa Cruz.Ben Lomond

WA

WA.King

WA.King.Bellevue

WA.King.Redmond

WA.King.Seattle

Create a sample data structure in code that includes the value, description, and level of each item in the hierarchy. The following example includes a fourth field that specifies which item should be the default value.

You can change the order of rows within each level by specifying the sort order when you create the lookup table. However, the sub-levels must be directly under the correct parent. That is, Washington state is level 0, King county is level 1 under Washington, and Redmond and Bellevue are level 2 under King county. The default field can contain any string, including an empty string; specify a unique value for the default row.

Create the code mask sequence structure. For example, the sample lookup table contains uppercase characters in level 0 and mixed uppercase and lowercase characters in levels 1 and 2. The Microsoft.Office.Project.Server.Library namespace includes a MaskSequence enumeration for the sequence types you can specify. The following example shows a reference data structure for the code mask sequence of the sample lookup table.

Create a data structure for the number of characters and separator character in each level of the code mask. The method that creates the code mask rows can change a string to an integer number or use an enumeration to get the correct value for "any." In the sample lookup table, the state (level 0) is limited to two characters; the county and city can contain any number of characters.

The maskSequence and textMaskValues structures must contain the same number of rows and match the number of levels in the hierarchy—three rows for three levels (0, 1, and 2) in the sample lookup table.

Add the sample data structures to any application that you want to use for testing the class and methods you develop to create lookup tables and custom fields. For example, you can add a button and click event handler to the application described in How to: Log on to Project Server Programmatically.

After you design the data structures and add them to a test application, create a class with methods that use the data to create lookup tables. The LookupTable class contains the CreateLookupTable PSI method. The method uses a LookupTableDataSet that contains the following three tables to which you add data using your data structures.

Creating a Class for Lookup Table Utilities

Procedure 2 describes a class with a CreateLookupTable method that accepts the data structures for the sample lookup table.

Procedure 2. To create a class with lookup table methods:

In the Visual Studio Solution Explorer, add a class to your application, for example CustomFieldUtils.

Copy the Microsoft.Office.Project.Server.Library.dll assembly from the directory C:\Program Files\Microsoft Office Servers\12.0\Bin on the Project Server computer to your development computer. Add a reference to the assembly.

Add the class constructor and necessary references, as in the following example. Your namespace will be different.

Add an outline of class methods to create a lookup table. You can factor the methods in several ways. For example, create a single public CreateLookupTable method with parameters that accept the data structures for the sample lookup table. CreateLookupTable in turn calls private utility methods that create a mask row and lookup table row.

The CreateLookupTable sample method returns the GUID of the lookup table and takes the following parameters:

lookupTable is an instance of the LookupTable class, where WebSvcLookupTable is the arbitrary name for a Web reference to the LookupTable Web service.

ltName is the name of the lookup table.

maskSequence takes the data structure for the code mask sequence that you defined in Procedure 1, such as UPPERCASE or CHARACTERS.

maskValues takes the data structure for the code mask values for number of characters and separator in each level of the mask.

ltRowDefaultUid returns a default value GUID to the caller. This can be used if you want to create the lookup table and custom field in the same application, but it is not necessary to create a lookup table.

Add the main method variables and validation checks to CreateLookupTable. The lookupTableDataSet variable contains the code masks and lookup table rows that the CreateLookupTables PSI Web method uses. The CreateLookupTable example is designed to handle a maximum of five code mask levels. In the following example, validation checks are minimal: the method returns empty GUIDs if the number of levels in a code mask exceeds five or if the number of sequences does not match the number of rows in the code mask values.

Create a private method that sets values for each code mask row, using data for the specified lookup table, sequence, length, and separator. For example, the following CreateLookupTableMasksRow method includes the necessary parameters to create one code mask row.

There is only one lookup table that ltDataSet defines. The LT_UID of the mask row must be for the defined lookup table. In the sample data, the code mask for level 0 has a sequence of UPPERCASE, a length of two characters, and a "." separator.

Add a private method that sets values for each row in the lookup table tree, using data for the specified lookup table, parent node GUID, row GUID, and the node value and description. For example, the following AddLookupTableValues method includes the necessary parameters to create one lookup table tree row.

If a lookup table tree node is at the top level, it has no parent node. In the sample data, Washington has no parent node so the parentUid must be set to null. The parent of Redmond is the GUID for King county and the parent of King county is the GUID for Washington.

Creating the LookupTableDataSet Row and Code Masks

The first step to actually create a lookup table is to create a row in the LookupTableDataSet, which becomes LookupTablesRow[0] in LookupTables. You can then add the code mask data as one LookupTableMasksRow for each code mask level in the LookupTableMasksDataTable.

The LookupTableTreesRow.LT_VALUE_SORT_INDEX is a decimal value that specifies the sort order in a lookup table. Project Server calculates LT_VALUE_SORT_INDEX when you set LT_SORT_ORDER_ENUM to the LookupTables.SortOrder value of Ascending or Descending, and then programmatically add the row to the LookupTableDataSet. In Project Web Access, you can achieve the same result when you click Sort ascending or Sort descending on the Edit Lookup Table page.

Note

If you try to programmatically add a row to the lookup table without specifying Ascending or Descending sort order, or set the UserDefined sort order without setting LT_VALUE_SORT_INDEX, you get the SOAP exception LookupTableSortOrderIndexInvalid Instructions. You can set a unique value for LT_VALUE_SORT_INDEX of a lookup table node as long as you first specify the parent sort order. If the LT_VALUE_SORT_INDEX value is incorrect, you get error 11054 (invalid lookup table sort order index).

Procedure 4. To create a LookupTableDataSet row and add the code masks:

In the CreateLookupTable method, add the code to create a lookup table row. After you instantiate the lookupTableRow object, assign a GUID to the LT_UID property and also save the GUID to returnIds, to return when the method is complete. You can also set the sort order. Add lookupTableRow to the lookupTableDataSet object.

To create the code mask, first create a LookupTableMasksDataTable that contains one LookupTableMasksRow. Then iterate through the code mask levels, call the utility method CreateLookupTableMasksRow you created in Adding Methods to Create Code Mask and Lookup Table Tree Rows, and add each ltMasksRow to the LookupTableMasks table in the lookupTableDataSet object. In the sample code, the CreateLookupTableMasksRow parameters and values for the third-level code mask row (the mask for cities such as Redmond) are as follows:

Add exception handlers as necessary for your application. For example, add a message box that includes ex.Message for a Windows application or write the information to the console for a Console application.

Adding Values and Creating the Lookup Table

After you create the code mask, you can add the lookup table values for each lookup table row and then call the PSI CreateLookupTables method with the LookupTableDataSet to create the lookup table.

Procedure 5. To add values and create the lookup table:

Add the lookup table values. The following code example does not show the try … catch block. The code example performs five actions:

Iterates through each row of lookup table data in ltValues

Calls AddLookupTableValues to add the row values

Sets the parent GUID of the next row, for the next iteration. The node does not have a parent if it is at the base level. Each row can potentially be a parent for an upcoming sub-node, until the sub-node level is reset to a lower level. In the ltValues sample data, for example, nodes below King county can be children of King county until the lower level California is processed.

Checks if the row is the default; if so, saves the row GUID to return

Adds the row to the LookupTableTrees table in the lookupTableDataSet object.

To see how the indexDiff, parentIndex, and parenUid variables change, set a breakpoint in the iteration and step through it.

At the end of the CreateLookupTable method, create the lookup table using the PSI and return the lookup table GUID and the ltRowDefaultUid out parameter. If there are errors, return empty GUIDs for the lookup table and the default node.

The lookupTable variable is an instance of the LookupTable class that your application passes to CreateLookupTable.

Add the Url property, and then add the Windows credentials or the Project Server forms logon cookie to the lookupTable object. The following code shows lines added to the AddContextInfo method in the LogonProjectServer.cs sample, in How to: Log on to Project Server Programmatically.

If your data is valid and the CreateLookupTable method completes without errors, you can see the new lookup table using Project Web Access. Browse to the Custom Fields page in Project Web access (http://ServerName/ProjectServerName/_layouts/PWA/Admin/CustomizeFields.aspx), and click the name of the lookup table you created. For an example in Project Web Access that is similar to the sample data, see Figure 2 in Local and Enterprise Custom Fields.

If the lookup table does not match your expectations, check the input data.

Note

A typical error in the input data is that the LT_VALUE_TEXT property in a lookup table tree node contains a separator character for that level.

There are many additional checks you could add in the CreateLookupTable code to validate the input data before sending it to the PSI. Some checks you could add follow:

Check that no separator character exists in the LT_VALUE_TEXT property for that level.

Check that the number of characters in the LT_VALUE_TEXT property matches the number specified in the code mask value for that level.

Check that there is only one default value.

If the input data is correct, set breakpoints in key areas and watch for a specific variable value, and then step through the code in the Visual Studio debugger. For example, set a breakpoint at the line that calls CreateLookupTables. When you run the application, check the contents of the lookupTableDataSet parameter: click the looking glass icon when you pause the mouse pointer over the parameter to see the DataSet Visualizer dialog box, and then check the contents of the three tables in the LookupTableDataSet. The following table shows part of LookupTableTrees with GUIDs of the tree nodes of the sample data in ltValues. The LT_UID is the same for all nodes because they are all for the same lookup table. The top-level nodes have no LT_PARENT_STRUCT_UID value; the LT_PARENT_STRUCT_UID value of each sub-node matches the LT_STRUCT_UID of the correct parent. Of course, the GUIDs are different each time you run the application.

LT_STRUCT_UID

LT_UID

LT_PARENT_STRUCT_UID

LT_VALUE_TEXT

9c93eb45-9fbd-4297-b3da-22e08d0bad18

a84bc74f-a1aa-4232-9bfa-ccdaf3ba94fa

WA

91fb9d56-c008-4541-a2ba-19837ff64da8

a84bc74f-a1aa-4232-9bfa-ccdaf3ba94fa

9c93eb45-9fbd-4297-b3da-22e08d0bad18

King

ab0a6b0e-73bb-4e47-bd57-bdf9dd8fd2be

a84bc74f-a1aa-4232-9bfa-ccdaf3ba94fa

91fb9d56-c008-4541-a2ba-19837ff64da8

Bellevue

f34e5cc1-b310-424e-8448-0a72f86e496b

a84bc74f-a1aa-4232-9bfa-ccdaf3ba94fa

91fb9d56-c008-4541-a2ba-19837ff64da8

Redmond

e1c229ef-5e59-4b21-ba39-45d442725c54

a84bc74f-a1aa-4232-9bfa-ccdaf3ba94fa

91fb9d56-c008-4541-a2ba-19837ff64da8

Seattle

092b2d37-6506-4df7-8a79-ed6fdb2a6212

a84bc74f-a1aa-4232-9bfa-ccdaf3ba94fa

9c93eb45-9fbd-4297-b3da-22e08d0bad18

Snohomish

35ced8fa-34e9-46f6-aa95-0a3ff8a074de

a84bc74f-a1aa-4232-9bfa-ccdaf3ba94fa

092b2d37-6506-4df7-8a79-ed6fdb2a6212

Snohomish

cbdc2754-360b-41a9-b8cb-8270f1bb4d0d

a84bc74f-a1aa-4232-9bfa-ccdaf3ba94fa

CA

9ab221d9-a268-495b-91d8-464d0b8313ab

a84bc74f-a1aa-4232-9bfa-ccdaf3ba94fa

cbdc2754-360b-41a9-b8cb-8270f1bb4d0d

Santa Cruz

ce4e91f7-5592-4913-a48c-c36540d745c0

a84bc74f-a1aa-4232-9bfa-ccdaf3ba94fa

9ab221d9-a268-495b-91d8-464d0b8313ab

Ben Lomond

Complete Code Example

Following is the code for the CustomFieldUtils class. Procedures 2 through 5 in the previous sections explain how to create the class, the public CreateLookupTable method, and the private CreateLookupTableMasksRow and AddLookupTableValues methods. Calling CreateLookupTable and Checking the Results shows how to call the CreateLookupTable method.