Informations about the "pm_portfolio_project" table

[Giacomo Polett2]

Hello everyone,

We would like to know more about the "pm_portfolio_project" table.
I know it can be used to link multiple portfolios to a project. (for reporting purposes?)
I can't understand why it is possible to create more "pm_portfolio_project" records on the same project.
Also the fields on the table and the dashboard update.

Thanks in advance to everyone.

Giacomo P.

[Earl L]

Giacomo,

This had me wrapped around the axle for a while when I started working with the PPM application. From my experience, you don't really need to interact with this table or it's data directly. It appears that ServiceNow is taking care of business behind the scenes. In my production instance, if I navigate to pm_portfolio_project.list and pm_project.list I have the same number or records in both tables. And I don't need to do anything to make that happen.

Now, you might be able to add records to the to one or the other table directly, but without understanding the embedded business rules I wouldn't advise that. Those are easy enough to investigate if you're a system administrator. You can also look at the schema map for both tables to see how they relate to each other.

As it turns out, pm_portfolio_project has a reference column to the pm_project table. And most of the nitty gritty details of projects are keyed off the pm_project table, not pm_portfolio_project. Some high level portfolio metrics are linked to the pm_portfolio_project table, but as I said, I'm not sure you need to worry too much about the underlying details unless you're trying to accomplish something very specific that's not being taken care of for you with the existing table design and business rules.

If you want to see how the sausage is made, get yourself a developer.servicenow.com account and fire up a clean instance and you can poke around to your hearts content, without worry of breaking anything on your own instance.

Earl

[Earl L]

And here's one business rule that you can take a look at to see some of the sausage grinding...

(function executeRule(current, previous /*null when async*/) {
	var portfolioProject = new GlideRecord('pm_portfolio_project');
	portfolioProject.addQuery('pm_project', current.sys_id);
	portfolioProject.query();
	portfolioProject.next();
	var portfolioProjectRelation;
	if(!current.primary_portfolio.nil()){
		portfolioProjectRelation = new GlideRecord('pm_m2m_portfolio_project');
		portfolioProjectRelation.addQuery("project", portfolioProject.sys_id);
		portfolioProjectRelation.addQuery("portfolio", current.primary_portfolio);
		portfolioProjectRelation.query();
		if(!portfolioProjectRelation.next()){
			portfolioProjectRelation.initialize();
			portfolioProjectRelation.setValue("project", portfolioProject.getValue('sys_id'));
			portfolioProjectRelation.setValue("portfolio", current.getValue('primary_portfolio'));
			portfolioProjectRelation.insert();
		}
	}
	if (!previous.primary_portfolio.nil()) {
		portfolioProjectRelation = new GlideRecord('pm_m2m_portfolio_project');
		portfolioProjectRelation.addQuery("project", portfolioProject.getValue('sys_id'));
		portfolioProjectRelation.addQuery("portfolio", previous.getValue('primary_portfolio'));
		portfolioProjectRelation.query();
		if (portfolioProjectRelation.next()) {
			portfolioProjectRelation.setWorkflow(false);
			portfolioProjectRelation.deleteRecord();
		}
	}
})(current, previous);

[Giacomo Polett2]

Hi Earl,

Thank you for your answer.

Yes, I see that when you create a pm_project a business rules create a pm_portfolio_project and a pm_m2m_portfolio_project record.

I would like to understand the need for servicenow to "copy" the project to the pm_portfolio_project table. And to understand why the portfolios are connected on the pm_portfolio_project table and not to the project.

Regards,

Giacomo