Multiple-criteria relationship based on value list item?

[eljefejb]

Hi all -

I hope this is the proper section to post in... I'm trying to come up with a calculation, but it would be based on related records. I'm having trouble figuring out how to build the relationship, though.

Here's the background: I have a Parts table and a Trainings table. For each part, multiple persons can be trained. Each training can have a different status (from a value list), such as "Approved", "Disapproved", "First Time", "Update", etc.

I already have one relationship set up (for another purpose) that links the two tables based on Part Number. (One-to-many from Parts to Trainings.) What I would like to do now is to define an auto-enter calculation in the Parts table that gives a count of the number of related records which have the value "Approved" selected from the value list. Using the existing relationship between the two tables, it is easy to get a count of the number of related records for each part, but that includes all the different values from the training status value list. How would I define a relationship that would give me records related by part AND contain only the "Approved" value from the status list? I'm thinking that if I can get the relationship set up correctly, then I can define my calculation to just be a count of the primary key of the related table, and that would give me the number of "Approved" trainings by part number.

Clear as mud? I'm confoozled, too...

Thanks, in advance!

[Dr. Evil]

I have created a file for your reference on how to build the relationships you will need in order to get a record count that meets your criteria.

Can you tell me how you got your portal record count?

Hope it helps you out, let me know if you have questions, I will attempt to answer them.

Take care.

:goodluck:

TrainerModel.fp7.zip

[eljefejb]

Thanks, Dr. Evil!

I actually got it to work without any portals... I defined two fields in my Parts table - "FindCertifiedMatch" and "NumCertified". "FindCertifiedMatch" is set up as a global text field, and "NumCertified" is a calculation. The relationship from Parts to Trainings is set up so that records in the Trainings table are matched when Part Number is equal AND CurrentStatus (Training) = FindCertifiedMatch. I added the FindCertifiedMatch field to my Parts layout and tied it to the Status value list, used the list to enter "Certified" into the global field, then removed the field from the layout so that it can't be changed by Joe User. Now the "NumCertified" field always displays the number of trainings classified as "Certified" (as opposed to another status)! Woo hoo!!!

[Dr. Evil]

Cool!

Glad you worked it out, have a good one.