OL Learn

Detailed table sorting with array

Need a detailed item list sorted in Datamapper.
What am i doing wrong here, when I try to push the table back into a new detailed table ?

Script for creating array:
var ItemsSortedArray = [];
//build array from detail table
for(var index=0;index<record.tables[“items”].length;index++) {
ItemsSortedArray.push( {
lm:record.tables[“items”][index].fields[“LineNumber”],
im:record.tables[“items”][index].fields[“ItemNumber”],
it:record.tables[“items”][index].fields[“ItemText”],
dd:record.tables[“items”][index].fields[“Deliverydate”],
no:record.tables[“items”][index].fields[“NumberOrdered”],
ut:record.tables[“items”][index].fields[“Unit”],
ln:record.tables[“items”][index].fields[“LocationNumber”]
});
}
//sort array
ItemsSortedArray.sort(function(a,b){ return a.ln>b.ln;});
//copy array back into detail table




image
Pakkeliste XML 20220505 v1.04.OL-datamapper (11.3 KB)

I am unable to open your Datamapper. In which version is it done?

Can you resave it and publish it again?

You will have more chance at many users looking into it if you save it in the current latest public release of Connect which is 2021.2.1.

Connect 2022.1 has yet to be release for all :wink: .

Is it was made in 2022.1
Just saved it to the old version.Pakkeliste XML 20220505 v1.04 (2021.2).OL-datamapper (13.1 KB)

Normally I would say:

  1. Remove the second Repeat Step.
  2. Add the Table and the (Table) Fields to the Data Model – otherwise using “.addRow()” doesn’t work.
  3. Make sure that the property names used in the JavaScript code “ItemsSortedArray.push({...})” matches with the names of the Field added to the Table, which you’ve just added to the Data Model.
  4. Add the following lines of JavaScript code to the to the Action Step applied Script:
for(var i = 0; i < ItemsSortedArray.length; i++)
    record.tables["itemsSorted"].addRow(ItemsSortedArray[i]);
}

But this will unfortunately result in an error by execute the JavaScript code:

record.tables["itemsSorted"].addRow(ItemsSortedArray[i]);

The error: Cannot call method “addRow” of undefined

Therefore I am going to suggest to:

  1. Remove the second Repeat Step.
  2. Add the Table and the (Table) Fields to the Data Model – otherwise using “.addRow()” doesn’t work.
  3. Add a Action Step to the first Repeat Step
  4. Add the following JavaScript code to the to the Action Step applied Script: “record.tables.itemsSorted.addRow({}); // Adds an empty row to the table
  5. Make sure that the property names used in the JavaScript code “ItemsSortedArray.push({...})” matches with the names of the Field added to the Table, which you’ve just added to the Data Model.
  6. Add the following lines of JavaScript code to the Script applied to the Action Step
for(var i = 0; i < ItemsSortedArray.length; i++) {
    record.tables["itemsSorted"][i].set(ItemsSortedArray[i]);
}

Pakkeliste XML 20220505 vJC.OL-datamapper (12.8 KB)

I can’t tell you why your steps don’t work and I suggest you report this to our Technical support team via our website, but through scripting I accomplished what you want.

Saved it in 2021.2.1 so other users can see the script until 2022.1 is released.

Here is a cleaner version that I redid after reading @Marten’s post:

Pakkeliste XML 20220505 vJC2.OL-datamapper (12.7 KB)

Thanks guys it is working.
Just one more question for the experts.

I need to sort the datalist by first location and then itemnumber
I tried this in the script, but it does not work.

    return a.LocationNumber>b.LocationNumber  ||  a.ItemNumber > b.ItemNumber;
and
 return a.LocationNumber>b.LocationNumber  ||  a.ItemNumber.localCompare > b.ItemNumber;

I have added a new xml to the datamapper with only two items on same location but with different item numbers.
I want 0882 before 0883 on that location.
Can you also solve that ?[1152325.xml|attachment](upload://bXs60AZaP0hOZSZaZPWhMZOxIBV.xml) (29.2 KB)

Try this:

return a.LocationNumber==b.LocationNumber ? 
       a.ItemNumber>b.ItemNumber : 
       a.LocationNumber>b.LocationNumber;

Pakkeliste XML 20220505 vJC3_2021.2.1.OL-datamapper (18.6 KB)

@Phil, I tried your code but for some unknown reason, the subrecord with LocationNumber = 0 ends-up the last one… So here’s my way :stuck_out_tongue_closed_eyes:

Perfect both version work for me.
So happy :slight_smile:

Pakkeliste XML 20220505 vJC_Phil_2021.2.1.OL-datamapper (18.5 KB)

@Phil showed me a even cleaner version of it.