Lookups empower your workflow to display dynamic data. When a workflow runs, the lookups in that workflow can retrieve data from different data sources such as lists, libraries and the workflow data. Lookups can also create a customized end-user experience for people using the site.
This article provides some conceptual information about lookups and explains how to define workflow lookups. Also find some tips on working with lookups and ensuring unique lookups.
What makes workflow lookups possible is data binding, a feature in Microsoft ASP.NET technology that is leveraged in Office SharePoint Designer 2007. Data binding allows the linking of data across different data sources in a database. In Office SharePoint Designer 2007, data binding in workflows enables you to retrieve data from data sources such as lists, libraries, and workflow data such as variables and initiation parameters. Workflow lookups retrieve data from a data source at a workflow’s run time and can use the retrieved data to perform some other workflow action. Lookups create the connection between the data source and the workflow.
You can insert a lookup to a current item, workflow data, and other SharePoint lists and libraries.
|You can insert a lookup to…||Description|
|Current Item||Refers to the item that started the current instance of the workflow. Choosing this option enables you to select a value from the item that the workflow is currently running on.|
|Workflow Data||Refers to a workflow variable that is a part of the current instance of the workflow. Choosing this option enables you to use workflow variables and initiation parameters in the current workflow.|
|Other Lists and Libraries||You can also retrieve data from other lists and libraries in the current SharePoint site.|
Lookups can also provide a customized end-user experience for site users. For example, suppose that you are a content author who submits documents to the Shared Documents library in your team site. You want a workflow to send an automatic e-mail message notifying the appropriate reviewer each time a document is uploaded. By adding lookups to the e-mail message in the workflow, you can create a customized end-user experience. This means that whenever a document is uploaded, the workflow will send an e-mail automatically to the appropriate reviewer with the name of the document in the e-mail body.
The following illustration highlights some of the places in the e-mail message where lookups could be used.
After replacing the highlighted areas with lookups, the e-mail message now looks like this.
Note how the lookup entries appear in the e-mail body. After you have defined the lookup, it is displayed in the format [%Data source:Field name%] in the place where you entered the lookup. So if you defined a lookup where the data source is Shared Documents and the field from which you want to retrieve the value is Reviewer name, the lookup entry will display as [%Shared Documents:Reviewer name%].
Note The lookup entry does not display the square brackets [ ] and the percent icon % on the To:, CC:, and Subject lines of the e-mail message.
When a document is uploaded to the Shared Documents library, the reviewer will receive an automated e-mail that includes the name of the document that was uploaded. When the workflow runs, the lookups retrieve the data from the various data sources, and that data appears in the e-mail message.
You can think of a SharePoint list or library as a table. Every table is composed of rows and columns.
For a workflow lookup to retrieve a specific value from a list, you need to specify the field (meaning the column) and the item (meaning the row).
A workflow lookup can retrieve a value from any list or library in the current site, but it needs this information every time: which field (column) and which item (row).
If you are familiar with relational databases, such as Office Access 2007 or Microsoft SQL Server, it might be helpful to think of each list and library in a site as a separate table in a database.
Every list and library has an ID column that acts as the primary key. In a database, a primary key is a field (or set of fields) in a table that provide a unique identifier for each row.
The ID column satisfies all of the criteria for a good primary key because it:
- Uniquely identifies each row.
- Is never empty or null.
- Never changes.
You can see the ID column by adding it to any view of a list or library.
Because the ID is the primary key for any list or library, it is especially useful in workflow lookups for identifying a specific item (or row), as shown in the examples later.
As noted in the previous section, if you are familiar with relational databases, it might be helpful to think of each list and library in a site as a separate table in a database.
In fact, the data in lists and libraries is stored in tables in SQL Server. (While it is not technically true that each list and library corresponds to a separate table in the database, this mental model is useful when it comes to workflow lookups.)
If you are familiar with relational databases, defining a lookup is very much like querying a database — for example, suppose you have the following lookup.
This lookup says, “From the Tasks list, select the value of the Status field where the ID of the task item matches the ID stored in the variable Task item ID for this instance of the workflow.”
You can read the preceding lookup as the following SQL query. SQL queries use the SELECT keyword to retrieve data from a specified table — or, in this case, a specific list or library.
— OR —
The most basic type of lookup is where you look up the value of a field in the current item.
This lookup says, “From the current item, select the value of the Name field.” This lookup is easy to do because you only need to specify the field (column). The item (row) is fixed as the current item. In other words, there’s only one row to choose from. For this reason, this type of lookup is sometimes called a “flat” lookup.
As noted in the previous section, a lookup to a value in the current item is always a “flat” lookup because the row is fixed as the current item — you need only to specify the column (or field).
Similarly, a lookup to the workflow data (meaning the data stored in workflow variables and initiation parameters) is also a flat lookup because every variable can hold only a single value. The value stored in a variable may change from one workflow instance to the next, and the value may change at some point during a workflow — for example, if the workflow uses the Set Workflow Variable action to set the variable to a different value. But the variable always contains only a single value, so a lookup to the workflow data is always flat.
You can think of the workflow data as residing on a scratchpad that contains a series of locations where the workflow writes and stores data. This series of locations comprise a single “row” because each location (a variable or parameter) contains only a single value.
After this instance of the workflow is completed, the values written on the scratchpad are erased, and the next instance of the workflow will cache its own values.
When you want to look up a value in an item by applying a filter criteria to the list where the current item resides, or to a different list, the lookup dialog expands.
Why does it expand? The top half of the dialog specifies the field (column) of the value you’re interested in. However, because you want to apply a filter criteria, you also need to specify the item (row) of the value you’re interested in — and you identify a unique row by picking a field whose values can be used as filter criteria to filter the list down to a single row. For example, this lookup needs to know which item (row) you want the value from. The top half of the dialog specifies the column; now you need a row.
The more basic — and typically less useful — type of lookup is where you enter a static value (called a literal) to find the item or row that you’re interested in. For example, this lookup will go to the Tasks list and select the value of the Recommendation field from the first task item where the Recommendation field equals “Reject”.
When you click OK, you see the following warning.
What does this mean? It’s possible for many items in the Tasks list to have a Recommendation field that equals “Reject,” as shown in the following picture, so the lookup will find many items that satisfy its criteria. In this case, the lookup simply retrieves the value of the Recommendation field from the first item in the Tasks list where Recommendation = Reject, as shown here.
There are specific scenarios where you would need this type of lookup, especially when you are working with custom lists. To make this clear with an example, suppose that you have a custom list that lists the regional managers for each geographic region, as shown here.
Now suppose that your team is in the Northeast region, so this is the only region you care about. You have a workflow attached to a different list (not Regional Managers), and you want the workflow to look up the senior manager of the Northeast region from the Regional Managers list. By storing the people in a custom list and having the workflow look up this data, you make it possible for the workflow to always dynamically retrieve the latest data, plus you don’t have to open and modify the workflow anytime a manager in your region changes. Your lookup for this purpose would look something like this.
When you click OK, you see this warning.
You receive this warning because there can conceivably be more than one item in this list where the value of the region is Northeast. Unlike the ID (defined earlier as a primary key), the value Northeast is not necessarily unique because you could add many items to the list with this value. However, because you own the Regional Managers list, you can safely ignore this warning because you know that there is only one item whose value for the region equals Northeast, so you know that your lookup is unique.
To put it another way, the field that you use for the filter criteria acts as the key for that list. When you use an ID field as the key, those values are by definition unique. But if you use some other field as the key — like Region in this example — you are warned that the values in that field need to be unique in order for that field to be a useful key and to identify a unique item.
Another common scenario is to lookup a value in a specific item, which is described in the next section.
By far the most common type of lookup is where your workflow creates an item – for example, by using the Collect Data from a User action to create a task item, or by using the Create List Item action to create an item in any list or library in the current site.
When the workflow creates an item, the ID of the new item is stored in a variable.
The ID of an item is an integer that is always unique in that list or library. The ID acts much like a key in a database table.
Because the ID is always unique, and because the ID is stored in a dynamic variable, using the ID is the best, most common way to ensure that you are using a unique value to identify exactly the item you want. For example, assume that the Collect Data from a User action creates a task whose ID is stored in a variable named Task item ID. You can use that variable to ensure a unique lookup, as shown here.
When you click OK, you will not see the warning about ensuring unique lookups because you are using the ID to find the item (or row) that you’re interested in, and the ID is always unique.
This lookup says, “From the Tasks list, select the value of the Recommendation field where the ID of the task item matches the ID stored in the variable for this instance of the workflow.” In other words, the top half of the dialog specifies the column, as usual. And the bottom half of the dialog identifies a unique row. In this example, this instance of the workflow has created a task whose Tasks list ID is 5. Because 5 is the value stored in the variable Task item ID, the lookup goes to the row number 5 in the Tasks list.
Here are some tips to help you use workflow lookups:
- The guaranteed way to ensure a unique lookup is to use a list item ID to find the item with the matching ID. If there are variables of the data type List Item ID that are already associated with the current workflow, try to use them in the lookup. To identify those variables that are of List Item ID data type, click the Variables button in the Workflow Designer. A list of all variables associated to the current workflow appears. Check for variables of the type List Item ID.
- If you cannot use the ID field for some reason, or if no single field in a list can act as a key to the list, then you can try using a concatenated string of list item values which may render uniqueness to the list item. You can use the Build Dynamic String action in your workflow to create this string and store the string in a variable. You can then use this variable in a lookup to identify a unique item in a list.
- When you create the lists in your site, you can create a parent-child or one-to-many relationship between related data in different lists by using a lookup column in one list to point to the ID field of another list. For example, suppose that you have a Classes list and a Registrations list, and there’s a one-to-many relationship between one class and the many registrations for that class. You can add a column to the Registrations list that is a lookup column to the ID of the related class in the Courses list. Then, if you have a workflow running on the Registrations list, the workflow can always look up values from the related item in the Classes list: This lookup would say, “From the Classes list, select the value of [any field in that list], where the ID in the Classes list matches the ID of the lookup field in the current item.” Creating ID relationships (basically primary and foreign key relationships) between lists wherever possible makes workflow lookups easy, and using IDs is an easy way to guarantee a unique lookup match.
Here are a few things to know when working with lookups:
- Attach your workflow to the list that is most like a master list and that has more or less all the data that can be used in your workflow. This way you can use a lot more values from the current item in your lookups rather than creating complex lookups to get values from other lists.
- Whenever a lookup does not find matching data, it returns the string ?????. For example, if you define a workflow lookup to select the value of the Title field from the Tasks list where the Assigned To field equals “John”, but there is no task assigned to “John”, the lookup would return ?????. To resolve this, you can open the appropriate lookup and change the filter criteria so that the query returns a matching item.
- Whenever a lookup encounters a variable that does not have data in it, it returns the string ****. To resolve this, open the workflow and make sure that the variable is set to a value (or initialized).
- To clear the contents of a Define Workflow Lookup dialog box, click the Clear Lookup button in that dialog box.