Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
PSI - Reading Custom Lookup Field Text value RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear All,
    i use this code to read custom lookup field text value but all i can get the guid of it "CODE_VALUE".

    SvcProject.ProjectDataSet ProjectDataset = new SvcProject.ProjectDataSet();
      ProjectDataset = projectClient.ReadProject(new Guid("7079a88e-5e2d-4f22-bbf1-5115014718cc"), SvcProject.DataStoreEnum.PublishedStore);
      string x = ProjectDataset.ProjectCustomFields[0].CODE_VALUE.ToString();

    is there any way to get the text value of custom lookup field direct ?

    Thanks

    Regards, Mohamed
    Monday, January 24, 2011 4:44 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Mohamed,

    Several comments:

    1. You shouldn't check out the lookup tables in the ReadLookupTables method. You aren't going to modify them.
    2. There is no language with an LCID value of 0. English, e.g., is 1033. So, the params should be, for English: ReadLookupTable(string.Empty, false, 1033);
    3. The lookup table GUID (LT_UID) is not the custom field GUID (MD_PROP_UID). The GUID of the lookup table value (LT_STRUCT_UID) and the text value (LT_VALUE_TEXT) are in the LookupTableTrees table, not the LookupTables table. 
    4. You don't need to convert the GUIDs to a string to compare them. So, the iteration should be: 
       SvcLookupTable.LookupTableDataSet lookupTableDs = 
    lookupTableClient.ReadLookupTables(string.Empty, false, 1033);

   string theLookupTableValue;
   Guid customFieldValueUid = projectDs.ProjectCustomFields[0].CODE_VALUE;

   for (int i = 0; i < lookupTableDs.LookupTableTrees.Count; i++)
   {
    if (lookupTableDs.LookupTableTrees[i].LT_STRUCT_UID == customFieldValueUid)
    {
     theLookupTableValue = lookupTableDs.LookupTableTrees[i].LT_VALUE_TEXT;
     break;
    }
   }
    Reading all of the lookup tables is not very efficient. To reduce the amount of data sent from the server, you could create a an xmlFilter parameter that finds the lookup table containing the LT_STRUCT_UID.
    --Jim
    Jim Corbin [MSFT]
    Tuesday, January 25, 2011 3:56 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Nope, no direct way. You need to get the text value of the lookup table item that is assigned to the custom field.

    1. Get the GUID of the custom field. That is the MD_PROP_UID property in a ProjectCustomFields row.
    2. Get the GUID of the lookup table value, which is the CODE_VALUE property in the ProjectCustomFields row that contains the correct MD_PROP_UID.
    3. Create a reference to the LookupTable service, and set the endpoint.
    4. Get the LookupTableDataSet from the ReadLookupTables method (use the CODE_VALUE to create a filter, or use string.empty for the filter, and then go to Step 5).
    5. Iterate through the lookup table values to get the LT_VALUE_TEXT property that corresponds to the LT_STRUCT_UID (which is also the CODE_VALUE) in the LookupTableTrees row.

    --Jim

    Jim Corbin [MSFT]
    Monday, January 24, 2011 9:02 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Jim,

    I did all steps but in step 5: unfortunately, i couldn't find LT_VALUE_TEXT neighter LT_STRUCT_UID. I tried to use LT_UID but no matching! 

    here is my code:

    lookupTableDataSet = lookupTableClient.ReadLookupTables(string.Empty, true, 0);
    for (int i = 0; i < lookupTableDataSet.LookupTables.Count; i++)
                {
                    if (lookupTableDataSet.LookupTables[i].LT_UID.ToString() == ProjectDataset.ProjectCustomFields[0].MD_PROP_UID.ToString())

    what do you think ?

    Thanks

    Regards, Mohamed
    Tuesday, January 25, 2011 10:41 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Mohamed,

    Several comments:

    1. You shouldn't check out the lookup tables in the ReadLookupTables method. You aren't going to modify them.
    2. There is no language with an LCID value of 0. English, e.g., is 1033. So, the params should be, for English: ReadLookupTable(string.Empty, false, 1033);
    3. The lookup table GUID (LT_UID) is not the custom field GUID (MD_PROP_UID). The GUID of the lookup table value (LT_STRUCT_UID) and the text value (LT_VALUE_TEXT) are in the LookupTableTrees table, not the LookupTables table. 
    4. You don't need to convert the GUIDs to a string to compare them. So, the iteration should be: 
       SvcLookupTable.LookupTableDataSet lookupTableDs = 
    lookupTableClient.ReadLookupTables(string.Empty, false, 1033);

   string theLookupTableValue;
   Guid customFieldValueUid = projectDs.ProjectCustomFields[0].CODE_VALUE;

   for (int i = 0; i < lookupTableDs.LookupTableTrees.Count; i++)
   {
    if (lookupTableDs.LookupTableTrees[i].LT_STRUCT_UID == customFieldValueUid)
    {
     theLookupTableValue = lookupTableDs.LookupTableTrees[i].LT_VALUE_TEXT;
     break;
    }
   }
    Reading all of the lookup tables is not very efficient. To reduce the amount of data sent from the server, you could create a an xmlFilter parameter that finds the lookup table containing the LT_STRUCT_UID.
    --Jim
    Jim Corbin [MSFT]
    Tuesday, January 25, 2011 3:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Jim,

    Thanks much. I appreciate your comments and your reply really helped me :) 

    Thanks,

    Regards, Mohamed
    Tuesday, January 25, 2011 8:13 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Mohamed and Jim,

    I have read this post but I can't really understand what you are talking about. This is my problem:

    I have created two task custom fields through using the project server: phase number and task number. Each of these custom fields is of type number and is set to a numbers lookuptable. I need a way to access these values using the PSI. I was able to access these values before I set them to a lookuptable using the below code:

     SvcProject.ProjectDataSet projectds1 = project.ReadProjectEntities(projectGUID,64, SvcProject.DataStoreEnum.WorkingStore);
     projectds.TaskCustomFields[k].NUM_VALUE;

    After I have set them to lookuptable, the NUM_VALUE returns a DBNULL exception. I have also displayed the whole table using the WRITETABLESTOCONSOLE function available in the SDK but it also shows that the NUM_VALUE is empty. At the same time, there is no column that shows the values of the custom fields. So is what you guys are discussing the same problem I am facing? If yes can you please give an elaborated explanation of the solution because I quite didn't really get it from what you already have.

    Thanks,
    George

    Monday, February 14, 2011 9:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi George,

    As your application shows, NUM_VALUE doesn't exist in a task custom field, when the value is from a lookup table. You need to find the CODE_VALUE for that custom field, which is the GUID of the lookup table value.

    Then iterate through the lookup table trees, until you find the LT_STRUCT_UID that matches the CODE_VALUE. The numeric value in your case is then LT_VALUE_NUM.

    hth,
    --Jim

    Jim Corbin [MSFT]
    Monday, February 14, 2011 6:37 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Jim,

    I tried what you described what I am getting is very strange. The  projectDs.ProjectCustomFields is always empty. The ProjectCustomFields table does not contain any value although I have the customfields and I am using them in the project! What is causing this? How may I fix it?

    Thanks,
    George
    Monday, February 21, 2011 8:20 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    If your custom fields are local (defined only in Project Pro within the project) and not enterprise custom fields, they do not show in the ProjectCustomFields table.
    Jim Corbin [MSFT]
    Monday, February 21, 2011 2:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Jim,

    No my custom fields are enterprise global custom fields.
    This is why I'm finding it weird that the projectDs.ProjectCustomFields.Count returns zero!  While lookupTableDs.LookupTableTrees.Count returns 87 values.

    Are there any other reasons for the projectDs.ProjectCustomFields.Count to return no values?

    Regards,
    George

    Monday, February 21, 2011 3:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Jim,

    First of all, thanks for posting!  I'm posting in the same thread because my questions are directly related.  I'm essentially trying to do the same thing: read the value of a custom field that contains a look-up table through PSI.  A few concerns derived from your posts (let's call my field Field1, and it is a Project Custom Field):

    1. The field does not show up in MSP_EpmProject_UserView.  I'm guessing if the field contains a lookup table, it won't show in this table/view.
    2. I need to read the value of the field immediately after the PDP is saved and submitted to workflow.  If there's any lag with published/reporting database synchronizationm then querying the reporting DB is out of the question and querying the published DB is not that desirable, although doable.
    3. Field1 is a multivalue select. It may hold more than one entry, which complicates things just a bit.

    So my 2 options are:
    1. Get the CODE_VALUE(s) through querying the tables directly or,
    2. Get the CODE_VALUE(s) through PSI, like you described.

    If I went with option 1:
    The shortest way to get to the code values (if I was to query the reporting DB) seems to be in querying MSPCFPRJ_Field1_AssociationView, filtering the results by EntityUID and CustomFieldUID.  Does this sound right or do you know of a better way?  I haven't bothered to look into possibility of being forced to query the published DB yet (due to al ag in reporting DB update, if one existed).

    If I went with option 2:
    Here's what I've done so far:
    1. ReadProjectEntities(projectGuid, 32, published);
    2. Here, in order to filter out the rows that I need, I thought I could use ProjectWebService.FindByCustomFieldUid method, but came to realization that the parameter this method takes is not MD_PROP_UID, but Custom_Field_Uid, a system identifier Id in MSP_PROJ_CUSTOM_FIELD_VALUES.  The method is quite useless to me in this case.  Why would anyone even want to filter out by this field whose purpose is being a primary key?  Usually what's available to a programmer is a field GUID, not some database primary key. 
    3. In lieu of this, how would I filter out the projectDataSet to just the rows that I need (I do have MD_PROP_UID and PROJ_UID available)? Since xmlFilter is not available in ReadProjectEntities, how would this be done programmatically?  (Actually I have just came across an article that suggests using DataTable.Select() or Linq to filter data returned by PSI, but haven't had a chance to read it yet.  It talks about task data, but I'm guessing it can be applied to any table, not just Tasks.  Link below if anyone is interested.)

    http://projectserverblogs.com/?p=2159

    Anyway, sorry for the long post and I was wondering if you could provide any input.

    Thank you,
    Ilya

     

    Monday, September 19, 2011 6:54 PM