Update Lookup Type and non- lookup Custom Field using PSI Project Server

Update Lookup Type and non- lookup Custom Field using PSI Project Server:-

Update Lookup Type and non- lookup Custom Field using PSI Project Server. In my previous post, I explained Create Lookup type Custom Field using PSI. From the below code can update none attribute and lookup type attribute custom field using psi. A custom field can be updated by adding PSI web service reference as well Project Server Service DLL.  If you don’t know how to create Project Server Service Dll, Follow my Post Create Project Server Service dll .In the below code I used PSI web service reference.

If you don’t know how to add service reference then follow my post. Add PSI Web Reference in Solution file

 Web service reference is mentioned in below screen for update custom field using psi

Update Custom Field using PSI

Section 1.How to check custom field is lookup type or non- lookup type

Guid LookupValueGuid = GetGuidForLTValue(customFieldDataSet, lookUpDataSet, CFGuid, CustomeFieldName, Value);

                    if (LookupValueGuid == Guid.Empty)
                    {
                        ISLookup = false;
                    }
                    else
                    {
                        ISLookup = true;
                    }

Here GetGuidForLTValue check custom field has lookup table or not. If it has lookup table means the custom field is a lookup Type otherwise non-lookup type.

 

Section 2.Update lookup type custom field using below code.

 Guid Program_CFUID = new Guid("72e06469-a004-e711-80db-005056a26fae");
                        Guid LookupSelectedValue = Guid.Empty;
                        string CustomFieldValue = Value;
                        if (!string.IsNullOrEmpty(CustomFieldValue) && CustomFieldValue != "NA")
                        {
                            if (SingleprojectDS.ProjectCustomFields.Select("MD_PROP_UID = '" + Program_CFUID + "'").Length > 0)
                            {
                                Guid MDLookUpTableGuid1 = new Guid(customFieldDataSet.CustomFields.Select("MD_PROP_UID = '" + Program_CFUID + "'")[0][customFieldDataSet.CustomFields.MD_LOOKUP_TABLE_UIDColumn].ToString());
                                // lookUpDataSet = LookupClient.ReadLookupTablesByUids(new Guid[] { MDLookUpTableGuid1 }, false, 1033);

                                if (lookUpDataSet.LookupTableTrees.Select("LT_VALUE_TEXT = '" + CustomFieldValue + "'").Length > 0)
                                {
                                    LookupSelectedValue = new Guid(lookUpDataSet.LookupTableTrees.Select("LT_VALUE_TEXT = '" + CustomFieldValue + "'")[0][lookUpDataSet.LookupTableTrees.LT_STRUCT_UIDColumn].ToString());
                                    SingleprojectDS.ProjectCustomFields.Select("MD_PROP_UID = '" + Program_CFUID + "'")[0][SingleprojectDS.ProjectCustomFields.CODE_VALUEColumn] = LookupSelectedValue;
                                }
                            }
                        }

 

Section 3.Update without lookup type custom field using below code

 if (SingleprojectDS.ProjectCustomFields.Select("MD_PROP_UID = '" + CFGuid + "'").Length == 0)
                        {
                            ProjectDataSet.ProjectCustomFieldsRow FunctionCFRow = ProjDS.ProjectCustomFields.NewProjectCustomFieldsRow();
                            FunctionCFRow.PROJ_UID = ProjectGuid;
                            FunctionCFRow.MD_PROP_UID = CFGuid;
                            FunctionCFRow.CUSTOM_FIELD_UID = Guid.NewGuid();
                            FunctionCFRow.TEXT_VALUE = Value;

                            ProjDS.ProjectCustomFields.AddProjectCustomFieldsRow(FunctionCFRow);
                        }
                        else
                        {

                            SingleprojectDS.ProjectCustomFields.Select("MD_PROP_UID = '" + CFGuid + "'")[0][SingleprojectDS.ProjectCustomFields.TEXT_VALUEColumn] = Value;
                            ProjDS = SingleprojectDS;
                        }

 

[AdSense-A]

using Update_Custom_Field.CustomFieldsSvc;
using Update_Custom_Field.LookupSvc;
using Update_Custom_Field.SvcProject;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Web.Services.Protocols;
using System.Xml;
using Update_Custom_Field.SVCQueue;
using Microsoft.SharePoint;
using PSLibrary = Microsoft.Office.Project.Server.Library;
namespace Update_Custom_Field
{
    class Program
    {
        public static SvcProject.ProjectDataSet ProjectDataSet = new SvcProject.ProjectDataSet();
        public static SVCQueue.QueueSystem wsqueue = null;
        public static int l = 1;
        public static bool IsProjectCreated = false;
        public static string PWAPath = "http://ServerName/pwaName/";
        public static Guid ProjectGuid = Guid.Empty;
        public static int SuccessProjects = 0;
        public static int FailureProjects = 0;
        public static CustomFields customFieldsWS = null;
        public static LookupTable lookupTableWS = null;
        public static CustomFieldDataSet customFieldDataSet;
        public static LookupTableDataSet lookUpDataSet;
        public static ProjectDataSet SingleprojectDS;

        static void Main(string[] args)
        {
            Project ProjectWS = new Project
            {
                Credentials = System.Net.CredentialCache.DefaultCredentials,
                Url = string.Format("{0}{1}", PWAPath, "_vti_bin/PSI/Project.asmx")
            };
            QueueSystem queueWS = new QueueSystem
            {
                Credentials = System.Net.CredentialCache.DefaultCredentials,
                Url = string.Format("{0}{1}", PWAPath, "_vti_bin/PSI/queuesystem.asmx")
            };
            ProjectDataSet = ProjectWS.ReadProjectList();
            string CustomFieldName = "LookupCF", Value = "IT"; //If your custom field is lookup type then in value parameter give the same value in lookup table. 

            customFieldsWS = new CustomFields { Credentials = System.Net.CredentialCache.DefaultCredentials, Url = string.Format("{0}{1}", PWAPath, "_vti_bin/PSI/CustomFields.asmx") };
            lookupTableWS = new LookupTable { Credentials = System.Net.CredentialCache.DefaultCredentials, Url = string.Format("{0}{1}", PWAPath, "_vti_bin/PSI/LookupTable.asmx") };
            customFieldDataSet = customFieldsWS.ReadCustomFields("", false);
            lookUpDataSet = lookupTableWS.ReadLookupTables("", false, 1033);

            SaveRecords(ProjectWS, ProjectDataSet, CustomFieldName, Value);

        }

       
        public static void SaveRecords(Project ProjectWS, ProjectDataSet projDataSet, string CustomeFieldName, string Value)
        {
            ProjectDataSet ProjDS = new SvcProject.ProjectDataSet();
            try
            {
                Guid sessionGuid = Guid.NewGuid();
                Guid jobGuid = Guid.NewGuid();

                string ProjectName = "ProjectName";
                if (!string.IsNullOrEmpty(ProjectName))
                {

                    DataRow[] row = ProjectDataSet.Project.Select("PROJ_NAME='" + ProjectName.Trim() + "'");
                    ProjectGuid = new Guid(row[0]["PROJ_UID"].ToString());
                    SingleprojectDS = ProjectWS.ReadProject(ProjectGuid, SvcProject.DataStoreEnum.WorkingStore);

                }

                DataRow[] customrow = customFieldDataSet.CustomFields.Select("MD_PROP_NAME='" + CustomeFieldName + "'").Length > 0 ? customFieldDataSet.CustomFields.Select("MD_PROP_NAME='" + CustomeFieldName + "'") : null;
                Guid CFGuid = Guid.NewGuid();
                if (customrow != null)
                {
                    bool ISLookup = false;
                    CFGuid = new Guid(customrow[0]["MD_PROP_UID"].ToString());
                    Guid LookupValueGuid = GetGuidForLTValue(customFieldDataSet, lookUpDataSet, CFGuid, CustomeFieldName, Value);

                    if (LookupValueGuid == Guid.Empty)
                    {
                        ISLookup = false;
                    }
                    else
                    {
                        ISLookup = true;
                    }

                    if (ISLookup == true)
                    {

                        Guid Program_CFUID = new Guid("72e06469-a004-e711-80db-005056a26fae");
                        Guid LookupSelectedValue = Guid.Empty;
                        string CustomFieldValue = Value;
                        if (!string.IsNullOrEmpty(CustomFieldValue) && CustomFieldValue != "NA")
                        {
                            if (SingleprojectDS.ProjectCustomFields.Select("MD_PROP_UID = '" + Program_CFUID + "'").Length > 0)
                            {
                                Guid MDLookUpTableGuid1 = new Guid(customFieldDataSet.CustomFields.Select("MD_PROP_UID = '" + Program_CFUID + "'")[0][customFieldDataSet.CustomFields.MD_LOOKUP_TABLE_UIDColumn].ToString());
                                // lookUpDataSet = LookupClient.ReadLookupTablesByUids(new Guid[] { MDLookUpTableGuid1 }, false, 1033);

                                if (lookUpDataSet.LookupTableTrees.Select("LT_VALUE_TEXT = '" + CustomFieldValue + "'").Length > 0)
                                {
                                    LookupSelectedValue = new Guid(lookUpDataSet.LookupTableTrees.Select("LT_VALUE_TEXT = '" + CustomFieldValue + "'")[0][lookUpDataSet.LookupTableTrees.LT_STRUCT_UIDColumn].ToString());
                                    SingleprojectDS.ProjectCustomFields.Select("MD_PROP_UID = '" + Program_CFUID + "'")[0][SingleprojectDS.ProjectCustomFields.CODE_VALUEColumn] = LookupSelectedValue;
                                }
                            }
                        }
                    }
                    else
                    {
                        if (SingleprojectDS.ProjectCustomFields.Select("MD_PROP_UID = '" + CFGuid + "'").Length == 0)
                        {
                            ProjectDataSet.ProjectCustomFieldsRow FunctionCFRow = ProjDS.ProjectCustomFields.NewProjectCustomFieldsRow();
                            FunctionCFRow.PROJ_UID = ProjectGuid;
                            FunctionCFRow.MD_PROP_UID = CFGuid;
                            FunctionCFRow.CUSTOM_FIELD_UID = Guid.NewGuid();
                            FunctionCFRow.TEXT_VALUE = Value;

                            ProjDS.ProjectCustomFields.AddProjectCustomFieldsRow(FunctionCFRow);
                        }
                        else
                        {

                            SingleprojectDS.ProjectCustomFields.Select("MD_PROP_UID = '" + CFGuid + "'")[0][SingleprojectDS.ProjectCustomFields.TEXT_VALUEColumn] = Value;
                            ProjDS = SingleprojectDS;
                        }

                    }
                }
                
                try
                {
                    if (projDataSet.Project[0][projDataSet.Project.PROJ_SESSION_UIDColumn] != DBNull.Value)
                    {
                        sessionGuid = new Guid(projDataSet.Project.Rows[0]["PROJ_SESSION_UID"].ToString());
                    }
                    else
                    {
                        sessionGuid = Guid.NewGuid();
                    }
                    ProjectWS.QueueCheckInProject(jobGuid, ProjectGuid, true, sessionGuid, "");
                    ProjectWS.CheckOutProject(ProjectGuid, sessionGuid, "");
                    //WaitForQueueJobCompletion(jobGuid, -1);
                    ProjectWS.QueueUpdateProject(jobGuid, sessionGuid, ProjDS, false);
                    WaitForQueueJobCompletion(jobGuid, -1);
                    ProjectWS.QueuePublish(jobGuid, ProjectGuid, true, "");
                    WaitForQueueJobCompletion(jobGuid, -1);
                    ProjectWS.QueueCheckInProject(jobGuid, ProjectGuid, true, sessionGuid, "");
                    WaitForQueueJobCompletion(jobGuid, -1);
                }
                catch (Exception ex)
                {

                }
            }
            catch (Exception)
            {
                return;
            }
        }
  
    }
}

Through this method, you can get a custom field is a lookup or nonLookup type.

 public static Guid GetGuidForLTValue(CustomFieldDataSet CFDataSet, LookupTableDataSet LUDataSet, Guid CFGuid, string CustomFieldName, string LookupValue)
        {
            DataRow[] customrow = CFDataSet.CustomFields.Select("MD_PROP_NAME='" + CustomFieldName + "'");
            CFGuid = new Guid(customrow[0]["MD_PROP_UID"].ToString());
            Guid LookupValueGuid = Guid.Empty;
            try
            {
                if (CFDataSet.CustomFields.Select("MD_PROP_UID = '" + CFGuid + "'").Length > 0)
                {
                    string CFLT = (customrow.Length > 0 ? customrow[0]["MD_LOOKUP_TABLE_UID"].ToString() : (string.Empty));

                    if (!string.IsNullOrEmpty(CFLT))
                    {
                        Guid CFLTGuid = new Guid(CFLT);

                        if (LUDataSet.LookupTableTrees.Select("LT_UID = '" + CFLTGuid + "' and LT_VALUE_TEXT = '" + LookupValue + "'").Length > 0)
                        {
                            LookupValueGuid = new Guid(LUDataSet.LookupTableTrees.Select("LT_UID = '" + CFLTGuid + "' and LT_VALUE_TEXT = '" + LookupValue + "'")[0][LUDataSet.LookupTableTrees.LT_STRUCT_UIDColumn].ToString());
                        }
                    }
                    else
                    {

                    }
                }

            }
            catch (Exception ex)
            {


            }
            return LookupValueGuid;
        }

Wait for next job.

 public static bool WaitForQueueJobCompletion(Guid trackingGuid, int messageType)
        {
            QueueSystem queueSystemWS = new QueueSystem
            {
                Credentials = System.Net.CredentialCache.DefaultCredentials,
                Url = string.Format("{0}{1}", PWAPath, "_vti_bin/PSI/Queuesystem.asmx")
            };

            QueueStatusDataSet queueStatusDataSet = new QueueStatusDataSet();
            QueueStatusRequestDataSet queueStatusRequestDataSet = new QueueStatusRequestDataSet();

            QueueStatusRequestDataSet.StatusRequestRow statusRequestRow =
                queueStatusRequestDataSet.StatusRequest.NewStatusRequestRow();
            statusRequestRow.JobGUID = trackingGuid;
            statusRequestRow.JobGroupGUID = Guid.NewGuid();
            statusRequestRow.MessageType = messageType;
            queueStatusRequestDataSet.StatusRequest.AddStatusRequestRow(statusRequestRow);

            bool inProcess = true;
            bool result = false;
            DateTime startTime = DateTime.Now;
            int successState = (int)JobState.Success;
            int failedState = (int)JobState.Failed;
            int blockedState = (int)JobState.CorrelationBlocked;

            List<int> errorList = new List<int>();

            while (inProcess)
            {
                queueStatusDataSet = queueSystemWS.ReadJobStatus(queueStatusRequestDataSet, false,
                    SortColumn.Undefined, SortOrder.Undefined);

                foreach (QueueStatusDataSet.StatusRow statusRow in queueStatusDataSet.Status)
                {
                    if (statusRow["ErrorInfo"] != System.DBNull.Value)
                    {
                        errorList = CheckStatusRowErrors(statusRow["ErrorInfo"].ToString());

                        if (errorList.Count > 0
                            || statusRow.JobCompletionState == blockedState
                            || statusRow.JobCompletionState == failedState)
                        {
                            inProcess = false;
                        }
                    }
                    if (statusRow.JobCompletionState == successState)
                    {
                        inProcess = false;
                        result = true;
                    }
                    else
                    {
                        inProcess = true;
                        System.Threading.Thread.Sleep(500);  // Sleep 1/2 second.
                    }
                }
                DateTime endTime = DateTime.Now;
                TimeSpan span = endTime.Subtract(startTime);

                if (span.Seconds > 20) //Wait for only 20 secs - and then bail out.
                {
                    inProcess = false;
                    result = false;
                }
            }
            //}
            return result;
        }
private static List<int> CheckStatusRowErrors(string errorInfo)
        {
            List<int> errorList = new List<int>();
            bool containsError = false;

            XmlTextReader xReader = new XmlTextReader(new System.IO.StringReader(errorInfo));
            while (xReader.Read())
            {
                if (xReader.Name == "errinfo" && xReader.NodeType == XmlNodeType.Element)
                {
                    xReader.Read();
                    if (xReader.Value != string.Empty)
                    {
                        containsError = true;
                    }
                }
                if (containsError && xReader.Name == "error" && xReader.NodeType == XmlNodeType.Element)
                {
                    while (xReader.Read())
                    {
                        if (xReader.Name == "id" && xReader.NodeType == XmlNodeType.Attribute)
                        {
                            errorList.Add(Convert.ToInt32(xReader.Value));
                        }
                    }
                }
            }
            return errorList;
        }

 

Some other post is here. And all the code is 100% working like

Create Server Side Event Handlers in Project Server 2013

Create Custom Approval Center Project Server 2013

Update My Task Using PSI

Create Timesheet in Project Server 2013

Update Timesheet Using PSI

Approve and Reject Task Project Server 2013

Create Resource Plan in Project Server 2013

Sql Server Reporting Services(SSRS)

Demand Management Workflow in Project Server 2013


Comments

  1. Below line of codes has error. Project and QueueSystem are of interface type and cannot create an instance. What to do?

    Project ProjectWS = new Project
    {
    Credentials = System.Net.CredentialCache.DefaultCredentials,
    Url = string.Format(“{0}{1}”, PWAPath, “_vti_bin/PSI/Project.asmx”)
    };
    QueueSystem queueWS = new QueueSystem
    {
    Credentials = System.Net.CredentialCache.DefaultCredentials,
    Url = string.Format(“{0}{1}”, PWAPath, “_vti_bin/PSI/queuesystem.asmx”)
    };

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

error: Content is protected !!