Basic FileMaker 7/8 Query

[mattosaur4]

Hi

I'm moving some old simple multi-file FM 5 databases over to FM 7.

It's for tracking Assignment Scores and should be easy but I'm having some trouble. Currently I have these tables

Student

* StudentID

* Name

Assignment

* StudentID

* AssignNo (1-4)

* Mark

plus some other stuff I've removed to keep it simple. It used to be one student file and 4 assignment files that were linked together.

I've created and linked the tables above by the StudentID.

Basically I want to do two things.

1. Have a Layout that shows the Student on one line with all 4 of there Assignment Scores. I've tried a Portal but that puts it over 4 rows where as I want it one line. I also tried a horizontal portal hack but that doesn't work as I want 4 fields Assignment 1,2,3 and 4 of which you can do any mixture of, but this makes it impossible to line them up under the right headings as if a student only did 3 and 4 they appear under the 1 and 2 headings. Is there anyway to get this to work? In the old solution it was easy as there were 4 seperate fields Ass1Score, Ass2Score etc... but I'm try to make it relational so I can do the calculation below.

2. I also want to take the top 2 scores (out of 100) from a students assignments and perform some weighting and converting to percentages, which I can do with this relational setup easily but if I revert to the 4 Ass1Score, Ass2Score setup I can get step 1 done easily but this calcualtion becomes difficult.

Any ideas on how to solve this?

Thanks

[Logout?]

I think you want to make 4 portals, one row each, starting on subsequent portal rows.

Do this: on the layout with the students listed one line each, drag a portal out to the right of the student name. In the 'Portal Setup' dialog, choose the relationship to Assignments, then choose 'Initial Row' = 1, 'Number of Rows' = 1. Choose the Mark field for display in the portal. Repeat this process, but choose 'Initial Row' = 2 for this portal. Repeat for rows 3 and 4. Arrange these 4 portals horizontally next to the student, and you have 4 related values displayed horizontally.

Hope that helps!

[mattosaur4]

Hi

Thanks for the reply.

I had tried that alread "horizontal portal hack" I called it above. Problem is non of the assignments are compulsory so if I did Assignments 3 and 4 there'd only be two records and they'd show in columns 1 and 2 instead of 3 & 4. Easy way round this is to create a blank records for each student and assingment except that's not relational at all and just a fudge.

Any further ideas? This must be a pretty common use of FileMaker?

Thanks, Matt

Edited February 20, 2006 by Guest