WSS 3.0 Workflow - How to get data from joined lists

Question

I am having problems with WSS 3.0 Workflow using joined lists. Here is the description:

I am creating a simple work-holiday request/booking app. This is just for internal department use so that staff can book annual leave and have it authorised by their respective manager.

To that end, I have two lists: The first list is called 'Staff' and contains the person's name, email address, and manager name. The manager name is populated by a drop-down on the same list.

The second list is called HolidayRequest and has fields for name (a drop-down based on the staff list), start date and end date.

When ever a new entry is made to the HolidayRequest list I want to trigger a workflow that will send an email to that person's manager to notify them for approval. The problem I have is getting the manager's name and email address into workflow. The problem as I see it is that this information is obtained through a joined list:

       HolidayRequest                          Staff
StartDate, EndDate, Name  ----------------->   Name, EmailAddress, Manager
                                                 /\                   |
                                                  |                   |
                                                  +-------------------+

So what I need is for the workflow to use the Name field from HolidayRequest, to locate the record for the same name in the Staff list, and then get the manager's name from that record, get the manager staff record and thereby the manager's email address. Its not exactly rocket-science, but I just can't figure it out in workflow (using Sharepoint Designer).

I guess the first question is whether workflow is the correct tool for this, and secondly if it is, then is there a way I can achieve what I need?

Thanks for reading.

Answer 1

Well, it turns out that I was on the right track (as I thought) but hadn't quite got my head around the way that Workflow does it's lookups. Here is the answer:

Suppose that you have two lists "HolidayRequest" and "Staff". The HolidayRequest has fields for "Name" (which is a lookup on the Staff list), StartDate, and EndDate. The Staff list has "Name", "Email", and "Manager" (which is a lookup on the Staff list - a reference back to the same list but to a different record).

Because it involves a double lookup it is necessary to create a Workflow Variable to store the intermediate result. So I created Variable:ManagerName to store the manager name from the first lookup and then used that as my key for the second lookup to extract the email address for the manager.

My key to finally figuring the workflow lookup was to understand how it does the join - and to be honest I don't think that the "Define Workflow Lookup" in SPD is very clear. The DWL has two sections - one titled "Lookup Details" and here you tell it where you want the data to come from (a Source list, and a field in that list), and the second section is titled "Find the List Item" (this is where I was going wrong) - you use this to create the link between the two lists. The entry "Field" is where you tell it the field in the Data Source list (so Staff:Name, for my example), and the entry for "Value" is where you give the corresponding field from the other list (so, "Variable:ManagerName" in my example).

So now, when a new Holiday Request is received the workflow uses the name from the HolidayRequest as a lookup into the Staff list, extracts the manager name from the staff list and then uses that to do a second lookup on the staff list - this time for the manager - and extracts the email address.

Perfect.

Now I can use that email address to request the manager to authorise, or reject, the request.