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

[Abhijit4]

Hi,

Not sure why you want to do this without coalesce. but, you can have onBefore script where you can glide into target table and add query with primary field filter which validates the unique record.

e.g. Script : onBefore

Script :

var target=new GlideRecord("target_table_name");

target.addQuery("primary_field_name",source.primary_field_name);

target.query();

if(target.next()){

//Update existing duplicate record here.

}

Let me know if you have any further queries.

Please mark this as Correct or Helpful if it helps.

Thanks and Regards,
Abhijit

[DH7]

Thanks, Abhijit

 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?

[Abhijit4]

Are these some_val1, some_val2 same for all records? if yes, then you can just apply coalesce on "some_val3" field because anyway it has to be unique as combination of 3 is unique as per your explanation.

My above script and logic works fine (your script as well). However, we will have to understand your requirement clearly so that we make it more efficient. The above method might take enough amount of time for processing if record count in target table is very high as we are gliding into the table for each record.

Let me know if you have any further queries.

Please mark this as Correct or Helpful if it helps.

Thanks and Regards,
Abhijit

[DH7]

Hi Abhijit,

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?