What exactly does the coalesce field on a field mapping?

[Jacob64]

Hi All,
We have a table transfer map that inserts exernal master data from an interface to the core_company table.
This works fine for the whole company record but gives issues on the contact field value's and the parent field.
At first the field was populated with a new value (without email information etc) causing issues as this field is mastered by another sysyem. So now wh have plenty new users that we have to delete every day.

So we want this field to be unique.
When changing the Choice action field at the field mapping and change it to Ignore we expect it to ignore the wrong value from the interface and leave it as it is. But now the field is emptied every time.
We cannot use the reject option as that wil make the whole record to be rejected insteat of only this field.

So looking further i see the coalesce field which i can set to true.

In general it is writen that that wil make the field to nood to be exactly the same. Sounds as a possible solution but is that so?

Can someone explain what will happen if we set this field to be true?

Many thanks in advance!

Kind regards
Jacob.

[Dr Atul G- LNG]

Hi @Jacob64 

In simple words

Coalsec - If the match found in record it will update the record with else it will create the record.

https://youtu.be/uxjLkVP-S_g

[Jacob64]

Hi Atul,

Thanks for the information. I think i have a clue now of the Coalesce function.
I did some testing with it and saw good results but also something I do not want to see.
It does select available records but also inactive record which obvious i would not like to select.

What could be the best way to filter that out? 
And to be more specific, I would like to filter on active=true, email=<not empty>  and userid contains @domainname.

Any idea how to do so?

Thanks in advance.

Kind regards
Jacob.

[Dr Atul G- LNG]

Hi @Jacob64 

Thanks for your kind words! I'm happy that I’m able to share my knowledge.

Now, regarding your point, what I've learned is that adding more coalesces isn’t ideal. In your case, I can see there are 3 parameters. From what I understand, you want to update data only for records where the above conditions match. For this, you should use the transform script. In the script, we can perform dot walking, such as checking if the user ID contains certain values, which isn't possible with the usual coalesce method.

[Ankur Bawiskar]

@Jacob64 

So you want the contact field on core company to be populated only when the user in sys_user is active, email is not empty and userid contains something?

If yes then don't keep that Contact field in field mapping.

Instead use transform script and set the target contact field only when your condition satisfies for sys_user

something like this but please enhance

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	// Add your code here
	if(target.contactUserField.active.toString() == 'false' && target.contactUserField.email_address != '' && target.contactUserField.user_name.toString().indexOf('@domainname') > -1){
		// all 3 conditions matched so ignore
		target.yourField = source.yourField;
	}

})(source, map, log, target);

if my understanding is wrong, please share some screenshot on what's exact question along with some data

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