Fetch email ids based on the string field value

[SK41]

Hi,

I have a requirement where I need to fetch the email ids from the Account table based on the value user enter in the field, say TestID. The testid is stored in another custom table, say Mapping. So, I have to look for the TestID value and get the mapping value against it.. Mapping value is the reference field that refers to the Account table. So, what I need to do is first look for the testid records in Mapping table and then for all the Mapping values found, fetch  the emails stored in Accounts table for the Mapping values.

Mapping(u_mapping) table has two fields:

Test ID  (String)

Mapping Value (Reference) referring to Accounts table

Accounts table has  fields:

Mapping Value

Email ids

So, if user enters TestID, say 1234  then I have to look in the Mapping table and find all matching records. Suppose I found two records with this test id both have different Mapping values....then there will be two Mapping Values for both I need to fetch the email ids.

Test ID     Mapping Value

1234        35355

1234         33664

Accounts

Mapping Value      Email ids

35355                user1@test.com, user2@test.com

33664                user3@test.com,  user42test.com

So, I need to fetch all the four email ids here. There can be any number of records for Test ID. 

I have written the script but as of now it is just fetching the email ids for one testid record only. Please help in fixing the script

var tra = current.test_ids;

var getMne= new GlideRecord('u_mapping')
getMne.addQuery('test_id', tra);
getMne.query();
while(getMne.next())
{
var cli_mne= getMne.getDisplayValue("mappingvalue");


var getAcc= new GlideRecord('customer_account')
getAcc.addQuery('u_mapping',cli_mne);
getAcc.query();
if(getAcc.next())
{
gs.info("in next if fiss ");
var conRel = new GlideRecord('sn_customerservice_contact_relationship');
gs.info("inconrel fiss");
conRel.addQuery('company',getAcc.sys_id);
conRel.query();
var getEmails='';
while(conRel.next())
{
gs.info("in while fiss ");
getEmails= conRel.contact.email /*+ ';' + getEmails*/;


}
}


}

[Pratiksha]

https://www.youtube.com/watch?v=wYnwEfcUNnU&t=4s

https://www.servicenow.com/community/itsm-articles/how-to-read-body-text-in-inbound-email-flow/ta-p/...

Mark my answer as helpful/correct if it resolved your query

[Pratiksha]

Mark my answer as helpful/correct if it resolved your query

[Bert_c1]

Hi @SK41 ,

I suggest you add some additional debug to your script (looks like a business rule) as follows:

var tra = current.test_ids;

var getMne= new GlideRecord('u_mapping')
getMne.addQuery('test_id', tra);
getMne.query();
gs.info("myBusineesRule: Found " + getMns.getRowCount() + " records in the u_mapping table for tra = " + tra);
while(getMne.next())
	{
	var cli_mne= getMne.getDisplayValue("mappingvalue");

	var getAcc= new GlideRecord('customer_account')
	getAcc.addQuery('u_mapping',cli_mne);
	getAcc.query();
	gs.info("myBusineesRule: Found " + getAcc.getRowCount() + " records in the customer_account table for u_mapping = " + cli_mne);
	if(getAcc.next())
	{
		gs.info("in next if fiss ");
		var conRel = new GlideRecord('sn_customerservice_contact_relationship');
		gs.info("inconrel fiss");
		conRel.addQuery('company',getAcc.sys_id);
		conRel.query();
		gs.info("myBusineesRule: Found " + conRel.getRowCount() + " records in the sn_customerservice_contact_relationship table for company = " + getAcc.sys_id);
		var getEmails='';
		while(conRel.next())
		{
			gs.info("in while fiss ");
			getEmails= conRel.contact.email /*+ ';' + getEmails*/;
		}
	}
}

the you can use the 'Script Log Statements' to see what is going on, search based on 'message', 'starts with', 'myBusinessRule:'