Blog

Portfolio to Project Relationship (M:M)

In the base ServiceNow application, Portfolios to Projects is a 1:M relationship. To allow a M:M relationship (project can be in many portfolios, portfolio can be part of many projects), you can customize ServiceNow to allow this.

Add Many-to-Many Relationship (sys_m2m.list)

Wiki Article: http://wiki.servicenow.com/index.php?title=Creating_a_Many-to-Many_Relationship

From Table: Project [pm_project]
To Table: Portfolio [pm_portfolio]
Many to Many Table: u_m2m_portfolios_projects
From Field: u_project
To Field: u_portfolio

Set Related Lists

Wiki Article: http://wiki.servicenow.com/index.php?title=Personalizing_Forms#Adding_a_Related_List

  1. Project > Personalize > Related Lists
    Add Portfolio
  2. Project > Personalize > Form Layout
    Remove Portfolio Field (if existing)
  3. Portfolio > Personalize > Related Lists
    Remove Projects -> Portfolio
  4. Portfolio > Personalize > Related Lists
    Add Projects

Add Business Rules

Wiki Article: http://wiki.servicenow.com/index.php?title=Business_Rules

  1. Name: Add to Portfolio
    Table: u_m2m_portfolios_projects
    When: after
    Insert/Update: true
    Condition: current.u_project.changes() && !previous.u_project.nil()
    Script: 
    var pp = new GlideRecord("pm_portfolio_project");
    pp.pm_project = current.u_project;
    pp.pm_portfolio = current.u_portfolio;
    pp.insert();
    var ppu = new ProjectPortfolioUtils();
    ppu.refreshProject(pp);
  2. Name: Remove From Portfolio
    Table: u_m2m_portfolios_projects
    When: after
    Update/Delete: true
    Condition: (current.u_portfolio.changes() && !previous.u_portfolio.nil()) || current.operation() == "delete"
    Script:
    var pp = new GlideRecord("pm_portfolio_project");
     pp.addQuery("pm_project", current.u_project);
    if (current.operation() == "delete") {
     pp.addQuery("pm_portfolio", current.u_portfolio);
    }
    else {
     pp.addQuery("pm_portfolio", previous.u_portfolio);
    }
    pp.query();
    if (pp.next()) {
     pp.deleteRecord();
    }

List Control

Wiki Article: http://wiki.servicenow.com/index.php?title=Personalizing_Lists#List_Control

  1. u_m2m_portfolios_projects.u_portfolio, Omit New Button: true

 

2. u_m2m_portfolios_projects.u_project, Omit New Button: true

Script Includes

Adjust the TimelinePortfolioGanttPage Script Includes to allow M:M Timeline.

 

// Class Imports
var TimelineItem = Packages.com.glide.schedules.TimelineItem;
var StringUtil = Packages.com.glide.util.StringUtil;
 
var ERROR_TITLE = 'Error';
var GANTT_REQUIREMENT = 'This is required for displaying a gantt chart.';
var ERROR_NO_TASK_ID = 'No "sysparm_timeline_portfolio_id" specified in the original Url. ' + GANTT_REQUIREMENT;
var ERROR_NO_GR_TASK = 'Unable to find a matching portfolio record with the specified system Id.';
var START_LABEL = gs.getMessage('Start');
var END_LABEL = gs.getMessage('End');
var MESSAGES = [ERROR_TITLE, ERROR_NO_TASK_ID, ];
 
var TimelinePortfolioGanttPage = Class.create();
TimelinePortfolioGanttPage.prototype = Object.extendsObject(AbstractTimelineSchedulePage, {
 
 milestoneIds: {}, // milestone ids in form of
 // milestonIds[projectid] = (array of milestoneIds)
 
 // ////////////////////////////////////////////////////////////////////////////////////////////////
 // GET_ITEMS
 // ////////////////////////////////////////////////////////////////////////////////////////////////
 
 getItems: function () {
var port_id = this.getParameter("sysparm_timeline_portfolio_id");
 
if (port_id == null) return this.setStatusError(gs.getMessage(ERROR_TITLE), gs.getMessage(ERROR_NO_TASK_ID));
 
var portfolio = new GlideRecord('pm_portfolio');
if (!portfolio.get(port_id)) return this.setStatusError(gs.getMessage(ERROR_TITLE), gs.getMessage(ERROR_NO_GR_TASK) + ' ' + gs.getMessage(GANTT_REQUIREMENT));
 
// Specify the page title
this.setPageTitle(StringUtil.escapeHTML(portfolio.getDisplayValue()) + gs.getMessage(' Portfolio Gantt Chart'));
this._getProjects(portfolio);
 },
 
 _getProjects: function (portfolio) {
 
var portfolioRLGr = new GlideRecord("u_m2m_portfolios_projects");
portfolioRLGr.addQuery("u_portfolio", portfolio.getUniqueValue());
portfolioRLGr.query();
while (portfolioRLGr.next()) {
 // get project records
 var projectGr = new GlideRecord("pm_project");
 projectGr.addQuery("sys_id", portfolioRLGr.u_project.sys_id);
 projectGr.query();
 while (projectGr.next()) {
var projectId = projectGr.getUniqueValue();
// create project items
var projItem = new TimelineItem("pm_project", projectId);
projItem.setLeftLabelText(projectGr.getValue("short_description"));
// project span
var projSpan = projItem.createTimelineSpan("pm_project", projectId);
var planStart = projectGr.start_date.getGlideObject().getNumericValue();
var workStart = projectGr.work_start.getGlideObject().getNumericValue();
var planEnd = projectGr.end_date.getGlideObject().getNumericValue();
var workEnd = projectGr.work_end.getGlideObject().getNumericValue();
//use actuals if we have them
var projStart = planStart;
if (workStart > 0) var projStart = workStart;
 
var projEnd = planEnd;
if (workEnd > 0) projEnd = workEnd;
 
projSpan.setTimeSpan(projStart, projEnd);
 
// add percent complete span
var pc = parseFloat(projectGr.percent_complete, 10) / 100;
if (pc > 0) {
 projSpan.setInnerSegmentTimeSpan(projStart, projSpan.getStartTimeMs() + ((projSpan.getEndTimeMs() - projSpan.getStartTimeMs()) * pc));
 projSpan.setInnerSegmentClass('silver');
}
projSpan.setSpanColor("#333333");
 
this._getChildMilestones(projectId, projectGr);
 
if (!this.milestoneIds[projectId]) {
 this.add(projItem);
 continue;
}
 
var projectMilestoneIds = this.milestoneIds[projectId];
 
// add project milestones
var milestoneGr = new GlideRecord("planned_task");
milestoneGr.addQuery("sys_id", "IN", projectMilestoneIds);
milestoneGr.query();
var milestonesMissed = 0;
while (milestoneGr.next()) {
 var msStatus = "";
 var msItem = new TimelineItem("planned_task");
 msItem.setParent(projectGr.getUniqueValue());
 msItem.setLeftLabelText(milestoneGr.getValue("short_description"));
 
 var planEnd = milestoneGr.end_date.getGlideObject().getNumericValue();
 var workEnd = milestoneGr.work_end.getGlideObject().getNumericValue();
 var msSpan = msItem.createTimelineSpan("planned_task", milestoneGr.getUniqueValue());
 if (workEnd > 0) {
msSpan.setTimeSpan(workEnd, workEnd);
msSpan.setPointIconClass("black_circle");
msStatus = "Completed";
 } else {
msSpan.setTimeSpan(planEnd, planEnd);
if (milestoneGr.end_date.getGlideObject().compareTo(new GlideDateTime()) > 0) {
 msSpan.setPointIconClass("green_circle");
 msStatus = "Planned";
} else {
 msSpan.setPointIconClass("red_circle");
 milestonesMissed++;
 msStatus = "Missed";
}
 }
 msSpan.setTooltip(this._generateMilestoneTooltip(milestoneGr, msStatus));
 
 //if project has started set project span color based on milestone missed
 if (workStart > 0) {
if (milestonesMissed > 0) projSpan.setSpanColor("red");
 else projSpan.setSpanColor("green");
}
 
 this.add(msItem);
}
projSpan.setTooltip(this._generateProjectTooltip(projectGr, milestonesMissed));
this.add(projItem);
 }
}
 },
 
 _getChildMilestones: function (projectId, task) {
if (task.rollup != true) return;
 
// get children
var children = new GlideRecord("planned_task");
children.addQuery("parent", task.getUniqueValue());
children.query();
while (children.next()) {
 // capture milestones
 if (children.duration.getGlideObject().getNumericValue() == 0) {
if (!this.milestoneIds[projectId]) this.milestoneIds[projectId] = [];
 
this.milestoneIds[projectId].push(children.getUniqueValue());
 } else if (children.rollup == true) this._getChildMilestones(projectId, children);
}
 },
 
 _generateProjectTooltip: function (task, msMissed) {
var tt = '<div style="padding:2px;"><div style="font-size:10pt;border-bottom:1px solid #ccc;padding-bottom:5px;"><strong>' + StringUtil.escapeHTML(task.short_description) + '</strong><br />';
tt += task.number + '</div>';
tt += '<div style="padding-top:5px;">';
tt += '<strong>State: </strong>' + task.state.getDisplayValue() + '<br/>';
if (task.assigned_to != '') tt += '<strong>Assigned To: </strong>' + task.assigned_to + '<br />';
 
tt += "<strong>Time Constraint: </strong>" + (task.time_constraint == 'start_on' ? 'Specified' : 'ASAP') + '<br />';
 
if (!JSUtil.nil(task.work_start)) {
 tt += "<i>Planned Start: " + task.start_date.getDisplayValue() + '</i><br />';
 tt += "<strong>Actual End: </strong>" + task.work_start.getDisplayValue() + '<br />';
} else tt += "<strong>Planned End: </strong>" + task.start_date.getDisplayValue() + '<br />';
 
 
if (!JSUtil.nil(task.work_end)) {
 tt += "<i>Planned End: " + task.end_date.getDisplayValue() + '</i><br />';
 tt += "<strong>Actual End: </strong>" + task.work_end.getDisplayValue() + '<br />';
} else tt += "<strong>Planned End: </strong>" + task.end_date.getDisplayValue() + '<br />';
 
if (!JSUtil.nil(task.percent_complete) && parseInt(task.percent_complete, 10) > 0) tt += "<strong>% Complete: </strong>" + task.percent_complete + '%<br />';
 
if (msMissed > 0) tt += "<strong>Milestones Missed: </strong>" + msMissed + '<br />';
 
tt += '</div></div>';
return tt;
 },
 
 _generateMilestoneTooltip: function (task, status) {
var tt = '<div style="padding:2px;"><div style="font-size:10pt;border-bottom:1px solid #ccc;padding-bottom:5px;"><strong>' + StringUtil.escapeHTML(task.short_description) + '</strong><br />';
tt += task.number + '</div>';
tt += '<div style="padding-top:5px;">';
tt += '<strong>State: </strong>' + task.state.getDisplayValue() + '<br/>';
tt += "<strong>Time Constraint: </strong>" + (task.time_constraint == 'start_on' ? 'Specified' : 'ASAP') + '<br />';
 
if (!JSUtil.nil(task.work_end)) {
 tt += "<i>Planned End: " + task.end_date.getDisplayValue() + '</i><br />';
 tt += "<strong>Actual End: </strong>" + task.work_end.getDisplayValue() + '<br />';
} else tt += "<strong>Planned End: </strong>" + task.end_date.getDisplayValue() + '<br />';
 
tt += "<strong>Status: </strong>" + status + '<br />';
tt += '</div></div>';
return tt;
 },
 
 _noSpanTaskId: function () {
return this.setStatusError(gs.getMessage(ERROR_TITLE), gs.getMessage(ERROR_NO_GR_TASK));
 },
 
});