(Dev) Relate 2 tables to filter form records

redhotmustang

Hi there,

I have a big issue (at least for me - I only know how to do this in another programming languages) ](*,) .

I have a form whose SourceTable is Students.
This form has a Tab Control with text boxes that will be used to filter the records that appear on the form (Course and Class).

If nothing is filtered all the Students appear. When we fill the Class box (after choosing the Course first) the list of students should update to reflect only the ones that belong to that Class.

The issue is that the table Students does not have a field called "Class Code" that would be used to filter the students list. I can not create that field, since one student can belong to more than one class at the same time.

But there is another table that has that Class Code field: Students Registrations.
How do I relate these two tables to filter the records on the form?

I mean, I would like to get the students that belong to chosen class and display them on the list.

I tried this on OnAfterGetRecord() trigger.

recStudentsRegistrations.RESET;
recStudentsRegistrations.SETRANGE("Class Code",VarClass);
IF recStudentsRegistrations.FIND('-') THEN BEGIN
   REPEAT
   GET(recStudentsRegistrations."Student Code");
   UNTIL recStudentsRegistrations.NEXT = 0;
END;

I used a record variable called recStudentsRegistrations connected to the "Students Registrations" table, and filter it by "Class Code" using the value filled in the Class text box (VarClass).

Then, if any students are found in that class, I'll filter the current record by student code which was retrieved from the Students Registrations table. This is wrong, I don't want to filter the record itself but the entire list.

The result was an endless list of the same student, over and over again.
I tried to use another record variable connect to the Students table, but that does not update the list.

Any ideas how to solve this?
Thanks in advance.

PS: I know I shouldn't code on the form, but on the table. But how and where do I code this there? Should I create a function there and call it from the form?

Xypher

If I read this right... You have:

Student Table:
-Student Code
-Student Name etc etc

Student Registrations Table:
-Class Code
-Student Code


I have a feeling there is a much easier & better way to achieve this but off the top of my head...:

OnPush - (Button)

  recStudentsRegistrations.RESET;
  recStudentsRegistrations.SETFILTER("Class Code",'%1',VarClass);
  //*shrug* - I like using setfilter with single, non-range, filters

  CLEAR(txtStudentFilter);

  IF recStudentsRegistrations.FINDSET THEN
    REPEAT
      IF STRPOS(txtStudentFilter,(recStudentsRegistrations."Student Code"+'|')) = 0 THEN
        txtStudentFilter += recStudentsRegistrations."Student Code" + '|';
    UNTIL recStudentsRegistrations.NEXT = 0;

  IF STRLEN(txtStudentFilter) > 0 THEN
    Rec.SETFILTER("Student Code",'%1',txtStudentFilter)
  ELSE
    ERROR('No students/class found.');

Then again, I'm not sure if the SETFILTER will allow/identify the '|' (Or's) being that it's not in the 'String' parameter itself.

Let me know what happens.

(The way you were attempting this was at the opposite extreme, where you were going through each student record and checking the registrations table for that student under the current class filter. Highly recommend against that logic )

redhotmustang

No, unfortunately that didn't work. That code made the students disappear.

Yeah, that is the rigth table structure.

Student Table:
-Student Code
-Student Name etc etc

Student Registrations Table:
-Class Code
-Student Code

What I was trying to do was to get to filter the "Students Registrations" by "Class Code". The value for the "Class Code" field filter was filled out on the form and assigned to the "VarClass" variable.

That would filter the "Students Registrations" and return the students from a specific class. With that I could go on and use that "list" (resulting from the filter) to get the student codes and use that to display the class students on the form.

The best I can do is to display one of the two students that should appear.

recStudentRegistrations.RESET;
recStudentRegistrations.SETRANGE("Class Code",VarClass);
recStudentRegistrations.SETRANGE("Student Code","Student Code");
IF recStudentRegistrations.FIND('-') THEN BEGIN
   SETFILTER("Student No.",recStudentRegistrations."Student Code");
END;

Xypher

You should not program in the OnAfterGetRecord for this solution type.

Lets try this...

If you have this form only created to list the students which are in a specified class how about...:


Set the SourceTableView to the Student Registrations table,
apply the "Class Code" filter directly and add this code to the "Student Code" text box column you add to the table:

OnFormat(VAR Text : Text[1024];)
  IF recStudents.GET(Text) THEN
    Text := recStudents."First Name" + ' ' + recStudents."Last Name"
  ELSE
    CLEAR(Text);

(something like that)

I am pretty sure I know what you're trying to do, it's just not that good to approach it by way of "I'm going to sort through ALL students and check EACH against the Student Registrations table to see if they belong to CLASS."

But rather in the above code you'd be filtering directly to the Student Registrations table and manipulating the display of the text. (So we go through a total of 20~ records rather than 3000+ just to find a single class roster)

redhotmustang

Thank you Xypher for your kind attention.

Maybe I did not explain myself very well.
Let me explain what I'm looking for.

I have a tabular type form whose Source Table is Students (which has all the students in the school.)

This form has a tab control above the lines with 2 text boxes, in the first one we choose the Course and (only) then we are able to select the Class.

This form is to be used to mass register students to the select class. We check the students we want (from all of them) and click the register button.

My boss told me that each class has a maximum amount of students (that one can set on the class card), let's say 28 students.

For the Course A, Class 1 there are 2 students already registered.

Table Structure
Students - Student No., Student Name, etc.
Students Registration - Student No., Student Name, Class Code

The goal here is to be able to view all the students in the school when we open the form. Then, if we fill out the Course and Class boxes they'll act as a class selecter showing only the students that are not registered to that class in order for us to be able to add them to the selected class.
That is, if we fill out the text boxes they will make the registered class' students disappear from the list (they are already on the class, so we don't need to register them again).

Then we select our students and the system would calculate how many students (the ones already registered + the new ones just selected) will be on the class, displaying an error message if that exceeds the maximum amount, thus not registering the select ones.

What I tried to do, since the Students table does not have a Class Code field, was to go to that table and get all the students registered in that Class that has just been chosen in the Class Code text box.

Then I would had the Students Codes and would use them to filter the list of students.

Picture this, the Students Registration is filtered by Class Code. Let's say Class 1. Two students appear. I get those 2 students' "Student No." and I would use that information to filter the Students table.

recStudentRegistrations.reset;
recStudentRegistrations.setrange("Class Code",'1');
if recStudentRegistrations.find('-') then
   firstStudentOnTheRange := recStudentRegistrations."Student No.";

if recRegis.find('+') then
    lastStudentOnTheRange := recStudentRegistrations."Student No.";

setrange("Student No.",firstStudentOnTheRange,lastStudentOnTheRange);

This is I wanted to do.
Now I have to reverse it. Using this range to make this 2 students invisible on the all Students list.

McClane

Hope i understand what you mean:
you have a form with some textboxes and and a table box, Source ist Students. If you enter a Class Code and Course Code in tow of the textboxes, the list of students should be filtered.

Let´s say you just have the Class Code.
Textbox ClassCode - OnAfterValidate:

rec.reset;
If ClassCode<>''then
  begin
    Class.SetRange("Class Code",ClassCode);
    if Class.find('-')then
      repeat
       if rec.get(Class."Student Code")then
         rec.mark(true);
       until Class.next=0;

    rec.markedonly(true);
  end;

CurrForm.update(false);

redhotmustang

I coded it like this:

reset;
recStudentRegistrations.reset;
recStudentRegistrations.setrange("Class Code",varClass);
if recStudentRegistrations.find('-') then begin
   firstStudentOnTheRange := recStudentRegistrations."Student No.";
   end;

if recStudentRegistrations.find('+') then begin
    lastStudentOnTheRange := recStudentRegistrations."Student No.";
    setrange("Student No.",firstStudentOnTheRange,lastStudentOnTheRange);
    end;

First I reset all the filter in the Students list.
Then I got the Student that were registered on the class chosen on the text box.

Then I set a range between the first and last student of that result.

Now I'd like to select all the students except those that are already registered, something like:
setrange("Student No." different from firstStudentOnTheRange .. lastStudentOnTheRange);

Oops, I don't how to set a filter to display all the students except a range of them.

Thanks McClane, but that didn't work. The form remains the same.

McClane

works fine here. Guess i misunderstood something :-k

redhotmustang

Mine is working with my code.

Now, I just need to reverse the filter. That is, select all the students except those registered on the class.

Since I Know who is registered I tried to set a filter on all the students except the range of the registered.

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.)

Xypher

Now that I understand what you're trying to do, it looks to me that McClane has a good idea for your solution. Just not sure what isn't working for you with his code.

What kind of records are displayed after implementing his code?

redhotmustang

I put his code on class text box, in the OnAfterValidate trigger.

The list of Students did not change.

Many thanks anyway for your effort.