Trying to solve a problem with moving data from one list to another. I have 2 lists.
1) a project list that contains Project info (project ID which is text, name, lots of details, and an "Assigned to" field which is a person/group lookup).
2) an Hours list that stores forecasted and actual hours worked against the various projects, by month/year. This list is keyed to the first via a lookup field referencing the Project ID.
Users will create many lines of hours for each project. One line for each month's hours. I currently have a webpart page that lets them select a project in one web part, which is passed via a row filter to a second web part where they can enter their hours for that project.
The catch is, users want to be able to view all hours for all projects assigned to them, for a single month at a time. The logical way to do this is to send a set of rows as a filter to the 2nd web part where they can then filter by month, but this isn't possible (far as i can tell). In straight up SQL this action to join the lists is trivial...
An alternate solution that I'm trying to figure out, is to pass along the Assigned To field to the Hours List. This would let me sort by the assigned to field (so they only see their own project's hours) and then they can sub-filter by month. The catch here is A) sharepoint won't let you pull a lookup field along with another lookup field as a Projected Field (e.g. when the user enters new hours and picks the project field the hours relate to, you can pull along the project name but not the assigned staff) and B) I can't figure out how to make a workflow pull the same data with a workflow and C) the workflow would have to fire off based on changes to the hours table.
What i think needs to happen is: User enters new hours in the hours table (via a web part). They select the project it relates to (via the lookup field) and hit save. Workflow launches due to item creation and compares the project ID in the hours table to the Project IDs in the project list (note the project ID is not the sharepoint issued "ID" field) to find the right project. Workflow grabs the assigned to field from that project list item and copies into the assigned to field in the hours table.
But I can't figure out how to make this work.