FlowField as the SumIndexField

Andwian · 2010-12-30

Dear Experts,

I create a new FlowField "Balance at Date" in Customer table :

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:

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

As I understand, this is because there is no SumIndexField for Remaining Amount.

Hence, I created the SumIndexField, but there is another error:

Microsoft Dynamics NAV
A FlowField cannot be a SumIndexField.

Field: Remaining Amount
Table: Cust. Ledger Entry
Key fields: Entry No.
OK

This is because, the FlowField could not be the SumIndexField.

Hence, the "Balance at Date" must be changed to:

Sum("Cust. Ledger Entry"."AMOUNT" WHERE (Customer No.=FIELD(No.),Due Date=FIELD(UPPERLIMIT(Date Filter))))

Is there any other way?

Thank you for your helps.

kriki · 2010-12-30

1) you can't create a SIFT on a flowfield.
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))))

Andwian · 2010-12-31

kriki wrote:

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))))

I want to check the Overdue Balance of the customer, as of the WORKDATE. If we use this Calculation Formula, the Late Payment (i.e. the posting date of the payment is later than the Invoice's due date), then the payment is not recognized, and the invoice is recognized as open still.

Refer to this:
Show Open Entries Only in Report Customer Detailed Aging

kriki · 2011-01-11

Maybe better use a function to calculate.

Andwian · 2011-01-12

Thank you, Kriki.

David_Singleton · 2011-01-12

kriki wrote:

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.

:thumbsup: This is the way I would do it. (I was going to make a similar post).

Andwian · 2011-01-13

Thank you, David.

FlowField as the SumIndexField

Answers

Categories