Multiple records from a relationship - possible?

[Reni]

I have created an FMPro 7 database that replicates the structure of an SQL database used for the online registration of individuals and projects for a science fair. A Users table captures info on individuals (including students, adults, judges, etc.), each with a unique ID and record. Another table captures project info - each with a unique ID. These two are linked through a table (UserRoles) that associates each UserID with one or more RoleIDs and a separate ProjectMembers table that connects one or two MemberIDs to a ProjectID (Projects can be entered by one or two students). In summary the key relationships are:

Users.ID -> UserRoles.UserID

UserRoles.ID -> ProjectMembers.MemberID

ProjectMembers.ProjectID -> Projects.ID

Award winners are captured in a table (AwardPrizeWinners) that contains a unique ID for each instance, plus the PrizeID and ProjectID. These relationships are as follows:

Projects.ID -> AwardPrizeWinners.ProjectID

AwardPrizeWinners.PrizeID -> AwardPrizes.ID

AwardPrizes.AwardID -> Awards.ID

I need to print participation and award certificates, cheques, labels, etc. for members of particular projects (e.g., award winners). I have no difficulty displaying the one or two project members in layouts using portals but I have been unable to construct relationships that will allow the printing of these items for all the project members. (Projects with one student are no problem, but I cannot figure a way to include the second student, where required.) This would seem to require that a new table be generated/populated that contains the AwardPrizeWinners.PrizeID and the individual UserIDs of each project member. This would generate a separate record for each user who won each prize; however, I had hoped that this could be accomplished through relationships. After many attempts, I can't see how. Any help would be appreciated.

Thanks.

[Ender]

I think I get it. If my interpretation of your structure is correct, then you should be able to put your certificates, cheques, and labels in layouts based on the Table Occurence of the ProjectMember table.

You can easily jump to the award winners using the Go to Related Records script step (going from Award to ProjectMember, and only showing related records.)

You can also drop a portal on a layout based on Award, showing related records from ProjectMember, to easily view the Project-Member-AwardPrizeWinners.

Gotta love the relational power of FM7!

[Ender]

If it's possible in your system for a project to be awarded more than one prize, then there may be a little more work required. Maybe another Table Occurence.

I don't know...can't think anymore...past bed time...

[Reni]

Thanks so much for the quick response! I can't believe I missed using the ProjectMember table as the basis for generating the student certificates, etc. - works great!

As it is possible for a project to be awarded more than one prize, the next challenge is to be able to generate all the award certificates. I assume I have to start from the AwardPrizeWinner table, but that contains project records (not user records) - it's easy to create certificates for the first student, but how do I get a separate record for the second students in the project?

Thanks again.

[Ender]

Without populating another table, I don't think there's a way to show your award prize winners for all projects just through the relationships.

My suggestion is to build the award certificates and cheques in the ProjectMember table and use globals to set the award title, cash value, etc. This would need to be scripted to pull up each award's recipients. The script might look something like this:

Go to Layout [ Award ] //this could also be done in AwardPrizeWinner

Perform Find [ restore find for active awards ]

Loop

Set Field [ Award::gAwardTitle ; Award::AwardTitle ]

Set Field [ Award::gAwardValue ; Award::AwardValue ]

Go to Related Records [ Show ; from table: ProjectMember ; using layout: ProjectMember Print Award ]

Print [] //print all records

//the Print Award layout would use the globals gAwardTitle, gAwardValue, or whatever other Award globals you need.

Go to Layout [ Award ]

Go to Record/Request/Page [ Exit after last ; Next ]

End Loop

[Reni]

Thanks again. I was pretty sure that a relationship wouldn't be able to do it. I'll give your scripted approach a try. It looks like this script would work to generate the certificates on-the-fly, but there are additional requirements for this information, such as generating labels for the certificate envelopes so it may be better to populate a new or existing table. The trade-off in populating a table is that it can get out of sync with the real data unless related scripting is diligent in refreshing the table before using the data. I'll play with the on-the-fly approach to see if it will meet the need.

[Ender]

I see your problem (with labels especially.) Populating an additional table wouldn't be too much work. Since the data in the additional table is only needed for the AwardPrizeWinner-ProjectMembers, I would say to only populate it when an Award is assigned to a Project. The ProjectMembers aren't likely to change from that point until the labels and certificates can be printed (though if they do, you can add scripts to update the AwardPrizeWinner-ProjectMember records when ProjectMembers change.)

[Reni]

Thanks. Your script idea led to a script that populates/refreshes new tables containing AwardPrizeID and ProjectMemberID (generating a record for each award won by each student) and another table containing ProjectID and the names of the students as Student1 and Student2. This latter table wouldn't normally be required, as any report requiring both names can be created using a portal; however, creating a tab-delimited export file with the two names was made much easier by having this table as well.

Your help was most valuable in working through this - thanks again!

Reni