Linking one-to-many (Employee to Salary History) lists in SharePoint

Question

Here is my issue:

[Edit]: Alright, this is almost turning into a separate question at this point...my apologies. To cut right to the chase, here is my business requirement. I have an Employee record (an item in a list) that contains a Salary field. I need to be able to view a report of the Employee's salary history.

That's it. In my mind (which is definitely coming from a SQL/database background) this meant a one-to-many table (one Employee with many Salaries). Turns out this is not so simple in SharePoint since I would have to filter the Salary field to only display Salaries from the SalaryHistory list for a given Employee.

My new-ish question is what's the best way to do this? Is it possible to use only an Employee list with a Salary field and then somehow view or display the history of that particular Salary field? For example, if the Salary was changed from 50,000 to 60,000 for Employee 00001, can I view a report/page of some sort that shows the history of this Salary field?

[/Edit]

I want to convert an existing MS Access/VBA legacy system to SharePoint. The existing system is fairly simple: a database of Employees, with typical Employee information. The one issue I am having trouble with is that an Employee has not only a Salary, but a Salary History. What this means is that a supervisor should be able to view an Employee's Salary History, and not just the current salary of the Employee.

In my mind, this is a one-to-many relationship: an Employee table linked to a Salary History table (the one-to-many is that an Employee can have many Salaries over a period of time).

[Edit]: Sorry if I'm a little vague, I'm still trying to wrap my head around the particular implementation of these requirements. I was only given a screenshot of the Access application and told to make a (roughly) equivalent SharePoint site.

A little more info: I have a SalaryHistory list that contains an EmployeeID lookup, so I do have a list of Employee salaries. What I really would like is this:

In the main Employee list, when viewing an existing Employee, I would like to only see the Employee's current (i.e. most recent) Salary. If this Salary gets changed and saved, then a record should be added to the SalaryHistory table. When creating a new Employee, upon Save, a record is added to the SalaryHistory list with the new Employee's Salary.

[/Edit]

What's the best way to implement this in SharePoint? I have tried using InfoPath as well as some customized code, but I can't seem to get it correct. In my mind, this is a relatively simple concept, but I'm having trouble taking it from concept to a concrete SharePoint solution.

Any ideas?

Thanks,

Glen

Answer 1

The first way I can see to do this is to create a custom newform and editform and perhaps a viewform, too, which interrogates the Salary History list for all items with a given employee ID, and then lays that out in a dynamic table that would allow you to either edit existing entries or add a new one (thinking about it, you could probably get away with using the same form for new and edit and set it up that way in Designer).

Depending on the limitations of your farm, you could either accomplish this server-side or client-side (you didn't state if you had any such limitations but I wanted to point out that either way was possible). You'd need to call webservices on a client-side application, of course, but these are pretty easy to use (just use SPServices if you have 2007 or 2010 or the built-in MS webservices if you're using 2013). I would suspect you would also want to use some kind of validation on new item creation to make sure that the user isn't doubling up an already extant item.

You could also just create a view that does the filtering on it that you're looking for (or group list items by Employee). The one thing that SP doesn't like to do is give you actual access to the values in those lookup fields; that is, you can make 3 fields change according to a Lookup field (at least in 2010 and 2013) but what you can't do is take, say, a "salary multiplier" number from that field and use it in a calculated column on the 2nd list. If you go this route, validation is still strongly recommended; luckily, client-side validation is very, very easy thanks to the built-in PreSaveAction() function that you can override in your script (simply have it return false if you want to deny a save and true if you want it to go through).

Oh yeah... or if you're using 2010 or 2013 you could just turn on versioning for the list. The advantage to this is that you wouldn't have to create a second list at all; if you wanted to change a salary, you'd just change it and the list itself would keep a record of the old value. The disadvantages to this method are:

1. It will store versions based on any changes you make to that item (which you can then delete, but you have to know how to delete previous versions, and there's not a way to renumber versions easily once you've gotten rid of the ones you don't want).
2. Point #1 also makes it a lot harder to curate. I guess you could write an event receiver that automatically gets rid of previous versions that don't change the item you want to track, but that seems like even more extra work than what I elucidated above.
3. It can make a list significantly larger (for instance, a list with even 1,000 items on it and an average of 10 salary changes per item literally has its size increased tenfold).
4. It's not nearly as easy to get at the different versions compared to just having them represented on a list with views you can then edit/filter. If you need to make reports out of this, there can be a significant ramp-up in complexity.
5. You may find that it's too easy for people who can look at versions to remove prior versions and, what might be worse, revert changes to a previous one.

Personally I think I would go the route of making the filtered view and then, if necessary, custom new and edit forms with validation added because a. it's probably the simplest and least time-consuming option and b. it leaves non-developers with the largest amount of freedom to make changes in the future, but for the purposes of exhaustiveness I've gone ahead and presented all three options.

Answer 2

The salary history list should have a lookup column where you select the corresponding Employee

Answer 3

How about a list with a column that relates to the User's Id as well as a column for the salary amount, start date, end date, etc.? Then the salary history could be retrieved via a LookUpMulti type column (LookUp column with multiple values). I can think of a few different approaches here depending on the desired end result.

For instance, without hooking into a multi-valued LookUp column you could interface with this data by writing a web part where you could query by username or whatever other piece of information you want to supply to produce the salary history.

Answer 4

Salary List can have a lookup to Employee field in the Employee List. The Salary List can have Employee Name, Salary Amount and Time Period as its fields. Then when an Employee's details are viewed, all salary details from the Salary List can be shown filtered by the Employee name.

Does this help?