3 Replies Latest reply on Mar 20, 2014 9:01 AM by philmodjunk

    Task/Subtask report where not all task have subtasks.

    PhilHarper

      Title

      Task/Subtask report where not all task have subtasks.

      Post

           Guys I apologize in advance for this because I know this has to be a "nobrainer". But I’m struggling. The problem is simple, I have two tables, Task and Subtask, I want to create a list report showing task as the heading and its child subtask listed below. Thought this is simple using a subsummary report on the Subtask table, putting the “Task” in the Subsummary part, but of cause if a task does not have any subtask it will not show up in the list because the report is based on the Subtask table. The only thing I can think of is creating an automated merged table as each record is created and basing the report on that. Thoughts? Cause I could do in a layout, but then the tasks with no subtasks would have big gaps in the list.

        • 1. Re: Task/Subtask report where not all task have subtasks.
          philmodjunk

               It's not a "no brainer" and illustrates a key limitation to FileMaker Summary reports. The options for working around it aren't all that great:

                 
          1.           Create a "dummy" sub task for each task that does not have a sub task.
          2.      
          3.           Base your report on Tasks and use a Portal to list the SubTasks. (Make the portal many more rows taller than you expect to ever need for the sub tasks and set it to "slide up" , "re-size enclosing part".)


               Key facts about sliding layout objects:

                 
          1.           It's only visible in preview mode and when you print/save as PDF...
          2.      
          3.           Sliding fields will shrink but not expand.
          4.      
          5.           All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
          6.      
          7.           Objects in headers and footers will not slide.
          8.      
          9.           Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
          10.      
          11.           Fields will slide up only if Top alignment is specified for it and will slide left only if Left alignment is specified.
          12.      
          13.           Consistent side borders are difficult to achieve with sliding fields.
          14.      
          15.           In FMP13, hidden objects (”Hide object when”) will slide/resize.
          • 2. Re: Task/Subtask report where not all task have subtasks.
            PhilHarper

                 Thank you Phil, much appreciated advice. I have considered the first option of creating a dummy field but thought that was a cheat that might come back at me later down the line, guess I could force some default data like “No subtasks” just to give it some definition.

                  

                 However, I am still interested in the idea of creating a table that would be used for reporting only, merging both the task and subtask tables, this would be handy for all types of reports related to the data in these tables. My thoughts were to:

                  

                 - create a table with matching fields of both tasks and Subtasks; IE, Table name: Task_Subtask

                 - on creation of a new record in either the Task or Subtask tables, Create a new record in Task_Subtask  

                 - evaluate the ID field of the new Task or Subtask being created by the user (via a calculation) to identify the record in Task_Subtask as a Task or Subtask and get the remaining information in that record.

                  

                 I should then have a table Task_Subtask that combines all the records in both task and Subtask and use that as my reporting table.

                  

                 What would be even better would be to automate the creation of a temporary table based on a found set of Task  and Subtask records and mearge them, though have not seen any means to do this, though I am very much a beginner in terms of DB’s and FMPro.

            • 3. Re: Task/Subtask report where not all task have subtasks.
              philmodjunk

                   It's possible, but a complex solution to this issue and one that will be pretty slow for large sets of records (that may not be an issue for your project).

                   It does have the advantage that you aren't "littering" your table with dummy records that then have to be omitted from reports, portals etc...

                   Creating a new table via script is possible in FileMaker if you use Import Records with the New Table option for the target table but it's not really a practical option as your script can't create a layout to use with that new table so you won't have any way to set it up for a report. You also cannot delete a table via a FileMaker script so it would not be a temporary table.