BDC, multiple choices for filters and wildcards

February 19th, 2009 | Categories: MOSS, SharePoint

I’ve been looking a lot around to find an option to have multiple filters available on BDC that would not exclude each other out. The scenario is:

  • we have a BDC connection to a SQL table with entity that contains company ID and Company name. Company ID is a primary key. Both ID and name are strings.
  • we would like to filter either by company ID or by company name and use the wildcard option (to search by part of string or whole).

The best way to set up your ADF is with BDC MetaMan.

1. Set up appropriate filters on getdbo method (the Finder method). The FilterDescriptors have to be of type wildcard. Put any value as default.

image

 

2. Build the XML

3. Modify the XML:

a. The Select statement in RdbCommandText property of the finder method (Getdbo.[tableName]) – change the AND operator to OR.
 
Select [companyID],[companyName],[address],… From dbo.[companies] where([companyID] LIKE @CompanyID) OR ([companyName] LIKE @CompanyName)
(use your column names in the example above).

b. Delete or comment out default values for “In” parameters of that method.
 

 
   
    
     123
     
>
    

4. Publish the XML. If publishing the xml gives you version error, just increment the version number from 1.0.0.0 to 1.0.0.1

This did the trick for me. Below are the “before” and “after” parts of the code with highlighted changes. Click for a bit enlarged preview.

Code comparison

Technorati Tags: ,,