FlowField as the SumIndexField

AndwianAndwian Member Posts: 627
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.
Regards,
Andwian

Answers

  • krikikriki Member, Moderator Posts: 9,086
    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))))
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • AndwianAndwian Member Posts: 627
    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
    Regards,
    Andwian
  • krikikriki Member, Moderator Posts: 9,086
    Maybe better use a function to calculate.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • AndwianAndwian Member Posts: 627
    Thank you, Kriki.
    Regards,
    Andwian
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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).
    David Singleton
  • AndwianAndwian Member Posts: 627
    Thank you, David. :D
    Regards,
    Andwian
Sign In or Register to comment.