Navision filter limitations

mabl4367

Hi all!

Is there anyone who has created som some functionality to get around the limitations of filtering in navision?

1. There is a limit to how log a filter can be (250 characters). This can usualy be solved by applying one part of the filter at a time and then storing the records that pass each part in a temporary table.

2. All filters set on fileds accumelate so it's only possible to construct filters on the form (Field 1 satisfies condition A) AND (Field 2 satesfies condition . It is not possible to create a filter on the form (Field 1 satisfies condition A) OR (Field 2 satesfies condition . This problem can also be solved with temporary tables.

3. It's not possible to filter by the result of comparing fileds from the same record. Like this: ("Sales Header"."Document Date"<"Sales Header"."Posting Date"). Again you have to use temporary tables and code to get around this.

What I'd like to know is:

Has anyone created some function that take a recordRef to a tepmporary table and a string as parameters and fill the temporary table with the records that pass the filter in the string argument. The function must be able to parse the string argument according to some well defined syntax for creating the kind of filters I describe above.

If there was such a function it would even be possible to let the users take advantage of it by creating a text box controll on any form and send whatever filter the user enters into that textbox as the string parameter to this new function of my dreams wich would then filter the sourcetable of the form.

There might also be other more elegant ways to solve these problems.

I would really like to hear about any of them.

kine

Question is, when you need so long filter? Isn't it just because misuse of something?

veerendra

I think temporary recordref is not required.
1) Take a normal recordref with reference to a table.
2) Apply the filter to the recordref.
3) Use SETTABLE and copy the filters to the record variable (to show on the form)
If you want to compare the field in the same record, duplicate the recordref to another recordref.

I did some kind of work to filter the data using recordref and found limitations like field value or filter should not have the special characters like @,&...

mabl4367

Problems with long filters usualy occur when you construct a filter automatically in code. One example is if you have a lot of items in the item table and hou want to filter it on som complex conditions involving data that isn't even in the same table. The resulting filter is usually something like:

"No."=120|122|124|127|135

The way around this is to put these items in a temporary. If a specific item is in the temporary table it means it would have passed the filter.

I just want to do the neded coding for this type of problem once and then reuse it.

I would like a funktion to handle these kinds of problems. Maby it's bossible to execute sql-code and put the response in a temp table.

[TweaK]

And what exactly is wrong with Marks??
If your filter is too long, split it up..
Filter the first range, mark them.
Filter second range, mark them.
etc ..
=> View All Marked

jreynolds

You can modify the OnFindRecord and OnNextRecord triggers on the form/page to handle any special filtering requirements.

fverkel

Indeed, you can use OnFindRecord and OnNextRecord for most of these problems.
I've made a solution for this, over a decade ago. Still use it.
No temp tables, no marks, all realtime.

A problem with temp files and marks can be in multi user environments, where records are being changed all the time.

With this approach you can use C/AL-code for your filtering.
So a condition like "Document date" < "Posting date" is no problem.
The OR-condition is also not a problem
( (Field 1 satisfies condition A) OR (Field 2 satisfies condition ).

One thing to keep in mind, is performance.
The Navision client retrieves the records from the server, and then decides to show them or not.

jlandeen

I think performance related issues are the biggest issue to be taken into account when you are trying to perform this type of advanced or complex filtering on a given records set either for a form, report or other purpose.

One additional option that I have considered and used in the past (other than some of those already suggested here) is SQL Views and Linked Object tables. SQL views can be built to behave like tables in NAV but can encapsulate complex logic at the SQL level. This allows for more complicated filters as SQL joins can be used to enter advanced where clauses or even sub select statements.

As this logic executes SQL side it can reduce the amount of data that is being trucked back to the front end NAV client and typically will work much faster than marking all of the records as the NAV client still needs to retrieve all of the records, mark all the records and then use them.