How to do a Filter in a BCS External Data picker

Question

I am using an OData web service with BCS (testing with Northwind) and I would like to define some filters when using the External Data picker. I'd like to be able to have a drop down that then performs the lookup based on an OData filter, like filter=substringof("Value",Field_Name).

The filter is working when I create a VIEW on an EXTERNAL LIST app. However, when trying to use it in the EXTERNAL DATA COLUMN Picker, the filter options do show up in the dropdown, but do not return any results. Instead I always get this error message:

The same results from the External List VIEW

The code I am using to add to my "ReadAllCustomers" method include: (1) The Filter Descriptor

                <!-- ********* this is NOT working *********** -->              
            <FilterDescriptor Name="CustomerIDFilter" FilterField="CustomerID" Type="Wildcard">
              <Properties>
                <Property Name="UsedForDisambiguation" Type="System.Boolean">true</Property>
                <Property Name="IsDefault" Type="System.Boolean">true</Property>
                <Property Name="CaseSensitive" Type="System.Boolean">false</Property>
                <Property Name="LogicalOperatorWithPrevious" Type="System.String">And</Property>
              </Properties>
            </FilterDescriptor> 
            <!-- ********* this is working *********** -->              
            <FilterDescriptor Name="CustomerIDCompare" FilterField="CustomerID" Type="Comparison">
              <Properties>
                <Property Name="IsDefault" Type="System.Boolean">false</Property>
                <Property Name="Comparator" Type="System.String">Equals</Property>
                <Property Name="LogicalOperatorWithPrevious" Type="System.String">Or</Property>
              </Properties>
            </FilterDescriptor> 

(2) The Input Parameter

                <!-- ********* this is NOT working *********** -->
            <Parameter Name="@CustomerIDFilter" Direction="In">
              <TypeDescriptor Name="CustomerIDFilter" TypeName="System.String" AssociatedFilter="CustomerIDFilter">
                <Properties>
                  <Property Name="ODataFilterUrl" Type="System.String">substringof('@CustomerIDFilter',CustomerID)</Property>
                </Properties>                 
                <DefaultValues>
                  <DefaultValue MethodInstanceName="ReadAllCustomer" Type="System.String"></DefaultValue>
                </DefaultValues>
              </TypeDescriptor>
            </Parameter>    
            <!-- ********* this is working *********** -->
            <Parameter Name="@CustomerIDCompare" Direction="In">
              <TypeDescriptor Name="CustomerIDCompare" TypeName="System.String" AssociatedFilter="CustomerIDCompare">
                <Properties>
                  <Property Name="ODataFilterUrl" Type="System.String">CustomerID%20eq%20'@CustomerIDCompare'</Property>
                  <Property Name="LogicalOperatorWithPrevious" Type="System.String">None</Property>
                </Properties>
                <DefaultValues>
                  <DefaultValue MethodInstanceName="ReadAllCustomer" Type="System.String"></DefaultValue>
                </DefaultValues>
              </TypeDescriptor>
            </Parameter>

(3) The ODataEntityUrl which applies the filter as determined by the parameter input

<Property Name="ODataEntityUrl" Type="System.String">/Customers?$top=@LimitCustomers&amp;$filter=@CustomerIDFilter%20or%20@CustomerIDCompare</Property>

Again, with these snippets, the filters work like a charm when creating a view and entering the values, but I cannot for the life of me get it to work with the filter drop downs.

Anyone know how to do this or have examples of working ones that I can copy from?

Answer 1

So after much trial and error and reading everything I could, I have found the general issue, so sharing so anyone else that stumbles upon this. The issue is that the wildcard does not seem to be capable of passing the functions such as substringof() or startswith() to the ODataEntityUrl. You have to use a Comparison instead of a Wildcard.

Here is the filter that works:

                <FilterDescriptor Name="CustomerIDFilter" FilterField="CustomerID" Type="Comparison">
              <Properties>
                <Property Name="Comparator" Type="System.String">Equals</Property>
              </Properties>
            </FilterDescriptor> 

Here is the parameter that works:

                <Parameter Name="@CustomerIDFilter" Direction="In">
              <TypeDescriptor Name="CustomerIDFilter" TypeName="System.String" AssociatedFilter="CustomerIDFilter">
                <Properties>
                  <Property Name="ODataFilterUrl" Type="System.String">substringof('@CustomerIDFilter',CustomerID)%20eq%20true</Property>
                  <Property Name="LogicalOperatorWithPrevious" Type="System.String">Or</Property>
                </Properties>   
                <DefaultValues>
                  <DefaultValue MethodInstanceName="ReadAllCustomer" Type="System.String">null</DefaultValue>
                </DefaultValues>
              </TypeDescriptor>
            </Parameter>

Here is the ODataEntityURL (with a few other filters included) which works:

                <Property Name="ODataEntityUrl" Type="System.String">/Customers?$top=@LimitCustomers&amp;$filter=@CustomerIDFilter%20or%20@CompanyNameFilter%20or%20@CountryFilter%20or%20@CustomerIDStartsWith</Property>

This makes my drop down filters in the Picker work (but also breaks the External List view), so I had to create a second Method to display all Customers without filters by default.

Answer 2

I know this is quite old, but I managed to get mine working in a much simpler way and haven't found it anywhere else on the internet, so since Brent's response was the most useful one that I found I thought I would add to it.

In my BDC model export, the read list method is listed with the SQL visible, so instead of using oData to modify the filter, I went straight for the SQL.

So originally it looked like this:

<Method IsStatic="false" Name="VW_BRANCH_OFFICESRead Item">
    <Properties>
        <Property Name="BackEndObject" Type="System.String">VW_BRANCH_OFFICES</Property>
        <Property Name="BackEndObjectType" Type="System.String">SqlServerView</Property>
        <Property Name="RdbCommandText" Type="System.String">SELECT [Branch Name] , [Branch Code] , [Country Name] , [Country Code] , [Local Region] , [Area] , [Region] , [Branch S1 Code] FROM [CMD].[VW_BRANCH_OFFICES] WHERE [Branch Code] = @parameter</Property>
        <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
        <Property Name="Schema" Type="System.String">CMD</Property>
    </Properties>

And I modified the "RdbCommandText" property to use a LIKE instead of =:

<Method IsStatic="false" Name="VW_BRANCH_OFFICES_2Read List">
    <Properties>
        <Property Name="BackEndObject" Type="System.String">VW_BRANCH_OFFICES</Property>
        <Property Name="BackEndObjectType" Type="System.String">SqlServerView</Property>`
        <Property Name="RdbCommandText" Type="System.String">SELECT [Branch Name] , [Branch Code] , [Country Name] , [Country Code] , [Local Region] , [Area] , [Region] , [Branch S1 Code] FROM [CMD].[VW_BRANCH_OFFICES] WHERE ((@parameter IS NULL) OR ((@parameter IS NULL AND [Branch Name] IS NULL) OR [Branch Name] LIKE'%'+@parameter+'%'))</Property>
        <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
        <Property Name="Schema" Type="System.String">CMD</Property>
    </Properties>

Worked for both the picker and the external list view.