Subtotals in Matrix Box - Is it possible?

Business Central/NAV Developers Forum
1

We have a form whose source table is Item table. We have a matrix box whose source table is Unit of Measure. There is a textbox on the right part of the form where Qty on Hand for each Unit of Measure is displayed. The source expression for this text box is: Calc_QOH_For_UOM(“No.”,CurrForm.ItemUOMMatrix.MatrixRec.Code,“Quantity on Hand”) where, the function Calc_QOH_For_UOM is defined as: IF ItemNo <> ‘’ THEN BEGIN IF ItemUnitofMeasure.GET(ItemNo,UOMCode) THEN UOM_QOH := BaseQOH / ItemUnitofMeasure.“Qty. per Unit of Measure” ELSE UOM_QOH := 0; END; Each row in the form displays one Item record from the Item table with base UOM and QOH(base UOM). In the textbox on the right in the same row we display the QOH for each UOM. The customer would like to see subtotals by Material type(this is a new field in the Item table. Each item will have a material type code) Is this possible. If so, how can it be done? I do not see a lot of material on matrix box in the Application Designer’s guide. Is there someother place where I can get more info on matrix box and how to use it? Thanks. Pari Somasundaram

2

Hi Pari, as far as i know there is not a standard way to do it, so here is a workaround. When the form is based on a temparary record you can add a line each time the Material type changes: Run the form from the commandline:

FORM.RUNMODAL(FORM::YourForm, TempararyRec);

In the OnOpenForm –trigger :


YourRec.FIND('-');
REPEAT

  IF (MaterialType <> YourRec.MaterialType) AND (MaterialType <> '') THEN BEGIN
    "No." := "No." + 'SUBTOTAL';
    INSERT;
  END;

  Rec := YourRec;
  INSERT;

UNTIL YourRec.NEXT = 0;

"No." := "No." + 'SUBTOTAL';
INSERT;

Maybe not pretty but effective. Regards, Reijer. Edited by - reijer on 2001 Oct 25 17:30:03

3

yes it’s posible. But it is not a posibility of matrix box. Matrix box like simple list form, but have a litle bit more compilicated logic. Take a care it have some bugs and strange behavior in trigers calling sequence. Everything OK if you don’t “dig to deeply”. :slight_smile: 1. way - like G/L: Add field to item table “Sum range” Add suming items to table. If calc func. add: IF “Sum range”<>’’ then …calculate items range QOH … ELSE …normal calc. 2. Make this form on temp table. Then inserting recs to temp table track Material status and add aditional “totaling” rec. 3. modify form logic using OnFindRecord/OnNextRecord. 1 method makes some scrap, but this solution is very easy, 2 solution can take a lot of time on open, if there are a lot of Items and Items ledger recs. 3 is most pretty, but it most complicated.

4

quote:

Originally posted by reijer: Hi Pari, as far as i know there is not a standard way to do it, so here is a workaround. When the form is based on a temparary record you can add a line each time the Material type changes: Run the form from the commandline:

FORM.RUNMODAL(FORM::YourForm, TempararyRec);

In the OnOpenForm –trigger :


YourRec.FIND('-');
REPEAT

  IF (MaterialType <> YourRec.MaterialType) AND (MaterialType <> '') THEN BEGIN
    "No." := "No." + 'SUBTOTAL';
    INSERT;
  END;

  Rec := YourRec;
  INSERT;

UNTIL YourRec.NEXT = 0;

"No." := "No." + 'SUBTOTAL';
INSERT;

Maybe not pretty but effect

I do not understand very well how we can use temporary table as source for a form. Is that what you are suggesting? When we create a form the Source Tale has to be a real table and not temporary table. When you use run the form modally from command line you cannot accept filters from the user. I also do not understand the meaning of MaterialType and YourRec.MaterialType. Can you pleae explain? Thanks. Pari Somasundaram

5

Sorry, have read it again and it is indeed a bit vague.

quote:

Originally posted by paris: I do not understand very well how we can use temporary table as source for a form. Is that what you are suggesting? When we create a form the Source Tale has to be a real table and not temporary table.

It is not possible to assign a temporary record to a form right away, but when you open the form by code with a temp record as parameter it can be done. (saves you trouble because you do not have to program the OnFindRecord and OnNextRecord triggers yourself.)

quote:

Originally posted by paris: When you use run the form modally from command line you cannot accept filters from the user.

When setting the filters to the temp record, before opening the form, you can copy them to YourRec in the OnOpenForm trigger: YourRec.copyfilters(Rec);

quote:

Originally posted by paris: I also do not understand the meaning of MaterialType and YourRec.MaterialType. Can you pleae explain?

downloadYour customer wants to see the subtotals by material type so you need to sort the table by Marterial Type (or whatever the name of the field is) and look where MarterialType changes to add a subtotal line. The best way to explain is a fob: Best Regards, Reijer Edited by - reijer on 2001 Oct 25 19:12:04

6

Have a look at Form 409 (G/L Balance By Departments) I think you will find it does exactly what you want. Just change Item for Account, and UOM for Department. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________