none
Will an OData query on a nonexistent (local custom) field cause a bad request? RRS feed

  • Question

  • I am trying to query MS Project Server to retrieve some tasks, filtering (in part) on a local custom field. My query looks like this:

    http://server/site/_api/ProjectServer/Projects('project guid')/Tasks?$filter=BaselineFinish gt datetime'0001-01-01T00:00:00' and BaselineFinish le datetime'2018-12-31T22:00:00' and IsMilestone eq false and IsSummary eq false and LocalCustom_x005f_Published_x005f_c9c0419bca2be911b47034f39a71aba5_x005f_000039b78bbe4ceb82c4fa8c0b40014e eq 'My Term'&$top=500&$skip=0

    I tried it and I get a 400 - bad request. However, before I added in the filter for the local custom field, this query worked just fine:

    http://server/site/_api/ProjectServer/Projects('project guid')/Tasks?$filter=BaselineFinish gt datetime'0001-01-01T00:00:00' and BaselineFinish le datetime'2018-12-31T22:00:00' and IsMilestone eq false and IsSummary eq false&$top=500&$skip=0

    So, the only difference is the addition of the filter for the local custom field. And I know that if a local custom field was never assigned a value, then it actually doesn't exist on the task object in the server.

    So considering that I know that some of the tasks might not have that field present, it makes me wonder if the absence of that field on some tasks is what is causing my query to return a bad request? Or should I be looking at something else there?



    Dylan Cristy | MCSA: Web Applications

    Thursday, February 14, 2019 5:17 PM

All replies

  • Hi Dylan,

    Local custom fields aren't available in the oData feed, so they are not something you can query on.


    Ben Howard [MVP] | web | blog | book | downloads | P2O

    Thursday, February 14, 2019 5:26 PM
    Moderator
  • I beg to differ.

    Local custom fields aren't available through the "_api/ProjectData/" endpoint, but if I do a query for tasks that does not include the local custom field name in the filter, like the one I said works in my original question, through the "_api/ProjectServer" endpoint, then definitely, if values for local custom fields have been set for those tasks, they certainly do exist on those tasks that are returned by the query:

    Dev tools screenshot

    Now, you might be saying that they aren't queryable, but I would certainly count that as being available, since I can get those tasks and find out the values of the local custom fields.


    Dylan Cristy | MCSA: Web Applications


    • Edited by Dylan Cristy Thursday, February 14, 2019 5:59 PM
    Thursday, February 14, 2019 5:59 PM
  • Okay, I don't know using that endpoint.

    Ben Howard [MVP] | web | blog | book | downloads | P2O

    Thursday, February 14, 2019 7:43 PM
    Moderator
  • Hi Dylan,

    Using Local custom Field in an OData is perfectly acceptable and for sure it will return results. I have tried the below URL and it was returning results based on the local custom field filter value.

    ttp://server/site/_api/ProjectServer/Projects(guid'<id here>')/Tasks?$filter=BaselineFinish gt datetime'0001-01-01T00:00:00' and BaselineFinish le datetime'2018-12-31T22:00:00' and IsMilestone eq false and IsSummary eq false and LocalCustom_Published_ec420bc1fe2be81180e100155d48d621_000039b78bbe4ceb82c4fa8c0b400033 eq 'Core'

    But this OData fails when you query a project where this local custom field is not exist. 

    ---------

    Rajkumar


    Friday, February 15, 2019 6:57 AM
  • But this OData fails when you query a project where this local custom field is not exist. 

    Right, but that is kind of at the crux of my question - I know that if a local custom field has not been assigned a value for a specific task, then it does not exist on that particular task.

    BUT - if it *has* been assigned a value on a different task, then it *does* exist on that task.

    See my screenshot above - you can see that there are about a dozen local custom fields there. Clearly that is not representative of the total number of local custom fields available, since there are 30 text fields alone, not counting the other types of local custom fields (flag, number, etc.).

    So in my situation, the local custom field I am trying to filter on does exist on *some* of the tasks, but not *all* of the tasks. So.... does the fact that that field does not exist on *some* of the tasks make the query fail?  Or should I expect it to ignore the tasks where that field does not exist, and filter correctly on the tasks where it *does* exist?

    ALSO: I noticed that in your query you use regular underscores in your local custom field name, instead of the encoded underscores (_x005f_) like how it appears on a task object.  I had been using the encoded version in my query because I figured the name had to match the internal name of the field on the server, but I tried switching my query to use regular underscores as you have done.  However, it still fails with a 400 bad request.


    Dylan Cristy | MCSA: Web Applications


    • Edited by Dylan Cristy Saturday, February 23, 2019 9:01 PM
    Saturday, February 23, 2019 9:00 PM