Table filters

jpjesus · 2006-01-03

Hi,
I am trying to apply a filter over table 21 (Cust. Ledger Entry). If targets the field "Customer Posting Group".
In this case we have several records with NAC, NAV_1, NAC_2 and others with UE, UE_1, N_UE and so on.
The rule is: all the records with the string 'NAC' in it belong to a customer group. All others, without the 'NAC' string belong to another group.

So what I am trying to do is create a report that 1st filters the Cust. Ledger Entry table with this:

txtFilterCustomerPostGroup := 'NAC*';

"Cust. Ledger Entry".SETFILTER("Customer Posting Group",'=%1',txtFilterCustomerPostGroup);

and this works fine

. At this point the report does what it has to do and I move on to the next stage. Now I need apply a new filter to get all the other records, those that don't have 'NAC', so my first reaction was:

txtFilterCustomerPostGroup := 'NAC*';

"Cust. Ledger Entry".SETFILTER("Customer Posting Group",'<>%1',txtFilterCustomerPostGroup);

This seemed to be obvious since it just just to apply the oposite logic.

Unfortunatly this second filter doesn't work

. The operator '<>' with the wirldcard (*) don't work well together and all the records appear.

Does anyone know how to apply filters this way? :?:
I am using a Navision database (not SQL) version 3.70.

Thanks

kine · 2006-01-03

Please, see:

http://www.mibuso.com/forum/viewtopic.php?t=4067

jpjesus · 2006-01-04

Thanks Kine, for your answer.

That was a great help. Nevertheless it doesn't solve my problem.
On the link
http://www.mibuso.com/forum/viewtopic.php?t=4067
you can find a solution if the string 'NAC' is on the begining. This way a filter similar to '..NABZZZZ|NACZZZZ..' would work. The problem is if the user decides to create a code such as 'a_NAC_b'. Then the filter would not work properly.

Bottom line, I don't think there is a way to apply a filter like 'DOES NOT EQUAL *NAC*'.

But to every problem there must be a solution.
So I changed the report and instead of applying the filter on top, now I am filtering the records on the "Cust. Ledger Entry - OnAfterGetRecord()" trigger. How?
Quite simply using something like this:

intPosition := STRPOS("Customer Posting Group", 'NAC');

IF ((intLoop = 0) AND
(intPosition = 0)) THEN
CurrReport.SKIP;

IF ((intLoop = 1) AND
(intPosition > 0)) THEN
CurrReport.SKIP;

For the first run (intLoop = 0) I want to get all records where the field "Customer Posting Group" contains the string 'NAC'. So, if the value of intPosition is equal to 0 it means that it is not, thus I SKIP the record and move on to the next.

On the second run (intLoop = 1) I want to get all records where the field "Customer Posting Group" DOES NOT contain the string 'NAC'. So, if the value of intPosition is greater than 0 it means that it is, and so I SKIP the record and move on to the next one.

Once again, thank you for your help.

i4tost · 2006-01-04

Can you imagine how it will work on big database? Uch :whistle:
I can recommend you to go to SQL database. Then your problem will be solved, because you will be able to use filter <>*NAC*

jpjesus · 2006-01-04

Yes, you are right. In a big database this may became a problem.

But even in SQL the '<>*NAC*' doesn't work. And you can easily test it, just go to table 21 and over the field "Customer Posting Group" apply a similar filter '<>*SOMETHING*' depending on the data you have on your database. You will see that it won't work.

There are other solutions, but in this case I need to stick to the report and the only change was a new key on table Cust. Ledger Entry in order to make if faster, according to all the filters I need in this report.

Savatage · 2006-01-04

jpjesus wrote:

The problem is if the user decides to create a code such as 'a_NAC_b'. Then the filter would not work properly.

Maybe it's easier to come up with somekind of code rule where NAC has to be the first characters. Instead of, "they can make up anything they want as long as it has NAC in it", rule? :-k

kine · 2006-01-05

Or you can add some new field, in OnValidate test the code if it include NAV substring, save some flag into the new field and filter this new field... :-)

Marije_Brummel · 2006-01-05

kine wrote:

Or you can add some new field, in OnValidate test the code if it include NAV substring, save some flag into the new field and filter this new field... :-)

Yes, this is probably the best thing. This also allows you to create some key for prerformance.

You can use the STRPOS statement for this.

kine · 2006-01-05

Or you can stop using code for some meaning, and start using some Option field for marking the cards "this is this type and this is another type". If you need some meaning which you can use in C/AL code for filtering etc., it is better to use new field which will clearly mark the records and not hide the meaning into some code...

For example:

Code Option field
NAV_1 Vendor
NAC_1 Customer
NAC_2 Customer

etc...

jpjesus · 2006-01-05

Guys,

Thanks for all your replies, but as I stated on my previous post I managed to solve the problem with the STRPOS.

However, I see this topic going on so many directions and losing focus on the primary target:

- How to use a NOT EQUAL TO '*AAA*' filter condition.

There are many ways to achieve the same result. Just not in a filter condition. My goal would be to find a way to do this without having to create new fields or classifications.
We can debate on the customer logic and how it is gathering the information. But that is not really the issue here or at least it was not the purpose of my post.

kine · 2006-01-05

Yes, but there is still time to thing about other ways, because in some time, you can be in situation, you will need more and more filter in this way, and you can think about it now, because you can save lot of time if you change thinks now... :-)

jpjesus · 2006-01-05

I wonder if we could put some pressure on Microsoft/Navision to change the setfilter property to allow something like:

table.setfilter(field,'=%1','*AAA*')

or

table.setfilter(field,'<>%1','*AAA*')

As you know, we can already use the '@' symbol in the filter to ignore case (either uppercase or lowercase are allowed).

Maybe with the use of another symbol these filters could act like

string CONTAIN 'AAA'

or

string DOES NOT CONTAIN 'AAA'

and maybe add some parameters like the one in DELCHR (Where):

NewString := DELCHR(String [, Where] [, Which])

= Character in String that matches a character in Which. (default)

< Leading character in String that matches a character in Which.

> Trailing character in String that matches a character in Which.

Its just a thought....

Table filters

Comments

Categories