COALESCE fields in transform maps

[indrajeetma]

So I have a requirement where in the sheet there multiple columns I am taking 3 columns here for that I need Help
1. name

2. site ID

3. company
In transform map I have selected COALESCE true for site ID and company field this is check at the start if both are matched for other record exisiting it would update it

But other than that after if site ID and company field is not matched we want to check name and company field if both are matched then also the exisiting record should be updated? how can I add this logic

[Ankur Bawiskar]

@indrajeetma 

sorry your requirement is not clear.

share some screenshots, transform map is on what table?

Also if you require multiple combinations for Coalesce then better use onBefore transform script and don't have any field maps.

This gives you flexibility since you can write the script as per your requirement.

if you use field map then it's OOTB process

a) an exact match -> update

b) no match -> insert

If my response helped please mark it correct and close the thread so that it benefits future readers.

[indrajeetma]

if exact match is found then how to just update the matched record with source record? can you give me script

[Dr Atul G- LNG]

Hi @indrajeetma 

---

"This is out-of-the-box (OOTB) behavior. If a match is found, the system updates the record; if no match is found, it creates a new one. No script is needed for this.

[Ankur Bawiskar]

@indrajeetma 

the onBefore transform script is only required if you require complex logic for Coalesce and not required when straight forward mapping is there.

please share target table for transform map etc and what you tried so far and what didn't work?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Fabio Fernandes]

This is the script but bear in mind that you need to adapt it to your needs. also if the record is found, the record will be updated based on the other mappings.

answer = (function transformEntry(source) {

	var grSite = new GlideRecord("incident");
	grSite.addQuery("name", "value");
	grSite.addQuery("site_id", "value");
	grSite.addQuery("company", "value");
	grSite.setLimit(1);
	grSite.query();
	if (grSite.next()) {
		return grSite.getUniqueValue();
	} else {
		var grCompany = new GlideRecord("incident");
		grCompany.addQuery("name", "value");
		grCompany.addQuery("company", "value");
		grCompany.setLimit(1);
		grCompany.query();
		if (grCompany.next()) {
			return grCompany.getUniqueValue();
		}
	}
	return ''; // return the value to be put into the target field
})(source);