1

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:

enter image description here

The same results from the External List VIEW

enter image description here

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?

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.