1 Reply Latest reply: Apr 26, 2012 9:19 AM by Marc Livingston RSS

    Maintain employee info in one company, when employee's main company is different.

      I have a situation that I am trying to overcome.

       

      First off, our software supports multiple companies in the same database where it is defined by a company field in every table of the database.

       

      For this example I will use 3 Companies, A,B, and C where A is the main company, B and C are other acquired companies.

       

      We maintain Employee records in company A. All employee fields like hire dates and inactive dates are stored here. They also exist in the company they will be working at. So say employee 1 works at company C. Their hire info is only recorded in company A.

       

       

      A sample row for the above could be:

       

      Employee Table:

      CompanyEmployeeHire DateInactive DateStatus
      A11/1/2012
      Active
      C1

      Active

       

      So when I do a table showing Head count by month using hire date, employee 1 only shows up in company A, when I really want to see them in company C. I was to solve this so that they could show up in the headcount for company C when it is selected by making a new table with only employee and the hire and inactive dates and linking that back into the employee table by Employee. This gives me the below:

       

      CompanyEmployeeHire DateInactive DateStatus
      A11/1/2012
      Active
      C11/2/2012
      Active

       

      Now the issue I have is that when I display Company Headcount, with a dimension of Company, they show up in each company. Company C will show correctly (Except for people who are mainly working at company A and B but were created in C to do some work). Company A will always however show the total throughout, and not only those whose main company is A.

       

       

      I know which company is their main based on another field Plant like:

      CompanyPlant
      A1
      A2
      B3
      B4
      C5
      C6

       

       

       

      What would be a good way for me to get the correct data and display from this? As I said they can do work in more than 1 company, so I can not do

      if(match(Plant,'1','2'),A) as Company to link the hire data in because the records where they work at company B will be labeled as such:

       

      CompanyEmployeePlantHours
      C165
      C163
      B167
      A168
      C162

       

      Anyone have any Ideas?