How to update only an active record through the transform map/import set

[Bonnie Bennet]

Hi All,

I have created a transform map for the service_offering table. I set the Service offering name and company as the Coalesce fields. When there are multiple matches for any row on the target table I want the system to always update the Active record. Can someone please help me know is there a way to do this ? If yes how??

Thanks in Advance!!!

[SanjivMeher]

You can add an onBefore Transform script to check if target.active!='true', ignore the update. That will ignore update for that row.

Ex

if (target.active!='true')

ignore = true;

[Bonnie Bennet]

Hi Sanjiv, Thanks for looking into this!

The suggested code line just prevents the update to the target record when it is inactive. But when the target record is inactive, how to move on to next target record to search for an active record and update it ?? 
Thank you!!

[AshishKM]

Hi @Bonnie Bennet , 

The transform map will auto move on next record and again check the onBefore() logic.

As we don't know how many records in source table and target table but we assume there are many records in both the table because you have transform map, means some significant records in both the table.

answer to your query "how to move on to next target record to search for an active record and update it ?? "

--When you run the transform map, it will start from first record ( in stage table ) and keep iterating till last record ( in stage table ) and for every row it will check the onBefore() for active status.  

[Bonnie Bennet]

Hi Ashish,
Thanks much for the detailed explanation on this.. The system is automatically moving on to next record from the source. But in a scenario where there are multiple target records for a single source record, how do we check and update only the active record from the bunch of target records.

I have created an OnBefore script with the given code. I have 2 target results for the given source record - One is active and the other is inactive. But when I run the transform map, the update is just ignored by the OnBefore script saying - "More than one target records exists for target table".

Any further thoughts on this please ?!

[AshishKM]

How many individual record in target table for record in source table.

How you configure coalesce column, how the coalesce column is unique (in target table).

Workaround 

Disable that onBefore() check and write [Script] for one of coalesce mapping column, in that script, filter out the inactive record and update the active only.