Help with a find

[muskee]

Hi guys,

Would appreciate your help. I have a database for a university, basically I have students that enroll to a subject and can either : win, homologate, fail, or cancel the subject.

I must make a report that shows every student that has failed, canceled or hasn't seen certain subject. The catch is that if the student has seen the subject two or more times and for example the first time he failed it but the second time he won it, that student appears on the report even though he shouldn't because he won the subject on a second try.

So, how can I make a report that excludes a student that has enrolled a subject more than one time and has failed and won that same subject?

 

 

[comment]

2 hours ago, muskee said:

I must make a report that shows every student that has failed, canceled or hasn't seen certain subject.

Interesting question. What exactly does "hasn't seen certain subject" mean? In fact, I am not even sure what "homologate" means (not even after looking it up in a dictionary). So what exactly should this report show? Will it include more than one subject? And should students that have not enrolled in a subject at all be listed under it?

[muskee]

Ok, homologate means that you passed the subject in another university and you were transferred to our university thus you passed it (homologate is win) . But this is not necessary for our case .

The table relationship is like this:
Student --< Enrollment >---- Subject

The report is only meant for one subjet, I'll try to show an example, this is the subject Calculus 1 (It's inside the subject table),  I can create a find that shows me a list with all the students that have enrolled to Calculus 1 :

Calculus 1

StudentName StudentID Grade State       Date                    SubjectName

Fred                  11223           2,8     Failed     01/01/2015           Calculus 1
Jessica               12555          3.3     Failed     01/01/2015            Calculus 1
James                99234           4.5    Canceled  01/01/2017            Calculus 1
John                  65554            --       Homologate  01/01/2017     Calculus 1
Fred                  11223           3,9     Win         01/01/2017            Calculus 1

I'll also show the student table.

Student table:

StudentName StudentID Age  Cellphone

Fred                  11223        23     32222222
Jessica              12555        19     22333333
Ted                    33222       27        33334555
Lebron                5544       21        54345445      
James                99234       31       23433245
John                  65554       25        455476

So, my report should only show students that have failed or canceled the subject and those students that have never enrolled to it, but maybe I need 2 different reports? I'm not sure

Calculus 1: Student Report Failed/Canceled
StudentName StudentID Grade State       Date
Jessica               12555          3.3     Failed     01/01/2015
James                99234           4.5    Canceled  01/01/2017

**Fred is not in the list because he failed calculus 1 the first time but then won it in another date

Calculus 1: Student Report never enrolled
Ted                    33222 
Lebron                5544

If you have any more questions I'll gladly answer!

[bruceR]

I would think that terms would be failed or passed. Seems a very unusual and inappropriate use of "win".

[muskee]

21 minutes ago, BruceR said:

I would think that terms would be failed or passed. Seems a very unusual and inappropriate use of "win".

yeah, I have it as passed in the database, I just found it easier to explain with win

[comment]

17 minutes ago, muskee said:

my report should only show students that have failed or canceled the subject and those students that have never enrolled to it,

There are basically two options you have here: 

1. Produce the report from the Enrollments table. But this type of report could never show students that did not enroll in the subject at all, because there is no record of that. In fact, you could (at least in theory) have a student that had not enrolled in any subject - and thus will not appear on any report produced from the Enrollments table;
2. Produce the report from the Students table. But then you still have the problem of displaying multiple states from the Enrollments table (even if you omit the students that have passed the selected course). For example, suppose a student has first cancelled, then failed. 

Let us know how you wish to proceed and we'll help you with the next step.

[muskee]

12 minutes ago, comment said:

 

1. Produce the report from the Enrollments table. But this type of report could never show students that did not enroll in the subject at all, because there is no record of that. In fact, you could (at least in theory) have a student that had not enrolled in any subject - and thus will not appear on any report produced from the Enrollments table;

I think this approach would be better

[comment]

Okay, then. How will you select the course to report on? 

[muskee]

My report layout is based on the enrollment table, so with a script capture the course name from a drop down list and then perform a find?

[comment]

3 minutes ago, muskee said:

with a script capture the course name from a drop down list

I am not sure what exactly you mean by that. Let us assume you already have the selected course's name (should really be an ID) in a variable. Then you can do: 

Go to Layout [ Students ]
Enter Find Mode [ ]
Set Field [ Enrollments::CourseName; $courseName ] 
Set Field [ Enrollments::Status; "Pass" ]
Omit Record
Duplicate Record/Request
Set Field [ Enrollments::Status; "Transfer" ] 
Perform Find [ ]

At this point, you will have a found set in the Students table of all students that did not pass (or transferred) successfully in the selected course. Now we'll just get those students' enrollments - all of them at first, then we'll keep only those of interest:

If [ Get(FoundCount) ]
  Go to Related Record [ Show only related records; Match found set; From table: Enrollments ] 
  Enter Find Mode [ ]
  Set Field [ Enrollments::CourseName; $courseName ] 
  Constrain Found Set [ ]
End If

This may seem a bit convoluted, having to search for the course twice - but it requires no additions to the schema: neither relationships nor fields.

 

1 hour ago, muskee said:

I have it as passed in the database, I just found it easier to explain with win

I have used "Pass" and "Transfer" (instead of "win and "homologate", respectively) in the example code above.

[muskee]

Hey man, thanks a lot! I made your script and it seems to work fine, is there like an outer join function to find the records that aren't related?

[comment]

6 minutes ago, muskee said:

is there like an outer join function to find the records that aren't related?

You can find records that aren't related by finding records that are, then showing omitted. Not sure if that qualifies as "an outer join".

 

[muskee]

So in order to show the students that have never enrolled to a course I would have to do a find in the students table and omit the subjectID and subject::status where =passed, transfered, fail , right?

[comment]

No, in order to show students that have never enrolled in a given course (IOW, to omit students that did enroll in the course) you would do:

Go to Layout [ Students ]
Enter Find Mode [ ]
Set Field [ Enrollments::CourseName; $courseName ] 
Omit Record
Perform Find [ ] 

There are no "related" or "not related" records here. Unless you happen to be in the record of the given course in Courses; then you could do:

Go to Related Record [ Show only related records; From table: Students ] 
Show Omitted Only

 

 

 

Edited June 5, 2017 by comment

[muskee]

Hey, thanks alot for your help, I managed to implement the find as you said.
I have a problem with one thing though. Since I'm using a droplist to get the subject ID in order to get the script running,
How can I capture the subject name? When all I have is the subject ID in the enrollment table and later on the script is going to the student table.
The subject name is to put it in the layout

Edited June 6, 2017 by muskee
error

[muskee]

Ok, I solved it.
Made another script the went to the subject table, made a find

Enter Find Mode []
Set Field [Subject::ID; $subjectID ] 
Perform Find[]
set variable [$$SubjectName, Value: Subject::Name]

Don't know if there is another an easier way to do this, regardless, thanks for all

[comment]

1 hour ago, muskee said:

Don't know if there is another an easier way to do this

Me neither, because I don't know (1) why you need this and (2) how exactly you populate the $subjectID variable.

I wouldn't think you need this, because if you are in the Enrollments table, you can get the related subject's name directly from its record in Subjects.

 

 

Edited June 7, 2017 by comment

[muskee]

I have a drop down list where the user selects the subject they want to find the records for. The list shows the subject ID and the name, but only stores the ID.
Now that I have the ID I can perform the script you helped me made by storing the selected subject in the variable $SubjectID.

I perform the script, everything works and I can show the user a message that says "Found registers for <<$$SubjectName>> " but since I didn't have the subject name stored anywhere I made a find that could get it and store it in a global variable

[comment]

5 minutes ago, muskee said:

I have a drop down list where the user selects the subject they want to find the records for.

Is that a global field? In which table is it defined?

[muskee]

yeah a global field in the enrollment table

[comment]

There is nothing inherently wrong with your method, it just seems wasteful to perform another find. If you're using my script, then you end up in a layout of the Enrollments table, with all records in the found set belonging to the same subject. So here you could simply use "Found registers for <<Subjects::Name>>" to display the name. If you want to load it into a variable to be used in another script (such as the one that finds students the did not enroll in the subject), then just add the Set Variable[] step at the end.

 

Edited June 7, 2017 by comment