locked
Replace "is equal to" filter criteria of BDC list to "Contains" RRS feed

  • Question

  • Hi all,

    I created a BDC list with comparision filter. After deploying the BDC webpart in the MOSS site, the filter criteria shows "is equal to" option in the drop down. Is there any other way to change the "is equal to" to "Contains" without using the wildcard filter option in the xml file. If we use wildcard it shows all the four options in the in the drop down. So my requirement is to show only "contains" in the drop down, it should not show any other filter criteria in the drop down. So can anyone help me on this to change "is equal to" to "contains". Is this is possible using XSLT code?

    Thanks in advance,
    Kishore


    Kishore


    • Edited by Kishorekv Monday, March 26, 2012 10:08 AM
    Monday, March 26, 2012 10:07 AM

Answers

  • Hi Kishore,

    You may create a stored procedure with parameter (Finder) and use comparison filter for it. Implementation of this procedure should work as you need (it can work as wildcard search for example). In this case you will have "is equal to" for the filter but it will work as wildcard filter. Take a look at this example:

    Stored Procedure:

    CREATE PROCEDURE [dbo].[GetTerritoriesByDescription]
    	@description varchar(50)
    AS
    BEGIN
    	SELECT * FROM Northwind.dbo.Territories WHERE TerritoryDescription LIKE '%' + @description + '%'
    END

    Application Definition File:

    <?xml version="1.0"?>
    <LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.XSD" Type="Database" Version="1.0.0.0" Name="NorthwindLOBSystem" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
      <Properties>
        <Property Name="WildcardCharacter" Type="System.String">%</Property>
      </Properties>
      <LobSystemInstances>
        <LobSystemInstance Name="NorthwindInstance">
          <Properties>
            <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
            <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
            <Property Name="RdbConnection Data Source" Type="System.String">auriga\sqlexpress</Property>
            <Property Name="RdbConnection Initial Catalog" Type="System.String">Northwind</Property>
            <Property Name="RdbConnection User ID" Type="System.String">user</Property>
            <Property Name="RdbConnection Password" Type="System.String">12345</Property>
            <Property Name="RdbConnection Integrated Security" Type="System.String" />
            <Property Name="RdbConnection Pooling" Type="System.String">false</Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
      <Entities>
        <Entity EstimatedInstanceCount="0" Name="Territory">
          <Methods>
            <Method Name="MyFinderMEthod">
              <Properties>
                <Property Name="RdbCommandText" Type="System.String">dbo.GetTerritoriesByDescription</Property>
                <Property Name="RdbCommandType" Type="System.String">StoredProcedure</Property>
              </Properties>
              <FilterDescriptors>
                <FilterDescriptor Type="Comparison" Name="description" />
              </FilterDescriptors>
              <Parameters>
                <Parameter Direction="In" Name="@description">
                  <TypeDescriptor TypeName="System.String" Name="description" AssociatedFilter="description">
                    <DefaultValues>
                      <DefaultValue MethodInstanceName="MyFinderMEthodFinder" Type="System.String">a</DefaultValue>
                    </DefaultValues>
                  </TypeDescriptor>
                </Parameter>
                <Parameter Direction="Return" Name="MyFinderMEthod">
                  <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="MyFinderMEthodDataReader">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="MyFinderMEthodDataRecord">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.String" Name="TerritoryID" />
                          <TypeDescriptor TypeName="System.String" Name="TerritoryDescription" />
                          <TypeDescriptor TypeName="System.Int32" Name="RegionID" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="MyFinderMEthodFinder" Type="Finder" ReturnParameterName="MyFinderMEthod" ReturnTypeDescriptorName="MyFinderMEthodDataReader" ReturnTypeDescriptorLevel="0" />
              </MethodInstances>
            </Method>
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>

    The result on the page:


    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts

    Tuesday, March 27, 2012 8:54 AM

All replies

  • Hi Kishore,

    Please take a look at this thread Sharepoint 2007 list filter "does not contain"

    I think it can be helpful for you.


    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts

    Monday, March 26, 2012 12:23 PM
  • Hi Dmitry,

    Thanks for your reply. Is there any other way to solve this issue. Can we solve this problem without using sharepoint designer?


    Regards,
    Kishore


    Kishore

    Monday, March 26, 2012 2:23 PM
  • Hi Kishore,

    You may create a stored procedure with parameter (Finder) and use comparison filter for it. Implementation of this procedure should work as you need (it can work as wildcard search for example). In this case you will have "is equal to" for the filter but it will work as wildcard filter. Take a look at this example:

    Stored Procedure:

    CREATE PROCEDURE [dbo].[GetTerritoriesByDescription]
    	@description varchar(50)
    AS
    BEGIN
    	SELECT * FROM Northwind.dbo.Territories WHERE TerritoryDescription LIKE '%' + @description + '%'
    END

    Application Definition File:

    <?xml version="1.0"?>
    <LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.XSD" Type="Database" Version="1.0.0.0" Name="NorthwindLOBSystem" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
      <Properties>
        <Property Name="WildcardCharacter" Type="System.String">%</Property>
      </Properties>
      <LobSystemInstances>
        <LobSystemInstance Name="NorthwindInstance">
          <Properties>
            <Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
            <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
            <Property Name="RdbConnection Data Source" Type="System.String">auriga\sqlexpress</Property>
            <Property Name="RdbConnection Initial Catalog" Type="System.String">Northwind</Property>
            <Property Name="RdbConnection User ID" Type="System.String">user</Property>
            <Property Name="RdbConnection Password" Type="System.String">12345</Property>
            <Property Name="RdbConnection Integrated Security" Type="System.String" />
            <Property Name="RdbConnection Pooling" Type="System.String">false</Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
      <Entities>
        <Entity EstimatedInstanceCount="0" Name="Territory">
          <Methods>
            <Method Name="MyFinderMEthod">
              <Properties>
                <Property Name="RdbCommandText" Type="System.String">dbo.GetTerritoriesByDescription</Property>
                <Property Name="RdbCommandType" Type="System.String">StoredProcedure</Property>
              </Properties>
              <FilterDescriptors>
                <FilterDescriptor Type="Comparison" Name="description" />
              </FilterDescriptors>
              <Parameters>
                <Parameter Direction="In" Name="@description">
                  <TypeDescriptor TypeName="System.String" Name="description" AssociatedFilter="description">
                    <DefaultValues>
                      <DefaultValue MethodInstanceName="MyFinderMEthodFinder" Type="System.String">a</DefaultValue>
                    </DefaultValues>
                  </TypeDescriptor>
                </Parameter>
                <Parameter Direction="Return" Name="MyFinderMEthod">
                  <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="MyFinderMEthodDataReader">
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="MyFinderMEthodDataRecord">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.String" Name="TerritoryID" />
                          <TypeDescriptor TypeName="System.String" Name="TerritoryDescription" />
                          <TypeDescriptor TypeName="System.Int32" Name="RegionID" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="MyFinderMEthodFinder" Type="Finder" ReturnParameterName="MyFinderMEthod" ReturnTypeDescriptorName="MyFinderMEthodDataReader" ReturnTypeDescriptorLevel="0" />
              </MethodInstances>
            </Method>
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>

    The result on the page:


    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts

    Tuesday, March 27, 2012 8:54 AM