Detailed table sorting with array

klaus.soerensen · May 5, 2022, 9:18am

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

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

jchamel · May 5, 2022, 11:32am

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

Can you resave it and publish it again?

jchamel · May 5, 2022, 11:41am

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 .

klaus.soerensen · May 5, 2022, 11:46am

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)

Marten · May 5, 2022, 12:52pm

Normally I would say:

Remove the second Repeat Step.
Add the Table and the (Table) Fields to the Data Model – otherwise using “.addRow()” doesn’t work.
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.
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:

Remove the second Repeat Step.
Add the Table and the (Table) Fields to the Data Model – otherwise using “.addRow()” doesn’t work.
Add a Action Step to the first Repeat Step
Add the following JavaScript code to the to the Action Step applied Script: “record.tables.itemsSorted.addRow({}); // Adds an empty row to the table”
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.
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]);
}

jchamel · May 5, 2022, 12:52pm

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.

jchamel · May 5, 2022, 1:03pm

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

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

klaus.soerensen · May 5, 2022, 1:46pm

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)

Phil · May 5, 2022, 2:24pm

Try this:

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

jchamel · May 5, 2022, 2:34pm

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

klaus.soerensen · May 5, 2022, 2:50pm

Perfect both version work for me.
So happy

jchamel · May 5, 2022, 2:56pm

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

@Phil showed me a even cleaner version of it.

rommiei · September 29, 2022, 9:51pm

I’m looking on this resolution but my scenario is more complex having nested tables. Once the section table sorted, all its table details below should follow. Any idea how I can do it?

Tables_Sorting.OL-datamapper (41.2 KB)

jbeal84 · November 3, 2022, 11:57am

Is this the only way to sort data in an array?

How would sort the whole data file also if that was needed

klaus.soerensen · November 3, 2022, 12:13pm

I used this script to sort my detailed table on two parameters and then place the data in a new detailed table. Maybe you can use some of it.

ItemsSortedArray = ;

//build array from detail table
for(var index=0;index<record.tables[“items”].length;index++) {
ItemsSortedArray.push( {
LineNumber:record.tables[“items”][index].fields[“LineNumber”],
ItemNumber:record.tables[“items”][index].fields[“ItemNumber”],
ItemText:record.tables[“items”][index].fields[“ItemText”],
DeliveryDate:record.tables[“items”][index].fields[“Deliverydate”],
NumberOrdered:record.tables[“items”][index].fields[“NumberOrdered”],
Unit:record.tables[“items”][index].fields[“Unit”],
LocationNumber:record.tables[“items”][index].fields[“LocationNumber”]
});
}
//sort array by Locaton and Itemnumber
ItemsSortedArray.sort((a, b) => a.LocationNumber - b.LocationNumber || a.ItemNumber - b.ItemNumber);

//copy array back into detail table
for(var i=0; i < ItemsSortedArray.length; i++){
if(i === 0){
record.tables.ItemsSorted[i].set(ItemsSortedArray[i]);
}else{
record.tables.ItemsSorted.addRow(ItemsSortedArray[i]);
}
}

jbeal84 · November 3, 2022, 1:12pm

Thanks yeah does help I just would have thought something like this wouldn’t need a lot of script writing.

jchamel · November 3, 2022, 1:35pm

You can also do all of your sorting in a preprocessor script. Here one I use where I sort on multiple fields:

var fileIn = openTextReader(data.filename,"UTF-8");
var tmp = createTmpFile();
var fileOut = openTextWriter(tmp.getPath(),"UTF-8");

/*var tmpDebug = createTmpFile();
  var fileOutDebug = openTextWriter("c:/out/pi/sortJSON.json","UTF-8");*/

var line, JSONLine= "";
var emplArray, itemArray;
var theJSON;

while((line = fileIn.readLine())!= null){ 
	JSONLine += line;
}

theJSON = JSON.parse(JSONLine);

emplArray = theJSON.Lines.DpBidProjInvoiceEmplEntity;
itemArray = theJSON.Lines.DpBidProjInvoiceItemEntity;

if(emplArray.length > 0){
  emplArray = emplArray.sort( 
                  function mysort(a,b){
                     return (a.Name<b.Name) ? -1 : 1;
                  });

 theJSON.Lines.DpBidProjInvoiceEmplEntity = emplArray;
}

if(itemArray.length > 0){
  itemArray = itemArray.sort(
                function mysort(a,b){
                    var myResult;

                    if(a.SalesId < b.SalesId){
                            myResult = -1;
                    }else if(a.SalesId > b.SalesId){
                            myResult = 1;
                    }else if(a.DpObjEndUserAccount < b.DpObjEndUserAccount){
                            myResult = -1;
                    }else if(a.DpObjEndUserAccount > b.DpObjEndUserAccount){
                            myResult = 1;
                    }else if(a.DpObjCareContractNB < b.DpObjCareContractNB){
                            myResult = -1;
                    }else if(a.DpObjCareContractNB > b.DpObjCareContractNB){
                            myResult = 1;
                    }else if(a.RevRecContractStartDate === '1900-01-01T00:00:00Z' && b.RevRecContractStartDate === '1900-01-01T00:00:00Z'){
                    	      if(a.DpObjCareStartDate+a.DpObjCareEndDate < b.DpObjCareStartDate+b.DpObjCareEndDate){
                        	    myResult = -1;
                    		  }else if(a.DpObjCareStartDate+a.DpObjCareEndDate > b.DpObjCareStartDate+b.DpObjCareEndDate){
                            	myResult = 1;
                              }else if(a.DpObjLineNum < b.DpObjLineNum){
                            	myResult = -1;
                    		  }else if(a.DpObjLineNum > b.DpObjLineNum){
                            	myResult = 1;
                    		  }else{
                            	myResult = 0;
                    		  }
                    }else if(a.RevRecContractStartDate === '1900-01-01T00:00:00Z' && b.RevRecContractStartDate != '1900-01-01T00:00:00Z'){
                    	      if(a.DpObjCareStartDate+a.DpObjCareEndDate < b.RevRecContractStartDate+b.RevRecContractEndDate){
                        	    myResult = -1;
                    		  }else if(a.DpObjCareStartDate+a.DpObjCareEndDate > b.RevRecContractStartDate+b.RevRecContractEndDate){
                            	myResult = 1;
                              }else if(a.DpObjLineNum < b.DpObjLineNum){
                            	myResult = -1;
                    		  }else if(a.DpObjLineNum > b.DpObjLineNum){
                            	myResult = 1;
                    		  }else{
                            	myResult = 0;
                    		  }
                    }else if(a.RevRecContractStartDate != '1900-01-01T00:00:00Z' && b.RevRecContractStartDate === '1900-01-01T00:00:00Z'){
                    	      if(a.RevRecContractStartDate+a.RevRecContractEndDate < b.DpObjCareStartDate+b.DpObjCareEndDate){
                        	    myResult = -1;
                    		  }else if(a.RevRecContractStartDate+a.RevRecContractEndDate > b.DpObjCareStartDate+b.DpObjCareEndDate){
                            	myResult = 1;
                              }else if(a.DpObjLineNum < b.DpObjLineNum){
                            	myResult = -1;
                    		  }else if(a.DpObjLineNum > b.DpObjLineNum){
                            	myResult = 1;
                    		  }else{
                            	myResult = 0;
                    		  }
                    }else if(a.RevRecContractStartDate+a.RevRecContractEndDate < b.RevRecContractStartDate+b.RevRecContractEndDate){
                       	    myResult = -1;
                    }else if(a.RevRecContractStartDate+a.RevRecContractEndDate > b.RevRecContractStartDate+b.RevRecContractEndDate){
                           	myResult = 1;
                    }else if(a.DpObjLineNum < b.DpObjLineNum){
                            myResult = -1;
                    }else if(a.DpObjLineNum > b.DpObjLineNum){
                            myResult = 1;
                    }else{
                            myResult = 0;
                    }
                    return myResult;
            }
          );
  theJSON.Lines.DpBidProjInvoiceItemEntity = itemArray;
}

fileOut.write(JSON.stringify(theJSON));
//fileOutDebug.write(JSON.stringify(theJSON));

fileIn.close();
fileOut.close();
//fileOutDebug.close();
deleteFile(data.filename);
tmp.move(data.filename);