Summarizing records

Classic Servoy
1

I have the following tables / data:

COMPANT_T ( 1 record ) :
company_tid ( 1 )
comp_name ( NAMI, LTD )
…

STATS_T ( 2 records ) :
stats_tid ( 1, 2 )
company_tid ( 1, 1 )
stats_origin ( John, Maria )

STATS_DETAILS_T ( 4 records ) :
stats_details_t ( 1, 2, 3, 4 )
stats_tid ( 1, 2, 2, 1 )
company_tid ( 1, 1, 1, 1 )
year ( 2005, 2005, 2006, 2006 )
amount ( 1000, 10000, 9000, 20000 )
agg_amount ( aggregate sumarizing amount - SUM )

I display the data in a company form with nested tab panels (company → stats → stats_details).

The relations are:
company_TO_stats (company_t.company_tid = stats_t.company_tid)
stats_TO_stats_details_BY_company_tid (stats_t.company_tid = stats_details_t.company_tid)

The problem is that the tab panel shows two lines. One for each year, both with a agg_amount of 21.000, but my objective is to have two lines with the total for each year:

2005, 11.000
2006, 29.000

I realize I still have to discriminate across the years, but there’s no sense trying to figure that out when I sure it’s a pretty standard problem with a straigh forward answer.

Any tips :o
Miguel

2

I came up with a solution to my problem.

I would very much appretiate a code revision.
All comments criticisms are more than welcome.

I’m specially interested in ways of approching this in Servoy that might be missing. But you can kick my poor and shameless JavaScripting so I might learn something.

Attached to the onShow event of a grandchild panel of Company form (company_rec → stats_rec → stats_details_global_t).

Note that:

  • forms.stats_details_global_t uses useSeparateFoundSet.
  • fields named has vcol_* are virtual columns
forms.company_t.company_t_to_stats_details_t.sort('report_year asc');

var len = forms.company_t.company_t_to_stats_details_t.getSize();

var yearsArray = [];
// one Array for every year aggregate we wish
var invTotalArray = [0];
var creditTotalArray = [0];
var allArrays = [invTotalArray, creditTotalArray];
var yearIndex = 0; // to coordinate yearsArray with the other Arrays
var record = forms.company_t.company_t_to_stats_details_t.getRecord(1);
var year = record.report_year;

yearsArray.push(year);

for ( var i = 1 ; i <= len ; i++ ) {
  record = forms.company_t.company_t_to_stats_details_t.getRecord(i);

  if ( year != record.report_year ) {
    year = record.report_year;
    yearsArray.push(year);
    yearIndex += 1;
    // initialize as integers the various arrays
    for (var j = 0; j < allArrays.length; j++) {
      allArrays[j][yearIndex] = 0;
    }
  }
  // one line for every year aggregate we wish
  // I wonder how to abstract this (perhaps using eval ?)
  // instead of having to add a line here for each aggregate we wish to summarize
  invTotalArray[yearIndex] = invTotalArray[yearIndex] + record.invoices_acum;
  creditTotalArray[yearIndex] = creditTotalArray[yearIndex] + record.credits_acum;
}

// Lets take care of our foundset and display it
controller.loadAllRecord(forms.company_t.company_t_to_stats_details_t);

// Just keep the number of rows needed (same as no of years) to show the aggregates
if ( len != yearsArray.length ) {
  var noRows = len - yearsArray.length; // no of rows to exclude

  for (var i = 1; i <= noRows; i++) {
    controller.omitRecord();
  }
  len = controller.getMaxRecordIndex();
}

for (var i = 1; i <= len; i++) {
  controller.setSelectedIndex(i);

  vcol_report_year = yearsArray.shift();
  vcol_inv_acum = invTotalArray.shift();
  vcol_credits_acum = creditTotalArray.shift();
}

Txs,
Miguel