6 Replies Latest reply on May 15, 2013 9:38 AM by FileMakerNovice

    Creating a single instance table from an existing table

    FileMakerNovice

      Title

      Creating a single instance table from an existing table

      Post

           Hello all,

           I have a problem that I have a direction that I think I need to go but would like to know if there was another option:

           My database has a list of employees that is created upon startup.  The list of employees is created by viewing an external data source (Filemaker).  The external data source I'll call it "Data",  the local file, I'll call local.  The data file has a list of employees, manager names, email addresses, etc.  In addition, it has a boolean field called "include".  When my database is opened, it does a search for "include" and adds all employees to the local empoyees table.

           A little more detail about my local file...  It provides a ranking of employees.  The local employees links to multiple metric tables, such as "Sales".  The local employees table is linked to these metric tables by employee id.  The local employees table then has calculation fields that provide totals.  It then grades the employee and puts a score that I rank.  

           I am now wanting a manager summary of all the employees underneath him/her.  Previously, I used summary fields to gather this information but this limits the way I can sort that report.  I would like to be able to sort by Sales, TimeOnCalls, Score, etc.  

           My thought is that I need a manager table that is cleared and then created upon launch.  Something similar to http://help.filemaker.com/app/answers/detail/a_id/3441/~/finding-and-deleting-duplicate-records-in-filemaker-pro

           Am I going in the wrong direction?  Is there an easier way to this?  My basic goal is to have a manager summary that I can sort by their various metrics.

        • 1. Re: Creating a single instance table from an existing table
          philmodjunk

               I think you are going in the wrong direction. I don't see how using summary fields puts any significant limitations on how you sort your records.

          • 2. Re: Creating a single instance table from an existing table
            FileMakerNovice

                 I did have the feeling like I'm missing something.  Thanks for the response.

                 Currently, doing summaries provides two hesitencies for me:

                 1.  Time to load.  Doing summaries would require 21 summary fields and 3 calculations on these summary fields.  
                 For instance, we have Customer Surveys.  To get a manager's employee average, I would need to have 2 summary fields and 1 calculation.  I would have a summary for survey count, positive survey count and a calculation for good/count.  Currently, the employee table has a customer survey average but just doing a summary of this does not factor in that not all employees have the same count of surveys.

                 I will test out how long it takes for these summaries to load but I have a feeling that it would take several minutes to display the layout.

                 2.  Sorting capabilities.  My current layout is using the employees table and has a subsummary that displays the manager metrics.  If I sort by anything other than manager, it will only display a list of employees.  This is what I meant on my limitations on sorting.  If I wanted to sort by customer surveys, to see which manager's team had the most surveys, it would only show the employees.  I can do a sort by manager then surveys but this sorts by manager name and then the survey count for that team.  Would I need a subsummary part for all 21 of the summary fields?

                 Basically, these two reasons was why I was thinking of creating a manager table with it's own fields would be fastest and most sortable.  Thanks again for the help.  I really do appreciate it.  If you can put me on the right path I would be much appreciative.

                  

            • 3. Re: Creating a single instance table from an existing table
              philmodjunk
                   

                        To get a manager's employee average, I would need to have 2 summary fields and 1 calculation.

                   Why? You don't need a "Total of" summary field, a "Count of" summary field and a calculation field to divide the total by the count. Why not use an Average type summary field and then you have one summary field and no calculation field needed to get that average.

                   From your description, I don't see why you would need 21 summary fields.

                   2. Open the sort records dialog box and take a look at the bottom of the dialog box. There's an option there that permits you to reorder your results by a summary field so you can group your records for the sub summary totals but also use the sub summary totals to determine the order in which those sub totals are listed.

              • 4. Re: Creating a single instance table from an existing table
                FileMakerNovice

                     Let me start by explaining why a summary of just the average would not work.  Let's say that there are 5 employees on the team:
                     Employee     Survey Count         Positive Responses              Average
                     1                              5                                    4                                        90
                     2                              10                                  6                                        60
                     3                               5                                    5                                      100
                     4                               7                                    7                                      100
                     5                               6                                    4                                       66

                     So... if we did a summary-average of "Average", it would get 83.2.  The correct answer would be the sum of "Positive Responses" divided by the sum of "Survey Count":  78.8.

                     The customer survey metric is the simplest.  There are several other metrics that compose the 21 fields that I mentioned earlier.

                     Your #2 is a great tip.  I have never used this but I expect to use it in the future.  Thanks for this.

                     For my solution, I ended up creating a table of managers on startup and did all calculations in that table.  It works flawlessly.  There is the limitation of managers changing throughout the year but a quit and relaunch of the database will resolve.

                • 5. Re: Creating a single instance table from an existing table
                  philmodjunk

                       Check out the "weighted average" option. I think that would produce the results you want.

                  • 6. Re: Creating a single instance table from an existing table
                    FileMakerNovice

                         AH-MAZING!  That works out great!  I'm going to work with it a bit but I think that may have been one of the things I was missing.