Finding People who haven't taken a specific Course

[Jason H]

I'm trying to do a Find that finds all People who have not taken a specific Course.

 

There are only 2 tables: "People" and "Course"

Course is related to People by the PeopleID.

 

In the Course table there is a CourseName field that is populated by a valuelist that contains a list of possible courses.

People take many courses.

Some People have taken no courses (and thus have no records in the Course table)

 

This is for a report to show who has not taken a specific course.

 

What's the best way to script this search?

 

Thanks!

[eos]

There are only 2 tables: "People" and "Course"

Course is related to People by the PeopleID.

 

Your Courses table should properly be called something like “Enrollments”, since it is the join table between the People table and the real Courses table that you don't have – unless, of course, all you have to say about a course is that it exists; in that case, a Courses table would not be needed (though I find that scenario unlikely …).

 

Regardless of that, your script would work along these lines:

Go to Layout [ Courses ] 
Enter Find Mode 
Set Field [ Field: Courses::course ; Value: yourSpecificCourseName ] 
Set Error Capture 
Perform Find 
If [ not Get ( FoundCount ) ] 
  Show all records 
  Show Custom Dialog [ "No course by that name." ] 
  Exit Script 
End If
If [ IsEmpty ( People::peopleID ) ] 
  Show Custom Dialog [ "No one has taken this course yet!" ] 
  Exit Script 
End If 
Go to Related Record/Request [ Get related record from: People; Using layout: People; Show only related records; Match all records in current found set ] 
Show Omitted Only

[LaRetta]

I see no need to go to courses to perform the find.  The find is for people and you can find related just as easily and quickly since the related data is stored.  Here is an alternate script and an example file showing how it works:

Enter Find Mode [ uncheck pause]
Set Field [ courses::Checkbox; People::global ]
Set Error Capture [ On ]
Perform Find [ ]
If [ Get ( FoundCount ) = Get ( TotalRecordCount ) ]
  Show Custom Dialog [ "Every person has taken " & People::global, "OK", Commit: “No” ]
Else If [ not Get ( FoundCount ) ]
  Show Custom Dialog [ "No people have taken " & People::global, "OK", Commit: “No” ]
  Show Omitted Only
Else
  Show Custom Dialog [ Let ( num = Get ( FoundCount ) ; num & If ( num > 1 ; " people have " ; " person has " ) & "NOT taken " & People::global); “OK”, Commit: “No” ]
  Show Omitted Only
End If

Note that person id 4 (Gordon) has no course records at all so he is included in the found sets.  While it is true that we cannot find a person who has NO related record, we can find all who do and show omitted and Gordon would be included in that result.

I also agree with Oliver that a true Courses table will be what you want - showing the dates of the course, the teacher and other details.  Checklists are fine but only if you are SURE there will never be other details to track.  I would also suspect that you will want a report by course.  Using courses as checkbox, you cannot achieve that result - a checkbox can't be presented in two different report parts so instead, each combination of person and course should be a single record.

 

added: I suppose that if NO people have taken a course, the script should include a 'show all records' or 'show omitted only' step immediately after the message. I've modified the script and the file.Show Omitted only if no people have taken a course.

courses.fmp12.zip

[Jason H]

Thanks for the code samples. This is very helpful !!

 

I have written a script based on the info provided but I still need a bit of help.

 

First, some clarification.

It is indeed a Checklist that I have implemented and need to make reports on. Specifically, it is to capture which internal courses our faculty have taken (...and Not taken :-)

Actual Table names are: "Tutor" and "TutorTraining" rather than "People" and "Course" respectively.

 

 

The Code:

 

If [TutorTraining::TrainingDesc_Search = ""]

    Show Custom Dialog ["Search Criteria Missing"; "No soup for you!!"]

    Exit Script[]

End if

#

Set Variable[$Training; Value:TutorTraining::TrainingDesc_Search]

Set Variable[$ID; Value:TutorTraining::TutorID]

Show All Records

Enter Find Mode []

Set Field [TutorTraining::TrainingDescription; $Training]

Set Error Capture [On]

Perform Find []

Go to Related Record/Request [ Get related records; from: Tutor; Using layout: Training Outstanding Results; Show only related records; Match all records in current found set ]
Show Omitted Only

 

 

The Question: Ok. This works. But... I have a field in "Tutor" called "TutorStatus" which notes if the Tutor is still employed. The field will contain either "Active" or "Inactive".

So the results I get with the above script is correct but I now need to trim out all the "Inactive" Tutors.

How do I achieve this???

 

I have been exploring the Perform Find options and Constrain Found Set but I have yet to get it to work.

[eos]

The find is for people and you can find related just as easily and quickly since the related data is stored.

 

Yes, I know; doesn't mean I can't forget it … 

 

So the results I get with the above script is correct but I now need to trim out all the "Inactive" Tutors.

How do I achieve this???

 

Use fewer question marks.   

 

But seriously, if you use my approach, your idea to use Constrain was correct. Add

Enter Find Mode []
Set Field [TutorTraining::TutorStatus; "Active"]
Set Error Capture [On]
Constrain Found Set
If [ not Get ( FoundCount ) ]
  # none of the tutors in this training are active

As with Perform Find, you could forego Enter Find Mode[] and Set Field[] and use a stored query, but making this more explicitly helps in reading the script (and personally, I find the interface to define these queries rather counter-intuitive …)

 

In LaRetta's approach, you could include the Set Field[] from above directly in the initial Perform Find[] block, meaning: find all active tutors that have a related record for the specific training.

 

Some tips:

 

If [TutorTraining::TrainingDesc_Search = ""]

 

Use IsEmpty(); that's the officially sanctioned way to check for an empty expression.

 

You could also use that predicate to conditionally format the script button and give it an inactive look, then exit the script without a dialog. 

 

Set Variable[$ID; Value:TutorTraining::TutorID]

Show All Records

 

You define a variable that you subsequently don't use; it doesn't do any harm, but is confusing. Show all Records before Perform Find is unnecessary, since all records are searched by default. 

 

Also, it's a good practice to add a check to Go to Related Record[] (GtRR), either directly before or after the step itself – even if you're reasonably sure there must be related records. If e.g. you write a script to delete all child records for the current parent, and don't perform a check, and there are no children, the GtRR will fail. So you're still on the parent layout, and your script will now delete all parent records from the current found set … 

 

Either check on the existence of related records by looking for the primary key of the target table via the relationship you want to use for the GTRR. If [ IsEmpty ( targetTable::primaryKey ) ] , then there is no RR to Gt, and you can exit / do something else.

 

Or check for the specific error number that is returned when GtRR fails (which you'd do obviously after (trying) the step).

[Jason H]

Use IsEmpty(); that's the officially sanctioned way to check for an empty expression.

 

 

Cool! Good to know.

 

 

You define a variable that you subsequently don't use; it doesn't do any harm, but is confusing. Show all Records before Perform Find is unnecessary, since all records are searched by default.

 

That variable was a left over from trying some other things. I noticed it as soon as I posted :-P

Thanks for the Show records tip. Another good refinement :-)

 

I was attempting to use Stored Queries and was tying myself in knots!  AND, I was using Constrain Found Set wrong :-(

 

My script is now working Great (exclamation points are my favorite :-)

 

Thanks again !!!

[JP123]

I have sort of a related question but I'm very new to this.  Let's say I have classes, teachers and students and I want to find out which teacher can teach a class in Spanish on Wednesdays at 4pm.  It means searching for teachers who teach Spanish and who are listed somehow as "available" on Wednesday at 4pm.  I'm wondering how to approach designing the database, e.g., what fields I should be creating, what kinds of fields, and how I would search the records (e.g., would I create a script that is connected to a "find" button?).  Sorry, I'm just very new to this.  I would appreciate some direction.

[Lee Smith]

I have sort of a related question but I'm very new to this.  Let's say I have classes, teachers and students and I want to find out which teacher can teach a class in Spanish on Wednesdays at 4pm.  It means searching for teachers who teach Spanish and who are listed somehow as "available" on Wednesday at 4pm.  I'm wondering how to approach designing the database, e.g., what fields I should be creating, what kinds of fields, and how I would search the records (e.g., would I create a script that is connected to a "find" button?).  Sorry, I'm just very new to this.  I would appreciate some direction.

See if this Online Help answers your question Finding records that match multiple criteria

[Cable]

As to the 'listed somehow as "available" on Wednesday at 4pm', I'd simply create a "days available" and a "times available" field with value lists for days of the week and hours of the day displayed as checkboxes. Easy to manage and query against.