Using Arraylist in Mule to query Salesforce

We are trying to query Salesforce with an ArrayList in the where statement.

Below is is the error we ran into when we tried using the ArrayList in the where clause.

Query we used against Salesforce:

Select Id,Billing_Number__c from Call_Log__c where Id in #[flowVars.successlist] 

successlist contains the values ['a1o90000001msXwAAI', 'a1o90000001msXxAAI'].

Error Message:

Message: Failed to invoke query. Message payload is of type: ArrayList

How do I resolve this error?

Answer

Unfortunately, you can’t use ArrayLists as parameters in Salesforce (or Database) queries. You’ll have to create the query dynamically with a script component.

Try this:

        <scripting:script engine="Groovy">
<![CDATA[def sb = new StringBuilder()
sb.append('Select Id,Billing_Number__c from Call_Log__c')
if (flowVars.successlist != null && !flowVars.successlist.empty) {
    sb.append(' where Id in ('')
    for (i in 0 .. flowVars.size()) {
        if (i > 0) sb.append('','')
        sb.append(flowVars.successlist[i])
    }
    sb.append('')')
}
flowVars.query = sb.toString()]]>
        </scripting:script>  
        <sfdc:query query="#[flowVars.query]" doc:name="Salesforce - Query"/>

If you were using a Database, you would have to set the query type as dynamic:

        <db:select doc:name="Database">
            <db:dynamic-query><![CDATA[#[flowVars.query]]]></db:dynamic-query>
        </db:select>