How to remove duplicate records from a detail table in DataMapper

OL Connect Professional Designer
1

Dear All,

I have extracted a detail table and some of the value is same.

For example, record.tables[“detail”][1].fields.orifilename is having same value with record.tables[“detail”][2].fields.orifilename

So i need to remove record.tables[“detail”][2].fields.orifilename

Kindly advise how can I do this in DataMapper.

Thanks and Regards,
Janice

2

Dear @CLTeh_fbmy,

First of all, welcome to our community!

As in regards to your forum post: Can you share your current Data Mapping Configuration (data mapper) with us please? The reason why I am asking this is because then we can take a look at your data mapper and advice you what needs to be changed to achieve this.

TIP: Please make sure to first check whether the to your data mapper applied data sample file(s) don’t contain any sensitive information before sharing your data mapper with us.

3

Hi Marten,

Attached is the DataMapper. There are 73 records for record Attachment and some of the records are having same value. I would need to remove the duplicate records from record Attachment.

I do have an Action script to sort and filter the duplicate records but it does not work.

Kindly assist.

Thank You.

removeduplicates.OL-datamapper (9.3 KB)

4

Hello @CLTeh_fbmy,

  • Add a condition inside your Repeat statement
  • Set the condition to be Based on Javascript
  • Use the following code:

Script:

var oriFileName = '';
var length = 0;
var ok = false;

if(record.tables.detail){
   oriFileName = data.extract('COLUMN1',0);
   length = record.tables.detail.length;
   ok = oriFileName != record.tables.detail[length-1].orifilename;
}else{
    ok = true
}
ok;

Then put your Extraction(s) stps in the true branch.

5

The attached DataMapper config filters duplicates. It first creates a sourceRecord property named fileNames, an empty array in which we’ll be storing the various distinct file names.

In the loop, the content of column1 is checked against the content of the array: if the file name already exists in the array, the condition returns false and nothing is extracted for this line. If the file name is not yet in the array, then it is added to the fileNames property and the fields are extracted for that line.

removeduplicates-filtered.OL-datamapper (12.6 KB)

EDIT: I just saw that jchamel posted right before I did. His solution is valid, but takes for granted that all the orifilename values are sorted and grouped, whereas mine doesn’t care.

6

@Phil is right. But to feel secure about mine, simply go to your Settings pane and set the Sort on: field to COLUMN1 :wink:

7

Thank you @Phil and @jchamel !!