Update Duplicate record using transform map without using coalesce.

[DH7]

Hi everyone, 

Is there any way to update the duplicate record using transform map without using coalesce?

let's say we have target table 'A' it contains record 'rc_1' (ingested via transform map), now again I have record 'rc_1' which contains a few of the fields updated, can I update the 'rc_1' in table 'A' with the new 'rc_1' without using transform map coalesce?

Thanks

[Saurav11]

Hello,

Can you explain the reason behind the use case.

As if you want to update record you should do it using coalesce which is the best practice.

Now you can surely use transform scripts to do the same, but when Servicenow has given a OOB simple solution why to go to scripts.

Also writing scripts also have maintainability issue in longer terms as if you want to change primary fields it is much easier from coalesce checkbox rather than again editing the script.

So if you can explain the reasoning behind your requirement it would be great.

Thanks.

Please mark answer correct/helpful based on Impact.

[DH7]

The reason I am not using coalesce is that I have a field in the target table which is not available in the records in the staging table. I am populating that field in a different way in the target table based on my requirements.

Thanks.

[DH7]

Hi Saurav,

 What I am doing is that I am adding attachment in the Datasource and transferring it to the target table via staging table, the thing is that the attachment does not contain the fields let's say field_1, field_2 and I am populating those fields in the target table using let's say business rules.

my primary field is a combination of three fields: field_1, field_2, and field_3(available in attachment).

my onBefore sample script looks like:

var targetGr = new GlideRecord('target_table');

targetGr.addQuery('field_1', 'some_val1');

targetGr.addQuery('field_2', 'some_val2');

targetGr.addQuery('field_3', source.some_val3);

targetGr.query();

if(targetGr.next()){

    // updating target record.

}

can we achieve the same without using coalesce and script?

[DH7]

Hi Saurav,

The use case is to dedup the alerts based on the domain.
We are fetching the alerts from the third-party platform, and we are domain separating those alerts using domain separation plugin. We are storing those alerts into custom table named 'Alerts'.Ideally to dedup the alerts we should use coalase on <alert_id> + <domain> fields. But since we don't have a domain(sys_domain) field in the incoming alerts, we won't have domain field into staging alerts table as well. Hence we are not able to apply coalase on <alert_id> + <domain>.

To achieve our above use case, we are caching(using our app scope in transform script) the domain associated with the configuration from which those alerts are being fetched, and setting those domain value using transform script. Right now we are using the onBefore script that I have shared earlier with primary key as <domain> + <alert_id> which we are receiving in the incoming alert.

Could you please suggest an approach to achieve the above use case without using the transform script?

[shloke04]

Hi,

Your explanation is still not clear to me and looks confusion. I would suggest to share the use case given to you and some one can assist here.

Regards,

Shloke