Form Filter with Aggregates

[Mark Crichton]

Hi, would appreciate help with the following dilemma.

I have a tableview form based on product and it has relation with product_to_lines

Lines table has aggregrate function field totaling value column

columns displayed are - product.code, product.name, lines.sum_value - being totals of filtered transaction lines for a given date range.
If I set the lines table filter to the product range and date range required, I still get the total for all lines in the database displayed.

When the filter is applied only products that had sales during the filtered period are included, so filter is applying.

Should this work as described, and if so what could I be doing wrong.
Maybe if I could see the sql that is being applied that would help. Is it possible to view it?

Filter code used :
function onFilter(event) {
// Set user filter

if(controller.find() ) {

//Date
if(vFromDate && vToDate) {
fixproduct_to_fixlines.linstart = '#' + utils.dateFormat(vFromDate,'dd-MM-yyyy') + ' ... ' + utils.dateFormat(vToDate,'dd-MM-yyyy') + '|dd-MM-yyyy'
}

// Product Range
if(vStkStart && vStkStop) {
prdcode = vStkStart + '...' + vStkStop
}

[mboegem]

From a quick look at your problem, I guess you'll need to include the daterange in your relation as well.

[Mark Crichton]

Hi Marc
Not sure I understand your point ... may be my wording, the lines table is called fixlines. The date field is fixlines.linstart , so should be in the relation.
Please can you explain more.
Thanks

[mboegem]

Let me recap your problem:

you have products and transaction lines.
you want to show a summed value for each product
when you filter the displayed products based on transaction line date, you expect the summed value to meet the filter requirements as well.

At this moment you have a relation, something like this:
product_to_tx_lines, probably with product.prd_id = tx_lines.fk_prd

So what you're doing right now is filtering your products based on the transaction line date.
This will return all products which have a transaction in this daterange.
However the relation product_to_tx_lines still point to ALL transaction lines, returning you the summed value of ALL transaction lines as well.

So what I tried to point out is that you'll need to include this date range in the relation as well.
Probably a 2nd relation for this purpose is better.

However, you need to understand when you don't have a daterange applied as filter, you should still use a wide daterange (so it'll include all transaction lines) in order to make the relation work.

Hope this helps.

[Mark Crichton]

Hi Marc

Many thanks for the explanation. I created two global variables for the date range with defaults and added them to a new version of the relation. Also added a text filter 'Sale' = lintype and it work really well. Saves a lot of coding, and easily reusable.
Very useful feature I had never tried before, but now see many uses for it.