Before I describe what I did, let me point out that this might not be the best way (or even a sane way) to solve this problem. Regardless, it worked for me and I wanted to record it for posterity.
I have a SharePoint list (named “MLD” for short) that has two lookup columns, as shown here. Each one has a supporting list that the users maintain.
Originally, there was no connection between Release Lookup and Project Lookup, but I realized that Projects are tied to a Release (i.e. Everything in Project X will be in the same release). To support that, I added a Release column to the Projects list. I now had Release being set in two places, and maintaining the same information in two places is just asking for trouble. However, I didn’t want to remove Release from my MLD list, because I still want the ability to sort & filter by Release. What I needed was a workflow that would set the Release on MLD list items based on the Project that was chosen for that item.
I fired up SharePoint Designer (version 2007, which is the latest version compatible with my SharePoint site), and created a new workflow that would run any time an MLD item was created or updated. First, I created a variable to hold the ID of the selected project:
The next step was to store the Project ID from the current item in that variable, then look up the Release for that project in the Project Lookup list and assign that Release to the current MLD item.
