Concept of Foreign Record insert in Import sets

[maddamsetti]

Hello Everyone,

Can someone assist me with the concept Foreign Record Insert.

Please explain me with the Scenario when system will try to create the new record in foreign table if it's not available when it will skip/ignore this.

[G Ponsekar]

Hi @maddamsetti ,

A "Foreign Record Insert" in ServiceNow occurs during an import process when a new record needs to be created in a table that is referenced by the main target table. This happens when data in your import set, which is intended for the target table, contains a value for a reference field that does not yet exist in the referenced table. 

Imagine you are importing a spreadsheet of new user data into the sys_user table (your target table). One of the columns in your spreadsheet is department, which is a reference field that links to the cmn_department table. 

First Name Last Name Email Department

John	Doe	j.doe@example.com	Sales
Jane	Smith	j.smith@example.com	Marketing
Mary	Jones	m.jones@example.com	Research

During the transform process, the system performs the following steps:

1. Transforming for John Doe: The transform map processes the record for John Doe. It sees that the department value is "Sales" and looks for a matching department in the cmn_department table.
2. Referenced record found: If a "Sales" department record already exists, the transform proceeds by linking John Doe's new user record to that existing department record.
3. Foreign Record Insert: When the transform map processes the record for Mary Jones, it sees that the department value is "Research".
4. Referenced record not found: The system searches the cmn_department table and does not find a department named "Research".
5. Insert into foreign table: Instead of throwing an error, the system automatically creates a new record for "Research" in the cmn_department table (the foreign table). This is the "foreign record insert".
6. Link new records: After creating the new department record, the system proceeds to insert the new user record for Mary Jones into the sys_user table, referencing the newly created "Research" department record. 

Controlling foreign record inserts

This behavior is controlled by the Choice Action setting on the field map for the reference field. You have several options for how the system should handle new, non-existent reference values during an import: 

• create: (Default) If a value is not found in the referenced table, a new record is created in that table. This is what enables the foreign record insert behavior.
• ignore: If the value is not found, the reference field for the current record will be left blank.
• reject: If the value is not found, the entire record for the main target table will be rejected and not inserted. 

The onForeignInsert transform script

For more complex control, you can use the onForeignInsert transform script. This script runs before a foreign record is inserted and allows you to: 

• Perform custom logic, such as modifying the data for the new foreign record.
• Check for specific conditions and prevent the foreign record from being created using ignore = true.
• Prevent the main target record from being inserted by using reject = true.

If I could help you with your Query then, please hit the Thumb Icon and mark as Correct !!

Thanks, GP