Hello,
To give you some background we decided to create our own status report on the service portal with a drop down where the driver is the parent project and you can select the date of the report which will display the data.
On the status report table I've created a custom table called u_status_snap_shots which creates a copy of the current open risks, issues and project tasks - These are defined in the u_task_type on said table (Risk, Issue, Project Task)
For the most part its working fine however I cant get a list of of the records which relate to the status report to populated in the columns I.e. Risk and issues: u_task_type: Risk or Issue/Milestones u_task_type: Project Task
Please note I did add some validation into the script to see if it was resolving the records which is why there is a wild JSON Script here and there.
Thanks in advance!!!
Custom table fields - u_status_snap_shots
Server side
(function() {
var gr = new GlideRecord('project_status');
gr.orderBy('as_on');
gr.query();
var groupedData = {};
var projects = [];
while (gr.next()) {
var parent = gr.getDisplayValue('project') || 'No Project';
if (!groupedData[parent]) {
groupedData[parent] = [];
projects.push(parent);
}
var statusSysId = gr.getUniqueValue().toString(); // ensure string
groupedData[parent].push({
sys_id: statusSysId,
exec: gr.getDisplayValue('executive_summary'),
as_on: gr.getValue('as_on'),
updated_by: gr.getDisplayValue('sys_updated_by'),
comments: gr.getDisplayValue("comments"),
achievements: gr.getDisplayValue("achievements_last_week"),
keyachievements: gr.getDisplayValue("key_activities_next_week"),
sched_comms: gr.getDisplayValue("schedule_comments"),
cost_comms: gr.getDisplayValue("cost_comments"),
resource_comms: gr.getDisplayValue("resource_comments"),
scope_comms: gr.getDisplayValue("scope_comments"),
project_manager: gr.project.project_manager.getDisplayValue(),
project_sponsor: gr.project.primary_program.program_manager.getDisplayValue(),
phase: gr.project.state.getDisplayValue(),
progress: gr.getDisplayValue('comments'),
planned: gr.getDisplayValue('key_activities_next_week'),
overall_health: gr.getDisplayValue("overall_health"),
schedule: gr.getDisplayValue("schedule"),
cost: gr.getDisplayValue("cost"),
resource: gr.getDisplayValue("resources"),
scope: gr.getDisplayValue("scope"),
future: gr.getDisplayValue('u_future_outlook'),
previous: gr.getDisplayValue('u_previous_status')
});
}
var snapData = {};
var snapgr = new GlideRecord('u_status_snap_shots');
snapgr.query();
while (snapgr.next()) {
var reportRef = snapgr.u_project_report;
var reportId = reportRef.sys_id.toString(); // project_status sys_id
if (!snapData[reportId]) {
snapData[reportId] = {
risksAndIssues: [],
milestones: []
};
}
var item = {
title: snapgr.getDisplayValue('u_id'),
description: snapgr.getDisplayValue('u_commentary'),
date: snapgr.getDisplayValue('u_due_date'),
rag: snapgr.getDisplayValue('u_rag'),
task_type: snapgr.getDisplayValue('u_task_type'),
sys_id: snapgr.getUniqueValue()
};
if (item.task_type === 'Risk' || item.task_type === 'Issue') {
snapData[reportId].risksAndIssues.push(item);
} else {
snapData[reportId].milestones.push(item);
}
}
data.groupedData = groupedData;
data.projects = projects.sort();
data.snapshots = snapData;
})();
HTML Template
<div class="project-status-widget">
<div ng-if="c.filteredData && c.selectedProject && c.selectedDate">
<button ng-click="c.exportToPDF()">Export to PDF</button>
</div>
<label for="projectFilter">Select Project:</label>
<select id="projectFilter"
ng-model="c.selectedProject"
ng-options="project for project in ::data.projects"
ng-change="c.onProjectChange()">
<option value="">-- Select a Project --</option>
</select>
<div ng-if="c.dateOptions.length > 0">
<label for="dateFilter">Select Date:</label>
<select id="dateFilter"
ng-model="c.selectedDate"
ng-options="date as (date | date:'dd/MM/yyyy') for date in ::c.dateOptions"
ng-change="c.filterByProjectAndDate()">
<option value="">-- Select a Date --</option>
</select>
</div>
<div ng-if="c.filteredData && c.selectedProject && c.selectedDate">
<div ng-repeat="(parentName, records) in c.filteredData">
<h3>{{ parentName }}</h3>
<div class="record-row" ng-repeat="rec in records">
<div class="main-table">
<table class="status-table">
<thead>
<tr>
<th>Status Date</th>
<th>Project Manager</th>
<th>Exec Sponsor</th>
<th>Current Phase</th>
<th>Financial</th>
<th>Schedule</th>
<th>Resources</th>
<th>Scope</th>
</tr>
</thead>
<tbody>
<tr>
<td>{{ rec.as_on | date:'dd-MM-yyyy' }}</td>
<td>{{ rec.project_manager }}</td>
<td>{{ rec.project_sponsor }}</td>
<td>{{ rec.phase }}</td>
<td ng-class="c.getRAGClass(rec.cost)">{{ rec.cost }}</td>
<td ng-class="c.getRAGClass(rec.schedule)">{{ rec.schedule }}</td>
<td ng-class="c.getRAGClass(rec.resource)">{{ rec.resource }}</td>
<td ng-class="c.getRAGClass(rec.scope)">{{ rec.scope }}</td>
</tr>
</tbody>
</table>
</div>
<div class="status-box">
<div class="status-row">
<div class="label"><b>Previous Status</b></div>
<div class="value" ng-class="c.getRAGClass(rec.previous)">{{ rec.previous }}</div>
</div>
<div class="status-row">
<div class="label"><b>Current Status</b></div>
<div class="value" ng-class="c.getRAGClass(rec.overall_health)">{{ rec.overall_health }}</div>
</div>
<div class="status-row">
<div class="label"><b>Future Outlook</b></div>
<div class="value" ng-class="c.getRAGClass(rec.future)">
{{ rec.future }}<span ng-if="rec.future == 'A' || rec.future == 'R' || rec.future == 'G'" class="trend-arrow">➤</span>
</div>
</div>
</div>
</div>
<div class="executive-summary">
<div class="exec-title">Executive Summary</div>
<div class="exec-content">{{ records[0].exec }}</div>
</div>
<div class="progress-outlook">
<div class="column">
<div class="column-title">Progress</div>
<div class="column-content">{{ records[0].progress }}</div>
</div>
<div class="column">
<div class="column-title">Outlook Next Period</div>
<div class="column-content">{{ records[0].planned }}</div>
</div>
</div>
<div class="progress-outlook">
<div class="column">
<div class="column-title">Risks and Issues</div>
<div class="column-content">
<table class="status-table" ng-if="(data.snapshots[rec.sys_id]?.risksAndIssues || []).length">
<thead>
<tr>
<th>Number</th>
<th>Due Date</th>
<th>Commentary</th>
<th>RAG Status</th>
<th>Type</th>
</tr>
</thead>
<div>
<strong>rec.sys_id:</strong> {{ rec.sys_id }}<br>
<strong>Snapshot exists:</strong> {{ data.snapshots[rec.sys_id] ? 'YES' : 'NO' }}<br>
<strong>All snapshot keys:</strong>
<ul>
<li ng-repeat="(key, val) in data.snapshots">
{{ key }}
</li>
</ul>
</div>
<tbody>
<tr ng-repeat="item in data.snapshots[rec.sys_id].risksAndIssues">
<td>{{ item.title }}</td>
<td>{{ item.date }}</td>
<td>{{ item.description }}</td>
<td ng-class="c.getRAGClass(item.rag)">{{ item.rag }}</td>
<td>{{ item.task_type }}</td>
</tr>
</tbody>
</table>
<div ng-if="!(data.snapshots[rec.sys_id]?.risksAndIssues || []).length">
No risks or issues reported.
</div>
</div>
</div>
<div class="column">
<div class="column-title">Milestones</div>
<div class="column-content">
<ul>
<li ng-repeat="milestone in (data.snapshots[rec.sys_id].milestones || [])">
<b>{{ milestone.title }}</b><br>
<small>{{ milestone.date }}</small><br>
{{ milestone.description }}
</li>
<li ng-if="!(data.snapshots[rec.sys_id].milestones || []).length">
No milestones available.
</li>
</ul>
<pre>{{ data.snapshots | json }}</pre>
</div>
</div>
</div>
</div>
</div>
</div>
Client Script
function($scope) {
$scope.c = this;
$scope.c.selectedProject = '';
$scope.c.selectedDate = '';
$scope.c.filteredData = null;
$scope.c.dateOptions = [];
function stripHTML(input) {
return input ? input.replace(/<[^>]+>/g, '') : '';
}
$scope.c.getRAGClass = function(value) {
if (!value) return '';
var val = value.toUpperCase().charAt(0);
if (val === 'R') return 'rag-R';
if (val === 'A') return 'rag-A';
if (val === 'G') return 'rag-G';
return '';
};
this.onProjectChange = function() {
$scope.c.selectedDate = '';
$scope.c.filteredData = null;
$scope.c.dateOptions = [];
if (!$scope.c.selectedProject) return;
var records = $scope.data.groupedData[$scope.c.selectedProject];
if (records && records.length) {
var dates = [...new Set(records.map(r => r.as_on.split(' ')[0]))];
$scope.c.dateOptions = dates.sort().reverse();
}
};
this.filterByProjectAndDate = function() {
var selectedProject = $scope.c.selectedProject;
var selectedDate = $scope.c.selectedDate;
if (!selectedProject || !selectedDate) {
$scope.c.filteredData = null;
return;
}
var records = $scope.data.groupedData[selectedProject] || [];
var filtered = records.filter(r => r.as_on.startsWith(selectedDate));
filtered.forEach(r => {
r.exec = stripHTML(r.exec);
r.progress = stripHTML(r.progress);
r.planned = stripHTML(r.planned);
});
if (filtered.length) {
$scope.c.filteredData = {};
$scope.c.filteredData[selectedProject] = filtered;
} else {
$scope.c.filteredData = null;
}
};
this.exportToPDF = function() {
const { jsPDF } = window.jspdf;
const element = document.getElementById('pdfContent');
html2canvas(element).then(canvas => {
const imgData = canvas.toDataURL('image/png');
const pdf = new jsPDF('p', 'pt', 'a4');
const pageWidth = pdf.internal.pageSize.getWidth();
const ratio = canvas.width / canvas.height;
const imgWidth = pageWidth;
const imgHeight = pageWidth / ratio;
pdf.addImage(imgData, 'PNG', 0, 20, imgWidth, imgHeight);
pdf.save(`ProjectStatus_${$scope.c.selectedProject}_${$scope.c.selectedDate}.pdf`);
});
};
}
1 ACCEPTED SOLUTION
Hey @Ankur Bawiskar when it didnt work I resorted that, however it just made the whole thing fall over.
If it helps I pasted the custom tables field data (with references) in the post.
