Sum("Cust. Ledger Entry"."REMAINING AMOUNT" WHERE (Customer No.=FIELD(No.),Due Date=FIELD(UPPERLIMIT(Date Filter))))But when I run the Customer table, the error arouse:
As I understand, this is because there is no SumIndexField for Remaining Amount.
Microsoft Dynamics NAV
The FlowField value cannot be calculated.
You must define and activate a key that contains the key fields that appear below (the order is unimportant) and then associate the specified SumIndexField with it.
Key Fields: Customer No.
SumIndexField: Remaining Amount
Table: Cust. Ledger Entry
OK
This is because, the FlowField could not be the SumIndexField.
Microsoft Dynamics NAV
A FlowField cannot be a SumIndexField.
Field: Remaining Amount
Table: Cust. Ledger Entry
Key fields: Entry No.
OK
Sum("Cust. Ledger Entry"."AMOUNT" WHERE (Customer No.=FIELD(No.),Due Date=FIELD(UPPERLIMIT(Date Filter))))
Answers
2) your new field "Balance At Date" in the customer table should point to the "Detailed Cust. Ledg. Entry"-table.
probably you need something like:
Sum("Detailed Cust. Ledg. Entry".Amount WHERE (Customer No.=FIELD(No.),Due Date=FIELD(UPPERLIMIT(Date Filter))))
The problem is that you don't have "Due Date" in the "Detailed Cust. Ledg. Entry"-table.
To fix this, you can create the field, maintain it and put the correct indexes on the table. I would try to avoid this approach because the "maintain it" will be the pain-point.
Another way is to create a function in the customer table with the logic to calculate the value you want.
Something like :
CalculateBalanceAtDate(IcodCustomerNo,IdatDueDate) AS Decimal
And in this function you read the customer ledger entries and calculate the "Remaining Amount" for each customer ledger entry.
BTW: Do you really need to filter on "Due Date"? If Posting Date is ok, your new flowfield can have this formula:
Sum("Detailed Cust. Ledg. Entry".Amount WHERE (Customer No.=FIELD(No.),Posting Date=FIELD(UPPERLIMIT(Date Filter))))
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
Refer to this:
Show Open Entries Only in Report Customer Detailed Aging
Andwian
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
Andwian
:thumbsup: This is the way I would do it. (I was going to make a similar post).
Andwian