Setfilter different from a range

redhotmustang

Hi,

My form already lists all the students.
I need to display all the students except those on a range already defined earlier.

I wanted to set a filter different from a range, like this <> 34..56

setfilter("Nº",'<>', '%1..%2','206001','206002');

I tried many things, but none works.

setfilter("Nº",'<> %1..%2','206001','206002');

(206001 and 206002 are Student Nos. I'll use a variable when this works.)

Thanks.

Framus

And what about
setfilter("No",'<%1|>%2',34,56);

kriki

SETFILTER("No.",'<%1|>%2','34','56');

kriki

Framus wrote:

And what about
setfilter("No",'<%1&>%2',34,56);

The '&' means each value must be at the same time smaller than 34 and bigger than 56. This means you won't get any records.

redhotmustang

kriki wrote:

SETFILTER("No.",'>%1|<%2','34','56');

Doesn't this means that "No." must be bigger than 34 OR smaller than 56?

I wanted to filter by difference, meaning that all the students should appear minus the ones in the 34 to 56 range.

SETFILTER("No.",'<> %1..%2','34','56');

Of course this does not work.

<>206001&<>206002

This almost solves it, but it has to be a range of records.
Maybe the only way it to create a cycle that just keeps adding "&<>incrementingVar" on a on, until the last record.

Tkx.

kriki

My code was:

SETFILTER("No.",'<%1|>%2','34','56');

It means smaller than 34 OR bigger than 56.

redhotmustang wrote:

...create a cycle that just keeps adding "&<>incrementingVar" on a on, until the last record.

What exactly do you want to do?
If you explain what you want to do, maybe we can give a better solution.

redhotmustang

I know "<" = smaller, ">" = bigger,

What I want to do is very simple.
I have a list of all the students in the school.
Usually people filter by a determined field to find the results they want.

Example: filter by "No." = 206001

setrange("No.",'206001');

Only the student whose number is 206001 will show.

filter the "No." by a range = 206001..206002

setrange("No.",'206001','206002');

Only the students whose numbers are 206001 and 206002 will show.

This is what we usually do.

But I need to filter the students list in order to NOT show those records.
It's like "reverse select".

The filter by "No." field <> 206001..206002 should show every student but NOT the students with the numbers 206001 to 206002.

SETFILTER("Nº",'<>%1 & <>%2',206001,206002')

This does the trick. But what if there were more students to NOT show.

The code would grow:

SETFILTER("Nº",'<>%1 & <>%2  & <>%3',206001,206002,206003')

and so on...

Eugene

you have been given the answer to your question but you did not understand it, so
you should take navision introduction course and learn how to filter data, specifically what symbols | & * ? are used for


SETRANGE(Fld, A, is the same as SETFILTER(Fld,'>=A&<=B') the opposite to the latter is SETFILTER(Fld, '<A|>B');

---

A******B

---

****A

---

B*****

kriki

Maybe it is better to use FILTERGROUPS. You can define filtergroups from 10 to 250 (or 255).
So, you could do this :

FOR int := 10 TO 12 DO BEGIN
  FILTERGROUP(int);
  SETFILTER("No.",'<>%1',GetNextStudentToNOTFilter());
END;
FILTERGROUP(0);

You can also put different types of filters in the loop.

redhotmustang

Eugene wrote:

you have been given the answer to your question but you did not understand it, so
you should take navision introduction course and learn how to filter data, specifically what symbols | & * ? are used for

I have taken that course :oops: a month ago or so... but I haven't had time to study again and to take the exam. (I had to study to some functional exames I have to take) That's why I don't remember all the stuff.

Eugene wrote:

SETRANGE(Fld, A, is the same as SETFILTER(Fld,'>=A&<=B') the opposite to the latter is SETFILTER(Fld, '<A|>B');

---

A******B

---

****A

---

B*****

| -> OR
* -> Forms a part of value

I got it. SETFILTER(Fld, '<A|>B') create a range with every record less than A OR every record greater than B = ****A

---

B*****.

Thanks the answer had already been given earlier kriki, it did not work for some reason, but now it does.

EDIT: But there is an issue, if we have student no. 1 and student no. 4 ->
******1---4*******, student no. 2 and student no. 3 will be gone. and I don't want that.

Eugene

student no. 2 and student no. 3 will be gone. and I don't want that.

then what exactly do you want ?

if your initial condition is '=1|=4' then the opposite is '<>1&<>4

in general
NOT(A&B&..&Z)=NOT A | NOT B | ... | NOT Z
NOT(A|B|..|Z)=NOT A & NOT B & ... & NOT Z

redhotmustang

Hi, what I want exactly is described here near the end of the topic's first page.

As I said in this topic's title, I want to set a filter that returns me a list of students different from a set range.

I mean the filter can not just be <>1&<>2, because there can more records (that will be variable).

It would have to be like **----**----***---*** and not just ******1---4*******.

Eugene

so you have a set of ranges: A..B for i=1 to N

the questions are:
1)is there a maximum number of ranges that can be set ? (what is the maximum value of N?)
2) can your ranges intersect ?
for example
--A[1]---A[2]---B[1]---B[2]---
so that you have
--A[1]*********B[1]---

---

A[2]*********B[2]---
with intesection range A[2]---B[1]
in such a case is intersection range included or excluded ?
what about inclusion or exclusion of the ranges A[1]--A[2] and B[1]---B[2] ?

redhotmustang

There is only one range. As there is only one, no intersection will be necessary.

To make it simple to explain:

I have a list of students on a form (sourcetable - all the students in the school). This form is used to register students to a chosen class, one can select the class on the form's tab control.

That textbox should work has a filter (but it is not yet).

As we are choosing new students to be added to the chosen class, it is not logical that the ones already registered on the class appear on the list. (Why would we see them, if we don't need to register them?)

The students table does not have a class code field, because one students may belong to one or more classes. The only table that "knows" which students belong to each class is Students registration. I had to select from that table the students that belong to the class chosen on class textbox.

That is, student 1, student 3, student 54, 95, 1000 may belong to the class. And so they should not appear on the list. Thus, every student in the school but not these should appear.

The manual filter on the students table would be <>1&<>3&<>54&<>95&<>1000 if we knew which students belong to the class. But they can change, as more or less students are added to the class. So this filter will be based on a variable.

I'm trying to code this like this (pseudo-code - don't mind the code, just the idea) :

recRegis.RESET;
recRegis.SETRANGE("Class Code",VarClass);
IF recRegis.FIND('-') THEN
   firstStudentOnTheRange := recRegis."Student No." ;

IF recRegis.FIND('+') THEN
    lastStudentOnTheRange := recRegis."Student No.";

FOR i = firstStudentOnTheRange TO lastStudentOnTheRange
   if rec."Student No." = recRegis."Student No." then
      rec.next;

I'm trying to cycle to all records on the students' table, and there is a match between the student no from this table and the student no. from the students registration for that class, the record wont appear.

valkatamake

you can make it this way if you have to make exeptions make in reqform 3 texboxes with sourceexpr: var1,var2,var3 and write this

IF record."field"='var1' THEN CurrReport.SKIP;
IF record."field"='var2' THEN CurrReport.SKIP;
IF record."field"='var3' THEN CurrReport.SKIP;

if you have more variables you can write more lines this would i do if I have to do it with code

the other way i think is when you run your report window opens where you can input filters. you choose the field you want (No.) and then in filter just type needed records for example 206001,206005,206010

if don't want always to choose field everytime . DataItem->properties-> ReqFilterFields -> (in you case No.) . and then everytime you run the report field No. will be choosen and you just have to type the numbers you don't want to see ....

Albertvh

Hi

Maybe you could do something like this in the OnAfterValidate of Class

recRegis.RESET; 
recRegis.SETRANGE("Class Code",VarClass); 
IF recRegis.FINDSET THEN 
  REPEAT
     StudentFilter := StudentFilter + '&<>' + RecRegis."no."; 
  UNTIL RecRegis.NEXT = 0;
StudentFilter := COPYSTR(StudentFilter,2);
FILTERGROUP := 10
SETFILTER("No.",StudentFilter);
FILTERGROUP := 0;

I think that is what you are trying to do.

Hope this helps

Albert

Eugene

Here is an example of how you could solve your problem - i have created two tables:

Regis with fields:
"Student Code" Code 20
"Class Code" Code 20

and Student with fields:
"Code" Normal Code 20
"NotInClass" FlowField Boolean
"Class Filter" FlowFilter Code 20

for the field NotInClass enter the following CalcFormula:

-Exist(Regis WHERE (Student Code=FIELD(Code),Class Code=FIELD(Class Filter)))

then when you Student.SETFILTER("Class Filter", 'C1'); the values of the field Student.NotInClass will be set to true or false depending on if the registration for class 'C1' exists in Regis for the given students

on a table u can set "Class Filter" value by pressing Shift-F7
User can even have a TextBox on the form with SourceExp = "Class Filter" to be able to enter the value directly

And as long as you viewing the table data via form you can SETRANGE(NotInClass, TRUE) to filter the records of those students who are not in the selected class

redhotmustang

Thank you all!!=D>

Meanwhile, my boss told me to quit trying to filter different from a set range, and told a different way to try doing it.

All the students should appear on the list. Even those already registered in the class. But if one them was already register when we pressed the "REGISTER TO CLASS" button, the program should check if any of those who were selected to be registered was already registered. If so, an error should appear.

I think I found one way to do it.

recRegis.RESET;
recRegis.SETRANGE("Class Code",VarClass);
IF recRegis.find('-') THEN begin
   REPEAT
   rec.reset;
   Rec.setrange(Selected,TRUE);
   rec.setrange("Student No.",recRegis."Student No.");
   if rec.find('-') then
      ERROR('Student No. %1 was already registered.',"Student No.");
   UNTIL RecRegis.NEXT = 0;
   end;

Explaining: i'm filtering the "Students Registrations" (recRegis) table by Class Code (VarClass). If any record is found, I'll cycle through everyone of them and set a filter on the Students (rec) form, that is also select to be registered, for each recRegis record. If one of them matches, an error occurs and stops the program.

My solution was based on Albertvh's help.
But I thank: Eugene, valkatamake, kriki also.
I'll try to implement every solution presented by you all.

Eugene

just one post above i've given you a possible solution to your original problem with creation of two fields in the student table:
"NotInClass" FlowField Boolean
"Class Filter" FlowFilter Code 20

But if your boss has changed his mind then ok
Still i would do a bit different than you trying it to do

you should better run through your students and look if a Regis exist for that student for the gicen class:

Regis.SETRANGE("Student Code", Student.Code);
Regis.SETRANGE("Class Code", SelectedClass);
IF NOT Regis.ISEMPTY THEN SkipThatStudentOrGiveErrorMsg

redhotmustang

Tkx Eugene. I'll try your solution, even though my boss told me to quit, I still want to try it (for learning purposes). But he's not keen on changing or adding new fields to those tables... :S

Now, I'm trying to use Albertvh's idea to use in the filter, codind this

recRegis.RESET;
recRegis.SETRANGE("Class Code",VarClass);
IF recRegis.FINDSET THEN begin
  if recRegis.find('-') then begin
     StudentFilter := recRegis."Student No.";
     recRegis.next;
  end;
  REPEAT
     StudentFilter := StudentFilter + '&<>' + recRegis."Student No.";
     message('%1',StudentFilter);
  UNTIL RecRegis.NEXT = 0;
end else
   message('bye');
SETFILTER("Nº",StudentFilter);

on the class text button, but I don't know which trigger works better for the form to update correctly.

I'll try your new way latter. Why do you think I should turn things around? Let me answer, because it will be faster to filter the regis table than the students' table, won't it? I think so too.

Albertvh

Hi

I think it would best be placed in the OnAfterValidate trigger also I would change

  if recRegis.find('-') then begin 
     StudentFilter := recRegis."Student No."; 
     recRegis.next; 
  end; 

to

  if recRegis.find('-') then begin 
     StudentFilter := '<>' + recRegis."Student No."; 
     recRegis.next; 
  end; 

Albert

Eugene

the code in the last post is quit ugly looking (i fix it a bit below) and it has a nasty limitation - the StudentFilter has a limited length so you cannot put a list of thousand or more students in it.

StudentFilter := '';
recRegis.RESET;
recRegis.SETRANGE("Class Code",VarClass);
IF recRegis.FINDSET THEN begin
  REPEAT
     IF StudentFilter = '' THEN
       StudentFilter := '<>'+recRegis."Student No."
     ELSE
       StudentFilter += '&<>' + recRegis."Student No.";
  UNTIL RecRegis.NEXT = 0;
end

SETFILTER("Nº",StudentFilter);

redhotmustang

Yeah, thanks!

That's more pretty indeed. (I was just trying to see if it worked) It has limitations, StudentFilter is a text variable with a length of 1024, but if there are more students...

Albertvh

Hi
You can define 2 or 3 variables of 1024 and then depending on the length start filling in the 2nd then 3rd variables
e.g SETFILTER("No.",StudentFilter + StudentFilter2 + StudentFiletr2);


Albert

redhotmustang

Always learning! Tkx.