Skip to main content

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;
        }
    }
}

       

Comments

  1. For lookup entry does not exists scenario to work correctly, we need to add one line --> "ltrowuid = ltrow.LT_STRUCT_UID;", at the end inside if loop --> "if (ltrowuid == Guid.Empty)"

    ReplyDelete

Post a Comment

Popular posts from this blog

Sharepoint 2013 - Fullscreen mode/hide quick launch

If you want to hide the quick launch on a SharePoint page or web part page in 2013 you could of cause use CSS scripts as in 2010.
But as a new thing in 2013 the user have the possibility to click on full screen mode. so why not load your page in this mode and then the user can choose to exit the mode if he prefers.

Add a content editor webpart to your page and click on Edit HTML from the ribbon.
Add the following code:

<script type="text/javascript">
window.onload = function()
{
  SetFullScreenMode(true);
}
</script>

Install DLL in GAC - Windows 2008/2012 (Using Powershell, No GacUtil.exe)

If you want to install a DLL in the GAC and do not have the GACUtil.exe available. Powershell is properly the easiest way to procede.

Before Powershell you would properly just drag the DLL file into the C:\Windows\Assembly but this option is usually not available anymore.

Powershell - Add DLL to GAC So to install a DLL file in the GAC simply execute the below Powershell script. Remember you migth want to run the Powershell prompt as an administrator.
[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")$publish=New-ObjectSystem.EnterpriseServices.Internal.Publish$publish.GacInstall("c:\temp\MyDllFile.dll")iisreset
The first line adds a reference to the assembly we need to be able to mange the GAC.
The second and third lines retrives the GAC object and publish a new DLL file to it.
The last line resets the Internet Information Services. This is only needed if your DLL file is used in a websi…

SharePoint/Project Server - Firewall port open list

Firewall port open list Every time I have to install a new instance of Project Server I forget which ports have to be open.
The ports are divided into 3 lists, one list for the web front end servers, one list for the application server and one list for the SQL server.
These ports are based on a 2013 installation of SharePoint/Project Server. the 2010 installation defer a little bit.


Web Frontend ServerWhen a range is specified all ports between the range must be opened.
     Port(s)ProtocolBoundDescription
-80 TCPInhttp
-443 TCPInhttps/ssl
-25 TCPInSMTP for e-mail integration
-16500-16519TCPInPorts used by the search index component
-22233-22236TCPIn/OutPorts required for the AppFabric Caching Service
-32843-32845TCPInCommunication between Web servers and service applications
-32846TCPIn/OutSharePoint User Code Service
-808-809TCPInOffice Web Apps
-5725TCP