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.

[Jacob64]

Hi Ankur,

I believe your explanation brings me again a step further but I also have some thoughts about it.

Let me make it a bit more specific.
We have a webservice connection that puts data in the "u_core_company" source table using a TransformMap called "LoadCoreCompany" the transformmap knows up to 53 Field Maps.
The most important for this question is the field map u_contact that we need to match the Contact field of the "core_company" table.
But when i know how i can add rules (filters, scripts, etc)  to a Field map, i can increase the quality of everything we update via these field maps. 

All field maps have their own value and it is not wanted to skip all field maps if 1 of them does not meet the requirement I mentioned (Active=true, Email=<not empty> and user_name  contains @domainname)  
So if all field mappings have their own update rules and it is not wanted that if one of then does not match the specific field mapping rule nothing will be updated.

So for example if only the contact name does not match but other field maps do then only contact should not change (and also not be cleared) at all.
Meaning every field map shoul have it's own mapping rules or scripts.

Does this help?

Kind Regards
Jacob.

[Ankur Bawiskar]

@Jacob64 

is the target field a reference one? u_contact from source is mapped against which field of core_company table?

You can remove field map for that and handle it via transform script

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

[Ankur Bawiskar]

@Jacob64 

good explanation here

What is choice action field in transform maps used for? 

As per docs, so if you want it to be handled based on your conditions, then you need to remove it from field map and handle it via transform script

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

[Jacob64]

HI Ankur,

Thanks so far. i really appreciatie you helping me here.

I know the atricle and It was the first i found and implemented.

The ignore value should help me here but instead of just leave it as it is it clears the field instead of letterly ignoring it. So when not found it should do nothing but instead the field is emptied/ cleared which is unwanted behavior.
The other two do exactly what it says and the most irritating is create which creates a new contact. And as the contact in the core_company table is infact the username in the sys.user table it creates a new user in the sys_user table whitout any further fields populated. Therfore very unwanted.
The reject value is a whole different story. When the webservice interface brings only the contact name wrong all other fieldmaps with data that should change is ignored completely, which of course also is not wanted.

The whole issue would not be an issue if that field was also populated from masterdata but it is always manual populated so very often not matching with masterdata. Something to work at but not my system so I have to deal with this. 😞

I could remove the u_contact to contact field mapping but as there are more field mappings that need additonal rules the transform script might become to large.
With that in mind i prefer to create scripts in the field mapping. But i'm not sure if that is possible.

Many thanks again.

Kind regards
Jacob.

[Shivalika]

Hello @Jacob64 

You can add a ON BEFORE Transform script where you check these fields values as per your requirement like. 

If(!(target.active==true && target.email!='')) // you can add whatever conditions 

Ignore=true 

Such that if any of the conditions fail you will ignore whole record and not go into transformation analysis process. 

Kindly mark my answer as helpful and accept solution if it helped you in anyway,

Regards,

Shivalika 

My LinkedIn - https://www.linkedin.com/in/shivalika-gupta-540346194

My youtube - https://youtube.com/playlist?list=PLsHuNzTdkE5Cn4PyS7HdV0Vg8JsfdgQlA&si=0WynLcOwNeEISQCY