Walkthrough: Creating a Hierarchical Lookup Table

  • 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.

For general information about custom fields and lookup tables, see Local and Enterprise Custom Fields. For sample code that creates a custom field, see How to: Create an Enterprise Custom Field. For an example and information about updating lookup tables, see UpdateLookupTables. For information about the LookupTable class methods, see LookupTable.

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

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

  2. 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.

  3. 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.

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

  5. 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.

  6. 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.

NoteNote

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:

  1. 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

  2. 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.

    string[,] ltValues = 
{
    {"WA", "Washington state", "0", "0"},
    {"King", "King county", "1", ""},
    {"Bellevue", "Sales office", "2", "0"},
    {"Redmond", "Product development", "2", "default"},
    {"Seattle", "Company headquarters", "2", "0"},
    {"CA", "California", "0", "0"},
    {"Santa Cruz", "Santa Cruz County", "1", "0"},
    {"Ben Lomond", "Remote worksite", "2", "0"}
};

    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.

  3. 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.

    using PSLibrary = Microsoft.Office.Project.Server.Library;
. . .
byte[] maskSequence =  
{
    (byte) PSLibrary.LookupTables.MaskSequence.UPPERCASE,
    (byte) PSLibrary.LookupTables.MaskSequence.CHARACTERS,
    (byte) PSLibrary.LookupTables.MaskSequence.CHARACTERS
};

  4. 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.

    string[,] textMaskValues = 
    {
        {"2", "."},
        {"any", "."},
        {"any", "."}
    };
    NoteNote

    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.

  5. 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:

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

  2. 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.

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

    using System;
using System.Data;
using System.Net;
using System.Xml;
using System.IO;
using System.Collections;
using System.Web.Services.Protocols;
using PSLibrary = Microsoft.Office.Project.Server.Library;

namespace SomeNamespace
{
    class CustomFieldUtils
    {
        public CustomFieldUtils()
        {
        }
        /* Class methods */
    }
}

  4. 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.

    public Guid CreateLookupTable(
    WebSvcLookupTable.LookupTable lookupTable, 
    string ltName, 
    byte[] maskSequence,
    string[,] maskValues,
    string[,] ltValues,
    out Guid ltRowDefaultUid)
{
}
#region Lookup table utility methods
// Create a lookup table code mask row
. . . 
// Add a value to the lookup table row
. . .
#endregion

  5. 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.

    const string ANY = "any";
const string DEFAULT = "default";

#region Method variables
int levelLength;
string sLevelLength;
bool error = false;

// Save the return GUIDS for lookup table and default value (if any)
Guid[] returnIds = new Guid[2]; 
// There are 4 strings for each lookup table row       
int numLtRows = ltValues.Length / 4;
// There are 2 strings for each mask level
int maxLevels = maskValues.Length / 2; 

if (maxLevels != maskSequence.Length || maxLevels > 5)
{
    // The number of rows in the maskSequence and maskValues arrays must be the same.
    //        The hierarchical levels can't be more than 5 levels deep.
    ltRowDefaultUid = Guid.Empty;
    return Guid.Empty;
}
int[] parentIndex = new int[numLtRows]; // Index of each lookup table row parent
Guid[] parentUid = new Guid[numLtRows];  // Parent GUIDs of lookup table tree rows

for (int i = 0; i < numLtRows; i++)
{
    parentIndex[i] = Convert.ToInt32(ltValues[i, 2]);
}

WebSvcLookupTable.LookupTableDataSet lookupTableDataSet =
    new WebSvcLookupTable.LookupTableDataSet();
#endregion

Adding Methods to Create Code Mask and Lookup Table Tree Rows

The public method CreateLookupTable can call private utility methods to create each row in the code mask and each tree node in the lookup table.

Procedure 3. To create utility methods that add a code mask row and a lookup table tree row:

  1. 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.

    #region Lookup table utilities
// Create a lookup table code mask row
private WebSvcLookupTable.LookupTableDataSet.LookupTableMasksRow CreateLookupTableMasksRow(
    WebSvcLookupTable.LookupTableDataSet ltDataSet,
    int structLevel,
    byte maskSequence,
    int levelLength,
    string separator)
{
    WebSvcLookupTable.LookupTableDataSet.LookupTableMasksRow ltMaskRow =
        ltDataSet.LookupTableMasks.NewLookupTableMasksRow();

    ltMaskRow.LT_UID = ltDataSet.LookupTables[0].LT_UID;
    ltMaskRow.LT_MASK_STRUCT_LEVEL = structLevel;
    ltMaskRow.LT_MASK_STRUCT_TYPE_ENUM = maskSequence;
    ltMaskRow.LT_MASK_STRUCT_LENGTH = levelLength;
    ltMaskRow.LT_MASK_VALUE_SEPARATOR = separator;

    return ltMaskRow;
}

  2. 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.

    // Add a value to the lookup table row
private WebSvcLookupTable.LookupTableDataSet.LookupTableTreesRow AddLookupTableValues(
    WebSvcLookupTable.LookupTableDataSet ltDataSet,
    Guid parentUid,
    Guid rowUid,
    string ltValue,
    string ltDescription)
{
    WebSvcLookupTable.LookupTableDataSet.LookupTableTreesRow ltTreeRow =
        ltDataSet.LookupTableTrees.NewLookupTableTreesRow();

    ltTreeRow.LT_UID = ltDataSet.LookupTables[0].LT_UID;
    ltTreeRow.LT_STRUCT_UID = rowUid;

    if (parentUid == Guid.Empty)
        ltTreeRow.SetLT_PARENT_STRUCT_UIDNull();
    else
        ltTreeRow.LT_PARENT_STRUCT_UID = parentUid;

    ltTreeRow.LT_VALUE_TEXT = ltValue;
    ltTreeRow.LT_VALUE_DESC = ltDescription;

    return ltTreeRow;
}
#endregion

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.

NoteNote

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:

  1. 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.

    #region Create a lookup table row with code masks
try
{
    //Create a lookup table row from the LookupTableDataSet instance
    WebSvcLookupTable.LookupTableDataSet.LookupTablesRow lookupTableRow =
        lookupTableDataSet.LookupTables.NewLookupTablesRow();

    Guid lookupTableGuid = Guid.NewGuid();
    // Set the lookup table ID 
    returnIds[0] = lookupTableGuid;

    lookupTableRow.LT_UID = lookupTableGuid;
    lookupTableRow.LT_NAME = ltName;
    lookupTableRow.LT_SORT_ORDER_ENUM =
        (byte)PSLibrary.LookupTables.SortOrder.Ascending;
    lookupTableDataSet.LookupTables.Rows.Add(lookupTableRow);

  2. 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:

    • ltDataSet: contains the GUID of the lookup table

    • structLevel: 3

    • maskSequence: CHARACTERS enumeration

    • levelLength: ANY_LENGTH_SEQUENCE enumeration

    • separator: "."

        // Create the code mask and rows
    WebSvcLookupTable.LookupTableDataSet.LookupTableMasksDataTable masksDataTable = 
        new LoginDemo.WebSvcLookupTable.LookupTableDataSet.LookupTableMasksDataTable();

    WebSvcLookupTable.LookupTableDataSet.LookupTableMasksRow ltMasksRow =
        masksDataTable.NewLookupTableMasksRow();

    for (int level = 0; level < maxLevels; level++)
    {
        sLevelLength = maskValues[level, 0];
        if (string.Compare(sLevelLength, ANY, true) == 0)
            levelLength = PSLibrary.LookupTables.ANY_LENGTH_SEQUENCE;
        else
            levelLength = Convert.ToInt32(maskValues[level, 0]);

        ltMasksRow = CreateLookupTableMasksRow(
            lookupTableDataSet,
            level + 1,
            maskSequence[level],
            levelLength,
            maskValues[level, 1]);
        lookupTableDataSet.LookupTableMasks.Rows.Add(ltMasksRow);
    }
}
catch (DataException ex)
{
    // Add exception handler for ex
    error = true;
}
catch (SoapException ex)
{
    // Add exception handler for ex
    error = true;
}
#endregion

  3. 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:

  1. 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.

    WebSvcLookupTable.LookupTableDataSet.LookupTableTreesRow ltTreeRow =
    lookupTableDataSet.LookupTableTrees.NewLookupTableTreesRow();

int thisNode;
int nextNode;
int indexDiff;               // Difference in levels between nodes 
int rowLevel;                // Level of the current row
Guid rowUid;                 // GUID of the current level
Guid[] previousLevelUid = new Guid[5];  // GUIDs of up to five previous levels
                             //     [0]: level 1; ... ; [4]: level 5
parentUid[0] = Guid.Empty;   // Initialize the first parentUid

for (int row = 0; row < numLtRows; row++)
{
    rowUid = Guid.NewGuid();
    thisNode = row;
    nextNode = thisNode + 1;
    rowLevel = parentIndex[row];
    previousLevelUid[rowLevel] = rowUid;  // Reset the previous level

    ltTreeRow = AddLookupTableValues(
        lookupTableDataSet,
        parentUid[row],        // Parent GUID
        rowUid,                // Current row GUID 
        ltValues[row, 0],      // Value
        ltValues[row, 1]       // Description
     );
    // Set the parentUid of the next node.
    if (row < numLtRows - 1)
    {
        if (parentIndex[nextNode] == 0)
        {
            parentUid[nextNode] = Guid.Empty;
        }
        else
        {
            indexDiff = parentIndex[nextNode] - parentIndex[thisNode];
            switch (indexDiff)
            {
                case 1:
                    parentUid[nextNode] = rowUid;
                    break;
                case 0:
                    parentUid[nextNode] = parentUid[thisNode];
                    break;
                case -1:
                case -2:
                case -3:
                    indexDiff -= 1;
                    parentUid[nextNode] = previousLevelUid[rowLevel + indexDiff];
                    break;
            }
        }
    }
    // Check for the default GUID
    if (ltValues[row, 3] == DEFAULT)
        returnIds[1] = rowUid;
    lookupTableDataSet.LookupTableTrees.Rows.Add(ltTreeRow);
}

  2. 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.

    #region Create and return the lookup table
try
{
    if (!error)
    {
        bool validateOnly = false;
        bool autoCheckIn = true;
        lookupTable.CreateLookupTables(lookupTableDataSet, 
            validateOnly, autoCheckIn);
    }
}
catch (SoapException ex)
{
    string errMess = "";
    // Pass the exception to the PSClientError constructor to get 
    // all error information.
    PSLibrary.PSClientError psiError = new PSLibrary.PSClientError(ex);
    PSLibrary.PSErrorInfo[] psiErrors = psiError.GetAllErrors();

    for (int j = 0; j < psiErrors.Length; j++)
    {
        errMess += psiErrors[j].ErrId.ToString() + "\n";
    }
    errMess += "\n" + ex.Message.ToString();
    // Send error string to console or message box.

    error = true;
}
if (error)
{
    returnIds[0] = Guid.Empty;
    returnIds[1] = Guid.Empty;
}
ltRowDefaultUid = returnIds[1];
return returnIds[0];
#endregion
    NoteNote

    If the LT_VALUE_TEXT property of a lookup table tree node contains a character that matches the separator character for that level, the PSI returns a SoapException.

Calling CreateLookupTable and Checking the Results

After you add the CustomFieldUtils class to your application, test the CreateLookupTable method with sample data and then check the results.

Procedure 6. To call the CreateLookupTable method:

  1. Add the following references to the calling application:

    using System.IO;
using System.Collections;
using PSLibrary = Microsoft.Office.Project.Server.Library;

  2. Set class constants and variables for the Web service and the results of the CreateLookupTable method, for example:

    private const string LOOKUPTABLEWEBSERVICE = "_vti_bin/PSI/LookupTable.asmx";
private string baseUrl; // Example: http://ServerName/ProjectServerName/
private Guid lookupTableUid;
private Guid ltRowDefaultUid;

  3. Create a Web reference to the LookupTable Web service, and then instantiate the LookupTable class. For example, name the Web service namespace WebSvcLookupTable.

    public static WebSvcLookupTable.LookupTable lookupTable =
    new WebSvcLookupTable.LookupTable();
private static CustomFieldUtils customFieldUtils = 
    new CustomFieldUtils();

  4. 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.

    lookupTable.Url = baseUrl + LOOKUPTABLEWEBSERVICE;

if (winLogon)   // Add Windows credentials
{
    lookupTable.Credentials = CredentialCache.DefaultCredentials;
}
else            // Add Project Server logon cookie for Forms logon
{
    lookupTable.CookieContainer = loginUtils.Cookies;
}

  5. Add a test method to the application. For example, in a Windows application add a button with a click event method handler.

    private void btnCreateLookupTable_Click(object sender, EventArgs e)
{
    // Create a text lookup table named "Location" with the following structure:
    //     CA
    //     CA.Santa Cruz
    //     CA.Santa Cruz.Ben Lomond
    //     WA
    //     WA.King
    //     WA.King.Bellevue
    //     WA.King.Redmond
    //     WA.King.Seattle
    //     WA.Snohomish
    //     WA.Snohomish.Snohomish
}

  6. Add the test lookup table values to an array. Read the values from a database or an XML file, or simply add values to an array as follows.

        // Lookup table values array:
    //    Name
    //    Description
    //    Level in the hierarchy: 0 = top; 1 = child of previous top level, 
    //             2 = child of the previous level 1.
    //    Default: "default". The last default value in the list is used.
    //             CreateLookupTable returns the default row GUID
    //             for use in creating a custom field.
    string[,] ltValues = 
    {
        {"WA", "Washington State", "0", "0"},
        {"King", "King County", "1", ""},
        {"Bellevue", "Sales Office", "2", "0"},
        {"Redmond", "Product Development", "2", "default"},
        {"Seattle", "Company Headquarters", "2", "0"},
        {"Snohomish", "Snohomish County", "1", "0"},
        {"Snohomish", "R&R Center", "2", "0"},
        {"CA", "California", "0", "0"},
        {"Santa Cruz", "Santa Cruz County", "1", "0"},
        {"Ben Lomond", "Remote worksite", "2", "0"}
    };

  7. Add the code mask data structures that CreateLookupTable requires, as in the following example:

        byte[] maskSequence =  // Lookup table code mask character sequence
    {
        (byte) PSLibrary.LookupTables.MaskSequence.UPPERCASE,
        (byte) PSLibrary.LookupTables.MaskSequence.CHARACTERS,
        (byte) PSLibrary.LookupTables.MaskSequence.CHARACTERS
    };

    // Mask values array:
    //    Number of characters: specify value or "any"
    //    Separator character
    // Number of rows must equal the number of elements in maskSequence.
    string[,] textMaskValues = 
        {
            {"2", "."},
            {"any", "."},
            {"any", "."}
        };

  8. Set the name you want for the lookup table, and then call CreateLookupTable.

        string ltName = "Location";
    lookupTableUid = customFieldUtils.CreateLookupTable(
        lookupTable, 
        ltName, 
        maskSequence,
        textMaskValues,
        ltValues,
        out ltRowDefaultUid);

  9. Run the test application.

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.

NoteNote

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.

using System;
using System.Data;
using System.Net;
using System.Xml;
using System.IO;
using System.Collections;
using System.Web.Services.Protocols;
using PSLibrary = Microsoft.Office.Project.Server.Library;

namespace SomeNamespace
{
    class CustomFieldUtils
    {
        public CustomFieldUtils()
        {
        }

        /// <summary>
        /// Create a hierarchical text lookup table, with code masks and values.
        /// </summary>
        /// <param name="lookupTable">WebSvcLookupTable.LookupTable object</param>
        /// <param name="ltName">Name of the lookup table</param>
        /// <param name="maskSequence">Array of code mask sequences and separator characters</param>
        /// <param name="maskValues"></param>
        /// <param name="ltValues">Array of lookup table values: Name, description, level, default</param>
        /// <param name="ltRowDefaultUid">GUID of default value (out)</param>
        /// <returns>GUID of lookup table</returns>
        public Guid CreateLookupTable(
            WebSvcLookupTable.LookupTable lookupTable, 
            string ltName, 
            byte[] maskSequence,
            string[,] maskValues,
            string[,] ltValues,
            out Guid ltRowDefaultUid)
        {
            // In addition to the Method variables region, there are three regions 
            // with a try-catch block: 
            //     1. Create lookup table rows with code masks in a LookupTableDataSet
            //     2. Add herarchical values to each row in the lookup table
            //     3. Call CreateLookupTables with the LookupTableDataSet
        
            const string ANY = "any";
            const string DEFAULT = "default";

            #region Method variables
            int levelLength;
            string sLevelLength;
            bool error = false;

            // Save the return GUIDS for lookup table and default value (if any)
            Guid[] returnIds = new Guid[2];        
            int numLtRows = ltValues.Length / 4;   // There are 4 strings for each lookup table row
            int maxLevels = maskValues.Length / 2; // There are 2 strings for each mask level

            if (maxLevels != maskSequence.Length || maxLevels > 5)
            {
                // Error: The number of rows in the maskSequence and maskValues arrays must be the same.
                //        The hierarchical levels can't be more than five levels deep.
                ltRowDefaultUid = Guid.Empty;
                return Guid.Empty;
            }
            int[] parentIndex = new int[numLtRows]; // Index of each lookup table row parent
            Guid[] parentUid = new Guid[numLtRows];  // Parent GUIDs of lookup table tree rows

            for (int i = 0; i < numLtRows; i++)
            {
                parentIndex[i] = Convert.ToInt32(ltValues[i, 2]);
            }
            
            WebSvcLookupTable.LookupTableDataSet lookupTableDataSet =
                new WebSvcLookupTable.LookupTableDataSet();
            #endregion

            #region Create a lookup table row with code masks
            try
            {
                //Create a lookup table row from the LookupTableDataSet instance
                WebSvcLookupTable.LookupTableDataSet.LookupTablesRow lookupTableRow =
                    lookupTableDataSet.LookupTables.NewLookupTablesRow();
               
                Guid lookupTableGuid = Guid.NewGuid();
                // Set the lookup table ID 
                returnIds[0] = lookupTableGuid;

                lookupTableRow.LT_UID = lookupTableGuid;
                lookupTableRow.LT_NAME = ltName;
                lookupTableRow.LT_SORT_ORDER_ENUM =
                    (byte)PSLibrary.LookupTables.SortOrder.Ascending;

                lookupTableDataSet.LookupTables.Rows.Add(lookupTableRow);

                // Create the code mask and rows
                WebSvcLookupTable.LookupTableDataSet.LookupTableMasksDataTable masksDataTable = 
                    new LoginDemo.WebSvcLookupTable.LookupTableDataSet.LookupTableMasksDataTable();

                WebSvcLookupTable.LookupTableDataSet.LookupTableMasksRow ltMasksRow =
                    masksDataTable.NewLookupTableMasksRow();

                for (int level = 0; level < maxLevels; level++)
                {
                    sLevelLength = maskValues[level, 0];
                    if (string.Compare(sLevelLength, ANY, true) == 0)
                        levelLength = PSLibrary.LookupTables.ANY_LENGTH_SEQUENCE;
                    else
                        levelLength = Convert.ToInt32(maskValues[level, 0]);
                    
                    ltMasksRow = CreateLookupTableMasksRow(
                        lookupTableDataSet,
                        level + 1,
                        maskSequence[level],
                        levelLength,
                        maskValues[level, 1]);
                    lookupTableDataSet.LookupTableMasks.Rows.Add(ltMasksRow);
                }
            }
            catch (DataException ex)
            {
                // Add exception handler for ex
                error = true;
            }
            catch (SoapException ex)
            {
                // Add exception handler for ex
                error = true;
            }
            #endregion
            
            #region Add values to each row
            // Add the lookup table values
            try
            {
                WebSvcLookupTable.LookupTableDataSet.LookupTableTreesRow ltTreeRow =
                    lookupTableDataSet.LookupTableTrees.NewLookupTableTreesRow();
                
                if (!error)
                {
                    int thisNode;
                    int nextNode;
                    int indexDiff;                       // Difference in levels between nodes 
                    int rowLevel;                        // Level of the current row
                    Guid rowUid;                         // GUID of the current level
                    Guid[] previousLevelUid = new Guid[4];  // GUIDs of up to five previous levels
                                                         //     [0]: level 1; ... ; [4]: level 5
                    parentUid[0] = Guid.Empty;           // Initialize the first parentUid
                    
                    for (int row = 0; row < numLtRows; row++)
                    {
                        rowUid = Guid.NewGuid();
                        thisNode = row;
                        nextNode = thisNode + 1;
                        rowLevel = parentIndex[row];
                        previousLevelUid[rowLevel] = rowUid;  // Reset the previous level

                        ltTreeRow = AddLookupTableValues(
                            lookupTableDataSet,
                            parentUid[row],        // Parent GUID
                            rowUid,                // Current row GUID 
                            ltValues[row, 0],      // Value
                            ltValues[row, 1]       // Description
                         );
                        // Set the parentUid of the next node.
                        if (row < numLtRows - 1)
                        {
                            if (parentIndex[nextNode] == 0)
                            {
                                parentUid[nextNode] = Guid.Empty;
                            }
                            else
                            {
                                indexDiff = parentIndex[nextNode] - parentIndex[thisNode];
                                switch (indexDiff)
                                {
                                    case 1:
                                        parentUid[nextNode] = rowUid;
                                        break;

                                    case 0:
                                        parentUid[nextNode] = parentUid[thisNode];
                                        break;

                                    case -1:
                                    case -2:
                                    case -3:
                                        indexDiff -= 1;
                                        parentUid[nextNode] = previousLevelUid[rowLevel + indexDiff];
                                        break;
                                }
                            }
                        }
                        // Check for the default GUID
                        if (ltValues[row, 3] == DEFAULT)
                            returnIds[1] = rowUid;
                        lookupTableDataSet.LookupTableTrees.Rows.Add(ltTreeRow);
                    }
                }
            }
            catch (SoapException ex)
            {
                // Add exception handler for ex
                error = true;
            }
            catch (Exception ex)
            {
                // Add exception handler for ex
                error = true;
            }
            #endregion

            #region Create and return the lookup table
            try
            {
                if (!error)
                {
                    bool validateOnly = false;
                    bool autoCheckIn = true;
                    lookupTable.CreateLookupTables(lookupTableDataSet, 
                        validateOnly, autoCheckIn);
                }
            }
            catch (SoapException ex)
            {
                string errMess = "";
                // Pass the exception to the PSClientError constructor to get 
                // all error information.
                PSLibrary.PSClientError psiError = new PSLibrary.PSClientError(ex);
                PSLibrary.PSErrorInfo[] psiErrors = psiError.GetAllErrors();

                for (int j = 0; j < psiErrors.Length; j++)
                {
                    errMess += psiErrors[j].ErrId.ToString() + "\n";
                }
                errMess += "\n" + ex.Message.ToString();
                // Send error string to console or message box.

                error = true;
            }
            if (error)
            {
                returnIds[0] = Guid.Empty;
                returnIds[1] = Guid.Empty;
            }
            ltRowDefaultUid = returnIds[1];
            return returnIds[0];
            #endregion
        }

        #region Lookup table utilities

        /// <summary>
        /// Create a lookup table code mask row
        /// </summary>
        /// <param name="ltDataSet">WebSvcLookupTable.LookupTableDataSet object</param>
        /// <param name="structLevel">Level of the code mask</param>
        /// <param name="maskSequence">Mask sequence type</param>
        /// <param name="levelLength">Number of characters in this level</param>
        /// <param name="separator">Code mask separator character for this level</param>
        /// <returns>LookupTableMasksRow</returns>
        private WebSvcLookupTable.LookupTableDataSet.LookupTableMasksRow CreateLookupTableMasksRow(
            WebSvcLookupTable.LookupTableDataSet ltDataSet,
            int structLevel,
            byte maskSequence,
            int levelLength,
            string separator)
        {
            WebSvcLookupTable.LookupTableDataSet.LookupTableMasksRow ltMaskRow =
                ltDataSet.LookupTableMasks.NewLookupTableMasksRow();

            ltMaskRow.LT_UID = ltDataSet.LookupTables[0].LT_UID;
            ltMaskRow.LT_MASK_STRUCT_LEVEL = structLevel;
            ltMaskRow.LT_MASK_STRUCT_TYPE_ENUM = maskSequence;
            ltMaskRow.LT_MASK_STRUCT_LENGTH = levelLength;
            ltMaskRow.LT_MASK_VALUE_SEPARATOR = separator;

            return ltMaskRow;
        }

        /// <summary>
        /// Add a value to the lookup table row
        /// </summary>
        /// <param name="ltDataSet">WebSvcLookupTable.LookupTableDataSet object</param>
        /// <param name="parentUid">GUID of the parent row</param>
        /// <param name="rowUid">GUID of the current row</param>
        /// <param name="ltValue">Value of the row</param>
        /// <param name="ltDescription">Description of the row</param>
        /// <returns>LookupTableTreesRow</returns>
        private WebSvcLookupTable.LookupTableDataSet.LookupTableTreesRow AddLookupTableValues(
            WebSvcLookupTable.LookupTableDataSet ltDataSet,
            Guid parentUid,
            Guid rowUid,
            string ltValue,
            string ltDescription)
        {
            WebSvcLookupTable.LookupTableDataSet.LookupTableTreesRow ltTreeRow =
                ltDataSet.LookupTableTrees.NewLookupTableTreesRow();

            ltTreeRow.LT_UID = ltDataSet.LookupTables[0].LT_UID;
            ltTreeRow.LT_STRUCT_UID = rowUid;

            if (parentUid == Guid.Empty)
                ltTreeRow.SetLT_PARENT_STRUCT_UIDNull();
            else
                ltTreeRow.LT_PARENT_STRUCT_UID = parentUid;

            ltTreeRow.LT_VALUE_TEXT = ltValue;
            ltTreeRow.LT_VALUE_DESC = ltDescription;

            return ltTreeRow;
        }
        #endregion
    }
}

Security

To run the CreateLookupTables method, the user needs ManageEnterpriseCustomFields permission for Project Server.

See Also

Tasks

How to: Create an Enterprise Custom Field

How to: Log on to Project Server Programmatically

Reference

LookupTable

UpdateLookupTables

CreateLookupTablesMultiLang

Concepts

Local and Enterprise Custom Fields

Namespaces in the PSI