Flowfield for a MAX value- Sorting

poppins
poppins Member Posts: 647
in NAV Three Tier
Hi everyone,

I created a custom flowfield with the following calcformula:
Max(TabXxx."Posting Date" WHERE (Customer No.=FIELD(Customer No. Filter)))
whrere "Customer No. Filter" is a flowfilter.
When "Customer No. Filter" consists of a single customer no., the flowfield returns a correct value for the max date.
But when there are several customers in the filter, the flowfield returns a wrong value.
It looks like it is sorting by customer and getting the max date for the last customer in the filter...
Does it really work this way?

Thanks in advance :)

Best Answers

Answers

  • rishi_rishabh123
    rishi_rishabh123 Member Posts: 9
    How you are passing your filters, I tried it by passing two Customer No's 10000|20309920 or 10000..20309920 and it returns correct date with the same CalcFormula.

    I populated this data on my Table

    Customer No. || Posting Date

    10000 || 01-01-2016
    20000 || 07-01-2017
    10000 || 03-01-2017
    20309920 || 04-01-2017
    After both filters it returns 07-01-2017

    PK of my table was Entry No.
  • poppins
    poppins Member Posts: 647
    rishi_rishabh123 wrote: »
    How you are passing your filters, I tried it by passing two Customer No's 10000|20309920 or 10000..20309920 and it returns correct date with the same CalcFormula.

    I populated this data on my Table

    Customer No. || Posting Date

    10000 || 01-01-2016
    20000 || 07-01-2017
    10000 || 03-01-2017
    20309920 || 04-01-2017
    After both filters it returns 07-01-2017

    PK of my table was Entry No.
    I was going to ask you the same thing :)
    I am displaying the flowfield on a page based on the table of the flowfield and I am passing the filters within the OnOpenPage and the OnAfterGetCurrentRecord triggers of that page...
    I wrote a custom code for this:
    SETFILTER("Customer No. Filter", GetCustomerNoFilter(FieldX));
    GetCustomerNoFilter is a function that calculates the filter...
    The filter value is correct, I tested it many times, but the flowfield returns a correct value only when there is just one customer number in the filter...
    How did you pass your filters to get a correct result?

  • rishi_rishabh123
    rishi_rishabh123 Member Posts: 9
    You can use any of following :

    //TEST
    SETFILTER("Customer No. Filter",'10000..20309920');
    CALCFIELDS("Custom Date Field");
    //TEST

    //TEST
    SETFILTER("Customer No. Filter",'10000|20309920');
    CALCFIELDS("Custom Date Field");
    //TEST

    Make sure that you are using calcfields or SETAUTOCALCFIELDS for your flow fields.

    let me know if you have any questions.
  • poppins
    poppins Member Posts: 647
    rishi_rishabh123 wrote: »
    You can use any of following :

    //TEST
    SETFILTER("Customer No. Filter",'10000..20309920');
    CALCFIELDS("Custom Date Field");
    //TEST

    //TEST
    SETFILTER("Customer No. Filter",'10000|20309920');
    CALCFIELDS("Custom Date Field");
    //TEST

    Make sure that you are using calcfields or SETAUTOCALCFIELDS for your flow fields.

    let me know if you have any questions.

    I figured out the source of the problem...
    I have a key in my table "Customer No.","Posting Date". It looks that the flowdfield is using this key for its sorting and that is why it gives sometimes a flase value...
    Is there a way to control the key the flowfield is using?

Categories