Project Server - Restart workflow from code

When you want to restart workflow in Project Server, you can do that manually in Project Server in two ways:

1. When you open one of your project, in SharePoint Ribbon you will see "Options" button and its submenu "Restart Workflow". By clicking this option, you can restart your workflow, but you can not select the stage to which you want to restart workflow. This option will always restart workflow to the first stage, to the beginning. 




2. Second option better in sense that you can select particular stage to which you want to restart your workflow. You can also select multiple projects and some other options.

This option is located in: Server settings --> Change or Restart Workflows (in section Workflow and Project Detail Pages).




 Downside of this two options is that it has to be done manually, and that is not very convenient when you have to do this on large number of projects. So the best way to do this is from code behind.



 RESTART FROM CODE:

Workflow can be restarted to any stage using SubmitStage function from FluentPS library (which I described in one of my previous posts).

Here is the example of usage of that function:

public void RestartStage(Guid projectUID, string stageName)

{

      var logService = new LogService();

      var sessionService = new PSSessionService()

      {

           HostName = "serverName", // your PWA host name

           SiteName = "pwa" // your PWA site name

      };

      PsiContextService psiContextService = new PsiContextService();

      PSISvcsFactory psiSvcsFactory = new PSISvcsFactory(sessionService, psiContextService);

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

 

      Workflow svcWf = psiSvcsFactory.CreateSvcClient<Workflow>();

      SPSPagesService svcPage = new SPSPagesService(sessionService);

      SPSharepointService svcSP = new SPSharepointService(sessionService, svcPage, logService);

      PSWorkflowService svcPSWf= new PSWorkflowService(logService, svcWf, svcSP);

      WorkflowDataSet workflowDS = svcWf.ReadAvailableEnterpriseProjectTypes();

      List<PSWorkflowStatus> wfStatus = svcPSWf.GetProjectWorkflowStatus(projectUID);

      //Enteprise project template UID

      Guid _eptUID = new Guid();//

      //Stage UID

      Guid _stageGuid = new Guid();

      //Getting the stage GUID

      string _phaseName = "";

      for (int i = 0; i < wfStatus.Count; i++)

      {

           if (wfStatus[i].StageName == stageName)

           {

                _stageGuid = wfStatus[i].StageUid;

                _phaseName = wfStatus[i].PhaseName;

                break;

           }

      }

     

      //getting the enterprise project type GUID

      for (int i = 0; i < workflowDS.EnterpriseProjectType.Count; i++)

      {

          if (workflowDS.EnterpriseProjectType[i].ENTERPRISE_PROJECT_TYPE_NAME == _phaseName)

          {

                _eptUID = workflowDS.EnterpriseProjectType[i].ENTERPRISE_PROJECT_TYPE_UID;

                break;

          }

      }

           //RESTART to the stage

      Guid _restartGuid = svcPSWf.SubmitStage(projectUID, _eptUID, false, _stageGuid);

     

}

Project Server - Read custom field with value from lookup

In one of my previous posts, I've described how to update value of a Enterprise custom field that contains value from a Lookup table. 

Reading of that field is similar, only shorter and easier. Here, I'm using PSI functions from FluentPS library 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".

NOTE: If Custom field on your project contains no value, then you won't be able to see that field on that project. If field is empty, Project Server acts as if that field doesn't exist.  


This is the code to do it:

        public bool ReadProduct(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 _project = svcProject.ReadProject(projectGuid, FluentPS.WebSvcProject.DataStoreEnum.WorkingStore);

             try
             {
                 //Guid of lookup table (Product) in which we look for value
                 Guid _lookupTableUid = Guid.Empty;
                 //MD Guid of lookup custom field
                 Guid _lookupCustomFieldGuid = Guid.Empty;


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

                 //First, we need to find Lookup table guid for that Custom field on our project
                 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;

                         break;
                     }

                 }

                 //Then, we need to find the value of guid of Lookup table value stored in that Custom field
                 Guid _lookupTableValueGuid = Guid.Empty;
                 foreach (FluentPS.WebSvcProject.ProjectDataSet.ProjectCustomFieldsRow cfRow in _project.ProjectCustomFields)
                 {
                     if (cfRow.MD_PROP_UID == _lookupCustomFieldGuid)
                     {
                         _lookupTableValueGuid = cfRow.CODE_VALUE;
                     }

                 }

                 string _value = "";

                 using (FluentPS.WebSvcLookupTable.LookupTableDataSet lookupTableDs = svcLookupTable.ReadLookupTables(string.Empty, false, 1033))
                 {
                  //now, we search through Lookup table for the text value which corresponds to guid found in previous loop
                  for (int i = 0; i < lookupTableDs.LookupTableTrees.Count; i++)
                  {
                       try
                       {
                             if (lookupTableDs.LookupTableTrees[i].LT_STRUCT_UID == _lookupTableValueGuid)
                             {
                                   //and here we read the value we were looking for
                                   _value = lookupTableDs.LookupTableTrees[i].LT_VALUE_TEXT;
                                   break;
                             }
                        }
                        catch (Exception)
                        {
                                //this is only for possible null values
                         }

                  }
              }

              return _value;

          }
          catch (Exception)
          {
                return null;
          }
           
 }

Project Server - PSI unhandled communication fault occurred

Very often when you try to get some data from Project Server using PSI functions, you will get error "PSI unhandled communication fault occurred".

This error happens when you try to call web service from code (event receivers or workflow or something like that) and the problem is in the user account executing the call.


SOLUTION:

You need to disable Anonymous Authentication in IIS:

Start --> Administrative Tools --> Internet Information Services Manager (IIS) --> select the Web Application that is hosting your PWA --> double click on Authentication --> right click on Anonymous Authentication and select Disable

Now, your PSI calls should work.


If you are running PSI calls from workflow, then assure that account user account which you specify as the workflow proxy account must have appropriate permissions:

• Global permissions:
  
  • Log On
  • Manage Users And Groups
  • Manage Workflow
• Category permissions:
  
  • Open Project
  • Save Project
  • View Enterprise Resource Data
  • Edit Project Properties
  • View Enterprise Resource Data

Finding the name of SP list column in SP database

In my previous post, I've demonstrated how does SharePoint store items of its lists into SQL database.

But, as mentioned in that post, columns in SharePoint's SQL database (WSS_Content), in AllUserData table where all data is stored, have names like nvarchar1, navarchar2, nvarchar3...

There are two ways how can you find the name of column in which data from your SP list is stored:


1. Powershell script:

Change values marked in red with your values:

// Get your SPWeb

$web = get-spweb "http://yourserver/yoursite";

// Get your SPList

$list = $web.Lists["ListName"];

// Get the field you want to examine

$field = $list.fields["FieldName"];

// Parse the schemaxml into a PowerShell xml object

[xml]$schema = $field.SchemaXml;

// You can now iterate thought the xml attributes. For example the colName:

$schema.Field.ColName; 

2. C# function:

This solution is much better because it tells you the name of the column in which data is stored and it also tells you the row in which this data is located.

//list is the name of your SP list, field is the name of SP field

public void getXmlColName(string list, string field)

{

       string _siteUrl = ConfigurationSettings.AppSettings["YourSiteURL"];

       using (SPSite _siteCollection = new SPSite(_siteUrl))

      {

using (SPWeb _SPWeb = _siteCollection.OpenWeb())

             {

                   

                    SPList _list = null;

                    SPFieldCollection _listFields = null;

                   

_list = _SPWeb.Lists[list];

                    _listFields = _list.Fields;

                   

                    string _xmlSchema = _listFields.SchemaXml;

                    //searching for name of the field in entire xml scheme

                    int _startIndex = _xmlSchema.IndexOf(field);

                    if (_startIndex != -1)

                    {

                        int _endIndex = _xmlSchema.IndexOf("/>", _startIndex);

                        string _stringSearch = _xmlSchema.Substring(_startIndex, _endIndex - _startIndex);

                        int _startIndexColName = _stringSearch.IndexOf("ColName=\"") + 9;

                        int _endIndexColName = _stringSearch.IndexOf("\"", _startIndexColName);

                        //this is ColName

                        string _xmlColName = _stringSearch.Substring(_startIndexColName, _endIndexColName - startIndexColName);

                        Console.WriteLine("\n\nName of column in which your data is stored (ColName): " + xmlColName);

                        //now we need RowOrdinal, so that we can see in which row is data located

                        int _startIndexRowOrdinal = _stringSearch.IndexOf("RowOrdinal=\"") + 12;

                        int _endIndexRowOrdinal = _stringSearch.IndexOf("\"", _startIndexRowOrdinal);

                        string _xmlRowOrdinal = _stringSearch.Substring(_startIndexRowOrdinal, _endIndexRowOrdinal - _startIndexRowOrdinal);

                        if (_xmlRowOrdinal.Length > 1)

                            Console.WriteLine("\n\nRow index (RowOrdinal): No index");

                        else

                            Console.WriteLine("\n\nRow index (RowOrdinal): " + _xmlRowOrdinal);

                    }

                    else

                        Console.WriteLine("There is no SP field with that name");

                }

            }

        }

Now, when you have  column name and RowOrdinal, you can go to SQL database and search data with following query (let's say that your data is in nvarchar1 and RowOrdinal is 0):

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT nvarchar1

FROM [WSS_Content].[dbo].[AllUserData]

WHERE tp_RowOrdinal = 0