Trying to automatically trigger a script to flag a record.

I am tracking a bunch of students. I want to flag them as freshman or upperclassmen for example. for practical purposes this can be a 1 or 0 in a field in student table. 1 for freshman or 0 for upperclassman. I want this to be updated automatically based on events.

I have event records viewed in a portal in table students.

When I enter an event "Started Freshman year" I want to automatically flag freshman field to a "1". If I put "Completed first semester" nothing should happen. If I put "Started Sophomore year" then freshman flag should reset to 0. It seems like a very simple calculation using case function and having it look for specific words like freshman and sophomore to trigger results. However I do not want to trigger a flag in the event record but rather in the student record. If I could get those words to trigger a script it would be great. Then I could go to a specific field and set the field to 1 or 0. thanks

The flag field should be a caculation field and the result a number. With the following calc:

Case ( text_field = "Started Freshman year" ; freshman field = 1 ;

text_field = "Completed Sophmore year" ; freshman field = "" ;

0 )

This can be done across relationships. A change in one table will effect the other.

If understand correctly a script is not needed.

Instead of creating different flag fields for each level why not set one field according to the students level. In the Events table create a field called AcademicLevel with the following calculation = Case(EventDescription = "Started Freshman year"; 1; EventDescription = "Started Sophomore year"; 2;EventDescription = "Started Junior year"; 3; EventDescription = "Started Senior year"; 4; "") . Then create a calculation field called MaxAcademicLevel in the Student table = Max(StudentEvents::AcademicLevel).

Example file attached

Student.zip

That is very helpful. I need to tweak the solution. Max is a great idea but in my actual database (which is not students, just used as an example) After senior year they can go back to sophomore year for example. What I really need to determine is the most recent entry into the field Academic level.

Another way is I want to turn a switch on or off in the student table.

I wish I could say Case(EventDescription="Started Freshman Year";setfield (Academic_Level)="Freshman"; EventDescription="Started Sophomore Year;setfield (Academic_Level)="Sophomore";etc.) I do not think this is allowed.

Does that make more sense?

Scripting that is possible, but how do I trigger the script when an entry is made?

Additional question to quillpro's response. I understand your suggestion as well but this is a one to may relationship (one student:many events). How will filemaker know which event to look at? I would like it to look at the most recent.

See if the attached is what you want.

Student.zip

Very well done. If I understand it correctly, you are able to look at the most recent event by looking at the maximum value of an autoenter serial number. You set up a self join between two table occurences to compare the event number to itself and see if it was the last entry. I guess I could another way to do it would be to look at the date and time of the last entry and look for the max of this. Thanks. I am learning that with some creativity Filemaker is incredibly powerful. I picked up a filemaker book for the first time about 6 weeks ago and it has been incredible.