Detailed table sorting with array

[klaus.soerensen]

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)

[hamelj]

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

Can you resave it and publish it again?

[hamelj]

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]

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]

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]);
}

[hamelj]

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.

[hamelj]

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

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

[klaus.soerensen]

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]

Try this:

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

[hamelj]

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]

Perfect both version work for me.
So happy

[hamelj]

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

@Phil showed me a even cleaner version of it.

[rommiei]

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]

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]

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]

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

[hamelj]

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);