Create custom HTML field and populate with data from another table

[BiancaK]

Good day SN Community

Wondering if anyone could assist in achieving the below please?

I have created a custom HTML field on the project form (SPM) 

I would like to populate this HTML field with a table and rows, reflecting the resource data from the demand record

Thanks

Bianca

#spm

#strategic portfolio management

[Prasun Sarkar7]

Hi this article helped please refer this link:

https://www.servicenow.com/community/developer-forum/how-to-set-the-format-on-html-field/m-p/1992713

[BiancaK]

Hi thanks but this is not what I am looking for. 

Thus far I managed to create the HTML table with its headers. 

I have created a client script as well as a script include. However, I think there is still an issue with my script include as it is not pushing the data from the demand resource plans to the project table (HTML field)

[BiancaK]

Currently displaying all resource assignments instead of only the resource assignments associated to the demand/project

Any idea on the query that needs to applied?

[ChrisBurks]

Hi @BiancaK ,

For me it's a little confusing of what you're asking for however, I think I understand.

I think you are trying to get all the resources for the specific Demand record. If that is the case, the correction needed for the query is on line 26 of the screenshot you posted. (gr.getValue('sys_id');

That piece of code is asking to get the value of the sys_id field and would be used after the query is run. In order to match the sys_id, the syntax would be the same as line 24 (gr.addQuery('active', true)) except with the details for the demand. See below:

gr.addQuery('sys_id', <supply desired sys_id here>);

I don't see in the script include where you are passing the sys_id of the demand so you will have to modify your script include to have a parameter where the demand sys_id can be passed in.

It might look something like this:

var getResourceData = Class.create();
getResourceData.prototype = Object.extendsObject(AbstractAjaxProcessor, {
	getResourceDetails: function(){
		var demandSysId = this.getParameter('sysparm_demand_sysid');

		var rd_tb = '<table style="width: 100%" border="1px" cellspacing="1"><tr><th style="width: 33%">Resource name</th><th style="width:33%">Start date</th><th> style="width:34%">End date</th></tr>';

		var gr = new GlideRecord('sn_plng_att_core_resource_assignment');
		gr.addQuery('active', true);
		gr.orderByDesc('sys_created_on');
		gr.addQuery('sys_id', demandSysId);
		gr.query();
		while(gr.next()){
			rd_tb += '<tr><td>' + gr.user_resource.name + '</td>';
			rd_tb += '<td>' + gr.start_date + '</td>>';
			rd_tb += '<td>' + gr.end_date + '</td></tr>';
		}

		rd_tb += '</table>';
		return rd_tb;

	},
    type: 'getResourceData'
});

And of course you'll need to adjust the GlideAjax call to pass the demand sys_id.

Hopefully this helps.