Transform map - Creating and updating records with or without Coalesce field

[matthew_hughes]

On my transform map, I've got the following field map setup:

Each record in the target table Security Standard Non-Compliance table contains an SNC number, which is unique. However, the requirements that I'm trying to meet are:

• If a row has a value in the SNC number column, and there is a matching record in the u_security_non_compliances table with an SNC with that number, then update any fields where their column is populated with a valid value.
• If a row has a value in the SNC number column,
• and there is no match, then if possible skip that row.
• If a row has no value in the SNC number column, then create a new record.

I was just wondering if anybody has come across this scenario before where I can update records with an existing SNC number, ignore any where the SNC number doesn't exist, and create new records if the spreadsheet doesn't contain a SNC number

[Mark Manders]

If you coalesce on the SNC number, you're getting what you need: updating records that have the same number, creating records that don't yet exist and records with a number that is not on the import will remain the same. A transform map will only update/create records that are in the source table. It won't delete others (if that does happen, you have other logic that is doing that).

[Ankur Bawiskar]

@matthew_hughes 

responses inline

• If a row has a value in the SNC number column, and there is a matching record in the u_security_non_compliances table with an SNC with that number, then update any fields where their column is populated with a valid value. - happens OOB with coalesce
• If a row has a value in the SNC number column and there is no match, then if possible skip that row. - use onBefore transform script shared below
• If a row has no value in the SNC number column, then create a new record. - Set this checkbox on transform map

you can check in onBefore transform script and see if it's an insert then ignore that row

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

    // Add your code here
    if (action == 'insert' && source.u_snc_number != '')
        ignore = true;

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

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

[matthew_hughes]

Hi @Ankur Bawiskar  I've done the following:

My spreadsheet looks like the following:

However, when I run my transform map, I get the following results:

I don't understand why it won't create the new entry with the empty Coalesce.

[Ankur Bawiskar]

@matthew_hughes 

2 got ignored

I don't think your 3rd use case is a valid business requirement

If there is nothing in incoming value for a target coalesce field then if you create a new record what value will you set there?

It's like you are creating a user in sys_user without any user_name which is Unique on user table

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