13 Replies Latest reply on Jul 6, 2017 7:15 AM by beverly

    Reporting on field matrix

    cnschulz

      Hello,

       

      Im not quite sure on where to start. I have a requirement to report on the status of Jobs. These jobs have 50 tasks that must be performed at certain weeks out from project completion. They are broken into 5 categories. Each task has 5 pieces if data associated with them. I need to produce a "dashboard" that lists each job and the completion of each category. When all tasks in a category are complete that column in the job report turns green. If its not complete and the task is overdue, it turns red.

       

      The process to do this is quite straight forward but if I map a field to each task I will have over 100 fields I need to manage!!! Im sure theres a smart way to do this with a portal but I cant think of how to "pivot" portal rows into columns in the report.

       

      I hope ive been clear and that someone can point me in the right direction to a design methodology.

       

      Thanks in advance.

        • 1. Re: Reporting on field matrix
          fmpdude

          Yep, design your database first --- before you jump into FMP.

           

          Post your database ERD here for better feedback. Once you have a good database design, implementation is much easier.

           

          (Note: FMP's Relationship Graph is not a database designer.)

          • 2. Re: Reporting on field matrix
            cnschulz

            This is a filemaker only solution. The database design will be impacted on filemakers ability to readily report on it. As i said, I *can* just whack 100+ fields in a table and hardcode the layout to my needs. I'd *like* to have a task db and portals however the dashboad layout seems impossible to reproduce... unless i run a script daily to update a dummy table with report data.

             

            Any suggestions welcomed

            • 3. Re: Reporting on field matrix
              TomHays

              cnschulz wrote:

               

              The process to do this is quite straight forward but if I map a field to each task I will have over 100 fields I need to manage!

               

              Start your design from: each task should be a separate record in a Tasks table.

               

              -Tom

              • 4. Re: Reporting on field matrix
                fmpdude

                Sorry if I wasn't clear: design is a generic activity.

                • 5. Re: Reporting on field matrix
                  cnschulz

                  Thanks thats my thinking as well. Im just unsure how to then produce a layot that has rows of jobs and colums of job categories (milestones) which are summaries of tasks accessed via a portal.

                   

                  <JOB> <CATEGORY1:DONE> <CATEGORY2:OUTSTANDING> <CATEGORY3:DONE>

                   

                  where the categories (milestones) are a sub-set of tasks within a job.

                  • 6. Re: Reporting on field matrix
                    TomHays

                    Specific recommendations on how to produce content on a layout for reporting will require specifics of your data model.

                     

                    I assume that you have tables:

                    Jobs

                    Tasks

                     

                    Is there also a table of Categories (with 5 records)?

                     

                    -Tom

                    • 7. Re: Reporting on field matrix
                      cnschulz

                      Thanks Tom,

                       

                      Table of Jobs.

                           -JobId

                           - OtherFields

                       

                      Table of tasks

                           - JobId

                           - Category (milestome)

                           - DueWeek (1-52)

                           - Note

                           - Complete (Y/N)

                           - Overdue??

                       

                      So the milestone only becomes complete when ALL tasks in that milestone are complete. I could write a daily script that calculates the current week number and tests is against currently open jobs, setting the overdue flag when required. I want to list each category (milestone) against each job as list it as "outstanding" if ANY of the tasks in that milestone are overdue.

                       

                      <JOB> <CATEGORY1:DONE> <CATEGORY2:OUTSTANDING> <CATEGORY3:DONE>

                       

                      Thanks again for your help with this.

                      • 8. Re: Reporting on field matrix
                        TomHays

                        If these 5 categories are the same 5 categories for every task (as opposed to tasks for a job having 5 categories out of a possible 25 categories spanning different types of jobs)...

                         

                        On a layout displaying the Job record you want 5 filtered portals of Tasks records for that job.

                        Each portal is filtered to display Tasks records in a specific category.

                        Each portal is sorted by the Tasks field Complete in ascending order so that "N" values will sort ahead of "Y" values.

                        Make each portal one row high and display the Complete field.

                        Do conditional formatting to change the color of the field depending on its value "N" or "Y".

                         

                        -Tom

                        1 of 1 people found this helpful
                        • 9. Re: Reporting on field matrix
                          cnschulz

                          Thanks. Brilliant. Sounds like it will work. Ill test this out first thing in the morning.

                          • 10. Re: Reporting on field matrix
                            philmodjunk

                            You might also research "cross tab" and "pivot table" here in the forum. There are a number of ways to pull your data into the matrix that you want.

                            • 11. Re: Reporting on field matrix
                              cnschulz

                              Thanks, I have done that. Its the "hack" where the portal field only displays one row sorted by Y/N that is key here. All other solutions dont quite fit the model as far as I can see.

                              • 12. Re: Reporting on field matrix
                                philmodjunk

                                I tend to prefer that approach myself, but several of the other methods can also be used as well.

                                • 13. Re: Reporting on field matrix
                                  beverly

                                  You might also research "Gantt chart" on this forum and elsewhere.

                                  Beverly