RE: Concatenate filtered Portal

[cwest91]

Hi there,

I am a teacher in Richmond that is trying to design a database to improve the efficiency with which I can complete my report cards and I have been tinkering around with filemaker (after moving away from apple numbers) trying to streamline my process.

Currently I have a "students" table that is joined to "Assignments" table through a "grades" table. I give the student a grade for a particular assignment and that creates a record in the grades table and based on the grade each student is assigned a comment for their report card.

On the "students" layout I have a filtered Portal that shows the related records for each student for each subject.

This works great if I'm looking at the records on-sceeen. I can easily see how students did on each assignment for each subject.

What I am trying to do is to take those comments for each subject and put them on a layout to print there report cards. I have tried putting a portal on the "Print Report Card" layout but this is not ideal because some of the comments are 1 line and some are 3 lines. This creates very odd spacing since it appears that you cannot slide the records within a portal to erase unnecessary space. (Ideally I would also like to add bullets to each comment)

I figured I could create a new calculation field called "Math Comments Term 2" and somehow concatenate the comments that appear in the in the filtered list for each subject and place the concatenated field on the print layout... I have tried everything that I could think of but have not had any luck...

I'm thinking I could use a script to do this and have it loop through all the related records and concatenate them in a field but this seems like it would be a very inefficient way of doing this because I would have to do it for each student for each subject...

Any ideas, thoughts or suggestions would be greatly appreciated...

Thanks again

Chad

[comment]

I didn't get the filtered portal part. In any case, you should print the report cards from the Grades table, summarized by student.

[cwest91]

Hi there,

Thank you very much for your response.... I tried what you said and I got part way to what I was looking for, but I'm not sure where to go from here.

As you can see from the file below, my onscreen display works very well. I have a tab for each subject and the portals are filtered to display only the records for that subject.

The problem is that I need to get the "report card comment" part of the portal into the appropriate places in our districts standardized Report Card layout (see below). So I want to insert the 3 "report Card Comments" that you see in the Portal in the math tab in the image above to into "math Comments" section of the image below

I tried doing a sub-summary report like you suggested, but could not manage to organize and sort the fields to match my desired layout- when I tried to add the elements of my report card it seemed like the whole report was all wonky.

I will also include my database code.

Thanks again

Chad

Education Database no data.fp7.zip

I didn't get the filtered portal part. In any case, you should print the report cards from the Grades table, summarized by student.

Education Database no data.fp7.zip

[comment]

I don't really understand your file - it seems to me you have too many hard-coded components (like a table named 'Term 1 Comments', or the entire 'Letter Grades' table). Whenever you find a need to number your fields or tables, that's a sign of poor data structure.

Anyway, here's an attempt to steer you in the right direction.

Gradebook2.zip

[cwest91]

Sorry about the poor data structure- I'll be the first to admit that I really don't know what I'm doing and thank you for pointing me in the right direction- Ideally I would be able to find somebody in the Vancouver area to come sit down with me for a few hours so that I could wrap my head around how precisely filemaker works.

In the file that you gave me, there is one important thing that is missing. When I create an assignment I also create 4 comments to go along with that assignment- one for each level of achievement. Eventually I want the ability to use that comment on the students report card. So in my previous example, the join table also had a field that filled in the report card comment depending on the score for that assignment... Eg. If they got a 1, then the comment would be something like "This student has demonstrated excellent...." or something like that.

So to accomodate that I have added a "comment table" to hold the comments for each assignment. I have also changed the portal to show the report card comments that go with each assignment...

I am still unsure of how I will go about solving the original problem. As you can see from the portal in this image I have 3 math assignments that I have graded and the report card comment is a calculation that pulls the appropriate comment based on the grade.

This is great for my onscreen purposes because I will use this portal to look at a students math scores before I assign them a letter grade.

Now once I have assigned a letter grade I need to print out the students report card to match the format of our district report card. So I need some way to gather the Report Card comments for Math (and all the other subjects) and put them into the report card as in the picture below. As you can see the 3 comments from the portal have been combined and sit in the appropriate place in the layout... To create the sample below I copied and pasted the contents of the portal into a word processing document. But I am sure that there must be some way to do this in filemaker.

I'm hoping that there is some way to concatenate??? or otherwise combine the report card comments for each subject so that I can lay them out in the appropriate place for my report cards.

Thanks again

Chad

I don't really understand your file - it seems to me you have too many hard-coded components (like a table named 'Term 1 Comments', or the entire 'Letter Grades' table). Whenever you find a need to number your fields or tables, that's a sign of poor data structure.

Anyway, here's an attempt to steer you in the right direction.

Gradebook.com.fp7.zip

[comment]

I believe your Comments table should have a separate record for each comment. The fields in this table would be:

• CommentID (auto-entered serial)

• AssigmentID

• Mark

• Comment

Then you can define a relationship between Grades and (another occurrence of) Comments as:

Grades::AssignmentID = Comments 2::AssignmentID

AND

Grades::Mark = Comments 2::Mark

This way each record in Grades can "find" the corresponding comment - you only need to place the related Comments 2::Comment field on the report layout.

[cwest91]

Thanks again for your suggestions, I have implemented your suggestions but I am still not seeing the expected results. In the Portal on the student layout I see the 3 Math Comments as I would expect. However, when I go to the "Term 2 Report Card" layout and put the Comments 2::Comment field on the layout it only shows 1 of the comments. I would like it to display all 3 of the report card comments on the "Term 2 Report Card" layout. I have tried putting a portal on the "Term 2 Report Card" layou but because some comments are 1 line and others are 2 or 3 lines the layout looks terrible and very unevenly spaced.

Chad

I believe your Comments table should have a separate record for each comment. The fields in this table would be:

• CommentID (auto-entered serial)

• AssigmentID

• Mark

• Comment

Then you can define a relationship between Grades and (another occurrence of) Comments as:

Grades::AssignmentID = Comments 2::AssignmentID

AND

Grades::Mark = Comments 2::Mark

This way each record in Grades can "find" the corresponding comment - you only need to place the related Comments 2::Comment field on the report layout.

Gradebook.com.fp7.zip

[comment]

I have implemented your suggestions

Not all of them:

you should print the report cards from the Grades table, summarized by student.

I am also worried about your naming a layout "Term 1...". The actual term is DATA - it should go into a field. Your structure should be able to accommodate any number of terms - just as it can have any number of students and assignments.

Gradebook3.zip

[cwest91]

Yes! That's exactly what I'm looking for.

Thanks so much for all of your help and suggestions.

Chad

Not all of them:

I am also worried about your naming a layout "Term 1...". The actual term is DATA - it should go into a field. Your structure should be able to accommodate any number of terms - just as it can have any number of students and assignments.