0

We have a flow that presents a multi-picklist on a screen that presents related Accounts. We then want to create a related object on each of those selected Accounts.

To allow for modularity, we want to use the same code for any object in the future. A multi-picklist requires us to split the string by ';'

When I debug the flow it errors out on the apex action with:

Error Occurred: An Apex error occurred: System.QueryException: expecting a right parentheses, found 'h00000h1C59AAE'

I'm confident it is due to the SOQL query composition and the 'IN' clause from the QueyException, but not sure what is throwing the syntax error.

public class FlowController {
        
    @InvocableMethod( label='Show Outputs' description='Getting multiple values' )
    public static List<List<SObject>> showOutputs(List<Requests> inputs) {
        
        Requests objInput = inputs.get(0);
        System.debug('start flow');
          String tempStr = inputs[0].values;
          System.debug('start split');
          List<String> idValues = tempStr.split(';');

        String queryString = 'SELECT Id, Name FROM ' + objInput.objectType + ' WHERE Id IN ' + idValues;
        List<SObject> outputMember = Database.query(queryString);

        List<List<SObject>> responseWrapper = new List<List<SObject>>();
        responseWrapper.add(outputMember);
        return responseWrapper;   
    }
    public class Requests {
        @InvocableVariable
        public String objectType;
        
        @InvocableVariable
        public String values;
    }
}
3
  • "Id IN <sequence of strings from picklist>"? Quite apart from the weird idea that the picklist API names would be record IDs, the <sequence> needs to either be done using a binding or you need to make sure the array is turned into an appropriate string of the form ('First', 'Second', 'Third').
    – Phil W
    Mar 11, 2022 at 15:12
  • That makes sense, and my first preference was to use bindings. But I couldn't figure a way to bind the SObject/Table in the query.
    – Matt P
    Mar 11, 2022 at 15:14
  • The object type cannot use a binding, but the IN array can.
    – Phil W
    Mar 11, 2022 at 15:16

2 Answers 2

4

Change:

String queryString = 'SELECT Id, Name FROM ' + objInput.objectType + ' WHERE Id IN ' + idValues;

To:

String queryString = 'SELECT Id, Name FROM ' + objInput.objectType + ' WHERE Id IN :idValues';

This then uses a binding for the IN evaluation. To clarify, this works even in dynamic SOQL because this is a simple binding variable (I don't need to use anything more than a locally-accessible-at-time-of-query-execution variable's name. Dynamic SOQL doesn't support bindings that are more than a variable name, so cannot include function calls (()), property access (.) or array index access ([]) which you can use in static SOQL bindings.

1
  • In the Screen Flow it's displaying a collection of Account Names and returning Account Ids, so a bit different than a picklist on an object, I completely agree. And I didn't even connect binding to a query string, so thanks for that!
    – Matt P
    Mar 11, 2022 at 15:25
3

Phil W's answer covers the easy way to do this (dynamic SOQL can do simple variable binds).

But where's the fun in that?
This answer covers the 'hard' way (which I hope gives you a better/broader understanding of what's happening).

When you're working with a dynamic query and using the IN operator for a filter, you need to supply the opening and closing parenthesis (IN checks the target field against a collection of values, and enclosing them in parenthesis is how you specify the collection).

So instead of

'String query = SELECT Id, Name FROM ' + objInput.objectType + ' WHERE Id IN ' + idValues;`

you'd need to do something like

'String query = SELECT Id, Name FROM ' + objInput.objectType + ' WHERE Id IN (' + idValues + ')';`

That would only work for a single value though. Since you're using a list, you'll need to turn that into an appropriate string first. SOQL uses single quotes to represent string literals, but so does Apex. To make it work, your apex string needs to escape the inner single quotes.

In the end, you're looking for a string like '\'value1\', \'value2\', ... , \'valueN\'';

To generate that string from your list of values, you'd use String.join()

// String.join() only puts things between each item in the list
// You still need to add the bits at the start and the end
String filterValues = '(\'' + String.join(myList, '\', \'') + '\')';

Things like dynamic queries can get out of hand and hard to mentally parse pretty quickly. It's probably fine here, but one way to help with that is to use String.format();

List<String> queryFields = new List<String>{'Id', 'Name'};

String query = String.format('SELECT {0} FROM {1} WHERE Id IN {2}', new List<String>{
    String.join(queryFields, ', '),
    myObjectName,
    '(\'' + String.join(filterValues, '\', \'') + '\')'
});
2
  • 2
    Friday Afternoon fun, heh?! :D
    – Phil W
    Mar 11, 2022 at 15:40
  • Always appreciate a hard vs easy answer and glad to have both. I can see value here especially if we leverage a utility class
    – Matt P
    Mar 11, 2022 at 16:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .