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

[matthew_hughes]

@Ankur Bawiskar 

Could I still get the 3rd use case implemented?

[Ankur Bawiskar]

@matthew_hughes 

yes, I was able to test that out in my instance.

All 3 cases can be covered but if the SNC number in target is number field i.e. auto numbering is present then you should be good because an empty value in it will be data issue

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

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

[Ankur Bawiskar · Accepted solution]

@matthew_hughes 

I updated my onBefore transform script with some changes, please use that

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

[matthew_hughes]

Thanks @Ankur Bawiskar  That worked

[Ankur Bawiskar]

@matthew_hughes 

Glad to help.

Request you to close your earlier questions as answered if that worked.

Members have taken time and efforts in helping.

Marking question as answered will also help future community members looking for similar solution