Monday, January 14, 2013

Project Server update custom lookup field

This post will show you how to update Custom field in Project Server that contains values of a Lookup table using PSI functions from FluentPS library (described in this post) which is free and easy to use but the same PSI functions can be used when you set your own call of Project Server web services.

Let's say we have custom field called "Product" and it contains values from Lookup table called "Products". Lookup table "Products" contains values: "TV", "Mobile phone", "Laptop", etc.

We need to set (or change) value of Custom field (from "TV") to "Mobile phone".




This is the code to do it:

        public bool SetProduct(string projectUid)
        {        
            Guid projectGuid = new Guid(projectUid);

            if (projectGuid.Equals(Guid.Empty)) return false;
            var logService = new LogService();
            var sessionService = new PSSessionService()
            {
                HostName = "server_name",
                SiteName ="PWA"
            };

             FluentPS.Services.Impl.PsiContextService psiContextService = new PsiContextService();
             FluentPS.Services.Impl.PSISvcsFactory psiSvcsFactory = new                 PSISvcsFactory(sessionService, psiContextService);

             FluentPS.WebSvcLookupTable.LookupTable svcLookupTable = psiSvcsFactory.CreateSvcClient<FluentPS.WebSvcLookupTable.LookupTable>();
             FluentPS.WebSvcCustomFields.CustomFields svcCustomFields = psiSvcsFactory.CreateSvcClient<FluentPS.WebSvcCustomFields.CustomFields>();

             FluentPS.WebSvcProject.Project svcProject = psiSvcsFactory.CreateSvcClient<FluentPS.WebSvcProject.Project>();


             FluentPS.WebSvcProject.ProjectDataSet tProjEntities = svcProject.ReadProjectEntities(projectGuid, 32, FluentPS.WebSvcProject.DataStoreEnum.WorkingStore);
             FluentPS.WebSvcProject.ProjectDataSet tProject = svcProject.ReadProject(projectGuid, FluentPS.WebSvcProject.DataStoreEnum.WorkingStore);

             try
             {
                 //Guid of lookup table (Product) in which we look for value (Mobile phone)
                 Guid _lookupTableUid = Guid.Empty;
                 //MD Guid of lookup custom field
                 Guid _lookupCustomFieldGuid = Guid.Empty;
                 int _lookupId = 0;
                 byte _lookupByte = 0;
                 bool _customFieldAlreadyFilled = false;

                 FluentPS.WebSvcCustomFields.CustomFieldDataSet customFieldsDs = svcCustomFields.ReadCustomFields("", false);

                 FluentPS.WebSvcCustomFields.CustomFieldDataSet.CustomFieldsDataTable cfDataTable = customFieldsDs.CustomFields;
                 for (int i = 0; i < cfDataTable.Count; i++)
                 {
                        if (cfDataTable[i].MD_PROP_NAME == "Product")
                        {
                            _lookupCustomFieldGuid = cfDataTable[i].MD_PROP_UID;
                            _lookupTableUid = cfDataTable[i].MD_LOOKUP_TABLE_UID;
                            _lookupId = cfDataTable[i].MD_PROP_ID;
                            _lookupByte = cfDataTable[i].MD_PROP_TYPE_ENUM;
                            break;
                        }

                 }



                 if (_lookupCustomFieldGuid == Guid.Empty || _lookupTableUid == Guid.Empty)  
                           return false;

                 Guid _lookupTableValueGuid = Guid.Empty;
                 using (FluentPS.WebSvcLookupTable.LookupTableDataSet lookupTableDs = svcLookupTable.ReadLookupTables(string.Empty, false, 1033))
                 {
                        //now we look for guid that contains value "Mobile phone" in lookup table
                        for (int i = 0; i < lookupTableDs.LookupTableTrees.Count; i++)
                        {
                            try
                            {
                                if (lookupTableDs.LookupTableTrees[i].LT_UID == _lookupTableUid && lookupTableDs.LookupTableTrees[i].LT_VALUE_TEXT == "Mobile phone")
                                {
                                    _lookupTableValueGuid = lookupTableDs.LookupTableTrees[i].LT_STRUCT_UID;
                                    break;
                                }
                            }
                            catch (Exception)
                            {
                               
                            }

                        }

                 }

                 if (_lookupTableValueGuid == Guid.Empty) return false;
                 
                 foreach (FluentPS.WebSvcProject.ProjectDataSet.ProjectCustomFieldsRow cfRow in tProject.ProjectCustomFields)
                 {
                        if (cfRow.MD_PROP_UID == _lookupCustomFieldGuid)
                        {
                            cfRow.CODE_VALUE = _lookupTableValueGuid;

                            _customFieldAlreadyFilled = true;
                        }
                 }

                 //if Custom field exists, but it is empty, then we need to add it to project
                 if (!_customFieldAlreadyFilled)
                 {
                        var cfrow = tProject.ProjectCustomFields.NewProjectCustomFieldsRow();

                        cfrow.CUSTOM_FIELD_UID = Guid.NewGuid();
                        cfrow.FIELD_TYPE_ENUM = _lookupByte;
                        cfrow.MD_PROP_ID = _lookupId;
                        cfrow.MD_PROP_UID = _lookupCustomFieldGuid;
                        cfrow.PROJ_UID = new Guid(projectUid);
                        cfrow.CODE_VALUE = _lookupTableValueGuid;

                        tProject.ProjectCustomFields.AddProjectCustomFieldsRow(cfrow);

                 }

                 //Update of new field and project
                 Guid sessionUid = tProject.Project[0].PROJ_SESSION_UID;

                 Guid jobUid;

                 var tQueueService = new PSQueueSystemService(logService, psiSvcsFactory.CreateSvcClient<FluentPS.WebSvcQueueSystem.QueueSystem>());
                  
                 jobUid = Guid.NewGuid();
                 svcProject.CheckOutProject(projectGuid, sessionUid, "Checked-out");
                 svcProject.QueueUpdateProject(jobUid, sessionUid, tProject, false);
                 tQueueService.WaitForQueue(jobUid);
                 jobUid = Guid.NewGuid();
                 svcProject.QueuePublish(jobUid, projectGuid, true, null);
                 tQueueService.WaitForQueue(jobUid);
                 jobUid = Guid.NewGuid();
                 svcProject.QueueCheckInProject(jobUid, projectGuid, true, sessionUid, "Checked-in");
                 tQueueService.WaitForQueue(jobUid);

                 return true;

             }
             catch (Exception)
             {
                    return false;
             }
           
   }

In my next post, I will show how to read value from Custom field that contains value from Lookup table.

5 comments:

  1. Hi! You can use FluentPS Mapping to read project field value. It will do all the routine for you. See samples of code.

    ReplyDelete
  2. Thanks for the info. I will check that out!

    ReplyDelete
  3. Follow the below link it’s 100% working code for update custom field with lookup type
    http://projectservercode.com/update-custom-field-using-psi/

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete