Scripting on multiple coalesce fields in transform map

[ruchi_jha]

Hello, 
I have  a transform map where thereare 4 fields as coalesce. When i import any file, records get inserted/updated in my target table based on coalesce field mapping functionality of transform map. I do not want the records to be directly updated when coalesce matches. Rather i need to update one field 'cost' and then insert/update in target table.

For example, my file has following sample records which will be inserted/updated in target table.

Model No (coalesce)	Brand (coalesce)	Year(coalesce)	Cost
100	Mercedes	2013	2Cr
100	Mercedes	2013	2.5Cr
100	Mercedes	2015	1.5 Cr
200	Audi	2015	3.0 Cr
200	Audi	2015	2.0 Cr
200	Audi	2016	1.0Cr

In ideal case scenario, when i upload above file, there are 4 records total inserted as below:

Model No (coalesce)	Brand (coalesce)	Year(coalesce)	Cost
100	Mercedes	2013	2.5Cr
100	Mercedes	2015	1.5 Cr
200	Audi	2015	2.0Cr
200	Audi	2016	1.0Cr

But I want the cost field to be added and updated when coalesce field is matched. It should like as below :

Model No (coalesce)	Brand (coalesce)	Year(coalesce)	Cost
100	Mercedes	2013	4.5 Cr
100	Mercedes	2015	1.5 Cr
200	Audi	2015	5.0 Cr
200	Audi	2016	1.0 Cr

I am trying an on before script but that's not working. Records are getting inserted as ideal case scenario.
Any leads on how to achieve this ?I am trying below onbefore script:

if(action == "update"){
		var cost = +target.u_total_cost+ +source.u_total_cost;
	}

target.u_total_cost=cost;

[Tai Vu]

Hi @ruchi_jha 

"When coalescing on multiple fields, all coalesce fields must be a match for there to be a collision"

So, we can configure and enable coalesce for multiple fields to handle collisions. Have you tried enabling coalesce instead of relying on scripting?

In the other hand, you can define a field mapping for the Sys Id field, then do a script to query to the target table with your criteria/condition and return the record's Sys Id.

Could you also share your script here? Fellow community members will be happy to support and review it.

Cheers,

Tai Vu

[ruchi_jha]

Hello Tai Vu,
I am trying something like this in my onbefore script:

if(action == "update"){
var cost = +target.u_total_cost+ +source.u_total_cost; //Since my cost field is of type currency
}
target.u_total_cost=cost;

[Tai Vu]

Hi @ruchi_jha 

Yes in regards to Coalesce concern. First of all, we need to enable coalesce for these fields "Model No, Brand, Year" to be able to identify which record should be updated.

Sample below.

Load Result

Cheers,

Tai Vu

[Tai Vu]

Hi @ruchi_jha 

Regarding to the currency field type "Cost [u_cost]". Let's try to define a field mapping script for this field.

Since this is currency field type, the format should be "Currency Code + ';' + Cost Amount".

Sample below.

answer = (function transformEntry(source) {

	//Use this line with cost format "CR;1.0" in your excel file.
	var sourceCost = parseFloat(source.getValue('u_cost').split(';')[1]);

	//Use this line with cost only with amount "1.0" in your excel file.
	//var cost = parseFloat(source.u_cost);
	var targetCost = parseFloat(target.u_cost);
	var newCost = sourceCost + targetCost;
	var currencyCode = target.u_cost.getCurrencyCode();
	return currencyCode + ';' + newCost;

})(source);

Enjoy

Let me know if it works for you

Cheers,

Tai Vu