- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-17-2025 01:17 AM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-17-2025 03:45 AM
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.
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-17-2025 01:54 AM
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).
Please mark any helpful or correct solutions as such. That helps others find their solutions.
Mark
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-17-2025 01:58 AM - edited 01-17-2025 03:36 AM
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.
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-17-2025 02:17 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
01-17-2025 02:55 AM
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.
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader