7 Replies Latest reply on Apr 1, 2015 11:18 PM by bigtom

    Need some advice for organizational hierarchy.

    gilbro

      Hi all,

      I'm trying to figure out what is the best way to represent the hierarchy of the organization in my employees data table that will make it easy to build reports.

      I saw some examples where we only see the employee number related to their manager number, which i don't find it useful when trying to make reports to all organizational levels (Vice-presidencies; Directions, Departments; Groups of employees...)

       

      So, as i know that SAP can generate reports for all levels as shown below, I thought it would be more simple to enter every organizational level (name and number) for each employee record.  What do you think would be the best practice?

       

      Employee first nameLevel1nameLevel1numberLevel2nameLevel2numberLevel3nameLevel3numberStructural Group NumberWork title
      PaulPresidency10001President
      MaryPresidency1000Vice-Presidency Finance20002V-P
      CarlPresidency1000Vice-Presidency Finance2000Direction of commercial accounts30003Director
      Sophia
      Presidency
      1000Vice-Presidency Finance2000Direction of commercial accounts30003Secretary
        • 1. Re: Need some advice for organizational hierarchy.
          DanielShanahan

          What kind of reports are you trying to create?

           

          Level#Name and Level#Number are redundant fields.  I would normalize the data.  If you absolutely cannot run the report you want from normalized data, you may be able to create a temporary table for the purpose of flattening out the data for your report.

          • 2. Re: Need some advice for organizational hierarchy.
            gilbro

            i want to be able to import new data or updated data from SAP to FileMaker.  So i think i need both, level number and level name if there are some organization changes in the company structure or group name.  If i put those informations in the employee record, isn't it easier to produce reports?

            • 3. Re: Need some advice for organizational hierarchy.
              gilbro

              I also want the users to be able to get reports from different levels without having to manipulate data.

              • 4. Re: Need some advice for organizational hierarchy.
                Benjamin Fehr

                How about a simple list, sorted by "StructuralGroupNumber" and others with Sub-summary area with Title of hierarchy levels?

                 

                Presidency

                               Paul

                   Vice-Presidency Finance

                               Mary

                          Direction of Commercial accounts

                               Carl

                               Sophia

                • 5. Re: Need some advice for organizational hierarchy.
                  DanielShanahan

                  gilbro wrote:

                   

                  i want to be able to import new data or updated data from SAP to FileMaker.  So i think i need both, level number and level name if there are some organization changes in the company structure or group name.

                  Right - I'm not saying don't include level number and level name.  I'm saying they belong in a different table in order to normalize the data.  There may be reasons NOT to normalize the data, but as a default, I like to start with normalized data.  So it might look something like:

                   

                  EMPLOYEE-----<EMPLOYEELEVEL>-----LEVEL

                   

                  Where LEVEL table has the fields LEVEL::number and LEVEL::name and 1000|Presidency, 2000|Vice President, etc. are records.

                   

                  gilbro wrote:

                   

                    If i put those informations in the employee record, isn't it easier to produce reports?

                  It depends on the kind of report you want, but normalized data gives you the ability to pull reports as well.

                   

                  The problem with your structure is that it can cause data anomalies.  For example, in your spreadsheet above, if you deleted the records for Carl and Sophia you would lose all information on 3000|Direction of commercial accounts.

                  • 6. Re: Need some advice for organizational hierarchy.

                    In this example you could create a calculated field that would take each of the numerical designation fields and rank them.  Level1 x 1000 x 1000 + level 2 x 1000 + level 3  If this number becomes two large for FileMaker to sort then adjust the primary numbers accordingly.  While this is good for sorting its not good for creating a hierarchical report.  Why can't you just create a list break for each of the number categories?  Use the number fields for a report category?