Search code examples
odatapowerappspower-automatepower-platformdataverse

Is there an IN operator for Power Automate Odata List Rows - Filter Rows expression

What is the syntax of a Filter Expression for the DataVerse List Rows component, using a comparison similar to the SQL IN Statement.

I have tried things such as

name in ('name1','name2')

with no luck.

Solution

  • Use function Microsoft.Dynamics.CRM.In(PropertyName='name',PropertyValues=['name1','name2']).

    E.g. when you need to select accounts by accountnumber, the query would look like this:

    /api/data/v9.1/accounts?$select=accountnumber,name&$filter=Microsoft.Dynamics.CRM.In(PropertyName='accountnumber',PropertyValues=['12345888','004567763','91313313'])

    FetchXml operators not natively supported in oData are modeled as query functions structured like this: Microsoft.Dynamics.CRM.OperatorName(PropertyName='logicalname', PropertyValues=[value1, value2, ...]).

    So these options are also available:

    $filter=Microsoft.Dynamics.CRM.NotIn()
$filter=Microsoft.Dynamics.CRM.Between()

    Note: query filter function names are case sensitive!

    The complete list can be found on MS Docs - Web API Query Function Reference.