As we all know, Microsoft stated that “Using Limit totals to is not supported. Use Filter on column instead” in Feature Limitations of the Microsoft Dynamics NAV Web Client.
We also know that accountant massively uses this functionality, among others. One of my customers said to me: I like the new web client, but I missed my flowfilters.
Such a waste! RIP CTRL+F7 & SHIFT+F7 :'(
If you also missed those two lovely buttons from Dynamics NAV 2009 (Classic Client) and older versions… Well, today we will resurrect them.
Today, I’ll show you how to combine those two powerful functions into one tiny powerful function (just like Goku and Vegeta’s fusion 😛 ; don’t mind if you don’t know Dragon Ball).
Enough bla bla bla. The new “Table Filters” looks like this in the Web Client. It also works on Windows Client:
The trick consists of a simple action “TableFilters” you can add to every page on your database. You do not need to modify or adapt the code to fit other pages based on different tables.
Here is the complete code:
Do not worry, I will not let you write the code again and try to guess the variable types 😉
Here is the Page 16 as an example tablefilter. I also included the code for NAV 2013 as it needed some rework.
Of course, I’ll let you enhance my “Table Filter” 😉
Hey Microsoft, could you include this (or something like this) in all pages for new NAV versions?
This blog is available also on Microsoft Dynamics NAV Community
Hello,
Can you help me?
I can’t download ‘TableFilter.fob’ file.
Thank you in advance.
Hello,
I’ll check the the download file and let you know 🙂
Hello Xhevat,
I Updated the link. I also updated the fob because I noticed there was a missing comma.
Besides, you need to know that I don’t handle fields CaptionClass in the page filter. So, instead of “DEPARTMENT CODE” you will see “Global Dimension 1 Code”.
I hope this help !
Thank you a lot!
Is it possible to fill the “table filter” with the already setted filters, so you can modify the filter instead just setting new filters?
You’re welcome. Yes you can update existing filters too.
Since the last time I wrote this post, I released a new version. Here is the code:
Code:
**************************
RecRef.GETTABLE(Rec);
FOR i := 1 TO RecRef.KEYINDEX(1).FIELDCOUNT DO
View += STRSUBSTNO(‘%1,’, RecRef.KEYINDEX(1).FIELDINDEX(i).CAPTION);
TempString := ”;
Field.SETRANGE(TableNo, RecRef.NUMBER);
Field.FINDSET;
REPEAT
IF TempString.ToString ” THEN
TempString := TempString.ToString + STRSUBSTNO(‘,”%1″=”%2″‘, Field.FieldName, RecRef.FIELD(Field.”No.”).GETFILTER)
ELSE
TempString := STRSUBSTNO(‘”%1″: “%2″=”%3″‘, TABLENAME, Field.FieldName, RecRef.FIELD(Field.”No.”).GETFILTER);
UNTIL Field.NEXT = 0;
TableFilters.SetSourceTable(TempString, RecRef.NUMBER, TABLECAPTION);
RecRef.CLOSE;
TableFilters.RUNMODAL;
TempString := ”;
TempString := TableFilters.CreateTextTableFilterWithoutTableName(FALSE);
IF TempString.ToString ” THEN BEGIN
TempString := TempString.Replace(‘=’, ‘=FILTER(‘);
TempString := TempString.Replace(‘,’, ‘),’) + ‘)’;
View := STRSUBSTNO(‘SORTING(%1) WHERE(%2)’, COPYSTR(View, 1, STRLEN(View) – 1), TempString);
END ELSE
View := STRSUBSTNO(‘SORTING(%1)’, COPYSTR(View, 1, STRLEN(View) – 1));
Rec.SETVIEW(View);
**************************
Variables :
**************************
Name DataType Subtype Length
Field Record Field
TableFilters Page Table Filter
RecRef RecordRef
TempString DotNet System.String.’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′
View Text
i Integer
**************************
Maybe I should update this post or better create another one when I have some time!
Thanks again!
Second version looks better but had to make some small changes to make it work for me, may will also help others:
1. Only add fields to TempString if there is actually a filter set
2. in the building list table: “field1″:”filter1″ commas where missing (maybe also because of the wordpress escaper)
3. Field.”Field Caption” instead Field.Name so it works also in our language.
REPEAT
IF RecRef.FIELD(Field.”No.”).GETFILTER ” THEN BEGIN
IF TempString.ToString ” THEN
TempString := TempString.ToString + STRSUBSTNO(‘”%1″=”%2″,’, Field.”Field Caption”, RecRef.FIELD(Field.”No.”).GETFILTER)
ELSE
TempString := STRSUBSTNO(‘”%1″: “%2″=”%3″,’, TABLENAME, Field.”Field Caption”, RecRef.FIELD(Field.”No.”).GETFILTER);
END;
UNTIL Field.NEXT = 0;
I’m glad it helps.
Yes, WordPress do that kind of stuff. It removes/changes some characters 😉