Create External Data File from Input File, then sort by a field

Hello.

Thanks in advance for any help.

I have need to sort by a field value so I can do dynamic chunking. The input file is presorted for mailing so the "order number" field values are not contiguous. So I think/hope I can use the input file in an "on job start" something like this (name of input file is not static)

var inFile = FusionPro.Composition.inputFileName;
var exData = new ExternalDataFileEx(inFile, ',');

then I need to "sort" the records in the external data based on the field "Order Number". Can I populate an array with the external data

sortedList = [];
for (var i=1; i<=26; i++){
sortedList.push(exData.GetFieldValue(FusionPro.Composition.inputRecordNumber,i));
   }

and somehow sort the array by the field "Order Number"? My attempts have sorted the rows, not the columns.

sortedList = sortedList.sort().join("<br>");

Ideally, I would then like to be able to use the external data as the input file so that I do not have to change rules, but if that is not not possible I will re-write all the rules to use the external data, and "On Record Start" use the FieldChanged() to create my chunks by order number. I am unclear on how to reference the field in the external data.

if (FieldChanged([color="Red"]the "Order Number" field in sortedList??[/color]))
   FusionPro.Composition.OpenNewOutputFile(Field([color="red"]the "Order Number" field in sortedList??[/color]) + "." + FusionPro.Composition.outputFormatExtension);

Sure you can create an array of your data file and sort it. The larger your data file, though, the more likely you'll be to run out of resources when it comes to composition. Data should really be properly sorted before it gets to FP. That being said, I see questions about this pretty frequently so I decided to take a stab at this:

First things first, we need to set a global variable to flag when the "Order Number" value of a record has changed so that we know to start a new output file. Add this to your JavaScript Globals:

flag = '';

Then we need to bring in the data as an array and sort it. You should probably do this in the OnJobStart callback so that you're not sorting it every time you change records:

Note: You'll have to hardcode the path to the "input" variable to see the results in preview

//==================
// (Global) Variables 
//==================
sorted_data = []; 
inputFields = FusionPro.Fields;
sortField = "Order Number" // Name of field to sort by

//==================
// External Data 
//==================
var input = FusionPro.Composition.inputFileName;
var ex = new ExternalDataFileEx(input, ',');
var records = ex.recordCount;

//==================
// Populate Array & 
// Sort Array
//==================
for (var i = 1; i<=records; i++) {
       var obj = {};
       for (var l in inputFields){
           obj[l] = ex.GetFieldValue(i, l);
       }
       sorted_data.push(obj);
}

// Sort the array based on the sort field specified in variable section
sorted_data = sorted_data.sort(function(a,b){return (a[sortField] < b[sortField]) ? -1 : (a[sortField] > b[sortField]) ? 1 : 0;});

Then we can use that array to map the FP fields to the contents of the array. This way any rules that exist that pull in "Field("Order Number")" (or any other field) will be mapped to the fields in the sorted array. This is done OnRecordStart:

var recFlag = sorted_data[FusionPro.Composition.inputRecordNumber-1][sortField];

for (var i in inputFields){
    FusionPro.Composition.AddVariable(i, sorted_data[FusionPro.Composition.inputRecordNumber-1][i]);
}

Then we can use the global flag we created and compare it to the value of the "recFlag" in OnRecordStart to determine if it's time to open a new output file:

if (flag != recFlag){
   flag = recFlag;
   FusionPro.Composition.OpenNewOutputFile(recFlag + "." + FusionPro.Composition.outputFormatExtension);
}

Hopefully that helps.

It is a great help. It would be days before I got this far. I can't thank you enough.

Hardcoding the path, in OnJobStart, doing a

return sorted_data;

result in this

[object Object],[object Object],.............

but in OnRecordStart

return recFlag;

results in the value of "Order Number" being displayed in ascending order.

The output pdf is not affected, though a pdf named undefined.pdf is output additionally. They are identical in content, number of pages, etc. The data has 5 different "Order Numbers". I imagine I have something wrong in OnRecordStart.

example_template.zip