Jun 10

Over the years of working with NAV, users have always asked for ways to store their frequently used filters for reports and forms – eg. month end reports (A/R, A/P, Trial Balances), that often needs to be reported in multiple views.
Most often the users have come up with a system of storing the filterstrings in Notes in eg. Microsoft Outlook or similar. This is not a productive way, and leaves a lot of options for errors when copying the wrong filter, especially when filtering on multiple fields.

Other ERP products, such as Great Pains has this option as standard, called “Report Options”. These can be setup and saved by the user, and then accessed through “My Reports”.

In NAV we will implement the following functionality:

  • allow users to store and retrieve table filters
  • allow setup per userid
  • Allow editing of the filters the users created in a regular filter input in NAV
  • having minimal impact on existing objects

To design a similar feature in NAV, we need these new objects:

  • 1 table object
  • 1 form object

And also need to do changes to existing objects, where we want to make the functionality accessible. In this example we set up a single shortcut key on a existing menuitem with the shortcut ALT+F7, but you could also add buttons to the form, for loading and saving the filters, if this makes it easier for users to understand.

First identify your next available table, that is accessible with your license (Available objects in my license). Create these fields in the new table:


Most of the field should be pretty self explanatory for most developers; and you will see comments in the “Description” field explaining the properties set for the field.

Remember to redefine the primary key on the table, as we want to allow each user to store multiple filters per table:


If you have the granule 7200 Application Builder on your license, you can also add this validation code for the Insert and Modify triggers:

OnInsert()
"Created On" := TODAY;
"Created By" := USERID;
OnModify()
"Modified On" := TODAY;

Next step create our form to get and set the filters. For simplicity this could be created as 2 separate forms, but for now lets take a look at how to handle this in one form.

Create a new form, based on the newly created table. Use the Form Wizard to built a “Tabular – Form Type” form. Add a Frame under the TableBox, to show the concatenated string for the tables view, something like this:


Add to functions on the form, SetView() and GetView():

SetView(Filter : Text[1000];TableID : Integer)
GlobalFilter := Filter;
IF STRPOS(Filter,'WHERE') = 0 THEN
  EXIT;
SETFILTER("User ID",USERID);
SETRANGE("Table ID",TableID);
SETRANGE("Filter Text 1",COPYSTR(Filter,1,250));
SETRANGE("Filter Text 2",COPYSTR(Filter,251,250));
SETRANGE("Filter Text 3",COPYSTR(Filter,501,250));
SETRANGE("Filter Text 4",COPYSTR(Filter,751,250));
IF FINDFIRST THEN BEGIN
  MARK(TRUE);
  SETRANGE("Filter Text 1");
  SETRANGE("Filter Text 2");
  SETRANGE("Filter Text 3");
  SETRANGE("Filter Text 4");
END ELSE
  SETFILTER("Filter Code",'<>%1','');
GetView() : Text[1000]
EXIT("Filter Text 1" + "Filter Text 2" + "Filter Text 3" + "Filter Text 4");

And also this code

Form - OnOpenForm()
MARKEDONLY(TRUE);
IF FINDFIRST THEN;
MARKEDONLY(FALSE);

Form - OnNewRecord(BelowxRec : Boolean)
"Filter Text 1" := COPYSTR(GlobalFilter,1,250);
"Filter Text 2" := COPYSTR(GlobalFilter,251,250);
"Filter Text 3" := COPYSTR(GlobalFilter,501,250);
"Filter Text 4" := COPYSTR(GlobalFilter,751,250);

Lets put the objects to an test by implementing this on Form 21 Customer Card. Add another menu item to the Customer MenuButton, call it “Filter Selections” and assign it the shortcut key “Alt+F7”:


The code should be this:

FilterSelection.SetView(Rec.GETVIEW,DATABASE::Customer);
FilterSelection.RUNMODAL;
SETVIEW(FilterSelection.GetView);

Where FilterSelection is local variable to the previous form we created.

Lets testdrive it now, see this screencast recording: (notice you can also use the saved filters from your reports by selecting the arrow button on the request form, and have the functionality on your list form):

[dm]4[/dm]





5 Responses to “Saving filters in Dynamics NAV – Filter Selections: Practical usage of Rec.GETVIEW and Rec.SETVIEW.”

  1. Veerendra CH says:

    HI Søren Nielsen, My comment is not related to the post but If possible please enable RSS or Atom to you site, so that outsiders can be notified automatically.

    • SNielsen says:

      The RSS feeds already exist, just look at the right side of the address bar in your browser – there should be a RSS feed link. I have now added direct links on the pages too. Have fun!

  2. Tennek says:

    Hi,

    When I click on the download file the website transfers me back to “home”.
    Could you make the file downloadable ?

  3. SCoupet says:

    Thanks a lot for this post.
    We have Dynamics Nav 2009 and even if there is some filters saving capabilities on RTC, it’s not possible to save filters on worksheet pages.
    So i’ve used your idea and replaced the Form with a Page to select the filter and it does the job perfectly !
    Thanks again (from France) !

  4. ERO says:

    Hahaha, I had a good laugh about “Great Pains” … it might be a competitor of Horracle ERP 🙂

Leave a Reply

preload preload preload
pornpants.com pornofri.com kilporn.com