none
How can I get a task Guid filtering from several custom fields? RRS feed

  • Question

  • Hi,

    I'm building a console application to connect a self program to Project Server.

    The application reads data from SQL and create a projects, task and custom fields for this task using PSI.

    Now I have to update this task but the information I have in SQL data are store on custom fields. Is there a way to get the task filtering by the custom fields values?

    Thank you!

    Monday, March 26, 2012 3:07 PM

All replies

  • Hi LyE-,

     if a project is published, you´ll find values of task level customfields in the MSP_EpmTask_UserView of the reporting database.

    Greets
    Maik

    Tuesday, March 27, 2012 5:21 AM
  • Hi magran,

    Thank you for your answer. I'm looking at the table and it can be very usefull.

    But is there no way to get it through PSI services? Maybe not always the project is published.

    Greets.


    • Edited by LyE- Tuesday, March 27, 2012 11:28 AM
    Tuesday, March 27, 2012 10:59 AM
  • Hi LyE-,

    sure you can get the values throught PSI.

    - get the CustomfieldUID from the Customfield WebSvc ( you can filter it by entity and name )

    - you can use e.g the ReadProjectEntities method to read only customfields.

    Greets
    Maik

    Tuesday, March 27, 2012 2:03 PM
  • Hi Maik,

    Yes I can read customfields, but I think that's not what I want...

    I need to update a task and to identify it I only know the values of two of its custom fields. If the project is published I can use the table you told me before, MSP_EpmTask_UserView, to get the Task_Uid but if the project is not published what can I do to get it?

    Maybe I am not explain me very well (and my english it's no the best :S)...

    Greets.

    LyE

    Tuesday, March 27, 2012 3:46 PM
  • Hi LyE-,

    Task filtering can be done using the PSI.

    Below is the piece of code, which we used some time back.

    Steps:

    1. Read the project.

    newProjectData = pjsrv.ReadProjectEntities(proj_guid, 64, SvcProject.DataStoreEnum.PublishedStore);
            newProjectData = pjsrv.ReadProject(proj_guid, SvcProject.DataStoreEnum.PublishedStore);

    2. Check out the project.

    pjsrv.CheckOutProject(proj_guid, sessionGuid, "Checking out the project");

    3. Check whether there is any task exists with a particular Guid and return the same Guid.

    string TaskUid = newProjectData.Tables[newProjectData.Task.TableName].Select("TASK_UID = '" + projL2WBS.ToString() + "'")[0]["TASK_UID"].ToString();
    TaskUid = TaskUid.ToUpper();
            projL2WBS = projL2WBS.ToUpper();

    4. Check whether the Guid received from DB (projL2WBS in this case) and Guid received from PSI (TaskUid in this case) same or not and update the required custom fields as shown below

                            if (TaskUid == projL2WBS)
                            {
                                DataView dvTask = new DataView(newProjectData.Tables[1]);
                                foreach (DataRow drTask in newProjectData.Tables[1].Rows)
                                {
                                    if (drTask["TASK_UID"].ToString().ToUpper() == TaskUid.ToString())
                                    {
                                        drTask["TASK_NAME"] = strWBSL2Desc;
                                    }
                                }
                            }

    Hope the above explanation helps. If not, pl let me know for further queries.


    B Rajasekhar Reddy. Life’s real failure is when you do not realize how close you were to success when you gave up - Vinod Kumar (http://blogs.extremeexperts.com/)

    Wednesday, March 28, 2012 11:36 AM
  • Hi Rajasekhar,

    I can find a task searching by its Guid, but I need to find it searching by two or three values of its custom fields...

    An example, I have some tasks with its Guid:

    • TASK_NAME = Task1 - TASK_UID = Task1_Guid
    • TASK_NAME = Task2 - TASK_UID = Task2_Guid
    • TASK_NAME = Task3 - TASK_UID = Task3_Guid

    And some custom fields for these tasks:

    • MD_PROP_NAME: CustomField1

              - For Task1: TASK_UID = Task1_GUID - TEXT_VALUE = "20060.00"
              - For Task2: TASK_UID = Task2_GUID - TEXT_VALUE = "20060.01"
              - For Task3: TASK_UID = Task3_GUID - TEXT_VALUE = "20060.00"

    • MD_PROP_NAME: CustomField2

              - For Task1: TASK_UID = Task1_GUID - NUM_VALUE = 30
              - For Task2: TASK_UID = Task2_GUID - NUM_VALUE = 91
              - For Task3: TASK_UID = Task3_GUID - NUM_VALUE = 91

    Now I need to update some tasks and for example I know that I have to update the task with CustomField1 = "20060.00" and CustomField2 = 91. How can I get its Guid?

    As Maik tolds me I can use this view, MSP_EpmTask_UserView, if the project is published. But before publish it? Is there any way?

    Thanks!!

    Thursday, March 29, 2012 10:37 AM
  • Hi LyE-,

    i´m not sure if there´s another way using PSI, but you could do something like this

    - Use the ProjectSvc.ReadProjectList method to get a list of all projects ( this call requires ManageQueue and ManageSecurity permissions, but i´m assuming that you use a administrative account to update the values )

    - Loop through all projects and call the ProjectSvc.ReadProjectEntites method (like Rajasekhar wrote) with the ProjectEntityType parameter set to 64 for TaskCustomFields, and the dataStore parameter set to DataStoreEnum.WorkingStore ( cause you want to check non published values )

    - Check the TaskCustomFieldsDataTable for the specified values  ( TEXT_VALUE, NUM_VALUE etc ), if you found the values you found the task and project uids too. 

    Depending on your hardware and numbers of projects this can be very slow...

    I´m pretty sure that this can also be done by using SQL directly against the draft database but, as always, this is not supported in any way :-)

    Greets
    Maik

    Friday, March 30, 2012 7:44 AM