Project Server - PSI - Update enterprise custom field with lookuptable

In Project Server - PSI it should be very fast to assign a new value to a custom field with a lookuptable. However this usually takes a lot of time for me mainly because I never can remember extactly how the field should be updated.

That is why i created this post to show how a custom field (assigned to a lookuptable) can be assigned a new value.
The biggest issue is that I can't remember how the different entities are connected together. The binding between the project and the lookuptable values are shown in the picture below.

There are a number of steps to assign a value to a custom field with a lookuptable.
 - Read the project dataset
 - Find the custom field; MD_PROP_UID and MD_LOOKUP_TABLE_UID
 - Find the ProjectCustomFieldRow with the same MD_PROP_UID
 - Find the LookupTable with the same MD_LOOKUP_TABLE_UID
 - Find the LookuptableTreesRow with the LT_VALUE_TEXT you want to assign.
 - Assign the LT_STRUCT_UID from the LookupTableTreesRow to the ProjectCustomFieldsRow's CODE_VALUE
    - If the ProjectCustomFieldsRow does not exists, create it.
 - Save and publish the project

The basic code for updating the custom field is:
#region Set CF value
WS_CF.CustomFieldDataSet cfds = customfields.ReadCustomFields("", false);
WS_Project.ProjectDataSet.ProjectCustomFieldsRow projectfield = null;
WS_CF.CustomFieldDataSet.CustomFieldsRow field = null;
//find the custom field row in the project dataset
foreach (WS_CF.CustomFieldDataSet.CustomFieldsRow cfrow in cfds.CustomFields)
{
if (cfrow.MD_PROP_NAME == customfieldname)
{
    field = cfrow;
    foreach (WS_Project.ProjectDataSet.ProjectCustomFieldsRow pcf in projectDS.ProjectCustomFields)
    {
        if (pcf.MD_PROP_UID == cfrow.MD_PROP_UID)
        {
            projectfield = pcf;
            break;
        }
    }
}
}
if (field.IsMD_LOOKUP_TABLE_UIDNull() == false && field.MD_LOOKUP_TABLE_UID != Guid.Empty)
{
Guid ltrowuid = Guid.Empty;
#region find LT Code Value
WS_LT.LookupTableDataSet ltds = null;

ltds = lookuptable.ReadLookupTablesByUids(new Guid[] { field.MD_LOOKUP_TABLE_UID }, false, 1033);


decimal newSortIndx = 1;
foreach (WS_LT.LookupTableDataSet.LookupTableTreesRow valueRow in ltds.LookupTableTrees)
{
    if (valueRow.LT_VALUE_SORT_INDEX >= newSortIndx)
    {
        newSortIndx = valueRow.LT_VALUE_SORT_INDEX + 1;
    }
    if (valueRow.LT_VALUE_FULL == (string)customfieldvalue || valueRow.LT_VALUE_TEXT == (string)customfieldvalue)
    {
        ltrowuid = valueRow.LT_STRUCT_UID;
    }
}
#region create LT entry if it does not exists
if (ltrowuid == Guid.Empty)
{
    //Create row
    WS_LT.LookupTableDataSet.LookupTableTreesRow ltrow = ltds.LookupTableTrees.NewLookupTableTreesRow();
    ltrow.LT_UID = field.MD_LOOKUP_TABLE_UID;
    ltrow.LT_STRUCT_UID = Guid.NewGuid();
    //ltrow.LT_PARENT_STRUCT_UID = ltuid; //Top level
    ltrow.LT_VALUE_TEXT = customfieldvalue;
    ltrow.LT_VALUE_FULL = customfieldvalue;
    ltrow.LT_VALUE_SORT_INDEX = newSortIndx;
    ltds.LookupTableTrees.AddLookupTableTreesRow(ltrow);



    lookuptable.CheckOutLookupTables(new Guid[] { field.MD_LOOKUP_TABLE_UID });
    lookuptable.UpdateLookupTables(ltds, false, true, 1033);

}
#endregion
#endregion

if (projectfield == null)
{
    projectfield = projectDS.ProjectCustomFields.NewProjectCustomFieldsRow();
    projectfield.MD_PROP_UID = field.MD_PROP_UID;
    projectfield.CUSTOM_FIELD_UID = Guid.NewGuid();
    projectfield.PROJ_UID = projectDS.Project[0].PROJ_UID;
    projectDS.ProjectCustomFields.AddProjectCustomFieldsRow(projectfield);

}

projectfield.CODE_VALUE = ltrowuid;

The Complete Code for updating a custom field with a lookuptable value is provided below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SetCustomField
{
    class Program
    {
        static void Main(string[] args)
        {
            Guid projectUid = new Guid("113d1cb6-a6b6-4dc4-a55b-e3bf1f65fbb6");
            string customfieldname = "Project Status";
            string customfieldvalue = "Completed";

            #region Init
            string ProjectServerURL = "";
            string ProjectServerAccount = "";
            string ProjectServerAccountPassword = "";
            string ProjectServerDomain = "";
            WS_Project.Project wsproject = null;
            WS_Queue.QueueSystem wsqueue = null;
            WS_CF.CustomFields customfields = null;
            WS_LT.LookupTable lookuptable = null;

            ProjectServerURL = "http://ProjectServer/PWA/";
            ProjectServerAccount = "Administrator";
            ProjectServerAccountPassword = "pass@word1";
            ProjectServerDomain = "CONTOSO";

            System.Net.NetworkCredential cred = new System.Net.NetworkCredential(ProjectServerAccount, ProjectServerAccountPassword, ProjectServerDomain);


            wsproject = new WS_Project.Project();
            wsproject.Url = ProjectServerURL + "/_vti_bin/psi/project.asmx";
            wsproject.Credentials = cred;


            wsqueue = new WS_Queue.QueueSystem();
            wsqueue.Url = ProjectServerURL + "/_vti_bin/psi/queuesystem.asmx";
            wsqueue.Credentials = cred;

            customfields = new WS_CF.CustomFields();
            customfields.Url = ProjectServerURL + "/_vti_bin/psi/customfields.asmx";
            customfields.Credentials = cred;

            lookuptable = new WS_LT.LookupTable();
            lookuptable.Url = ProjectServerURL + "/_vti_bin/psi/lookuptable.asmx";
            lookuptable.Credentials = cred;
            #endregion

            #region read project
            WS_Project.ProjectDataSet projectDS = wsproject.ReadProject(projectUid, WS_Project.DataStoreEnum.WorkingStore);
            #endregion

            #region Set CF value
            WS_CF.CustomFieldDataSet cfds = customfields.ReadCustomFields("", false);
            WS_Project.ProjectDataSet.ProjectCustomFieldsRow projectfield = null;
            WS_CF.CustomFieldDataSet.CustomFieldsRow field = null;
            //find the custom field row in the project dataset
            foreach (WS_CF.CustomFieldDataSet.CustomFieldsRow cfrow in cfds.CustomFields)
            {
                if (cfrow.MD_PROP_NAME == customfieldname)
                {
                    field = cfrow;
                    foreach (WS_Project.ProjectDataSet.ProjectCustomFieldsRow pcf in projectDS.ProjectCustomFields)
                    {
                        if (pcf.MD_PROP_UID == cfrow.MD_PROP_UID)
                        {
                            projectfield = pcf;
                            break;
                        }
                    }
                }
            }
            if (field.IsMD_LOOKUP_TABLE_UIDNull() == false && field.MD_LOOKUP_TABLE_UID != Guid.Empty)
            {
                Guid ltrowuid = Guid.Empty;
                #region find LT Code Value
                WS_LT.LookupTableDataSet ltds = null;

                ltds = lookuptable.ReadLookupTablesByUids(new Guid[] { field.MD_LOOKUP_TABLE_UID }, false, 1033);


                decimal newSortIndx = 1;
                foreach (WS_LT.LookupTableDataSet.LookupTableTreesRow valueRow in ltds.LookupTableTrees)
                {
                    if (valueRow.LT_VALUE_SORT_INDEX >= newSortIndx)
                    {
                        newSortIndx = valueRow.LT_VALUE_SORT_INDEX + 1;
                    }
                    if (valueRow.LT_VALUE_FULL == (string)customfieldvalue || valueRow.LT_VALUE_TEXT == (string)customfieldvalue)
                    {
                        ltrowuid = valueRow.LT_STRUCT_UID;
                    }
                }
                #region create LT entry if it does not exists
                if (ltrowuid == Guid.Empty)
                {
                    //Create row
                    WS_LT.LookupTableDataSet.LookupTableTreesRow ltrow = ltds.LookupTableTrees.NewLookupTableTreesRow();
                    ltrow.LT_UID = field.MD_LOOKUP_TABLE_UID;
                    ltrow.LT_STRUCT_UID = Guid.NewGuid();
                    //ltrow.LT_PARENT_STRUCT_UID = ltuid; //Top level
                    ltrow.LT_VALUE_TEXT = customfieldvalue;
                    ltrow.LT_VALUE_FULL = customfieldvalue;
                    ltrow.LT_VALUE_SORT_INDEX = newSortIndx;
                    ltds.LookupTableTrees.AddLookupTableTreesRow(ltrow);



                    lookuptable.CheckOutLookupTables(new Guid[] { field.MD_LOOKUP_TABLE_UID });
                    lookuptable.UpdateLookupTables(ltds, false, true, 1033);

                }
                #endregion
                #endregion

                if (projectfield == null)
                {
                    projectfield = projectDS.ProjectCustomFields.NewProjectCustomFieldsRow();
                    projectfield.MD_PROP_UID = field.MD_PROP_UID;
                    projectfield.CUSTOM_FIELD_UID = Guid.NewGuid();
                    projectfield.PROJ_UID = projectDS.Project[0].PROJ_UID;
                    projectDS.ProjectCustomFields.AddProjectCustomFieldsRow(projectfield);

                }

                projectfield.CODE_VALUE = ltrowuid;


            }
            #endregion

            #region Update project
            Guid sessionUid = Guid.NewGuid();

            wsproject.CheckOutProject(projectUid, sessionUid, "Checkout by code");
            Guid jobid = Guid.NewGuid();
            wsproject.QueueUpdateProject(jobid, sessionUid, projectDS, false);
            WaitForQueue(wsqueue, 60 * 5, jobid);

            jobid = Guid.NewGuid();
            wsproject.QueuePublish(jobid, projectUid, true, string.Empty); //string.empty == create std workspace
            WaitForQueue(wsqueue, 60 * 5, jobid);

            jobid = Guid.NewGuid();
            wsproject.QueueCheckInProject(jobid, projectUid, false, sessionUid, "Checkin by code");
            #endregion

        }
        public static bool WaitForQueue(WS_Queue.QueueSystem q, int timeOut, Guid jobId)
        {
            int INCREMENTALSLEEPTIME = 2;

            int wait;                 // Number of seconds to wait
            decimal seconds;          // For reporting wait time in decimal
            // format
            string xmlError;          // XML error output from the queue
            string queueStatus;       // Outer XML of xmlError string
            string status = "";       // Summary status report for output
            bool firstPass = true;    // First iteration through the while 
            // statement
            int timeSlept = 0;        // Total time slept (seconds)
            bool jobIsDone = false;   // The queue job completed 
            // successfully, if true
            bool stopWait = false;    // Abort the wait, if true
            WS_Queue.JobState jobState; // Status of the queue job

            while (true)
            {
                // On the first iteration, wait the incremental sleep time 
                // or the maximum requested timeout. 
                if (firstPass)
                {
                    // Get the estimated time to wait for the queue to 
                    // process the job.
                    // The output from GetJobWaitTime is in seconds.
                    wait = q.GetJobWaitTime(jobId);

                    status = string.Format("Estimated job wait time: {0} seconds", wait);

                    if (timeOut < INCREMENTALSLEEPTIME) wait = timeOut;
                    else wait = INCREMENTALSLEEPTIME;

                    firstPass = false;
                }
                else
                {
                    // If job is not done, wait the incremental sleep time
                    wait = INCREMENTALSLEEPTIME;
                }

                System.Threading.Thread.Sleep(wait * 1000); // Milliseconds

                timeSlept += wait;

                // Check job state
                jobState = q.GetJobCompletionState(jobId, out xmlError);

                // Add the XML error output to the status
                System.IO.StringReader sr = new System.IO.StringReader(xmlError);
                using (System.Xml.XmlReader reader = System.Xml.XmlReader.Create(sr))
                {
                    reader.MoveToContent();
                    queueStatus = reader.ReadOuterXml();
                }
                // Don't add an empty <errinfo> tag
                if (queueStatus != "<errinfo />") status += "\n\n" + queueStatus;
                if (jobState == WS_Queue.JobState.Success)
                {
                    jobIsDone = true;
                }
                else if (jobState == WS_Queue.JobState.Unknown
                    || jobState == WS_Queue.JobState.Failed
                    || jobState == WS_Queue.JobState.FailedNotBlocking
                    || jobState == WS_Queue.JobState.CorrelationBlocked
                    || jobState == WS_Queue.JobState.Canceled)
                {
                    stopWait = true;
                }

                if (!jobIsDone && timeSlept >= timeOut)
                {
                    // Cancel the job, otherwise the queue keeps processing 
                    // until the job is complete.
                    //q.CancelJobSimple(jobId);
                    stopWait = true;
                    status += string.Format("\n\nExceeded timeout of {0} seconds", timeOut);
                }

                if (jobIsDone || stopWait)
                {
                    seconds = Convert.ToDecimal(timeSlept);
                    status += string.Format(
                        "\n\nJobState: {0:G}\n\nTotal time slept: {1:N} seconds",
                        jobState, seconds);
                    break;
                }
            }
            //statusOut = status;
            return jobIsDone;
        }
    }
}