Transform map still inserting even though choice action is set to ignore?

[astanley]

Hi I need some assistance with my transform map. My goal is to have records from a staging table and update the user table with one date value. Here is my transform map:

I am trying to have the records be coalesced on the user_id as that's where matches will be found, and if a match is found, the "end date" value will update the "u_term_date" field. If a match is not found, then the record will be ignored. However I found that if a match is found, it is still being inserted into the user table and the "term date" value isn't updated. How can I make sure that the field "term date" on the user table is updated and also no records are inserted if no match it found?

[Saurav11]

Hello,

How is the data coming in is it from excel or from some other database. Did you verify that the user name matches exactly with the user name on your User table. If it then the coalesce should work.

Please mark my answer as correct based on my Impact.

[SANJEEV4]

Hi @Saurav11 

Am also facing the same issue with data SFTP source cvs file 

Created onBefore script

Insert action='insert' ignore=true

Please suggestions 

[kamlesh kjmar]

Hi @astanley ,

Unfortunately you can't make coalesce work that way. Coalasce work on simple logic, if matching field value is found it will update, else it will insert new record. If you have coalasce set on a field then you can't make it to ignore if that field value is not found. In nut shell insert/ignore/reject is not ment for coalasce filed, it is free of that choice.

Coming to why term date is not populated, I beleive that's a date or date/time field, if yes then you do not need choice action set to ignore for it. Also make sure format of the date field in your excel sheet is synchronised as per the format of your user table term date field. If not then use the Date format field option available in field mapping for this to convet accordingly

I hope this help.

Please mark this helpful if this helps and Accept the solution if this solves your issue.

Regards,

Kamlesh

[Ahmmed Ali · Accepted solution]

Hello,

No need to update choice action as ignore, as the mentioned fields are not of CHOICE OR REFERENCE type field. The choice action is application only to the above type fields.

Question on date population: make sure you have selected right date format in field mapping.

Question on ignoring if match not found: Coalesce doesn't ignore the the records if the match is not found, instead it creates new record. This is base functionality of the coalesce config. 

If you want to ignore the records for which user record did not match, you can create onbefore transform script (available in related list new to field map) with script as below:

Type: onBefore

script:

if(action == "insert"){

ignore = true;

}

Thank you,

Ali