Totals in Matrix Forms

bull

Hi all ,

I created a matrix form following the instructions of the mibuso link and it worked very well! But i dont want only that .. I want to add in matrix form totals for items. The tbls i am using are: Item Variant and Location and in matrix columm i have the location code (for label) and the sum of item ledger entry qty (from Value Entry with record variable type ) for each Location Code.

example:

i create this:

item no.___Variant Code___Warehouse1___Warehouse2...
0001______black_________0_____________2__________
0001______red___________4____________3__________

i want this :

item no.___Variant Code___Warehouse1___Warehouse2......Totals
0001______black_________0_____________2____________2
0001______red___________4____________3_____________7
Totals:__________________4_____________5

The last row is not necessary.

help please [-o< ,
thanks in advance ,
Bull

Marije_Brummel

In that case you'll need to either make some reporting buffer table to use for the lines, use the integer table or experiment with FINDREC and NEXTREC triggers.

Have a look at the Item Statistics Buffer table. This is used to create a matrixform.

In other words: It is possible but complex.

David_Singleton

bull wrote:

Hi all ,

I created a matrix form following the instructions of the mibuso link and it worked very well! But i dont want only that .. I want to add in matrix form totals for items. The tbls i am using are: Item Variant and Location and in matrix columm i have the location code (for label) and the sum of item ledger entry qty (from Value Entry with record variable type ) for each Location Code.

example:

i create this:

item no.___Variant Code___Warehouse1___Warehouse2...
0001______black_________0_____________2__________
0001______red___________4____________3__________

i want this :

item no.___Variant Code___Warehouse1___Warehouse2......Totals
0001______black_________0_____________2____________2
0001______red___________4____________3_____________7
Totals:__________________4_____________5

The last row is not necessary.

help please [-o< ,
thanks in advance ,
Bull

Actually Mark, its quite easy 8).

The way I do it, is just to create a new SKU "ZZZZ" and location "ZZZZ" (or something similar. Then just add a flow field in the item and locaiton cards that displays the totals.

You can also add sub totals this way.

David_Cox

Why not start with the Total from the Item Iventory field?
Then nothing to complicated is required, just a little code and add a new field, Inventory ("Net Change" if you are using "Date Filter"), from a variable of the Item, as you are following an example, I will do a step by step:

C/AL Globals:
Create a variable Item2 ~ Record ~ Item

Code:
FORM: OnAfterGetRecord()
// Get the matching Record
Item2.GET("No.");
// Copy all filters like "date filter", Bin Filter"
Item2.COPYFILTERS(Rec);
// Clear the Location Filter
Item2.SETRANGE("Location Filter");
// Calculate the Totals
Item2.CALCFIELDS(Inventory,"Net Change");

The just add a new Text box from the toolbox at position 3, with properties:
SourceExpr=Item2.Inventory or Item2."Net Change"
DecimalPlaces = 0:5

The result should be with the Inventory from Item2!
____From Item Record_____ |______Matrix Record____________
No.____Variant___Inventory_|_____Warehouse1___Warehouse2
0001___black___________2 |______________0____________2
0001___red____________7 |________________4____________3

If they want totals at the bottom as well, copy and paste into excel, and add the totals, it could be done but it is a bit of work, and not that dynamic.

kamalb

i agree with David, thats the best way.
better not to go activate the SKU functionality and add fields and keys
just to use in this form.

David Cox wrote:

Why not start with the Total from the Item Invetory field?
Nothing to complicated required, just add the field, Inventory or "Net Change" if you are using "Date Filter"

____From Item Record_____ |______Matrix Record____________
No.____Variant___Inventory_|_____Warehouse1___Warehouse2
0001___black___________2 |______________0____________2
0001___red____________7 |________________4____________3

Waldo

If you look at form 408 (G/L Balance by Dimension), you see that it's solved in the same way.

bull

hi all,

thanks all of you for your help, it was very useful and eventually it works 8) !
For the totals in the bottom , are there any other ways to do it except the excel way(??) , because i want a dynamic way to preview the totals.

thanks in advance,
bull

Waldo

I would do it David's way ... .

Hanen_T

How can I get total matrix column?


Thanks for help!!!

Hanen_T

How can I get total matrix per column?


Thanks for help!!!