Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV forum
Answered

Filters in OnInit of a list page

Posted on by 2

I created a new list page because I need a special filter (days difference between two fields).

I'm not sure how to do that filter, are there any examples? I haven't found any on the internet...

Say I want all records where Due Date is 7 days or more before Start Date in my Assemble Header (900) list.

  • Stefano Demiliani Profile Picture
    Stefano Demiliani 37,152 on at
    RE: Filters in OnInit of a list page

    Yes I was writing that ;-)

  • Verified answer
    SamCha Profile Picture
    SamCha 2 on at
    RE: Filters in OnInit of a list page

    I got it, the filter for 0D needs to be this.

    SETFILTER("Start Date",'<>%1', 0D);

    Thanks for your help Stefano.

  • SamCha Profile Picture
    SamCha 2 on at
    RE: Filters in OnInit of a list page

    Yes

    OnOpenPage()
    
    SETFILTER("Start Date",'<>0D');
    
    IF FINDSET THEN
    REPEAT
    BEGIN
      IF "Due Date" >= CALCDATE('-7D', "Start Date") THEN
        MARK(TRUE)
      ELSE
        MARK(FALSE);
    END
    UNTIL NEXT=0;
    
    //Now show only the marked records.
    MARKEDONLY(TRUE);
  • Stefano Demiliani Profile Picture
    Stefano Demiliani 37,152 on at
    RE: Filters in OnInit of a list page

    Have you placed this code in OnOpenPage trigger?

  • SamCha Profile Picture
    SamCha 2 on at
    RE: Filters in OnInit of a list page

    When debugging, it looks like it works for the first record it finds, but the second iteration, the Start date is empty.... and the calcdate errors.

    In the variable list, it looks like the Start date filter is <> today... instead of <> empty date (0D?)

  • Verified answer
    Stefano Demiliani Profile Picture
    Stefano Demiliani 37,152 on at
    RE: Filters in OnInit of a list page

    Maybe I've understood now your requirements, sorry :)

    Every record has its own Due Date and Start Date fields, and you want only the record where Due Date >= Start Date + 7 days right?

    If so, a simple filter is not enough because the calculation (if due or not) must be done for every record. You can use MARKS.

    Something like:

    SETFILTER("Start Date",'<>0D'); //Remove the empty start date records

    IF FINDSET THEN

    REPEAT

    BEGIN

     IF "Due Date" >= CALCDATE('+7D',"Start Date") THEN

       MARK(TRUE)

     ELSE

       MARK(FALSE);

    END

    UNTIL NEXT=0;

    //Now show only the marked records.

    MARKEDONLY(TRUE);

  • SamCha Profile Picture
    SamCha 2 on at
    RE: Filters in OnInit of a list page

    How do I "apply" the filter?

    I tried to have both lines at the same time in the OnInit but it still gives an error of comparing dates with 0D.

    OnInit()
    
    SETFILTER("Start Date",'<>0D');
    SETFILTER("Due Date",'>=%1',CALCDATE('+7D',"Start Date"));
    


  • Stefano Demiliani Profile Picture
    Stefano Demiliani 37,152 on at
    RE: Filters in OnInit of a list page

    First apply the filter for Start Date:

    SETFILTER("Start Date",'<>0D');

    Then:

    SETFILTER("Due Date",'>=%1',CALCDATE('+7D',"Start Date"));

  • SamCha Profile Picture
    SamCha 2 on at
    RE: Filters in OnInit of a list page

    Thank you Stefano, It's the basic NAV table, so it's "Due Date".

    Now my problem is that sometimes the start date is empty, so I can't do the difference calculation (says invalid date 0D).

    How do I exclude those as well?

  • Stefano Demiliani Profile Picture
    Stefano Demiliani 37,152 on at
    RE: Filters in OnInit of a list page

    What is the name of your date field that you want to filter? Is it not called DueDate?

    The suggested filter filters the field called "DueDate" for values >= of Start Date + 7 days.

    Correct the name of the fields with your correct field name and it should work.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans