How to display variables values in Email Notification with table format

Geet Burande · ‎04-29-2024

Hi All,

Can any one help me regarding to email notification. I have created BR for display data table wise in status report form of 'Deliverables and Sprints' field. And it is showing correct but now I want to display same data in email notification. What I can do for this. I am showing data with variable display directly in notification but with this data it is showing untidy data. I have attached screenshot for this.

Thanks in advance!

Regards,

Geetanjali Burande

1__AnithaHV · ‎04-29-2024

Hi @Geet Burande ,

Could you please share the code that you are using.

Thanks & Regards,

Anitha H V

Geet Burande · ‎04-29-2024

Yes sure @1__AnithaHV

Business Rule run on 'project_status' table

when to run: Before insert and update

(function executeRule(current, previous /*null when async*/ ) 
{
  
    //current.u_activities_planned_last_period = '';
  //var style_font = "font-family: 'trebuchet ms', geneva; font-size: 10pt;";
   //gs.log("first log"+current.project);
    //Get Status Date
    var aDate = current.u_prev_status_report_date;
    //var ActualEndDAte = current.work_end; //
    var NextStatusReportDate = current.u_next_status_report_date;

    //Get Status Date
    var bDate = current.as_on;
    //Get Project sys_id
    var statusRepPrj = current.project;
    //Add time to the dates
    var aDateTime = aDate + " 00:00:00";
    var bDateTime = bDate + " 23:59:59";

    //var queryString = "start_dateBETWEEN" + aDateTime + "@" + bDateTime;
    var queryString = "start_date" + "<=" + bDateTime;
    //current.u_activities_planned_last_period = '';

    //*****************Logic for requirements*******************


    var agileArray = [];//number
    var agileSDArray = []; //Short Description Array
    var agileStartArray = []; //Start Date Array
    var agileEndArray = []; //End Date Array
    var agilePercentArray = []; //Percent Array
    var agileStateArray = []; //State Array
    //var agileAssignedto = []; //assigned to for waterfall

	var gr = new GlideRecord('pm_project_task');
    //gr.addEncodedQuery(queryString);
    gr.addQuery("top_task", statusRepPrj).addOrCondition("parent", statusRepPrj); // fetch project
    gr.addQuery('phase_type', 'agile');

    gr.orderByDesc('state');
    gr.orderByDesc('start_date');
    gr.query();
    while (gr.next()) 
	{
        agileArray.push(gr.number.toString());
		gs.log("number in while" +gr.number);
        agileSDArray.push(gr.short_description.toString());
        agileStartArray.push(gr.start_date.getValue().split(' ')[0]);
        agileEndArray.push(gr.end_date.getValue().split(' ')[0]);
        agilePercentArray.push(gr.percent_complete.getValue());
        agileStateArray.push(gr.state.getDisplayValue());
    }
// //******ND- Sprint Table Start ********
    //var SprintTableMainAgile = '';
    var sprintTable = '';
    //var sprintRow = '';
    var style_font = "font-family: 'trebuchet ms', geneva; font-size: 10pt;";

for (var i = 0; i < agileArray.length; i++) 
{
        //sprintRow = '';

        var gr1 = new GlideRecord("rm_sprint");
        gr1.addQuery("parent.number", agileArray[i]);
        gs.log("agile array:"+agileArray[i]);
        gr1.addEncodedQuery('stateIN-6,1,2'); //open agile sprints
        //gr1.orderByDesc('end_date');
        //gs.log("end date:"+gr1.end_date.getValue());
        gr1.query();

		var grC = new GlideRecord("rm_sprint");
        grC.addQuery("parent.number", agileArray[i]);
        //gs.log("agile array:" + agileArray[i]);
        grC.addQuery('state', 3);
		grC.orderByDesc('closed_at');
        grC.setLimit(3);
        
        //gs.log("end date:" + grC.end_date.getValue());
        grC.query();

        if(gr1.hasNext()||grC.hasNext())
		 {
             //gs.log("in if blog");
	
            sprintTable += '<table id="ProjectTask" style="width:100%; ' + style_font + '"> <tbody>';
            sprintTable += '<tr><th>' + agileArray[i] + '</th>';
            sprintTable += '<th>' + agileSDArray[i] + '</th>';
            sprintTable += '<th>' + agileStartArray[i] + '</th>';
            sprintTable += '<th>' + agileEndArray[i] + '</th>';
            sprintTable += '<th>' + agilePercentArray[i] + '%'+'</th>';
            sprintTable += '<th>' + agileStateArray[i] + '</th>';
            sprintTable += '<th></th></tr>';

            sprintTable += '<tr background: #B2B9C2;><td><strong>Number</strong></td>';
            sprintTable += '<td><strong>Short Description </strong></td>';
            sprintTable += '<td><strong>Start Date </strong></td>';
            sprintTable += '<td><strong>End Date </strong></td>';
            sprintTable += '<td><strong>% Complete </strong></td>';
            sprintTable += '<td><strong>State </strong></td>';
            sprintTable += '<td><strong></strong></td></tr>';
			
		}
         while (gr1.next())
		{

                sprintTable += '<tr><td>' + gr1.number + '</td>';
                sprintTable += '<td>' + gr1.short_description + '</td>';
                sprintTable += '<td>' + gr1.start_date.getValue().split(' ')[0] + '</td>';
                sprintTable += '<td>' + gr1.end_date.getValue().split(' ')[0] + '</td>';
                sprintTable += '<td>' + gr1.percent_complete + '</td>';
                sprintTable += '<td>' + gr1.state.getDisplayValue() + '</td>';
                sprintTable += '<td></td></tr>';
        }
		while(grC.next())
		{
				sprintTable += '<tr><td>' + grC.number + ' </td>';
                sprintTable += '<td>' + grC.short_description + ' </td>';
                sprintTable += '<td>' + grC.start_date.getValue().split(' ')[0] + ' </td>';
                sprintTable += '<td>' + grC.end_date.getValue().split(' ')[0] + ' </td>';
                sprintTable += '<td>' + grC.percent_complete + ' </td>';
                sprintTable += '<td>' + grC.state.getDisplayValue() + ' </td>';
                sprintTable += '<td></td></tr>';
		}
			sprintTable += '</tbody></table><br/>';
      }
	  
	    //******Agile Sprint Tables End ********

    //***********Foe Waterfall scripting *********************/


    //non active: actual end date is between the previous status report date and the next status report date.
    //previous status report date= aDate (current.u_prev_status_report_date;) 
	//next status report date = NextStatusReportDate =( current.u_next_status_report_date;	)
	//both reports dates are available on status report list table(project_status)
	//actual end date = work_end (this is available on pm_project_task)

    var arrayobj=[];
    var parnum = current.project.number; //project task
    //gs.info("parent number"+ parnum);
    var grw = new GlideRecord('pm_project_task');
	//grw.addEncodedQuery('active=true^phase_type=waterfall');  active=true^phase_type=waterfall^end_date<=javascript&colon;gs.endOfToday()
	grw.addEncodedQuery('active=true^phase_type=waterfall^end_date<='+NextStatusReportDate + '^NQactive=false^phase_type=waterfall^work_end>'+ aDate + '^work_end<='+ NextStatusReportDate);
	//grw.addQuery("parent.number", parnum);//parent.parent=7d8752a61be56d10189acbb5464bcbc8
	grw.addQuery('sub_tree_root.number', 'CONTAINS',parnum);
	//addOrCondition('parent.parent.number', parnum)
	//grw.addQuery('parent.number','CONTAINS',parnum);
    //grw.orderByDesc('state');
	
    //grw.orderByDesc('start_date');
    grw.query();
    while (grw.next()) 
	{
     var obj = {};
		gs.log("in while:" +grw.number.getDisplayValue());
		obj.number = grw.number.getDisplayValue();
		//gs.info("in while loop of waterfall:"+obj.number);
		obj.short_description = grw.short_description.toString();
		obj.percent_complete = grw.percent_complete.getValue();
		obj.start_date = grw.start_date.getValue().split(' ')[0];
		obj.end_date = grw.end_date.getValue().split(' ')[0];
		obj.assigned_to = grw.assigned_to.getDisplayValue(); //grw.assigned_to.getValue()
		obj.state = grw.state.getDisplayValue();
		arrayobj.push(obj);
        
    }
	var sprintTable1 = '';
	sprintTable1 += '<table id="ProjectTask1" style="width:100%;' + style_font + '"> <tbody>';

            //sprintTable1 +='<tr align= "center"><strong>Waterfall project task</strong></tr>';
            sprintTable1 += '<tr><td><strong>Number</strong></td>'; //background: #B2B9C2;
            sprintTable1 += '<td><strong>Short Description </strong></td>';
			sprintTable1 += '<td><strong>% Complete </strong></td>';
            sprintTable1 += '<td><strong>Approved Start Date </strong></td>';
            sprintTable1 += '<td><strong>Planned End Date </strong></td>';
            sprintTable1 += '<td><strong>Assigned to </strong></td>';
            sprintTable1 += '<td><strong>State </strong></td>';
            sprintTable1 += '<td><strong> </strong></td></tr>';
	for (var x in arrayobj) 
	{
            //gs.info("Array object"+arrayobj[x].number + arrayobj[x].short_description);
		
              
                 sprintTable1 += '<tr><td>' + arrayobj[x].number + '</td>';
                 sprintTable1 += '<td>' + arrayobj[x].short_description + '</td>';
				 sprintTable1 += '<td>' + arrayobj[x].percent_complete + '</td>';
                 sprintTable1 += '<td>' + arrayobj[x].start_date + '</td>'; //start_date.getValue().split('')[0] + 
                 sprintTable1 += '<td>' + arrayobj[x].end_date + '</td>';
                 sprintTable1 += '<td>' + arrayobj[x].assigned_to + '</td>';
                 sprintTable1 += '<td>' + arrayobj[x].state + '</td>';
                 sprintTable1 += '<td></td></tr>';
				
	}
	sprintTable1 += '</tbody></table><br/>';

    current.u_activities_planned_last_period =   sprintTable + sprintTable1;
		//current.update();

})(current, previous);

1__AnithaHV · ‎04-29-2024

Please add the first line of the code and check if fixes your issue.

         sprintTable += '<style>table, th, td {border:1px solid black;}</style>';
          sprintTable += '<table id="ProjectTask" style="width:100%; ' + style_font + '"> <tbody>';
          sprintTable += '<tr><th>' + agileArray[i] + '</th>';
            sprintTable += '<th>' + agileSDArray[i] + '</th>';
            sprintTable += '<th>' + agileStartArray[i] + '</th>';
            sprintTable += '<th>' + agileEndArray[i] + '</th>';
            sprintTable += '<th>' + agilePercentArray[i] + '%'+'</th>';
            sprintTable += '<th>' + agileStateArray[i] + '</th>';
            sprintTable += '<th></th></tr>';

Geet Burande · ‎05-13-2024

Thank you!