comparing related records

[kisscass]

I work in an academic dept in a university and I'm trying to recreate the database which lists the course options that students want to study next year. Our course structure has become much more complicated than before so manual checking of individual choices would be very difficult, and the students often make mistakes so their choices must be double-checked.

There are various rules about option choices which I'd like the database to flag up if unfulfilled. For instance a few of the courses are scheduled to run at the same time so a student should only choose one of these courses. I'd like to set up some sort of calculation field that would flag up if a particular student has chosen both courses.

We have the Student database which lists all the students' details and the Options database which is filled in via a portal in the Students database and looks up course information from the Modules database.

I've tried to create a script to scroll through the portal and copy each course name into a separate field, but it gets a bit messy when moving to the next portal record. Only the first value gets copied successfully. I've also tried going to the related records and looping through them all but that's also messy. I figure that there must be a more elegant solution.

Can anyone help?

[Ender]

It's hard to know what's wrong with your script from your description here, but it's understandable that you're having trouble. Scheduling systems can get pretty complex.

I think you should keep trying the method of looping through the related records. This tends to be the cleanest. You will likely need to keep track of the parent's key before jumping to the related records, then you may need additional relationships from the child table to other tables based on the parent key. This way you don't need to keep jumping back and forth between tables for the same set of child records.

[kisscass]

Thanks for replying. I'll have another go in the morning when I'm back at work. I was hoping that there was some obscure function that would show all the repetitions of a repeating list of values such as this. If I still can't figure it out I'll post my test script for your advice.

[Fenton]

If you are using actual repeating fields you are never going to be able to really do what you want. There are several ways to see whether there is a match between related records. That is what you usually want to know.

In this case it sounds like you want a "conflict test" between 2 records, with the criteria being:

StudentID and Dates, or Day(s), and Times

In other words, a single person can't be in 2 places at once. It becomes a little more complicated, because one class may "overlap" the other. But FileMaker 7's compound relationships can catch this.

But, you must have the data for FileMaker to compare. To me this means that you must have a record for a "registration":

StudentID

ClassID

Day(s) of week

TimeStart

TimeEnd

You would not use ClassID in the test (because you're testing against _other_ classes), but all the other data. If Days is multi-values, more than one, you may have to calculated separate keys, with a Day in each.

[i don't know if using a multi-line Days field would screw up the compound relationship. Say: Wed & Thurs (multi-line checkbox), 9 am to 11 am. The times would use "_gTimeStart > TimeEnd" AND "_gTimeEnd < TimeStart". I can never remember that test either :-]

(P.S. It seems to work fine. See attached file.)

Or run a scripted Find with "Or", new requests to test each day.

The data for a new class could all go into global fields first, so you could run a script for this, or test the relationship for IsEmpty, before the new record is actually created.

TimeDays_Conflicts.zip

[kisscass]

hi Fenton,

thanks for your help. It's not a scheduling database per se, it's a student info database related to an options database which lists the course choices. As there are only a few courses that clash it's not necessary to list running times for each course. I've thought about it a bit more and I think the solution would be for the options database to have a calculation field (let's call it "clash"): if (course="performance" or "analysis" : 1 : "") then in the students database I'd have another calculation: if (count (clash) > 1 : "clash" ; "").