Hello,

How to sum a subset of records in a portal? Each student has a record with a child portal for grades with a date, grade, and a category (assignments, quizzes, and tests). How could I sum for each individual student, for example, the grades only for tests? Thanks.

Why try to force this into a portal? It would seem better to use a subsummary report.

I am describing that the data lies within a portal. I have no clue how to sum a subset of that data.

Well pretty straight forward:

http://www.filemaker.com/help/Script-Steps19.html

...and then:

--sd

What Bruce is suggesting, is that you will have a much easier time working directly in the Grades (child) table. This is a perfect use of sub-summary reports.

I am describing that the data lies within a portal. I have no clue how to sum a subset of that data.

Time to learn about standard reports in Filemaker then. If you start a new layout you'll be guided through it. There is also help available, see Help > Product Documentation > User Guide. Also take a look at the tutorial.

I appreciate everyone's ideas but I need a calc field to do that. Does any one know how?

There are several methods to do this. It's difficult to recommend one, when your requirement is not clear enough. Do you want to isolate one particular category, or perhaps one selected category at a time, or more than one - IOW, what is your purpose in this?

I appreciate everyone's ideas but I need a calc field to do that. Does any one know how?

What makes you say that?

Maybe you can present a screen shot or mocked up report or something.

When you've mastered standard standard Filemaker reporting techniques then ask the question about specialty techniques.

Edited January 2, 200917 yr by Guest

I appreciate everyone's ideas but I need a calc field to do that.

Do you?? Why - when I tell you this isn't a spreadsheet? You will end up with too many calc'fields slowly rendering. It's a common mistake ALL Excel jockeys makes when arriving to this tool. Read what the head of filemakers engineering LeCates has to say about it:

Anyhow, I asked about this statement and he says he always steers people towards traditional reporting methods (subsummary reports) for speed reasons and flexibility. Unless there is some really great reason to use relationships to simulate a report, don't do it. FileMaker was not designed to aggregate massive amounts of information through relationships. Anyhow, I just wanted to make sure readers were clear on the best approach to reporting.

Please do not in the future ignore what Bruce has to say!

--sd

Why don't you shoot the guy for good measure? I suppose you have never put a "live" summary under a portal, and Bruce hasn't written this. None of us knows what this is about, and we are in no position to judge whether it's "needed" or not until we do. I am embarrassed by the aggressive and rude tone of your responses.

Let me start from the top with the relevant portions:

Parent table has:

1- student name

2- contact info, etc.

Child table for grades

1- date

2- score

3- category of assignment ("assignment", "test", "quiz")

I need "something" that will appear on the layout of the parent table that will display the total points of the tests for the student I am currently looking at. It is trivial to sum all of the points for all of the work in the child table- that I do already- but how to restrict that summation to only one category? This is for a one teacher classroom- not a giant corporation.

None of us knows what this is about, and we are in no position to judge whether it's "needed" or not until we do. I am embarrassed by the aggressive and rude tone of your responses.

So any teaching of the "classics" are in wain? Why does it remind me of the title:

Education's End: Why Our Colleges and Universities Have Given Up on the Meaning of Life

(Kronmann)

Why isn't it PC to show or tell someone where to start, instead of endorsing eclecticism ...say as TV shows like "America got talent" - appearance can't be everything? Learning isn't to build dependancy on the teacher or oracle, but instead the foundation to build indepenent thinking on.

--sd

Here's a demo with portal calcs and a sub-summary report (see Scripts menu).

I figured that you'd need to limit the records in the portal by date (otherwise you'd have every grade counted in the averages).

I thought seeing something might help the discussion a bit. In the sub-summary report, all grades are found. You'd need to add a Find in Grades to limit the records in the report (ie, by date).

Also, this doesn't really give you a grade book, because there are no weights to these averages, so there's more math to do.

Grades.fp7.zip

Preaching isn't teaching. And even preaching can be done more politely.

Ahh. Thank you. That's the stuff.

It is this portion:

Average ( Grades~filtered::grade_Type3 )

that I didn't know about. Now that I see the pattern, I changed your sample to my actual need:

Sum ( Grades~filtered::grade_Type3 )

but I didn't know how to do that until you posted the little sample. And yes you get the award for understanding me the best. I wish I could send you a Subway coupon for that answer. When I got into Filemaker I could never have imagined it was such a large subject. I kind of wish Bento had come out 6 months earlier.

To me, learning FM is easiest taking apart little examples. So, I guess I'm the demo queen here, because I learn by seeing too.

I went for Average, because I thought that would be your next question, lol.

By the way, now that you are here online, do you have a recommendation for a book on Filemaker, one for some one with some experience, eg, writes simple scripts, is handy with layouts, and can do basic calc formulas? I bought the official Filemaker Training Series, but I've out grown it.

but I didn't know how to do that until you posted the little sample.

And in turn, that's why we asked YOU to post some kind of picture or mockup or more detailed description of what you were trying to accomplish. BCooney did a good job of reading your mind; but he shouldn't have to.

I did that- three times, and everyone got mad at me asking about something I wasn't sure about. Tough crowd here at Filemaker Forums.

Well, Andrew, I understand how someone might be frustrated at times but ...

THANKS! IT WORKS PERFECTLY! I wish I had tossed out using regular programming years ago. Filemaker is an amazing program.

And there are many more posts of yours where you say your assistance was outstanding! One post, where you don't get exactly the answers in exactly the precise format at precisely the exact time you expect it, doesn't a pattern make.

Stick around and relax ... there is no better place to get assistance. :smile2:

I did that- three times, and everyone got mad at me asking about something I wasn't sure about. Tough crowd here at Filemaker Forums.

No, you didn't. There are zero messages from you which include attachments.

Yes, it is important to provide enough descriptive detail so that people trying to help you have enough information.

BCooney did a good job of reading your mind; but he shouldn't have to.

Beyond it should have been "she" here, do we still lack the explanation, as to why these figures should be live calc'fields. It's similar to going to a SQL guru, and say "I want this, but without all the queries" ... the "Q" in the skillset - just looks buzzword'ish good. Modality sucks ... or does it:

http://www.smallco.net/RestrainYourself.pdf

(read at least the parts in italics - although the entire pdf is a good read)

Good grief, do I really have to distinguish between "Need to know" and "Nice to know" ?

What is required to acknowledge here, is even though spreadsheets exist as metaphor, doesn't it's behaviour necessarily rub off in a particular efficient way in database context. It boils down to choosing the right kind of tool to the task at hand.

BTW, could the template of hers be refined further down, the second TOG and the script is irrelevant ... and why not exploit the undocumented feature as well here - and use Edoshins crosstab approach using repeaters - to substantiate that we really wish to teach as many unhealthy methods as possible?

Welcome to the deep end of the pool!

--sd

Grades2.zip

Soren, thanks for the "she" (didn't know my pic was that bad, Bruce!)

I threw together the demo, and don't expect it to be perfect, Soren. I do hope, though, that it doesn't promote "unhealthy methods"!

What should be brought to attention, is that a sub-summary report is much more streamlined that "excel"-ing FM. Notice how one summary field provides all the averages for each assignment type. And so, Andrew, that's why this type of report was felt to be the best long-term answer to your requirement.

As for books, I own Love's FM8 book, and expect to buy Ray Cologon's new FM10 book when it's released (hoping for 10 for 10 demos).

In addition to Edoshins crosstab technique, I suggest visiting Ray's site for a beautiful demo of just how great sub-summaries can be. Link

Edit: Soren, I don't understand what you changed. It seems that you deleted the second TOG, and so, broke the sub-summary report.

Edited January 3, 200917 yr by Guest

And so, Andrew, that's why this type of report was felt to be the best long-term answer to your requirement.

I agree, Barbara. But some people on this thread are missing an important point ... we can't always provide the UI we want; we sometimes need to provide the UI which is requested by the business or the UI that we (or someone else) has built (and can't change it for a while). In addition, there are times that a portal displaying sub-summary outside is quite appropriate!

It is fine to suggest a sub-summary report. It is even fine to request additional information. But it is NOT fine to continue demanding a standard sub-summary report is the only way to go. Andrew has a portal, for goodness sakes! And he would like summary outside of the portal, that's all. I would bet that we ALL have had this need before.

People should assist him or move along once they've made their points and the points have been made. :wink2:

is much more streamlined that "excel"-ing FM

I'll hope this won't embarrass Comment, but it's exactly what I mean. The urge to do so, shouldn't perhaps be justified ... as long as the deployer are aware of the tradeoff...

But I do humbly believe that some elementary techniques comes sequentially before more sophisticated. If you as developer can't explain why ordinary subsummary reports won't do ... then?

--sd

I'll hope this won't embarrass Comment...

You MUST be kidding. You flatter yourself too much. :funnypost:

Edit: Soren, I don't understand what you changed. It seems that you deleted the second TOG, and so, broke the sub-summary report.

I used Comments discovery of summery fields behaviour seen a relation away, where the cross-tabbing surprisingly could be utilized. It's an attempt to abide to what LaRetta expresses this way:

we sometimes need to provide the UI which is requested by the business or the UI that we (or someone else) has built (and can't change it for a while)

I can however only feel sorry for someone tied up in a way - leaving no place for conscientious objection!

--sd

Now I see what you did. Interesting. Please note, I specifically wanted to include a sub-summary report (although redundant) for Andrew to evaluate as an alternate approach.