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

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…

Error occurred in deployment step 'Add Solution': Attempted to perform an unauthorized operation.

Received this error today when trying to create a SharePoint solution against a newly created site.


My first problem was the site was configured to use a port which was already used by another web site. Therefore the site could not start at all.

In my case both Default Web Site and SharePoint - 2000 was both using port 2000. Changing the port of Default Web Site and I was able to start the site.

However now I still received the same error message. When browsing the site I realized I did not have access to the site.

Giving access to my user and I was able to deploy the solution.

To sum up this post:
 - Check the site can start
 - Check the site can be browsed
 - Check your user have access to the site

SharePoint 2013 : Cannot connect to the targeted site.

On my Contoso environment I was trying to create a sandboxed solution. I have already configured my app domain and app management services. But when I tried to validate the site I got the following error message.
Error

Cannot connect to the targeted site. This error can occur if the specified site is not hosted on the local system. SharePoint solutions work only with locally-installed versions of SharePoint Foundation or SharePoint Server. Remote development is supported only for apps for SharePoint 2013.
If you search around the internet you will find many people suggesting to change your host file from 127.0.0.1 to the actual IP.

Solution However the solution, at least on a Contoso environment, is to type in the actual IP address in the host file.
(the IP 192.168.150.1 is the special IP for Contoso, I have 2 network adapters)
And do not forget to clear/flush the DNS cache. Now the solution can be created and validates.