none
How to use CSOM to get a list of projects based on a custom field value? RRS feed

  • Question

  • Using the client object model, how do you load a list of projects filtered by a custom field value? For example, the following loads projects based on the project name and whether or not the project is an enterprise project:

    var pubProjects = projContext.LoadQuery(projContext.Projects
        .Where(p => p.IsEnterpriseProject == true
         && p.Name == projName));

    Is it possible to do something like:

     var pubProjects = projContext.LoadQuery(projContext.Projects
         .Where(p => p.MyCustomField== "abc"
         ));


    Mike G.

    Wednesday, December 18, 2013 4:01 PM

Answers

  • Hi,

    Google brought me back to this old thread when I was searching for the solution to this exact issue myself.

    Looks like the tip above was on the right track, the custom field internal name is required, only the '_x005f_' XML code needs to be replaced with the correct '_' character, so for example (for task custom fields):

    var taskList = projContext.LoadQuery(
    	project.Tasks.Where(
    		task => task["Custom_2b67e0dc5ab6e3118a2f005056b02554"] == "value"));
    
    projContext.ExecuteQuery();

    Hope that helps anyone else out there who's looking for this solution.


    Martin Laukkanen
    Nearbaseline blog - nearbaseline.com/blog
    Bulk Edit and other Apps - nearbaseline.com/apps

    Thursday, April 10, 2014 8:28 AM

All replies

  • I haven't tried this myself, but looking at the REST data I expect that you will need to use the Custom Field 'Internal Name' not the full name. For example open:

    /_api/ProjectServer/Projects('e8361e9f-ba4c-e311-aebe-00155d508f13')/IncludeCustomFields

    (Replace the GUID with one of your projects or start from: /_api/ProjectServer/Projects)

    You'll see that what REST see's is something like:

    <m:properties>
    <d:ApprovedEnd m:type="Edm.DateTime">0001-01-01T00:00:00</d:ApprovedEnd>
    <d:ApprovedStart m:type="Edm.DateTime">0001-01-01T00:00:00</d:ApprovedStart>
    <d:CheckedOutDate m:type="Edm.DateTime">2013-12-18T02:23:47.83</d:CheckedOutDate>
    <d:CheckOutDescription>CSOM update</d:CheckOutDescription>
    <d:CheckOutId m:type="Edm.Guid">00000000-0000-0000-0000-000000000000</d:CheckOutId>
    <d:CreatedDate m:type="Edm.DateTime">2013-11-13T15:23:42.603</d:CreatedDate>
    <d:Id m:type="Edm.Guid">e8361e9f-ba4c-e311-aebe-00155d508f13</d:Id>
    <d:IsCheckedOut m:type="Edm.Boolean">false</d:IsCheckedOut>
    <d:LastPublishedDate m:type="Edm.DateTime">2013-12-18T02:23:49.073</d:LastPublishedDate>
    <d:LastSavedDate m:type="Edm.DateTime">2013-12-18T02:23:48.21</d:LastSavedDate>
    <d:OptimizerDecision m:type="Edm.Int32">0</d:OptimizerDecision>
    <d:PlannerDecision m:type="Edm.Int32">0</d:PlannerDecision>
    <d:ProjectType m:type="Edm.Int32">0</d:ProjectType>
    <d:Name>Inf Deployment Project</d:Name>
    <d:WinprojVersion m:type="Edm.Decimal">14.0000000000</d:WinprojVersion>
    <d:Custom_x005f_598496ba2037e311ad7600155d84ca1b m:type="Edm.Decimal">0.000000</d:Custom_x005f_598496ba2037e311ad7600155d84ca1b>
    <d:Custom_x005f_6e8496ba2037e311ad7600155d84ca1b m:type="Edm.Decimal">0.000000</d:Custom_x005f_6e8496ba2037e311ad7600155d84ca1b>
    <d:Custom_x005f_838496ba2037e311ad7600155d84ca1b m:type="Edm.Decimal">0.000000</d:Custom_x005f_838496ba2037e311ad7600155d84ca1b>
    <d:Custom_x005f_988496ba2037e311ad7600155d84ca1b>0</d:Custom_x005f_988496ba2037e311ad7600155d84ca1b>
    <d:Custom_x005f_ad8496ba2037e311ad7600155d84ca1b>0</d:Custom_x005f_ad8496ba2037e311ad7600155d84ca1b>
    <d:Custom_x005f_b4c1f33194bbe11195dc00155d02c97f xml:space="preserve">This is some really really long text</d:Custom_x005f_b4c1f33194bbe11195dc00155d02c97f>
    </m:properties>

    So based on that I would test by re-writing your LINQ to use an internal name of the field you want (see:  /_api/ProjectServer/CustomFields).

    I'd be interested to know if that works!


    Martin Laukkanen (Project Server Blog - www.nearbaseline.com/blog)

    Wednesday, December 18, 2013 11:34 PM
  • So, in C#, I can't just use:

    .Where(p => p.Custom_x005f_7ed5586de667e31196de00155db00a01 == "abc"

    ...as, that of course gives a compilation error.

    The following isn't valid, as the collection of fields returns objects, not strings:

     .Where(p => p["Custom_x005f_7ed5586de667e31196de00155db00a01"] == "abc"
    
    //or
    
    .Where(p => p.FieldValues["Custom_x005f_7ed5586de667e31196de00155db00a01"] == "abc"

    ToString isn't supported, and the following yields a "get_Item member cannot be used" message.

     .Where(p => (string)p.FieldValues["Custom...


    Mike G.

    Thursday, December 19, 2013 3:00 PM
  • Hi there,

    Hmm, okay then last suggestion (again still stabbing in the dark here); use the Include method with your query like you can do with SharePoint list collections, e.g.:

    ClientContext clientContext = new ClientContext(siteUrl);
    Web oWebsite = clientContext.Web;
    ListCollection collList = oWebsite.Lists;
    
    IEnumerable<SP.List> listInfo = clientContext.LoadQuery(
                    collList.Include(
                        list => list.Title,
                        list => list.Fields.Include(
                            field => field.Title,
                            field => field.InternalName)));
     
    clientContext.ExecuteQuery();

    Maybe something like that?


    Martin Laukkanen (Project Server Blog - www.nearbaseline.com/blog)

    Sunday, December 22, 2013 10:53 PM
  • Hi,

    Google brought me back to this old thread when I was searching for the solution to this exact issue myself.

    Looks like the tip above was on the right track, the custom field internal name is required, only the '_x005f_' XML code needs to be replaced with the correct '_' character, so for example (for task custom fields):

    var taskList = projContext.LoadQuery(
    	project.Tasks.Where(
    		task => task["Custom_2b67e0dc5ab6e3118a2f005056b02554"] == "value"));
    
    projContext.ExecuteQuery();

    Hope that helps anyone else out there who's looking for this solution.


    Martin Laukkanen
    Nearbaseline blog - nearbaseline.com/blog
    Bulk Edit and other Apps - nearbaseline.com/apps

    Thursday, April 10, 2014 8:28 AM
  • To clarify further, the reference to the custom field needs to be cast to the correct type:

    var pubProjects = projContext.LoadQuery(projContext.Projects
                    .Where(p => (string)p["Custom_9f2db926041fe311856e00155d416007"] == "SomeValue"
                    ));
    
    projContext.ExecuteQuery();


    Mike G.

    Tuesday, April 22, 2014 7:43 PM
  • This worked for me, but one thing that I noticed is that this type of query can take a long time to execute since these fields are not indexed.  
    Wednesday, March 16, 2016 11:19 PM
  • Thanks, Martin.  Your answer helped me solve the problem of retrieving a set of tasks whose CustomField values meet specified criteria.   There is almost no documentation out there on how to do this sort of thing, so I really appreciate your reply to Mike G.

    ...Jim Black

    Monday, May 2, 2016 3:16 PM
  • Hello,

    I have a similar issue but with a custom field that has an associated lookup table. How can I retrieve all the tasks with that custom field having a specific value from the lookup table? Any help much appreciated.

    George

    Monday, July 31, 2017 9:52 AM