CALCSUMS in the same table

Dynamics 365 Business Central/NAV User Group Developers Forum
#1

Dear all,

I’m trying to display “Qty. on Purch. Order” and “Inventory” on “Make PR to PO” form.

“Qty. on Purch. Order” is quantity of item which we’ve placed purchase order but not received yet.

“Inventory” is quantity of item which we’ve already received and not withdrawn yet.

Before issuing Purchase orders, users would like to confirm on “Make PR to PO” form how much quantity of item they have.

So I added 2 flowfields “Qty. on Purch. Order” and “Inventory” in Purchase Line table [39].

I refered to the Item table[27].

In Item table,“Qty. on Purch. Order” - Properties CalcFormula’s value as below

Sum(“Purchase Line”.“Outstanding Qty. (Base)” WHERE (
Document Type=CONST(Order),
Type=CONST(Item),
No.=FIELD(No.),
Shortcut Dimension 1 Code=FIELD(Global Dimension 1 Filter),
Shortcut Dimension 2 Code=FIELD(Global Dimension 2 Filter),
Location Code=FIELD(Location Filter),
Drop Shipment=FIELD(Drop Shipment Filter),
Variant Code=FIELD(Variant Filter),
Expected Receipt Date=FIELD(Date Filter),
Receipt Type=FILTER(<>Receipt),
Outstanding Qty. (Base)=FILTER(<>0)))

I copied this and added key to Purchase Line table as below.

Document Type,Type,No.,Variant Code,Drop Shipment,Shortcut Dimension 1 Code,Shortcut Dimension 2 Code,Location Code,Expected Receipt Date,Receipt Type,Outstanding Qty. (Base)

SumIndexFields:Outstanding Qty. (Base)

But it didn’t work.

In item table,“Inventory”

Sum(“Item Ledger Entry”.Quantity WHERE (
Item No.=FIELD(No.),
Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),
Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),
Location Code=FIELD(Location Filter),
Drop Shipment=FIELD(Drop Shipment Filter),
Variant Code=FIELD(Variant Filter),
Lot No.=FIELD(Lot No. Filter),
Serial No.=FIELD(Serial No. Filter)))

I copied this and did not add any key to Item Ledger Entry table.
It worked fine.

Form “Make PR to PO” which source table is Purchase Line. So I’m afraid I can not sum SumIndexFields in the same table.

But I’m not sure.

Are there any way to display “Qty. on Purch. Order” on the form which source table is also Purchase Line?

#2

Hi Masayuki,

It is possible to create a flowfield on a table, that sums up records in the same table…
So that is not the issue here…

I think You have to look at the calcfurmula You have defined…
e.g. I don’t have a field called “Location Filter” on Purchase Line table.

#3

Hi sande 7,

Thank you for your reply.

I’ve done everything I can think of. But I could not sum up flowfield.

So I deleted 2 flowfields from PurchaseLine table.

On trying to use “calcsums”, I could sum up SumIndexFields.

But I could not display sum total on proper line.

Yesterday,I wrote the code below in “Form - OnAfterGetRecord()”,

I finally could sum up SumIndexFields both in the same table and in the other table on proper line.

//Sum up Inventory

ItemLedgerEntry.SETCURRENTKEY(“Item No.”,“Variant Code”,

“Drop Shipment”,“Location Code”,“Posting Date”);

ItemLedgerEntry.SETRANGE(“Item No.”,“No.”);

ItemLedgerEntry.SETRANGE(“Location Code”,“Location Code”);

ItemLedgerEntry.SETRANGE(“Drop Shipment”,“Drop Shipment”);

ItemLedgerEntry.SETRANGE(“Variant Code”,“Variant Code”);

ItemLedgerEntry.CALCSUMS(Quantity);

//Sum up Qty. on Purch. Order

PurchaseLine.SETCURRENTKEY(“Document Type”,“Variant Code”,“Drop Shipment”,

“Location Code”,“No.”,Type,“Outstanding Qty. (Base)”,“Expected Receipt Date”,“Receipt Type”);

PurchaseLine.SETRANGE(“Document Type”,“Document Type”::Order);

PurchaseLine.SETRANGE(Type,Type::Item);

PurchaseLine.SETRANGE(“No.”,“No.”);

PurchaseLine.SETRANGE(“Variant Code”,“Variant Code”);

PurchaseLine.SETFILTER(“Receipt Type”,’<>Receipt’);

PurchaseLine.SETFILTER(“Outstanding Qty. (Base)”,’<>0’);

PurchaseLine.CALCSUMS(“Outstanding Qty. (Base)”);

I think I can calculate sum using the function “ItemCostMgt” [5804].

I referred code unit, but I could not understand this function.

Thanks again.

P.S.

We use “Location Filter” to identify the vender.

If the vender is in Thailand, we set “Domestic” to “Location Filter”

For the rest, we set “OVERSEA”.

#4

Hi Masayuki,

Sorry I missed that it was a flowfield You wanted to create a new flowfield on.

You have to create the new flowfields, so that they sum the records from the table UNDERLAYING the existing flowfield.
That can very well be impossible because of the complexity of the filter You may need to calculate correct.

e.g.
If I want to create a flowfield in G/L Account (table 15), that sums up the balance for all accounts.
I have to make this new flowfield sum on G/L Entry (table 17), and make sure the CalcFormula is created in a way that supports my need.
In table 15 You actually have a similar situation on the totalling-accounts.
The solution here have been to introduce the field “Totalling”.
If You check the CalcFormula for “Balance”, You’ll see that either field “G/L Account No.” in table 17 must equal “No.” in table 15, or field “G/L Account No.” in table 17 must be within the filter defined in Field “Totalling” in table 15 (mark in field “ValueIsFilter” in CalcFormula-window).