Get values from referencing table in notification

[Mike D1]

Hi 

We have a survey record on asmt_assessment_instance table that gets triggered when an incident is resolved.

I have a new requirement that when the survey instance is completed a notification gets sent to the incident assignee with the asmt_assessment_instance_question values.

I can see asmt_assessment_instance_question is referencing asmt_assessment_instance however my issue is trying to add the variable values from the asmt_assessment_instance record. They aren't showing in the tree picker??

Is there another way to pull these values in to the notification?

Thanks

Mike

[Suhyeon Lee]

you can using lookup record and notification action in flow designer

[Mike D1]

Hi Suhyeon

I see the same fields in the FD pill picker that I do in the notification. Are you able to specify how to achieve this in FD?

Thanks

Mike

[Suhyeon Lee]

sry this case is difficult in fd

add more info jaspal singh's solutioin

(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
    /* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
    /* Optional GlideRecord */ event) {



        var getquestans=new GlideRecord('asmt_assessment_instance_question');
        //getquestans.addQuery('instance',current.sys_id);
        getquestans.addQuery('instance', current.sys_id) ;
        getquestans.query();
    
        while(getquestans.next())
        {
            var answerVal = getquestans.value ;
    
            var asmtdef = new GlideRecord('asmt_metric_definition');
            asmtdef.addQuery('metric', getquestans.metric);
            asmtdef.addQuery('value', getquestans['value']);
            asmtdef.query();
            asmtdef.next();
            
            if(asmtdef.sys_name != asmtdef['value'])answerVal =  asmtdef.sys_name;
            
            template.print('Question :'+getquestans.metric.getDisplayValue()+'<br>'+'Answer : '+ answerVal +'<br><br>');
            
        }
    

})(current, template, email, email_action, event);

[Jaspal Singh]

You need a mail script where you can GlideRecord asmt_assessment_instance_question table. You can query using instance field in asmt_assessment_instance_question which will act as primary key between asmt_assessment_instance_question & asmt_assessment_instance table.

Something as below 

1. Navigate to System Notificaiton >> Email >> Notification Email scrpt

2. Create new

Name: surveyquestionanswer

(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
          /* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
          /* Optional GlideRecord */ event) {

	var getquestans=new GlideRecord('asmt_assessment_instance_question');
	getquestans.addQuery('instance',current.sys_id);
	getquestans.query();
	while(getquestans.next())
		{
			template.print('Question :'+getquestans.metric.getDisplayValue()+'<br>'+'Answer : '+getquestans.value+'<br><br>');
		}

})(current, template, email, email_action, event);

Call the mail script in notification body in format as below

${mail_script:surveyquestionanswer}

Above will work assuming your notification is on asmt_assessment_instance table.

Sample output: