Totals in Matrix Forms

bull · 2006-11-10

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 · 2006-11-10

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 · 2006-11-11

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 · 2006-11-11

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 · 2006-11-11

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 · 2006-11-11

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

bull · 2006-11-14

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 · 2006-11-14

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

Hanen_T · 2011-11-21

How can I get total matrix column?

Thanks for help!!!

Hanen_T · 2011-11-21

How can I get total matrix per column?

Thanks for help!!!

Totals in Matrix Forms

Comments

Categories