Transform Script

[Priya123]

I need to create cases via import thro Excel(transform ) 

REquirement:

Need to have 3 coalesce fields..

1) Inserts as usual

2) if there is no Change to the File (basically ignored records) I need to update a field. -- any leads ??

3) And If that record is not even there on next days Excel , (so on the import set there would not be any status for that record) based on the field we updated from step 2  , need to update the status of that Record

Any leads ?

[Ehab Pilloor]

Hi @Priya123,

To achieve the specified requirements for case creation and updates via Excel imports in ServiceNow, a combination of Transform Map, Import Set, and Transform Script is necessary.

Firstly, ensure that your Transform Map is appropriately configured to map the Excel fields to ServiceNow fields. 

Inserts as Usual: The standard insert operation can be handled through the Transform Script by mapping the Excel fields to the corresponding fields in the target table. Ensure that the `coalesce` fields are properly set in the Transform Map to identify existing records.

Updating a Field for Unchanged Records: To detect if there's no change in the file, you can compare the values of relevant fields between the incoming record and the existing record. If no changes are detected, you can use a combination of `current` and `source` objects in the Transform Script to update the desired field on the existing record.

Example:

if (current.someField != source.someField) {
// Update logic for changed records
} else {
current.updatedField = 'Updated Value';
}

Updating Status for Missing Records: If a record is not present in the new Excel import, you can use the `afterScript` in the Transform Map to identify missing records and update the status based on the field value updated in step 2.

Example:

if (!source.u_field_updated) {
// Record is not in the new import, update status based on field value
current.status = 'Closed';
}

Ensure that you carefully handle edge cases and error scenarios within your Transform Script. 

If you found this reply useful, please mark it as solution/helpful.

Thanks and Regards,

Ehab Pilloor