Hello,
it would be great to have the possibility to sort records within the datamapper before setting the record boundaries, at least for structured data formats like csv.
Thanks,
Thomas
sorting records isn’t a Datamapper task, but maybe this could be done in a preprocessor task.
I think it should be part of the datamapper (preprocessor).
Let’s say you have the following data file (
Customer;Invoice;Name;invoice_details
123456;IN110011;Thomas;100.00
123456;IN110011;Marco;20.00
987654;IN111999;Frank;12.99
123456;IN110011;Michael;33.50
987654;IN111999;Susan;80.00
You have to create an invoice table within your design template. So all invoice_details to one invoice number have to be in one record. Sorting data in the output would be too late, because in this case I would become 2 records for invoice number IN110011 and 2 records for invoice number IN111999. That’s not a unusual case.
Maybe it is possible in the preprocessor via script, but it would be a great help if the datamapper give us an option to sort the data without scripting.
It’s a feature request because I´m relatively often confronted with that case.
In your specific case, there are options to sort and group records in the Job Preset and create Sets. In this way, all invoices with the same invoice number or customer number could be grouped within the same set and included in the same envelope.
In your specific case, there are options to sort and group records in the Job Preset and create Sets. In this way, all invoices with the same invoice number or customer number could be grouped within the same set and included in the same envelope.
That doesn’t work. As I mentioned before all invoice_details should be in one invoice table. Your approach would create 2 documents for 1 invoice instead of 1 invoice document.
ok, so you seem to have some kind of transactional data but unsorted or mixed, so actually not really ready to use transactional data. So the issue rather is with the source file which has an unusual format, and in my opinion it’s not part of the Datamapper to correct wrong formatted data.
Anyway it should be quite easy to sort in a previous step, either in a Workflow task by script (what is quite easy in this case), or you do it inside the Datamapper in a preprocessor script, which also shouldn’t be a difficult task.
Implementing a sort feature into the Datamapper needs the software to be enhanced, not sure if this ever would be done as there are the above easy options. And the sorting feature would need to work for all sort of data, which isn’t easy to get implemented I guess.
Yeah, I agree with Martin. To cater for every type data would be a pain. And since your data is simply delimited, create a macro in Excel and then have it sort the data by Customer. I think you could also use the Run External Program plugin in Workflow to further automate that for you.
Regards,
S
And the sorting feature would need to work for all sort of data, which isn’t easy to get implemented I guess.
Ok, that’s a comprehensibly argument. So I wrote a script to sort csv files in a preprocessor script.
I agreed that this feature needs adding, I dont understand the un helpful comments on this post, some sugestions of coding etc would be more helpful!
I actually think there is a coding solution. I have a similar situation where the data is extracted from a text file. The text file already has some formatting with invoice/ order lines in a certain order. These lines need to be reorderded on thre values in the lines.
I extracted the lines using a repeat step which gave me a detail table. I used a script to parse the records (values) into an array and used the javascript sort method. (e.g. javascript - How to sort 2 dimensional array by column value? - Stack Overflow)
There might be some drawbacks to this method. Maybe some memory issue may occur with large arrays and to be able to create a detail table in designer, it needs te be done in code as well.
I would very much appreciate some way to do this in the datamapper. For transactional data, solving this in the workflow is a real option. Other data however is (as far as I can determine) can not be sorted in such a way in the workflow. But is only possible after extraction.
I ended up writing a PHP class for this and added it to our Web server that sorts the data and returns a sorted data file, I am finding I am doing more and more of this and only needing PreS for the designer tool, I am working on a HTML to PDF engine so I can do away with the whole lot, I maybe will just keep designer for ease of HTML creation…
I am using a script in the preprocessor where I read the input file (CSV), convert it to json, sort the lines by column and convert it back to csv. The result will be my new input file.
var sortCol = "InvoiceNr";
/* *** NICHT anzupassender Bereich: ************* */
var mainFile = openTextReader(data.filename);
var lines = [];
while ( ( main_zeile = mainFile.readLine() ) != null ) {
lines.push(main_zeile);
}
mainFile.close();
var json = csvJSON(lines);
var sortedJSON = sortByKey(json, sortCol);
var sortedCSV = jsonCSV(sortedJSON);
var tmpFile = createTmpFile();
var outFile = openTextWriter(tmpFile.getPath());
outFile.write(sortedCSV);
outFile.close();
deleteFile(data.filename);
tmpFile.move(data.filename);
//*** FUNKTIONEN **********************************//
function csvJSON(lines){
var result = [];
var headers = lines[0].split(";");
for(var i=1;i<lines.length;i++){
var obj = {};
var currentline = lines[i].split(";");
for(var j=0;j<headers.length;j++){ obj[headers[j]] = currentline[j]; }
result.push(obj);
}
return result;
}
function sortByKey(array, key) {
return array.sort(function(a, b) {
var x = a[key]; var y = b[key];
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
});
}
function jsonCSV(obj) {
var cols = "";
for ( key in obj[0] ) { cols += key + ";"; }
cols = cols.slice(0,-1) + "\n";
var jsObj = "";
var content = "";
for ( var i = 0; i < obj.length; i++ ) {
jsObj = obj[i];
for ( cont in jsObj ) { content += jsObj[cont] + ";"; }
content = content.slice(0,-1) + "\n";
}
return cols + content;
}
1 Like
I think the idea of being able to pre-sort tabular data makes a lot of sense. We could at least implement the feature for CSV/Database data. Other data formats would be much more of a challenge since there is no guarantee that a specific column is present in each record.
I will add this to our feature requests database.
1 Like
You can already sort a database using the query SORT BY
Yes, obviously, but many users may not be familiar with SQL syntax. So for specifying a basic sorting order, it would be nice to have an easy GUI that’s consistent across all tabular data formats. Of course, if one wants to build a more complex query with DB input, the Custom SQL feature would still be available.