Asynchronous transform Import set row behavior when there is no coalesce

[Sriharsha Radha]

Hello Team,

I have a web service import which has a transform map without coalesce field. Initially this was happening synchronously and we were seeing some duplicates because of this. So i changed it to Asynchronous transform with a business rule and running the transform periodically through scheduled job. 

I have also added a property : glide.soap.import_set_insert_serialized.<table name> thinking this would serialize the transform. However we still see some duplicates at times.

Does asynchronous transform does serially or concurrently on all rows?

Does any one know how to transform  web service imports when there is no coalesce to prevent duplicates permanently.  

[Mehta]

@Sriharsha Radha 

Without coalesce each record coming into stagging table will get into the target table. as there is no unique identifier which will identify the primary value on record to make system understand that this records needs to be updated and not to create a new one. 

servicenow has given this as a feature know as coalesce. if we do not have a field in transform map as coalesce  then the alternative is to figure out a scripting logic which will make system understand whether this needs to insert or update the record in target table.

You have to write script as onbefore script. 

Please Mark helpful or correct Answer , If it solves your query.

[Sriharsha Radha]

We have transform before scripts to ignore/update based on a logic. All that is working except that randomly duplicates are created where the record should have ignored /updated.  Since tranform script is little complex querying the data to determine, it might take few seconds in which i feel the next row is being transformed creating duplicate

[Riya Verma]

Hi @Sriharsha Radha ,

Hope you are doing great.

To address this issue, I recommend the following technical solution:

1. Convert the transform map to an asynchronous transform: Modify the existing synchronous transform to an asynchronous one. This can be achieved by creating a scheduled job that periodically runs the transform.

2. Implement a business rule: Create a business rule to handle the transformation process. The business rule should trigger the asynchronous transform when new data is imported through the web service.

3. Configure serialization property: Set the property "glide.soap.import_set_insert_serialized.<table name>" to serialize the transform. This property ensures that the transformation process is carried out sequentially, reducing the chances of duplicates.

In order to prevent duplicates permanently when there is no coalesce field, I recommend considering the following options:

• Analyze the data being imported through the web service and identify unique fields that can be used as a basis for deduplication. If there are no unique fields, you may need to explore other approaches such as generating a unique identifier based on a combination of available fields.

• Implement custom deduplication logic.

[Sriharsha Radha]

I have done steps 1-3 already. But still facing the issue randomly.

We have transform before scripts to ignore/update based on a logic. All that is working except that randomly duplicates are created where the record should have ignored /updated.  Since transform script is little complex querying the data to determine, it might take few seconds in which i feel the next row is being transformed creating duplicate