5 Replies Latest reply on Jun 2, 2015 3:29 PM by philmodjunk

    Is Join Table Required?

    sccardais

      Title

      Is Join Table Required?

      Post

      My database holds information about Employees, the Departments they are assigned to and their compensation history.

      I want to create a report showing payroll costs by department by Year and / or Quarter starting with a date entered by the User and I want the report to compare quarterly values side by side.

      Challenges:

             
      • For comparison purposes, I would like the data to appear in columns. e.g. 2010 > 2011 > 2012 OR  Q1-2014 > Q2 -2014 > etc.
      •      
      • Employees may need to appear in two deparments for the same quarter if they move from one department to another mid-quarter.
      •      
      • Employees compensation rates may also change mid quarter.

      Questions

      I'm assuming I'll need three tables (Departments, Employees and Join_Employee_Transactions.) The join table will record effective dates of department reassignments and effective dates of compensation changes and amounts. What type of relationship do I need between the tables to account for mid-quarter changes?

      What is the best way to create the columnar report comparing Quarters side by side? Should this be done in separate fields and if so, in which table? Might it require a 4th table with dedicated calc fields based on the other tables?

      I'm at a loss about structuring this but fortunately I have the opportunity to set this up right from the start.

      Thanks for any and all help.

      Using FMPA 14.

        • 1. Re: Is Join Table Required?
          philmodjunk

          What type of relationship do I need between the tables to account for mid-quarter changes?

          You need the join table and it would link to Employees by employee ID and to Departments by Department ID.

          Your columnar report would be a something called a "cross tab" report--something that you can research here and in other forums. There are multiple methods for producing such a report. A series of one row (often filtered) portals in a list view is often used in such a report. I will note that you haven't indicated what one row of your report will represent. One department or one Employee? If one Employee, what would you want to see for employees that worked in more than one Department in the same specified time interval?

          • 2. Re: Is Join Table Required?
            sccardais

             I will note that you haven't indicated what one row of your report will represent. One department or one Employee? If one Employee, what would you want to see for employees that worked in more than one Department in the same specified time interval?

            I envision this as a sub-summary type report with a section for each department and individual employees indented beneath each department showing the employee's name and compensation for the time period. If the employee's compensation increased mid-cycle, the total compensation should reflect the change for the portion of the period in which it was changed.

            If an employee changed departments mid quarter, their name and compensation should appear in both departments with the correct compensation showing for each. For example, assume an employee earns $2,000 per week. This person transferred from Customer Support to Sales effective week 7 of a 13 week quarter. The report would show 6 weeks ($12,000) in Customer Support and 7 weeks ($14,000) in Sales for the Quarter. Thereafter, and until the next change, that employee would appear in the Sales department only.

            As you said, the join table would link Departments to Employees by DeptID and EmpID. I'm assuming I would create a global g_DEPTS_startdate to hold the start date for the report. This would be converted to a Quarter value (Q1-2014, etc)

            To create the report, I think the Join table would also have to include an EffectiveDate and two additional fields. CompRate and DeptID. What would the relationship between DEPTS and JOIN be to calculate the cost properly? Using my example above, assume John Doe moves from Customer Support to Sales effective Feb 9, 2015 (week 7). Customer Support should show 6 weeks of John Doe and Sales should show 7 weeks for Q1 - 2015.

            Thanks for your help.

            • 3. Re: Is Join Table Required?
              philmodjunk

              What you describe sounds like a summary report layout based on the Join table with a self join relationship to another occurrence of the join table in order to set up the filtered portals.

              Employees--<Employee_Department>-----Departments

              Employee_Department -----<Employee_Department|SameEmpDept
              Employee_Department::_fkEmployeeID = Employee_Department|SameEmpDept::_fkEmployeeID AND
              Employee_Department::_fkDepartmentID = Employee_Department|SameEmpDept::_fkDepartmentID

              A one row portal to Employee_Department|SameEmpDept can be filtered to only show a record if the range of effective date values put's in the the time interval (year or quarter) specified for that column.

              To get department sub totals, you would use this relationship:

              Employee_Department -----<Employee_Department|SameDept

              and match only by department ID. A one row portal to this TO can use the same basic filter, but summary fields defined in the Join table can be put in the portal row to show Totals, Averages, Max, Min... type aggrgate values for all employees in that department who were working for it during the specified time interval.

              • 4. Re: Is Join Table Required?
                sccardais

                Phil

                Just a quick Thank You for your fast and detailed reply. I haven't tried it yet but I sure do appreciate your taking the time to reply in such detail.

                Scott C.

                • 5. Re: Is Join Table Required?
                  philmodjunk

                  Cross tab reports are a bit of a challenge as there are a lot of "moving parts" that all have to be just so to get the needed results. Take it one step at a time. If you can get one column of data displaying the way you want it, you can the make copies of that portal and just edit the portal filter expression to get date for a different time period.