Date Filter on Item Ledger Entries

MiSta
MiSta Member Posts: 19
edited 2008-11-05 in NAV/Navision Classic Client
Hello

i want the customers revenue for current year (until today) in my customer table.

i created a flowfield "revenue" (type sum with connection to ledger entries) which filters everything but the date.
i also have a field called "date filter" in my customer table, which is a flow filter field.
the flowfield filters according to the flowfilterfield. where do i have to tell my flowfilterfield, what and how it has to filter?

if the structure of my program is correct, i would be glad, if anyone could tell me, what i have to do.

thanks in advance

Answers

  • klavin
    klavin Member Posts: 117
    I'm not sure I completely understood the problem, but first
    If you look at another field like for example, Field 62: Sales (LCY)

    In the Calcformula you will see:

    Sum("Cust. Ledger Entry"."Profit (LCY)" WHERE (Customer No.=FIELD(No.),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Posting Date=FIELD(Date Filter),Currency Code=FIELD(Currency Filter)))

    You need to do the same assignment...

    To see the date filter and the change...
    If you are on the form, and have revenue shown and you want to adjust the Date Filter, press Shift+F7 to access the flowfilters, and change the date.

    If you are doing this in code, apply your filter to the Date Filter field and CALCFIELDS your revenue.
    -Lavin
    "Profanity is the one language all programmers know best."
  • Savatage
    Savatage Member Posts: 7,142
    How about customer card customer button->All Statistics->Sales

    Should pull up form 351

    Should give you sales n stuff for your choice of
    1 day
    1 week
    1 month
    1 quarter
    1 year
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • MiSta
    MiSta Member Posts: 19
    klavin wrote:
    If you are doing this in code, apply your filter to the Date Filter field and CALCFIELDS your revenue.

    Thanks so far,
    this is exactly what is missing.

    I would write something like:

    CustLedgEntries.Setrange("Posting Date", Calcdate(thisyear-1d+1d),today);
    calcfields(revenue);
    do i have to write it in onvalidate of the "date filter" of the customer table?

    Or am I completely wrong?



    klavin wrote:
    I'm not sure I completely understood the problem, but first
    If you look at another field like for example, Field 62: Sales (LCY)

    In the Calcformula you will see:

    Sum("Cust. Ledger Entry"."Profit (LCY)" WHERE (Customer No.=FIELD(No.),Global Dimension 1 Code=FIELD(Global Dimension 1 Filter),Global Dimension 2 Code=FIELD(Global Dimension 2 Filter),Posting Date=FIELD(Date Filter),Currency Code=FIELD(Currency Filter)))

    You need to do the same assignment...

    To see the date filter and the change...
    If you are on the form, and have revenue shown and you want to adjust the Date Filter, press Shift+F7 to access the flowfilters, and change the date.

    If you are doing this in code, apply your filter to the Date Filter field and CALCFIELDS your revenue.
  • klavin
    klavin Member Posts: 117
    CustLedgEntries.Setrange("Posting Date", Calcdate(thisyear-1d+1d),today);
    calcfields(revenue);
    do i have to write it in onvalidate of the "date filter" of the customer table?
    Or am I completely wrong?

    If you take a look at like an Aged Accounts Receivable report, you should see many examples of something like this:
      SETRANGE("Date Filter",0D,PeriodEndingDate[1]);
  CALCFIELDS("Net Change (LCY)");

    From the Online Help in Navision: 
    Example
This example shows how to use the CALCFIELDS function to find the balance on December 31, 2003 and the net change for a customer in 2003:

Customer.SETRANGE("Date Filter",010103D,123103D);
Customer.CALCFIELDS(Balance, NetChange);

The first line sets up a filter for the date filter field in the customer record. This field is a FlowFilter field which the system uses when it calculates some of the FlowFields in the customer record. The system then calculates the FlowFields in the second line.

    Since the calcformula in the FlowField is using the Customer Ledger's Posting Date linking directly to the Customer Records "Date Filter", you need to Filter on "Date Filter" before you run your Calcfields.

    The purpose of the flowfield is really so you aren't touching or declaring that other CustLedgeEntries, otherwise you could just repeat and loop through the records. Get what I mean? Hope it helps...

    --Lavin
    -Lavin
    "Profanity is the one language all programmers know best."
  • MiSta
    MiSta Member Posts: 19
    Hi

    got it. Thanks very much.

    MiSta

Categories