MatrixBox + PeriodType help!!!

abartonicek

First to say, I read all posts about matrixbox but...

I need a form which will show some info for records from Sales Line table where Document Type = Order & Type = Item

The form must have 3 buttons for PeriodType (day,week,month) and this 3 columns:
Item No. Description TotalQuantity

TotalQuantity is FlowField defined as:

Sum("Sales Line".Quantity WHERE (Document Type=FILTER(Order),Type=FILTER(Item),No.=FIELD(No.),Shipment Date=FIELD(FILTER(Date Filter))))

That columns are the left side of MatrixBox. The right side will be date periods (by day, by week, by month) and the cell values will display the TotalQuantity for that period.

That calculation thing was easy but I have problems displaying the information in MatrixBox.

I'm using Item table for Item No and Description + that control for TotalQuantity and the matrix source table is Datum (localized version of Date or something like that).
The value that I display in cells is calculated by setting the right Date Filter (new FlowFilter in Sales Line table).
The problem is that I'm not sure where to put my code because the values shown when I run the form are correct (for all 3 period types) but some cells that should have value 0,00 (decimal field) have values from some other cells.
In other words, setting filters on Date Filter field get all mess-up after few clicks on the form.

I try'd all triggers but it doesn't help ](*,)

I would like to hear some suggestions how to sort this out.
Completely new ways of creating that form are also welcome

kine

As a model form you can take for example form 113. If you look into code you will see:

Matrix - OnAfterGetRecord

BudgetedAmount := CalcAmount(TRUE);

Matrix - OnAfterGetCurrRecord

BudgetedAmount := CalcAmount(TRUE);

It is what you need. You need to enter the calculation into OnAfterGetRecord and OnAfterGetCurrRecord on Matrix box, not on the form itself.

abartonicek

I looked at that form, and form 554 and some others but the thing is that I don't use dimensions for this. I think that the problem is in setting that Date Filter for every calculation.
I'll probably need another way to do this requirement.

kine

You can ignore whole parts about the dimesnions etc. It is simple. Your CalcAmount (or some other name) function can look like:

SETRANGE("Date Filter",CurrForm.Matrix.MatrixRec."Period Start", CurrForm.Matrix.MatrixRec."Period End");
CALCFIELDS(Totalquantity);

And you will need to have it in
Matrix - OnAfterGetRecord()

that is all... (I skipped the part about useing the date as the columns, but it seems, that you have no problems with this...)

abartonicek

That's what I thought too but it doesn't work.

I show Items (No. & Description) from Item table,
I show periods from Datum (Localized Date table or something like that),
I calculate TotalQty from Sales Line table!!!

For every Item I have to get corresponding Sales Line record, set his Date Filter and call calcfields.

Is there something wrong? Did I overlook something?

kine

It seems yes... :-)

Are you sure that your code is in "Matrix - OnAfterGetRecord" and not in "OnAfterGetRecord"??? There are two "OnAfterGetRecord" triggers... can you send me the object in text to me (through PM)?

abartonicek

Sent

kriki

kine wrote:

It seems yes... :-)

Are you sure that your code is in "Matrix - OnAfterGetRecord" and not in "OnAfterGetRecord"??? There are two "OnAfterGetRecord" triggers... can you send me the object in text to me (through PM)?

Instead of using "Matrix - OnAfterGetRecord" to calculate the value to show, better use "OnFormat" of the matrixcell-variable.
With the Matrix - OnAfterGetRecord", the refreshing of the matrix-cells is not perfect. With the "OnFormat" it is.

kine

I am looking into your code and it seems, that you mixed some things...

Better is to define TotalQty field in table 27 with same definition (may be only change the name to something like "Qty on Sales Order Lines"). Because the meaning of this field is "quantity of THIS item on Sales order lines filtered by date..."

because the part

    SL.SETCURRENTKEY(SL."Document Type",SL.Type,SL."No.",SL."Shipment Date");
      SL.SETRANGE(SL."No.","No.");
      SL.SETRANGE(SL."Date Filter",CurrForm.Matrix.MatrixRec."Poźetak razdoblja",CurrForm.Matrix.MatrixRec."Kraj razdoblja");
      SL.CALCFIELDS(SL.TotalQty); 

is not clear...

abartonicek

Tried that too

I think that te problem is that refresh bug AND some of my stuff.
I just figured out that if I can't find the current Item in Sales Line table then the MatrixBox uses the last one :?:

Is there any way of skipping records from Item table?

abartonicek

Tried that too

I think that te problem is that refresh bug AND some of my stuff.
I just figured out that if I can't find the current Item in Sales Line table then the MatrixBox uses the last one :?:

Is there any way of skipping records from Item table?

kine

Yes, set filter on the form or on the rec... :-)

abartonicek

Ooops. didn't refresh before post. Sorry.

abartonicek

Well I would like to say it works but it doesn't ](*,)

I have some problems with setting that FlowFilter.

In Item table I added "Qty on Sales Order Lines" defined as:

Sum("Sales Line".Quantity WHERE (Document Type=CONST(Order),Type=CONST(Item),No.=FIELD(No.),Shipment Date=FIELD(QtyOnSalesOrderLinesFilter)))

and FlowFilter QtyOnSalesOrderLinesFilter because Date Filter is allready used for other things.

I can send you .txt version of my form if you have time to look it up.

kine

1) Did you tried to look into item table, set your filter and look at the value? are the values correct?

2) If yes, than the problem will be in the form
3) If no, the problem is in the definition

abartonicek

Done the way You said

I filtered (SourceTableView) the Item table records where that calculated amount was = 0. After calculating that amount for some period in MatrixBox the FlowFilter was set and because of that next Item record failed the SourceTableView filter.

After realizing that, it all went smooth

THX for your time, help AND patience