AddQuery()... Query - Serverside logic
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ā06-16-2025 02:41 AM
Hello,
I've created a serverside script for a portal widget which work for the most part, however I cant seem to get the data to populate if the table u_status_snapshots.u_project_report is the same as the parent.
Thanks in advance!
(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 Projects';
if (!groupedData[parent]) {
groupedData[parent] = [];
projects.push(parent);
}
var statusSysId = gr.getUniqueValue().toString();
groupedData[parent].push({ //Variables
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'),
benefit: gr.getDisplayValue('u_benefits_rag'),
previous: gr.getDisplayValue('u_previous_status')
});
}
// -- THIS PART -- //
var snapData = {};
var snapgr = new GlideRecord('u_status_snap_shots');
var prID = snapgr.getDisplayValue("u_project_report");
snapgr.addQuery(prID, gr.getDisplayValue("number"));
snapgr.query();
// ----------------- //
while (snapgr.next()) {
var reportRef = snapgr.getDisplayValue('u_project_report');
var reportId = reportRef.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'),
short: snapgr.getDisplayValue('u_title'),
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;
})();
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ā06-16-2025 02:54 AM
try this, changes below
1) when looping collect the sysId for each project
2) use addQuery('u_project_report', project_sys_id) to fetch the related snapshots
3) avoided calling getDisplayValue() on a field before record is selected
(function() {
var gr = new GlideRecord('project_status');
gr.orderBy('as_on');
gr.query();
var groupedData = {};
var projects = [];
var projectSysIds = [];
while (gr.next()) {
var parent = gr.getDisplayValue('project') || 'No Projects';
var projectSysId = gr.project.sys_id.toString();
if (!groupedData[parent]) {
groupedData[parent] = [];
projects.push(parent);
projectSysIds.push(projectSysId);
}
// ... (populate groupedData as before)
}
// Query all snapshots where u_project_report matches any project sys_id
var snapData = {};
var snapgr = new GlideRecord('u_status_snap_shots');
snapgr.addQuery('u_project_report', 'IN', projectSysIds.join(','));
snapgr.query();
while (snapgr.next()) {
var reportSysId = snapgr.u_project_report.sys_id.toString();
if (!snapData[reportSysId]) {
snapData[reportSysId] = {
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'),
short: snapgr.getDisplayValue('u_title'),
sys_id: snapgr.getUniqueValue()
};
if (item.task_type === 'Risk' || item.task_type === 'Issue') {
snapData[reportSysId].risksAndIssues.push(item);
} else {
snapData[reportSysId].milestones.push(item);
}
}
data.groupedData = groupedData;
data.projects = projects.sort();
data.snapshots = snapData;
})();
If my response helped please mark it correct and close the thread so that it benefits future readers.
Ankur
⨠Certified Technical Architect || ⨠9x ServiceNow MVP || ⨠ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ā06-16-2025 03:21 AM
@Ankur Bawiskar!
Thanks for coming back to me, I used addDisplayValue() more as a last resort as I can get all the data to display but I cant seem to filter it against the parent.
I tried the above but unfortunately its not working, it falls over and doesnt display anything.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ā06-16-2025 03:30 AM
@Ankur Bawiskar Ah, I think thats where the script isnt right, it should be collecting the project_status sys_id not the parent project.