SharePoint designer - User lookup from another list with multiple conditions

Question

Background

I have two lists:

• Approval list: There is three column with approver 1, 2 and 3 and the country
• User list: A list with the title of the user (Approver x), his email and his country. For each country, there's an approver 1, approver 2,...

What we're trying to achieve

Each line on our first list concern one country. We want to send mail to the first approver of a given country if it's column is at No, using our user list. Once he changes a column to yes, an email is sent to approver two, and so on.

The problem

When I do a lookup for the user in my workflow in Sharepoint Designer 2010, I'd like to be able to retrieve the email from my user list with the criter of title and country. Right now I can only use the title, meaning I'll always send a mail to the very first approver, regardless of the fact that the approval workflow doesn't concern him but an approver of another country.

How could I achieve a satisfying result?

Answer 1

The simplest way to handle this is with to use a calculated column with the values you need to query on (concatenated into a string, usually), and reference that in your WF query (using a variable to build the appropriate concatenated value).

Another (better IMHO) option is to use a 3rd party WF tool like Virtosoftware's Workflow Activities Kit. One of their activities lets you get records using a CAML query, so you won't have that annoying "only 1 column" problem. FWIW, the Virto WAK Is pretty cheap, and is easily deployed. It also comes with DOZENS of very useful activities and conditions.