Remove duplicate detail records and sort by a field.

[robyger]

Hi,

Sometimes we get xml files with duplicate records in the detail table. I have a xml file similar to this one: https://learn.objectiflune.com/qa-blobs/15935198453965077885.ol-datamapper

You can see in the detail table that books record index 1 and 4, then 9 and 13 are duplicates.

How can I skip duplicates in the data mapper.

I also I need to sort the detail table by book id becaus ethe dynamic table in designer must be sorted in this way.

Is there an option to sort detail table in data mapper or designer?

Many thanks

[Sharne]

Hi Robyger,

I’m not sure about within the datamapper without using a preprocessor script but I found a script that I modified and used a while ago that you can make use of to remove duplicates. This script will be used within the template. Just add a Script to you templates scripts pane and add the following to the scripts Selector. #table tbody tr (table is your table name, so change it to whatever you called your table.)

var seen = {};

results.each(function() {

var txt = this.text();

if (seen[txt])

    this.remove();

else

    seen[txt] = true;

});

Keep in mind that this script only deletes duplicate rows that are identical. For the sorting bit, I find JavaScript painful in that department. Perhaps the guys and girls here can help with that.

Regards,

S

[Phil]

And as far as sorting goes, the following script takes care of it:

var items=[];
results.each(function(index) {
 field = record.tables["detail"][index].fields["Number"];
 items.push(field);
});

items.sort();
results.each(function(index) {
 this.html(items[index]);
});

Note that this only sorts the current column and is therefore only meant as an example. You will have to adjust the code if your table has multiple columns and you want them all to follow the sort order of this column.

[robyger]

Hi Phil and Sharne,

Thank you. I guess I have to combine both solutions. I will give these a go.

Thank you.

[Rod]

If you want a solution in the Data Mapper,

Add an Action step with the below script which simply creates a multi-dimensional array of unique items sorted by id.

var sortedBooksList=[];
var bookCount = record.tables.books.length;
var bookFieldsCount = record.tables.books[0].fields.length;
var bookIndex;
var uniqueBooksList;

//SORT BOOKS
for(let i=0; i<bookCount; i++){
    let bookFields = [];
    for(let j=0; j<bookFieldsCount; j++){
        if(record.tables.books[i].fields[j] == record.tables.books[i].fields['id']){
        bookIndex = j;
    }
    bookFields.push(record.tables.books[i].fields[j]);
}
sortedBooksList.push(bookFields);
bookFields =[];
}

sortedBooksList.sort(function(a,b){
    if(a[bookIndex].toLowerCase() < b[bookIndex].toLowerCase()) return -1;
    if(a[bookIndex].toLowerCase() > b[bookIndex].toLowerCase()) return 1;
    return 0;
});

 // RETURN UNIQUE BOOKS
uniqueBooksList = groupUnique(sortedBooksList);

function groupUnique(arr) {
    let uniques = [];
    let booksFound = {};
    for(let x = 0, l = arr.length; x < l; x++) {
        let arrStr = JSON.stringify(arr[x]);
        if(booksFound[arrStr]) { continue; }
       uniques.push(arr[x]);
        booksFound[arrStr] = true;
    }
    return uniques;
}

Once you have the array, it is just a matter of using the guide here to generate a detail table of sorted unique items with a Repeat Step

https://learn.objectiflune.com/howto/create-detail-table-linear-data-structure/

I have attached the new data mapping config file here.

[Sharne]

Just…WOW!! Legend!!

[robyger]

Hi Rod,

This is exactly what I was looking for. You have just saved the team a lot of frustration and time.

I have now applied the same logic to our live data and it works beautifully.

Thank you so much for your precious help.

P.S. Is there anyway to contact you directly? We have quite a lot of projects underway and are looking for someone of your caliber :-).

[Rod]

You are very welcome!

On the other note, thank you for the offer, I am very flattered. If you need help with developing your projects, simply get in contact with your reseller or account manager at Objectif Lune. They will then organise some Professional Service.

[TDGreer]

Robyger,

Is there a way to contact you? I’m looking for contract/consulting work, and have a long history with Objectif Lune, PlanetPress, and Connect.

Thomas

[TDGreer]

I’m having trouble with the Goto Step in the middle of the loop that builds the sorted Detail table. My file is TEXT, with a variable number of lines (a check register, listing check no, check amount, payee, and check date). Your goto step refers to an XPATH, but I’m not sure what I’d need to change that into for a TEXT file. Also, the how-to link in your answer is broken, it returns a 404.

[Rod]

The principle is the same. In fact we are really no longer concerned about looping through the lines of the text file since we have already extracted the data we need for the detail table in an array.

For your text data, after the Extract step, add a Goto step, from Top Of Record, Move To 1

The Loop ALWAYS requires a Goto Step to be included. But by moving to Line 1 from the top of the record, you’re basically staying put.

The OL Learn website structure was recently changed, so here is the new link:

https://learn.objectiflune.com/howto/create-detail-table-linear-data-structure/

[TDGreer]

Thanks; I understood all of this conceptually, even the need to have SOME sort of “goto” inside the loop, it mattered not where it was going, simply as an artifact to give the loop something to loop through… my error was that I somehow had “Invert condition” checked on the Loop/Repeat step so was only getting one detail record. Not seeing one tiny check mark cost me a day of head-banging, no one’s fault but my own.

FEATURE REQUEST: Ability to sort a detail (or any, for that matter) table on a specified field! Perhaps a new “Action” type? Action Type “Sort Table”, then specify the table, the field, Ascending or Descending.

[dariuszb]

Hi Phil, Can you tell me what I doing wrong - I receive error "Multiple markers at this line

• Cannot read property “fields” from undefined
• Record index ‘33’ is out of range"

Sorry…

[Phil]

Uhm… would you like to provide a bit of context, like, for instance, your script?!?

[dariuszb]

Hi Phil,
thank you for respond.
I have detail table where is column ‘Total’ and I would like to sort the data by this column.

var items=[];
results.each(function(index) {
field = record.tables[“detail”][index].fields[‘Total’];
items.push(field);
});

items.sort();
results.each(function(index) {
this.html(items[index]);
});

[dariuszb]

This is the data for the Total![example_data|678x500]

… Image removed for confidentiality reasons…

Please let me know If you need more info.

Thank You

[dariuszb]

… Image removed for confidentiality reasons…

[Phil]

I think the following code would do what you want. Note that the selector has to be set to #table tbody

var result = "";
var arr = [];
for(var index=0;index<record.tables["detail"].length;index++) {
	arr.push( {
				d:record.tables["detail"][index].fields["Distributor"],
				c:record.tables["detail"][index].fields["Country"],
				t:record.tables["detail"][index].fields["Total"]
	});
}
arr.sort(function(a,b){	return a.t<b.t;});
arr.forEach(function(elem){
	result += "<tr>" +
				"<td>" + elem.d + "</td>" +
				"<td>" + elem.c + "</td>" +
				"<td>" + elem.t + "</td>" +
			  "</tr>";
});
results.html(result);

The code first stores all detail lines into an array, then it sorts the array on the Total field, in descending order. And finally, it creates from that array table lines that each contain 3 cells.

[dariuszb]

You are the best Phil.
I just need to delete from the source data ‘$’ in the total and start working correctly.
One more question there is any way to keep the formatting table like was before?
Because when I using this scrip formatting not working properly and grand total is missing!

… Image removed for confidentiality reasons…

after

… Image removed for confidentiality reasons…

Many thanks

[Phil]

This modified script should do it:

var result = "";
var grandTotal = 0;
var arr = [];
for(var index=0;index<record.tables["detail"].length;index++) {
	arr.push( {
				d:record.tables["detail"][index].fields["Distributor"],
				c:record.tables["detail"][index].fields["Country"],
				t:record.tables["detail"][index].fields["Total"]
	});
}
arr.sort(function(a,b){	return a.t<b.t;});
arr.forEach(function(elem){
	result += "<tr>" +
				"<td>" + elem.d + "</td>" +
				"<td>" + elem.c + "</td>" +
				"<td align=right>" + formatter.currency(elem.t) + "</td>" +
			  "</tr>";
	grandTotal += elem.t;
});
result += "<tr>" +
				"<td/>" +
				"<td align=right><b>Grand Total:</b></td>" +
				"<td align=right><b>" + formatter.currency(grandTotal) + "</b></td>" +
			  "</tr>";
results.html(result);