Are "open" and "Received" the only possible values in the status field? I'll assume that this is not the case. (You can simplify this approach if these are the only possible values.)
Define a pair of calculation fields as:
cOpenFlag: Status = "Open"
cReceivedFlag: Status = "Received" //modfiy the text in quotes to match the actual values entered in this field.
Then summary fields that compute the "total of" these two fields will give you the total number of records with each status. If you based your layout on Lesson-Task, you could set up calculation fields such as these for computing percentages at the Domain level:
GetSummary ( sTotalOpen ; DomainField ) / Get ( FoundCount )
GetSummary ( sTotalReceived ; DomainFIeld ) / Get ( FOundCount )
The calculations for the other levels are the same except you specify a different "break" field in place of "domainfield". The fields you specify there are the fields that are selected as the "when sorted by" fields for each sub summary part.
WIth the layout based on Lesson-Task, the last sub summary part would become the body layout part instead of a sub summary part.
The only drawback to this report is that it will leave out all lessons that do not have any task records in your report's found set.
Thanks, Phil. I do need to include the lessons without tasks. They are considered unassigned and the assignment of tasks is kind of a task in itself. As all these lessons will have an assignment eventually, do you think it makes sense (to make your suggestion work) to create a task in each with the status "unassigned?". There would be ramifications in the script that is used to create multiple tasks.
A dummy record in tasks is an option to consider.
The alternative may be a one row filtered portal with summary fields that use the "fraction of total" option on the cOpenFlag and
cReceivedFlag fields com compute your percentages. The filter expression would need to replicate the logic that determined what lesson records are in your found set and which also filters by "domain" to get the correct set of related task records. Depending on what criteria you may need to specifiy for the found set of lesson records this could be easy or complex to implement.
What is the typical criteria you specify when pulling up your found set of records for this report? (Date range? Subject? or ???)
Are you using FileMaker 11 or 12? (Must have at least version 11 to use filtered portals.)
I am using FM 11 pro advanced.
I am not really specifying any criteria at this point and am just sorting the info. I am currently basing the layout on Lessons, though. The portal suggestion sounds interesting.
at this point
But can you forecast how you might need to use this report? Such as all lessons for a specific text book or ???
It's not a trivial thing to deal with if we go with filtered portals.
You may have this set of relationships or you may need to add one more occurrence of Lessons to use for this:
Lessons::AnyFIeld X AllTasks::AnyField
Lessons 2::LessonID = AllTasks::LessonID
Using the X operator matches any record in Lessons to all records in Tasks. This produces a relationship where a portal to AllTasks will list all the tasks records in your tasks table until we employ a portal filter to reduce the number of records.
The sole purpose of Lessons 2 is so our portal filter can filter Tasks records by a value in one of your "level" fields such as "Domain".
You can then define sFractOpen and sFractReceived as "fraction of total" summary fields that refer to cOpenFlag and
You can then put a one row portal to AllTasks in your Domain sub summary part and put sFractOpen and sFractReceived in the portal's row. The final requirement is a portal filter expression such as:
Lessons::Domain = Lessons 2::Domain //This is where you'll have to add additional details once you need a report that does not include all records in teh Lessons table.
This report is a holistic view on all the tasks assigned to all the lessons all the time. This will not change (theoretically, never know what moves the boss might make). I'm interested in breaking down the stats by domain, level and week.
How would the new Lesson 2 table occurence gel with my current database setup? I've attached a screenshot.
You know, Phil. I think I'll go with option a. I think a task per lesson with the status unassigned will work just fine. All lessons need the content created as the first task, so I think it's ok to have the records.
I have a question. In the calculation "GetSummary ( sTotalOpen ; DomainField ) / Get ( FoundCount )" Where does the found cound happen? I don't perform a find in the report. Is that ok? Should the calculation be "GetSummary ( sTotalOpen ; DomainField ) / Status ≠ "Open".
All table occurrences have a found set whether you have performed a find on a layout that refers to them or not. You need the total count of the records being summarized. Keep it as it is.
Ok. Thanks for explaining. But I can't seem to make a value appear in the field. This is my setup:
From the table lesson-task I have a report with the following layout parts:
Sub-summary part sorted by Lessons::DomainName
Sub-summary part sorted by Lessons::Level
Sub-summary part sorted by Lessons::Week
Sub-summary part sorted by Lessons::LessonDay
Body part that displays task information.
In each sub-summary part there is the field that I am sorting by. So Sub-summary part sorted by Lessons::DomainName Lessons::DomainName, etc.
My calc and summary field is this:
cOpenFlag = Status = "Open"
cOpenSum= Total of cOpenFlag
OpenSummary = Unstored, = GetSummary (cOpenSum; Lessons::Domain) / Get (FoundCount)
I placed OpenSummary in the top Sub summary part and then sorted by domain. Have I gone wrong somewhere?
It may be a typo, but I see references to "Domain" and also to "DomainName". Are these two fields or just one field with part of the field name omitted by accident?
That's a typo. Sorry. They are the same field.
If you put cOpenSum in the body of your layout, do you see the expected values?
Yes. I get the correct count of open tasks. The part I seem to be missing is the percentage.