MatrixBox + PeriodType help!!!

abartonicek
abartonicek Member Posts: 162
edited 2005-12-13 in NAV/Navision Classic Client
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 :)
Better to be critical then self-critical :)

Answers

  • kine
    kine Member Posts: 12,562
    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.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • abartonicek
    abartonicek Member Posts: 162
    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.
    Better to be critical then self-critical :)
  • kine
    kine Member Posts: 12,562
    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...)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • abartonicek
    abartonicek Member Posts: 162
    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?
    Better to be critical then self-critical :)
  • kine
    kine Member Posts: 12,562
    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)?
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • abartonicek
    abartonicek Member Posts: 162
    Sent :)
    Better to be critical then self-critical :)
  • kriki
    kriki Member, Moderator Posts: 9,132
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • kine
    kine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • abartonicek
    abartonicek Member Posts: 162
    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?
    Better to be critical then self-critical :)
  • abartonicek
    abartonicek Member Posts: 162
    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?
    Better to be critical then self-critical :)
  • kine
    kine Member Posts: 12,562
    Yes, set filter on the form or on the rec... :-)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • abartonicek
    abartonicek Member Posts: 162
    Ooops. didn't refresh before post. Sorry.
    Better to be critical then self-critical :)
  • abartonicek
    abartonicek Member Posts: 162
    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.
    Better to be critical then self-critical :)
  • kine
    kine Member Posts: 12,562
    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
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • abartonicek
    abartonicek Member Posts: 162
    :D Done the way You said :D

    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 :D

    THX for your time, help AND patience :D
    Better to be critical then self-critical :)

Categories