Transform map - more than one target records exists

[Colleen]

I've created a transform map with 3 coalesce fields.  The student field on the target table is a reference field.  The source field value may not have a matching record in the student table. The field map choice action is set to reject if the source field value is invalid.  See image below.

Apparently, this warning message is being generated for each record where the source student_id is invalid.

More than one target records exists for target table <table name> with query ...

The problem is that there can be a lot of those warning messages, so I was looking for a way to prevent them being generated.

I tried setting ignore = true in an onBefore script if the source student_id is invalid.  There is a comment on the source record that the row transform was ignored by the onBefore script but the warning message is still being generated. According to this article, coalesce fields are processed before onBefore scripts are executed, so that is apparently expected behaviour.

Is there any way I can stop warning messages being generated if the coalesce field is invalid?

[Shashank_Jain]

@Colleen ,

you should always coalesce on a field that is guaranteed to be unique and populated in your source data and your target table.

If you have a source field that is a better candidate for a unique identifier, you should absolutely use it instead of the student reference field. This would be the best practice solution.

Hope it helps!