1 2 Previous Next 19 Replies Latest reply on May 31, 2012 12:40 PM by philmodjunk

    Count of tasks per different levels of a sorted report

    JessicaFiorini

      Title

      Count of tasks per different levels of a sorted report

      Post

      Hi,

      I have a database with three main tables. Lesson-->Lesson-Task<--Task. Lessons are organized by several layers of information: Domain, Level, Week, Lesson. I have built a report that organizes the info thusly:

      Sub-Summary part: Domain

      Sub-Summary part: Level

      Sub-Summary part: Week

      Sub-Summary part: Lesson

      Sub-Summary part: Task name, conatins portal to the task record in the Lesson-Task table

       

      What I am trying to do is have stats on the completeness of tasks on the Domain, Level and week levels. So, I'd like to say that for the whole Domain of geometry, x% of lessons have open tasks assigned and x% of tasks have been recieved. The same thing for Level. So Geometry level 3 has x% of lessons with tasks assigned and x% of tasks have been recieved. And lastly, Geometry level 3 week 2 has x% of lessons with tasks assigned and x% of tasks have been recieved. The task status is indicated by a Lesson-Task:status field that I include in the portal. I will also need to note how many lessons do not have any tasks assigned.

      Does this make sense?

      I have tried my hand at a slew of calc fields but failed gloriously. Thanks.

        • 1. Re: Count of tasks per different levels of a sorted report
          philmodjunk

          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.

          • 2. Re: Count of tasks per different levels of a sorted report
            JessicaFiorini

            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. 

            • 3. Re: Count of tasks per different levels of a sorted report
              philmodjunk

              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.)

              • 4. Re: Count of tasks per different levels of a sorted report
                JessicaFiorini

                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.

                • 5. Re: Count of tasks per different levels of a sorted report
                  philmodjunk

                  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----<AllTasks>----Lessons 2

                  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
                  cReceivedFlag respectively.

                  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.

                  • 6. Re: Count of tasks per different levels of a sorted report
                    JessicaFiorini

                    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.

                    • 7. Re: Count of tasks per different levels of a sorted report
                      JessicaFiorini

                      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.

                      • 8. Re: Count of tasks per different levels of a sorted report
                        JessicaFiorini

                        Hey Phil,

                        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".


                        Thanks!

                         

                        • 9. Re: Count of tasks per different levels of a sorted report
                          philmodjunk

                          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.

                          • 10. Re: Count of tasks per different levels of a sorted report
                            JessicaFiorini

                            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?

                             

                            Thanks!

                            • 11. Re: Count of tasks per different levels of a sorted report
                              philmodjunk

                              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?

                              • 12. Re: Count of tasks per different levels of a sorted report
                                JessicaFiorini

                                That's a typo. Sorry. They are the same field.

                                • 13. Re: Count of tasks per different levels of a sorted report
                                  philmodjunk

                                  If you put cOpenSum in the body of your layout, do you see the expected values?

                                  • 14. Re: Count of tasks per different levels of a sorted report
                                    JessicaFiorini

                                    Yes. I get the correct count of open tasks. The part I seem to be missing is the percentage.

                                    1 2 Previous Next