Get Project Status via APIAnswered
Where will I find the Project Status value in the API?
Currently I get to it from the database in dbo.Project.[Status].
On the Project model, there is a ProjectStatus_Id that is returned (if one is set).
Also, within Project Insight, you can see all the Project Statuses in the Administration section in order to validate the correct Project Status.
Let us know if you have any further questions!
Not quite... :-) Look at this query:
SELECT pr.[Status], ps.[Name], pr.ProjectStatusId, ps.ProjectStatusId
FROM dbo.Project pr
LEFT OUTER JOIN dbo.ProjectStatus ps ON ps.ProjectStatusId = pr.ProjectStatusId
WHERE 1 = 1
AND pr.ProjectId = '329015A7-BD0D-47FE-95D6-B003DA9FF5A0' -- Enter your own Guid here, or just run it for all...
I'd like to be able to get that pr.[Status] field for our projects. And I can't find it in the API so far.
And again I'll mention how helpful a cross-reference from db to API (and vice-versa) would be :-)
Project Status is a selection value within PI and is ProjectStatus and ProjectStatus_Id on the model. Then, there is State ,which is ProjectState and is an integer value on the Project model.
Are either of the fields you're describing?0
The Project Status data in the database is located in the dbo.ProjectStatus table. In our db, using this query:
SELECT [Name], ProjectStatusId, DisplayOrder FROM dbo.ProjectStatus WHERE 1 = 1 AND IsActive = 1 ORDER BY DisplayOrder
these are the values available for the Status ddl above:
Active, Active - Constrained, Oh Hold - Pending Resources, On Hold - Business Decision, Cancelled
In the ProjectInsight database the field dbo.Project.Status is defined as an NVarChar(max). It is a free form entry field that the users put notes in related to the value they set in the Status DDL you show above. The value in that free form field is the value I am looking for. Where is this field surfaced in the API?
Is it this statusDescription data field you're trying to get? I see it on the form of a legacy configuration, but not in the API references.0
That sounds like it. I don't know what it was before. I just walked through our current views for a known project on our system. I can't find the data on any of the Views I tried. But it is there, and the users are inputting it. And now they want to see it in their dashboard.
I'm asking the project owner for this dashboard we are creating for them where this field is in their data entry screen(s), but they have most likely left for the day.
The Status Description field is about the 5th from the bottom. It contains text that starts with "Project on hold pending..." in this example.0
Thank you for this detail, Gary. This is a "legacy" data field which explains why it is not in the API. It was replaced by a status comments log which provides more detailed history for project status changes while providing the more detail for the current status. The Status Description still exists in the database for legacy usage, so we should be able to make it available.
We should also check with your user base to see if they would prefer to update the Project Status using these comments to log the status and status changes instead. You'll need an upgrade to get any new API functionality so I'd like to ask if it makes more sense to use Comment Types in the future.0
Thank you, I will forward this to Joseph Isaac and he can handle the direction decision.
AFA making the Status Description available in the API, is that something that you can just turn on in our instance, or is it an "update" to our installation? If it is an "update" AND Joseph decides to upgrade to status comments log or Comments with a new Comment Type then we can forget adding that legacy field into the API. Personally I'd like to see them switch to one of those two list of comment type choices.0
Thank you for asking Joe about this. I'm sure he'll contact me/us if he has any questions or comments about using the newer method. We would have to get the API in a future application build and then upgrade you to that build in order to make this work.
What we could discuss with Joe would be to copy the current data from Status Description into a comment and then you could retrieve the latest project comment along with the Status you are currently able to get through the use of the API. I'm pretty sure we could accomplish that end goal without an upgrade.0
Please sign in to leave a comment.