How to use CAML IN clause with MultiLookup field? [closed]

Question

After making a long research I'd like to share the solution for executing a CAML query using IN operator dynamically based on a multi lookup field by workflow without any additional coding. Generally the IN operator should be used in the following manner (taken from here):

<Where>
    <In>
        <FieldRef Name='Designation' />
        <Values>
            <Value Type='Text'>Engineer</Value>
            <Value Type='Text'>Architect</Value>
        </Values>
    </In>
</Where>

First of all the problem: I have a list of tasks with Predecessors column which is a multi lookup to the same tasks list to define which task(s) is(are) the predecessor(s) of another. Then in a workflow I'd like to update amount of active predecessors of a successor task based on the state of the predecessor.

And now the solution: You'll need 2 components from CodePlex:

1) EF SharePoint 2010 workflow activities: This one brings many useful workflow activities with the [get items count from CAML] that we'll use.

2) SharePoint Designer Workflow String Actions: This one brings many useful workflow activities to perform on strings. Much more than a couple of OOTB ones.

Well, now the action!

First of all we'll need to create some workflow variable of type String and assign it a value of a multi lookup field.

Then we'll use the Replace() string action on our variable used as List Ids (Comma separated) to replace commas with the following string:

</Value><Value Type="Number">

Then we'll wrap the result of the Replace with additional tags as follows:

<Value Type="Number">[%Variable:Message%]</Value>

Afterwards we can use the [get items count from CAML] action with the CAML IN operator while the contents of < Variables > tag will be our variable. In my situation I just wanted to count the amount of active predecessors, those that are not deleted, canceled or completed.

<Where>
    <And>
        <In>
            <FieldRef Name='Predecessors' LookupId='TRUE' />
            <Values>
                [%Variable:Message%]
            </Values>
        </In>
        <And>
            <Neq>
                <FieldRef Name='Deleted' />
                <Value Type='Integer'>1</Value>
            </Neq>
            <And>
                <Neq>
                    <FieldRef Name='Status' />
                    <Value Type='Choice'>Complete</Value>
                </Neq>
                <Neq>
                    <FieldRef Name='Status' />
                    <Value Type='Choice'>Canceled</Value>
                </Neq>
            </And>
        </And>
    </And>
</Where>

The LookupId="TRUE" is crucial in the FieldRef tag of the predecessors field since it guides SharePoint to treat the Lookup column as id and not the concatenation of id and the title of the linked item. This was found here.

And voila! It works!

Good luck and have fun!