SUM Calculations with FILTER is NOT showing correctly

(0) Share

Report

Posted on by Community Member

Hi All,

I'm fairly new with NAV Coding. Can you please tell me what I'm doing wrong:

I want to calculate the SUM of quantities in "Item Ledger Entry" filtered by the Users "Shortcut Dimension 1 Code".

In the Item Table fields are:
- "Global Dimension 1 Filter"
       FieldClass = FlowFilter
       TableRelation = "Dimension Value".Code WHERE (Global Dimension No.=CONST(1))
- "InvQty" (for getting the sum of quantity)
        FieldClass = FlowField
        CalcFormula(Sum("Item Ledger Entry".Quantity WHERE (Item No.=FIELD(No.),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter)))

In the List Page I did this
- OnOpenPage
IF UserSetup.GET(USERID) THEN
VALIDATE("Global Dimension 1 Filter",UserSetup."Shortcut Dimension 1 Code");
MESSAGE('%1',"Global Dimension 1 Filter"); -- Just for checking if getting the correct data

I'm getting the total of all quantity and not filtered by "Shortcut Dimension 1 Code".

Please help

Thanks.

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (1)

Suggested answer

Saurav.Dhyani 14,380 Super User 2025 Season 2 on at

Like (0)

Report

If i am not wrong you have these two as custom fields -

> User setup - Shortcut Dimension 1 Code).

> Item - InvQty.

Try This -

IF UserSetup.GET(USERID) THEN

Item.SetFilter("Global Dimension 1 Filter",UserSetup."Shortcut Dimension 1 Code");

Item.Caclfields( InvQty);

Let us know how it goes.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Saurav,

I did what you suggest. I added Item table to C/AL Globals.

IF UserSetup.GET(USERID) THEN

Item.SETFILTER("Global Dimension 1 Filter",UserSetup."Shortcut Dimension 1 Code");

MESSAGE('%1',Item."Global Dimension 1 Filter");

Item.CALCFIELDS( InvQty);

On page loading the message is blank. It's not picking up the UserSetup."Shortcut Dimension 1 Code". I also changed it to SETRANGE but same result.

When I click the InvQty the "Item Ledger Entry" shows that it is only filtered by "Item No." no other filter.

Thanks.

Was this reply helpful? Yes No
Suggested answer

Ashwini Tripathi 4,624 on at

Like (0)

Report

On OpenPage trigger you need to write code similar to this:

usersetup.GET(USERID);

IF usersetup."GD1 Filter" <> '' THEN

SETFILTER("Global Dimension 1 Filter",usersetup."GD1 Filter");

thats all. Check the Field you defined in User Setup is having valid filter and the dimension sequence is correct like The Value you define is the valid value of Global Dimension 1 defined in your General Ledger Setup.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

Hi Saurav/Ashwini,

It now works. This is the code I used.

UserSetup.GET(USERID);

IF Item."Global Dimension 1 Filter" = '' THEN

SETFILTER("Global Dimension 1 Filter",UserSetup."Global Dimension 1 Code");

Thank you so much guys...

Best Regards,

Earl

Was this reply helpful? Yes No