none
Get custom enterprise colums from project server 2007 RRS feed

  • Question

  • hi all,

    i would like to get the enterprise custom colums which are available in ms projects hostied in project server 2007 using c#.net 

    right now using the webservice am able to get the list of projects and few columns from it but not the custom columns

    can some one help me in coding it? any samples will be appreciated

    thanks


    jayaraja
    Wednesday, May 12, 2010 5:50 PM

Answers

  • Sure, here is some code that should help (I'm doing quite a bit of code to filter what I want, strip this if you want everything) - it shows how to read and edit a project CF called PCTestCf. Lot of concepts to get to grips with here, the sdk is your friend:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=46007f25-b44e-4aa6-80ff-9c0e75835ad9&displaylang=en

    Cheers...pc

    using System.Web.Services.Protocols;  //Soap Exception

    using PSLibrary = Microsoft.Office.Project.Server.Library; // Filter, (dll from the SDK)

    using QuickProjectList.ProjectWS;  // Project data

    using QuickProjectList.CustomFieldWS; // CF Metadata

     

    namespace QuickProjectList

    {

        public partial class Form1 : Form

        {

            public const int ProjectCustomFields = 32; // Avoid reading the whole project

     

            public Form1()

            {

                InitializeComponent();

            }

     

            private void button1_Click(object sender, EventArgs e)

            {

                // Hookup the web service

                Project projectWS = new Project();

                projectWS.UseDefaultCredentials = true;

     

               

                Guid ProjectUid = new Guid("6da848ee-9f37-4c34-8a13-a406d95a6f89"); // Change to your value

     

                try

                {

                    // Read the Project level custom fields only, and from the working store (the editable version)

                    ProjectDataSet myProjectDS = projectWS.ReadProjectEntities(ProjectUid, ProjectCustomFields , DataStoreEnum.WorkingStore);

                    //this.dataGridView1.DataSource = myProjectDS.ProjectCustomFields;

     

                    // Get the Custom field metadata

                    CustomFieldDataSet myCustomFieldsDS = CustomFieldMetadata("PCTestCF"); // Change to your field name

                    //this.dataGridView1.DataSource = myCustomFieldsDS.CustomFields;

                   

                    // Check the Project data for this CF value

                    //MessageBox.Show(myCustomFieldsDS.CustomFields[0].MD_PROP_UID.ToString());

                 

                    // Does project level have TFS ID

                    DataRow[] cfRows = myProjectDS.ProjectCustomFields.Select(myProjectDS.ProjectCustomFields.MD_PROP_UIDColumn.ColumnName

                                                                     + " = '"

                                                                     + myCustomFieldsDS.CustomFields[0].MD_PROP_UID.ToString()

                                                                     + "'"); // beware loc bugs if not en-us (AE)

                    switch (cfRows.Length)

                    {

                        case 0:

                            // no TFS entry, so add it

                            ProjectDataSet.ProjectCustomFieldsRow cfRow = myProjectDS.ProjectCustomFields.NewProjectCustomFieldsRow();

                            cfRow.MD_PROP_UID = myCustomFieldsDS.CustomFields[0].MD_PROP_UID;

                            cfRow.MD_PROP_ID = myCustomFieldsDS.CustomFields[0].MD_PROP_ID;

                            cfRow.PROJ_UID = ProjectUid;

                            cfRow.FIELD_TYPE_ENUM = (int)PSLibrary.PSDataType.NUMBER;

                            cfRow.NUM_VALUE = 113; // Set to your value

                            cfRow.CUSTOM_FIELD_UID = Guid.NewGuid();

     

                           myProjectDS.ProjectCustomFields.AddProjectCustomFieldsRow(cfRow);

     

                            //Check out, send the changes, publish them and Check in

     

                            Guid jobId = Guid.NewGuid();

                            Guid sessionId = Guid.NewGuid();

                            projectWS.CheckOutProject(ProjectUid, sessionId, "TFS Custom Field Updater");

                            projectWS.QueueUpdateProject(jobId, sessionId, (ProjectDataSet) myProjectDS.GetChanges(), false);

                            jobId = Guid.NewGuid(); // new queue job

                            projectWS.QueuePublish(jobId, ProjectUid, false, string.Empty); // optionally publish changes to the reporting db/PWA UI

                            jobId = Guid.NewGuid(); // new queue job

                            projectWS.QueueCheckInProject(jobId, ProjectUid, false, sessionId, "TFS Custom Field Updater");

     

                            //WaitForQueue(q, jobId);

     

                            break;

                        case 1:

                            // TFS entry, why are we changing it?

                            break;

                        default:

                            //error, shouldn't be able to get here

                            break;

                    }

                }

                catch (SoapException ____)

                {

                    MessageBox.Show(String.Format("Soap Exception: {0}\nStack:\n{1}", ____.Code + " " + ____.Message, ____.StackTrace));

                }

                catch (Exception ex)

                {

                    MessageBox.Show(String.Format("General Exception: {0}\nStack:\n{1}", ex.Message, ex.StackTrace));

                }

            }

            private CustomFieldDataSet CustomFieldMetadata(string CustomFieldName)

            {           

                CustomFields customfieldsWS = new CustomFields(); // may want to move this out if iterating on this function

                customfieldsWS.UseDefaultCredentials = true;

     

                CustomFieldDataSet CustomFieldMetadata = new CustomFieldDataSet();

     

                // Column names for the filter (could hard code)

                string tableName = CustomFieldMetadata.CustomFields.TableName;

                string nameColumn = CustomFieldMetadata.CustomFields.MD_PROP_NAMEColumn.ColumnName;

                string uidColumn = CustomFieldMetadata.CustomFields.MD_PROP_UIDColumn.ColumnName;

                string entityUidColumn = CustomFieldMetadata.CustomFields.MD_ENT_TYPE_UIDColumn.ColumnName;

                string idColumn = CustomFieldMetadata.CustomFields.MD_PROP_IDColumn.ColumnName;

                string fieldColumn = CustomFieldMetadata.CustomFields.MD_PROP_TYPE_ENUMColumn.ColumnName;

               

                // Create a Filter object.

                PSLibrary.Filter cfFilter = new PSLibrary.Filter();

     

                // Restrict the filter to one table.

                // (the only table in the case of the CustomFieldsDataSet)

                cfFilter.FilterTableName = tableName;

     

                // Add fields to the filter to limit the columns you want returned.

                // Set the sort order on the name column (optional for the Field constructor).

                cfFilter.Fields.Add(new PSLibrary.Filter.Field(tableName, nameColumn, PSLibrary.Filter.SortOrderTypeEnum.None));

                cfFilter.Fields.Add(new PSLibrary.Filter.Field(uidColumn));

                cfFilter.Fields.Add(new PSLibrary.Filter.Field(idColumn));

                cfFilter.Fields.Add(new PSLibrary.Filter.Field(fieldColumn));

     

                // Set the EntityUID to restrict the cf definitions

                // In this case to Project Custom Fields

                Guid cfEntityUid = new Guid(PSLibrary.EntityCollection.Entities.ProjectEntity.UniqueId);

     

                // Use the Criteria property to set conditions for the rows you want returned. 

                // You can use multiple sets of operators, and connect the conditions with logical operators.

                // In this case, the MD_ENT_TYPE_UID field must have a value equal to the entityUid.

                PSLibrary.Filter.FieldOperator ProjectCustomFields = new PSLibrary.Filter.FieldOperator(PSLibrary.Filter.FieldOperationType.Equal,

                                                           entityUidColumn, cfEntityUid); // Project Custom Fields           

                PSLibrary.Filter.FieldOperator ByName = new PSLibrary.Filter.FieldOperator(PSLibrary.Filter.FieldOperationType.Equal,

                                                           nameColumn, CustomFieldName); // CustomFieldName

                cfFilter.Criteria = new PSLibrary.Filter.LogicalOperator(PSLibrary.Filter.LogicalOperationType.And, ProjectCustomFields, ByName);

     

                // The GetXml method creates the xmlFilter parameter for ReadCustomFields

                // ReadCustomFields gives us the metadata we need

                bool autoCheckOut = false;

                CustomFieldMetadata = customfieldsWS.ReadCustomFields(cfFilter.GetXml(), autoCheckOut);

                // Should throw if no CF definition found…

                return CustomFieldMetadata;

     

            }

     

        }

    }

     

    Thursday, May 13, 2010 1:33 AM
    Moderator
  • Hi Jayaraja - you should avoid using these "local custom" fields:

    1/ They are specific to a project (so there is no meta-data control across projects)

    2/ By default they don't go to the reporting database so they are hard to write reports against.

    use "Enterprise custom fields"  ECF instead - then the metadata is shared across projects making it easier to extract data.

    --> Use server settings/Enterprise custom fields and Lookup tables

    You can create project levels fields, resource level fields and task level fields. It sounds like you want task-level fields so you need to create one on the server, restart pro, open the project, add the ECF to the task sheet, set values and save the data back to the server.

     

    The code in my first post is for project level fields - you will need to alter it as follows:

    - ReadProjectEntities - change the enum to get the task table and task custom fields
    - myProjectDS.ProjectCustomFields.Select - select your data from the taskcustomfieldstable

    etc etc.

    ...pc

     

    • Marked as answer by Jayaraja Monday, May 17, 2010 8:59 AM
    Monday, May 17, 2010 7:11 AM
    Moderator

All replies

  • Sure, here is some code that should help (I'm doing quite a bit of code to filter what I want, strip this if you want everything) - it shows how to read and edit a project CF called PCTestCf. Lot of concepts to get to grips with here, the sdk is your friend:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=46007f25-b44e-4aa6-80ff-9c0e75835ad9&displaylang=en

    Cheers...pc

    using System.Web.Services.Protocols;  //Soap Exception

    using PSLibrary = Microsoft.Office.Project.Server.Library; // Filter, (dll from the SDK)

    using QuickProjectList.ProjectWS;  // Project data

    using QuickProjectList.CustomFieldWS; // CF Metadata

     

    namespace QuickProjectList

    {

        public partial class Form1 : Form

        {

            public const int ProjectCustomFields = 32; // Avoid reading the whole project

     

            public Form1()

            {

                InitializeComponent();

            }

     

            private void button1_Click(object sender, EventArgs e)

            {

                // Hookup the web service

                Project projectWS = new Project();

                projectWS.UseDefaultCredentials = true;

     

               

                Guid ProjectUid = new Guid("6da848ee-9f37-4c34-8a13-a406d95a6f89"); // Change to your value

     

                try

                {

                    // Read the Project level custom fields only, and from the working store (the editable version)

                    ProjectDataSet myProjectDS = projectWS.ReadProjectEntities(ProjectUid, ProjectCustomFields , DataStoreEnum.WorkingStore);

                    //this.dataGridView1.DataSource = myProjectDS.ProjectCustomFields;

     

                    // Get the Custom field metadata

                    CustomFieldDataSet myCustomFieldsDS = CustomFieldMetadata("PCTestCF"); // Change to your field name

                    //this.dataGridView1.DataSource = myCustomFieldsDS.CustomFields;

                   

                    // Check the Project data for this CF value

                    //MessageBox.Show(myCustomFieldsDS.CustomFields[0].MD_PROP_UID.ToString());

                 

                    // Does project level have TFS ID

                    DataRow[] cfRows = myProjectDS.ProjectCustomFields.Select(myProjectDS.ProjectCustomFields.MD_PROP_UIDColumn.ColumnName

                                                                     + " = '"

                                                                     + myCustomFieldsDS.CustomFields[0].MD_PROP_UID.ToString()

                                                                     + "'"); // beware loc bugs if not en-us (AE)

                    switch (cfRows.Length)

                    {

                        case 0:

                            // no TFS entry, so add it

                            ProjectDataSet.ProjectCustomFieldsRow cfRow = myProjectDS.ProjectCustomFields.NewProjectCustomFieldsRow();

                            cfRow.MD_PROP_UID = myCustomFieldsDS.CustomFields[0].MD_PROP_UID;

                            cfRow.MD_PROP_ID = myCustomFieldsDS.CustomFields[0].MD_PROP_ID;

                            cfRow.PROJ_UID = ProjectUid;

                            cfRow.FIELD_TYPE_ENUM = (int)PSLibrary.PSDataType.NUMBER;

                            cfRow.NUM_VALUE = 113; // Set to your value

                            cfRow.CUSTOM_FIELD_UID = Guid.NewGuid();

     

                           myProjectDS.ProjectCustomFields.AddProjectCustomFieldsRow(cfRow);

     

                            //Check out, send the changes, publish them and Check in

     

                            Guid jobId = Guid.NewGuid();

                            Guid sessionId = Guid.NewGuid();

                            projectWS.CheckOutProject(ProjectUid, sessionId, "TFS Custom Field Updater");

                            projectWS.QueueUpdateProject(jobId, sessionId, (ProjectDataSet) myProjectDS.GetChanges(), false);

                            jobId = Guid.NewGuid(); // new queue job

                            projectWS.QueuePublish(jobId, ProjectUid, false, string.Empty); // optionally publish changes to the reporting db/PWA UI

                            jobId = Guid.NewGuid(); // new queue job

                            projectWS.QueueCheckInProject(jobId, ProjectUid, false, sessionId, "TFS Custom Field Updater");

     

                            //WaitForQueue(q, jobId);

     

                            break;

                        case 1:

                            // TFS entry, why are we changing it?

                            break;

                        default:

                            //error, shouldn't be able to get here

                            break;

                    }

                }

                catch (SoapException ____)

                {

                    MessageBox.Show(String.Format("Soap Exception: {0}\nStack:\n{1}", ____.Code + " " + ____.Message, ____.StackTrace));

                }

                catch (Exception ex)

                {

                    MessageBox.Show(String.Format("General Exception: {0}\nStack:\n{1}", ex.Message, ex.StackTrace));

                }

            }

            private CustomFieldDataSet CustomFieldMetadata(string CustomFieldName)

            {           

                CustomFields customfieldsWS = new CustomFields(); // may want to move this out if iterating on this function

                customfieldsWS.UseDefaultCredentials = true;

     

                CustomFieldDataSet CustomFieldMetadata = new CustomFieldDataSet();

     

                // Column names for the filter (could hard code)

                string tableName = CustomFieldMetadata.CustomFields.TableName;

                string nameColumn = CustomFieldMetadata.CustomFields.MD_PROP_NAMEColumn.ColumnName;

                string uidColumn = CustomFieldMetadata.CustomFields.MD_PROP_UIDColumn.ColumnName;

                string entityUidColumn = CustomFieldMetadata.CustomFields.MD_ENT_TYPE_UIDColumn.ColumnName;

                string idColumn = CustomFieldMetadata.CustomFields.MD_PROP_IDColumn.ColumnName;

                string fieldColumn = CustomFieldMetadata.CustomFields.MD_PROP_TYPE_ENUMColumn.ColumnName;

               

                // Create a Filter object.

                PSLibrary.Filter cfFilter = new PSLibrary.Filter();

     

                // Restrict the filter to one table.

                // (the only table in the case of the CustomFieldsDataSet)

                cfFilter.FilterTableName = tableName;

     

                // Add fields to the filter to limit the columns you want returned.

                // Set the sort order on the name column (optional for the Field constructor).

                cfFilter.Fields.Add(new PSLibrary.Filter.Field(tableName, nameColumn, PSLibrary.Filter.SortOrderTypeEnum.None));

                cfFilter.Fields.Add(new PSLibrary.Filter.Field(uidColumn));

                cfFilter.Fields.Add(new PSLibrary.Filter.Field(idColumn));

                cfFilter.Fields.Add(new PSLibrary.Filter.Field(fieldColumn));

     

                // Set the EntityUID to restrict the cf definitions

                // In this case to Project Custom Fields

                Guid cfEntityUid = new Guid(PSLibrary.EntityCollection.Entities.ProjectEntity.UniqueId);

     

                // Use the Criteria property to set conditions for the rows you want returned. 

                // You can use multiple sets of operators, and connect the conditions with logical operators.

                // In this case, the MD_ENT_TYPE_UID field must have a value equal to the entityUid.

                PSLibrary.Filter.FieldOperator ProjectCustomFields = new PSLibrary.Filter.FieldOperator(PSLibrary.Filter.FieldOperationType.Equal,

                                                           entityUidColumn, cfEntityUid); // Project Custom Fields           

                PSLibrary.Filter.FieldOperator ByName = new PSLibrary.Filter.FieldOperator(PSLibrary.Filter.FieldOperationType.Equal,

                                                           nameColumn, CustomFieldName); // CustomFieldName

                cfFilter.Criteria = new PSLibrary.Filter.LogicalOperator(PSLibrary.Filter.LogicalOperationType.And, ProjectCustomFields, ByName);

     

                // The GetXml method creates the xmlFilter parameter for ReadCustomFields

                // ReadCustomFields gives us the metadata we need

                bool autoCheckOut = false;

                CustomFieldMetadata = customfieldsWS.ReadCustomFields(cfFilter.GetXml(), autoCheckOut);

                // Should throw if no CF definition found…

                return CustomFieldMetadata;

     

            }

     

        }

    }

     

    Thursday, May 13, 2010 1:33 AM
    Moderator
  • when i run this code, 

    i get an exception at this line

         ProjectDataSet myProjectDS = projectWS.ReadProjectEntities(ProjectUid, ProjectCustomFields ,DataStoreEnum.WorkingStore);

     

    {System.Net.WebException: The request failed with the error message:

    --

    <html><head><title>Object moved</title></head><body>

    <h2>Object moved to <a href="http://server:102/_layouts/1033/error.aspx?ErrorText=The%20request%20failed%20with%20HTTP%20status%20404%3A%20Not%20Found%2E">here</a>.</h2>

    </body></html>

    -.

       at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

       at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

       at TestPWA.ProjectWS.Project.ReadProjectEntities(Guid projectUid, Int32 ProjectEntityType, DataStoreEnum dataStore) in C:\Users\Hexacorp\Documents\Visual Studio 2005\Projects\TestPWA\TestPWA\Web References\ProjectWs\Reference.cs:line 508

       at TestPWA.Form1.button1_Click(Object sender, EventArgs e) in C:\Users\Hexacorp\Documents\Visual Studio 2005\Projects\TestPWA\TestPWA\Form1.cs:line 161}


    jayaraja
    Thursday, May 13, 2010 10:44 AM
  • hi,

    now am able to traverse thro the code..

    am getting error at this line     MessageBox.Show(myCustomFieldsDS.CustomFields[0].MD_PROP_UID.ToString());

    there is no row position at 0

    i have changed the name of custom column to my name but still no luck . i could not get any value.

    is there a way tat i could get all the records of the custom columns ? plz help


    jayaraja
    Thursday, May 13, 2010 12:48 PM
  • Hi Jayaraja - you seem to be climbing several learning curves at the same time, so I'm a little bit wary of being able to see your problem, but here are some deeper explanations of the code that might help unblock you:

     

    1/ CustomFieldDataSet myCustomFieldsDS = CustomFieldMetadata("PCTestCF");

    You must change PCTestCF to the field name you want to read/update.

    2/ Guid ProjectUid = new Guid("6da848ee-9f37-4c34-8a13-a406d95a6f89");

    You must change this guid to one in your system (a proj_uid from the msp_projects table in the draft database) that has the custom field value set already <-- so you must set the value for the cf for the project you are reading!!

    3/ cfRow.FIELD_TYPE_ENUM = (int)PSLibrary.PSDataType.NUMBER;

    If updating you must set this to be the correct type for the custom field

    Hope this helps...pc

     

     

    Friday, May 14, 2010 11:03 AM
    Moderator
  • hi patrick,

    thanks for you valuable reply.

    i changed my custom field to my field name say "Text1(text1))"

    i have change my projectuid to my project uid .

    still my code return 0 rows.. but if i open up the db of project published i am able to see the values in the db tables.

    where it goes wrong?


    jayaraja
    Friday, May 14, 2010 2:38 PM
  • Hmm, I have a feeling that you've defined a local custom field (and likely a task custom field - the code above is working on project custom fields). You need to go to the server settings page, manage cutom fields and then create a project custom field. Then open the Project in Project Pro and set the value and save the project back.

    ...pc

    Sunday, May 16, 2010 3:01 AM
    Moderator
  • hi pc,

    when i double clikc on a row in a project it comes up with tasks i think so ,on the last tab it shows custom fields , is tat a task custom field ? if so how could i reterieve those values ?

    when i open up the published db , and whn i open up the custom fields tables i am able to see these cols there and their values.

    how would i get these custom field values? thanks for ur timely help


    jayaraja
    Sunday, May 16, 2010 6:20 AM
  • Hi Jayaraja - you should avoid using these "local custom" fields:

    1/ They are specific to a project (so there is no meta-data control across projects)

    2/ By default they don't go to the reporting database so they are hard to write reports against.

    use "Enterprise custom fields"  ECF instead - then the metadata is shared across projects making it easier to extract data.

    --> Use server settings/Enterprise custom fields and Lookup tables

    You can create project levels fields, resource level fields and task level fields. It sounds like you want task-level fields so you need to create one on the server, restart pro, open the project, add the ECF to the task sheet, set values and save the data back to the server.

     

    The code in my first post is for project level fields - you will need to alter it as follows:

    - ReadProjectEntities - change the enum to get the task table and task custom fields
    - myProjectDS.ProjectCustomFields.Select - select your data from the taskcustomfieldstable

    etc etc.

    ...pc

     

    • Marked as answer by Jayaraja Monday, May 17, 2010 8:59 AM
    Monday, May 17, 2010 7:11 AM
    Moderator
  • thaank you for ur time and patience 
    jayaraja
    Monday, May 17, 2010 8:59 AM