How to group and sort detail tables

OL Connect Enterprise DataMapper
1

Hello,

Can I get a bit of assistance grouping and sorting my detail table by county?

The purpose is to generate an alphabetical list of counties in a dynamic table in my template.

https://learn.objectiflune.com/qa-blobs/12691210664761925734.ol-datamapper

https://learn.objectiflune.com/qa-blobs/3767206335911002234.csv

Many Thanks,

Steve

2

Can you tell us what your final goal is?

If it is to print them grouped by county, that is something you could do in a JobPreset.

You might want to look at the following link dealing wiht the same issue as you have:

remove-duplicate-detail-records-and-sort-by-a-field

datamapper-sort-records&show

and there are many more if you search on our OLLearn Q&A.

3

Download Example

I believe I have answered this before. I came up with a function which can sort a detail table by a specifc field name in ascending or descending order. The function takes up to 3 arguments: the table to be sorted, the field to sort by and the sort direction (optional).

The trick is to push every detail record into an array and then sort it.

Once you have a sorted array, use the technique described here to create a new sorted detail table in the datamapper

Please note by the default, the Designer will only show the first 200 records in the detail table. If you want to see more than the first 200 records, then click on Window > Preferences > Editng > Detail table Preview Limit and increase the value here.

Of course you may edit the below script to suit your own requirements:

if(record.tables.detail){
  var county= record.tables.detail[0].fields.county;
  var table = record.tables.detail;
  var sortedByCounty = sortDetailTable(table,county,'ASC');
}


function sortDetailTable(table, sortParam, direction){

  direction = (typeof direction === 'undefined' || direction !== 'DESC') ? 'ASC' : direction;

  let sortedDetailRecords=[];
  let detailRecordsCount = table.length;
  let detailFieldsCount = table[0].fields.length;
  let sortParamIndex;
  
  for(let i=0; i<detailRecordsCount; i++){
    let detailFields = [];
    for(let j=0; j<detailFieldsCount; j++){
      if(table[i].fields[j] == sortParam){
      sortParamIndex = j;
    }
      detailFields.push(table[i].fields[j]);
    }
    sortedDetailRecords.push(detailFields);
  }
  
  sortedDetailRecords.sort(function(a,b){
    if(a[sortParamIndex].toLowerCase() < b[sortParamIndex].toLowerCase()){ return -1;}
    if(a[sortParamIndex].toLowerCase() > b[sortParamIndex].toLowerCase()){ return 1;}
    return 0;
  });
    
  switch(direction){
    case 'ASC':
      return sortedDetailRecords;
    break;
    case 'DESC':
      return sortedDetailRecords.reverse();
    break;
    default:
      return sortedDetailRecords;
    break;
  }

}
1 Like
4

Yes you could use the example referenced in the above links as well and adjust to your own specific requirements. Except one of these removes duplicates… So you may remove the part of the script which strips out the duplicates. I will be positing an updated version here.

5

Hi Rod,

What a guy! You never cease to amaze me with the quality of your answers.

Thank you so much. This is what I needed.

Steve

6

Any chance you could reupload your example?

7

Here you go.