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.
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?