When coalescing on the number field, how can I be provided a default number if I POST a new record for insertion?

[Zach Hart]

Hi,

I've been running into a bit of a sticky issue recently involving Coalesced fields in the transform map of an import set as part of a scoped application.

The import set maps to the default incident table in ServiceNow, and needs to behave like the following:

If a POST request is sent to the import set API, with a number that matches the number of an existing record, the existing record should be updated rather than a new record inserted (this is achieved by including the number field in the transform map, and checking the Coalesce option). If a request is sent without a number (or with an empty number, either would be acceptable), then a new record should be inserted into the table with a number assigned by ServiceNow, as would happen were an incident created in the UI.

However, if the number field is included in my transform map and set to coalesce, POSTing to the import set API without the number field from the REST explorer returns me a 201 status code, but with the following body:

{
  "import_set": "ISET0010003",
  "staging_table": "<staging table name>",
  "result": [
    {
      "transform_map": "Testing Map",
      "status": "error",
      "error_message": "Unable to resolve target record, coalesce values not present: number; Invalid table ",
      "status_message": "Unable to resolve target record, coalesce values not present: number"
    }
  ]
}

If I post with an empty (or even nonsensical number), the item is inserted into the table, but with that same empty or nonsensical number (I do not have the transform set to coalesce on empty fields).

My question is: Without create a second import set/transform map that does not map the number field, and using that set for insertion only, is there a way via scripting or a configuration I've not been able to find to assign a default number (in the normal form of INCXXXXXXX) to an incident when it is inserted if the map is set to coalesce on the number field?

[Pradeep Sharma]

Hi,

Open the number field map and set the checkbox "Coalesce empty fields" to true.

You can control if an import set will coalesce on fields with no value. The field map Coalesce empty fields field allows you to coalesce on fields with no value.

By default, fields marked as Coalesce in the field map cause the import set to check for a target record with a field value that matches the value from the import set staging record. When Coalesce empty fields is selected for that field map, an empty value in the target record and import set staging record counts as a match for the purpose of coalescing.

For example, the User transform map coalesces on the email field. With the Coalesce empty fields option selected, a source record containing an empty email address coalesces to a target record containing an empty email address.

 -Pradeep Sharma

[Zach Hart]

This does not quite solve the issue I'm having - When I select Coalesce empty fields checkbox for the number field, if I POST to the endpoint and don't provide the number field in the body of my request, assuming there is no record in the incident table without a number, a new record will be created (which is desired), however this incident then has no number (undesired). If I go to the incidents table to view this record, it appears as so:

My desired outcome here is that, not only is a new record created, but also that it is provided a number upon insertion as it would if the number field was not set to coalesce.

Taking this further, if I send another POST with different data in the body (again without a number field), The response I'm given from the api indicates that the status of the record is "updated", not "inserted". Checking the incidents table again indicates that the alert with the (empty) number was, in fact, updated with the new values. This opposed to the desired action of a new record being inserted.