AddQuery()... Query - Serverside logic

[Andrew_TND]

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;
})();

[Ankur Bawiskar]

@Andrew_TND 

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.

[Andrew_TND]

@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.

[Andrew_TND]

@Ankur Bawiskar Ah, I think thats where the script isnt right, it should be collecting the project_status sys_id not the parent project.