Drop-down menu, if statements & a script - HELP!

[Raschelle]

I'm developing a rather complex training solution for a client. One database includes the employee info (names, department, etc.), fields for training dates and status of each class (about 100 classes). The second database has one field for each class where they put in the number of days between trainings ["training frequency"] (for example, annual training would be 365 days). This information is used to calculate the training status in the first database (ok, training required).

I would like to create a list (I have the layout all set; same layout no matter which class they want a list for.) where it would look for who needs the training. It sounds simple but here lies my problem. Certain training classes are only for certain departments, but some classes are for more than one department. I would like to start the script off by having them choose the class from a drop-down menu and hit the continue. It would use that choice to look for the employees who need that training and go to the "list" layout that I have created.

I have no idea how to continue this script without making 100s of subscripts and if that's the only way, what would be the best way to get that going. I'm already assuming that I need at least one subscript for each class that will find those who require training for that class. How do I set up the script so that it knows which subscript to use based on what is entered in the drop-down at the beginning?

Also, regarding the training status itself, how can I add "training optional" to the calculation without making it too complex (if that's possible)? I would need the majority of the status fields to do something like this:

If "dept" does not equal production or maintenance (could even have a third "or"), then "training optional", otherwise "training required". (Note: there also needs to include the part about if "training date" is less than or equal to the "training frequency", then "ok", otherwise [the preceding if].

I can do simple if statements, but this is way out of my league. And, last but not least, FMD 6, OS 9 & WinXP (being developed for Win but developing initially on the Mac).

[cjaeger]

you need a second relation between the 2 files.

in classes, you should have a (checkbox) field which indicates the departments(dep1,dep2,All). In the employees database, make a calc field

key= department & "q|" & "All".

a relation on this field will allow to select only the classes which are either for all employees ("All"="All")

or for his department only..

This is basically a multikey (many-to many) relation. Multiple entries in either file are "q|" (return) separated lists. no scripts needed here (apart from go to related record, but I would rather use a portal) as for the combination of criteria, I might send a sample file soon.

[cjaeger]

sorry - this is far more complex than can be solved in 30 minutes ..... so no file .

But here is the basic layount of the files:

you need another database "attendance.fp5" which stores the courses taken per employee. You can retrieve the last attendance of every corse per employee by using a key constising of emplyee_ID & " " & course_ID and the Last() calc.

in classes.fp5, split the field audience (department) into 2 fields: required and optional and use a list of departments for those fields.

you may also consider storing that info in a departments database, though. You could use that anyway for the value lists.

happy filemaking.

[Ugo DI LUCA]

Hi,

In fact as cjaeger said, you need a Join file.fp5 from employee to Class.fp5....

Saying that...I'm worried if the file you refer to is not your Employee file but rather a kind of "Mid-Join File".

If this is the case, you should change your structure as you seem to store records into 100 fields, going to this newly created Join.fp5 where each line would be one record for one employee.

Seems to me your "status" calculations could therefore be used from there and stored in the Employee file.

The "Finding/Filtering" process you are looking for would be easier; using the Department_Id (that should be a lookup into the Join.fp5) concanated to another indexed calculation (or lookup) that would "pass" the Employee_Id according to the status calc.

At the end, if this is possible, you may also hold a department file.

Even if this seems annoying, it would surely work better than any script.

[Raschelle]

Thanks for the feedback guys. If anyone has any other ideas, I'm listening.

I'll try to break this information up into more relational databases, as suggested. I guess I'm just one-dimensional on this kind of stuff. Comes from lack of official training, I guess.

I'll let you know how it works out.