Has anyone written a file content sort using javascript or other cloud supported function?

As a test, I created a quick CSV and put it in Multi-Tenant FileStorage. I then read the data, put it in an array and did a sort on 2 columns of the data. One column is ASC, the other DESC.

My input data is:

Bob,Smith,24,IT
Sarah,Brown,35,HR
Trish,Franklin,56,Benefits
Fred,Allen,35,HR

My output data is (age desc, lastname asc):

Trish,Franklin,56,Benefits
Fred,Allen,35,HR
Sarah,Brown,35,HR
Bob,Smith,24,IT

I have a sample flow called "sortFileExample". For the file attachment below, change the extension to "lpd" and you can view the flow. Hopefully, this will give you some ideas.[View:/cfs-file/__key/communityserver-discussions-components-files/57/4201.sortFileExample.txt:320:240]

All comments

unknown

javascript sounds gross and slow for sorting. Would it be an option for you to sort the file before it is input to the flow?

I'm in the same boat for the most part as I no longer have access to SQL or SysCmd. I've seen one option and would like better ones. I've seen an array.sort(). You might also be able to put it in a custom business class and then query that out with a Landmark transaction node.

I was going to suggest reading the file, then using javascript to store the content in an array, sort the array, then write the file back out.

You would store each line in the array to do this, correct? How would you go about sorting it based off of three separate fields/columns? (i.e. company, department, account) Would you concatenate those and sort? Or sort once, then a second time, then a third?

I am trying to use Infor delivered utilities that I know will still be available in the Cloud. Infor delivered a new uitilty for GHR called File Creation Utility that allows you to create file extracts and can immediately launch IPA flows to consume the data.

This is what support is suggesting but they can't provide any examples on how to do this.

robert-canham

I did this in javascript for a complex sort for an array. It isn't horrible, but kinda weird.

I was sorting a file based on the 2nd and 4th columns of the file. To do that, you write a custom sort function that you call when you use the sort. Mine looked like this:

a and b represent the two lines being compared, the 0, 1 and -1 represent whether they're the same, the first line is earlier (-1) or later (1). Then I called the sort function on my array.

fileLines.sort(compareSupervisors);

Yes, you would need to read the entire file into the array. To sort on 3 fields, I believe you would have to combine the three fields for the sort into one field. You can then sort the "one" field. I will look for an example.

I wrote something super basic. In the start node I made an array of numbers ([5,3,6,9,8,1,2,3,4,7]), and in an assign node I made wrote in javascript variable.sort() and it sorts it. The complex sorts that Robert and Richard are explaining is where I have gotten hung up.

Thank you! This will definitely help.

Thank you! I appreciate it. I'm running into needing to sum values based on multiple criteria and this would be a big help.

russel-cornelson

Good afternoon, can you add some more context around what you are attempting to achieve? I am not familiar with this "File Creation Utility that allows you to create file extracts and can immediately launch IPA flows to consume the data."

1. WARNING: JavaScript sort might be extremely expensive depending on the situation

2. I have an enhancement request to add the ability to sort output coming from a Replication Set. I think this is targeted for the next MT CU. (11.1.10)

3. I logged a new enhancement request to Process Automation to add a "Sort Activity Node".

For a short term workaround; sorting small amounts of data via JavaScript is possible but if this is a large dataset it may simply be too expensive to perform via javaScript. I would need to know what format the data is coming in; what type of data it is, how much data is there, etc.

Example dataset, and what you want to sort on may help.

Russel,

File Creation Utility is similar to the Replication Sets and it sounds like both were built by Infor without a sort option. One of the big differences is the FCU was built to only be used with GHR and can support linking rows with a One to Many relationship (for example employee to dependent benefits).

Like Replication Sets you are able to do queries that run through the Async queue and creates extract files that you can then process through an IPA flow that you attach to the Replication Set or FCU.

My current queries are accessing the Employee Business Class and related Classes,approximately 10,000 records, and need to be sorted by the Employee Number but future queries could include Employee benetits which can be a file of 20000 records. Each file could contain 50+ columns per record.

FCU appears to use a Key field to sort various data elements and use this as sort criteria but it then stores these numbers as AlphaLower not Numeric so this messes up the numeric sort.

andy

I had a similar issue and just ended up building a merge sort function in JS. This function is predicated on having an array of JSON objects each with a "ZeroCount" property.

//------------------------------------------------------------------------------
// Merge Sort - O(n) - Sorted by Descending ZeroCount values
//------------------------------------------------------------------------------
function mergeSort(array) {
if (array.length < 2) {
return array;
}
var mid = Math.floor(array.length / 2);
var left = array.slice(0, mid);
var right = array.slice(mid);
return merge(mergeSort(left), mergeSort(right));
}

russel-cornelson

@kyle-tucker

Are you pulling data out of Landmark, then needing the sum of the results?

unknown

For a large number of rows, try loading the data into temp SQL table and read it back with "ORDER BY" option.

As a test, I created a quick CSV and put it in Multi-Tenant FileStorage. I then read the data, put it in an array and did a sort on 2 columns of the data. One column is ASC, the other DESC.

My input data is:

Bob,Smith,24,IT
Sarah,Brown,35,HR
Trish,Franklin,56,Benefits
Fred,Allen,35,HR

My output data is (age desc, lastname asc):

Trish,Franklin,56,Benefits
Fred,Allen,35,HR
Sarah,Brown,35,HR
Bob,Smith,24,IT

unknown

I did something like this for a file upto a few thousand lines. We had one requirement where we were sorting millions of lines in 4GL. We went with sorting it outside of Cloudsuite and taking the sorted file into IPA for loading it in General Ledger.

Heres my code;

csv file comes from file channel in my example. we were trying to sort by 1st, 2nd and 5th column in our example.

presortArray , indexArray and fileArray are all declared as Array variables

I is an integer variable

presortArray=_inputData.split("
");

for (i=0;i<presortArray.length;i++)
{
   indexArray=presortArray[i].split(",");
   if(trim(indexArray[0])!="")
   {fileArray[i]=indexArray[0]+","+indexArray[1]+","+indexArray[4]+","+indexArray[2]+","+indexArray[3]+","+indexArray[5]+","+indexArray[6]+","+indexArray[7]+","+indexArray[8]+","+indexArray[9]+","+indexArray[10]+","+indexArray[11]+","+indexArray[12]+","+indexArray[13]+","+indexArray[14];
}
}

fileArray.sort();

Thank you LG for the example.