When developing BDC Meta Man we found a number of issues when an entity was made up of fields that included Guid’s and DateTime’s. This was centered around Search and also the creation of associations between entities. We got around these issues by describing these fields types as System.String rather than Guid or DateTime. This does however represent a problem when you want to do BDC filtering for DateTime fields as it will try to do a string comparison (eg contains, starts with, ends with etc) rather than a date comparison of equals, less than, more than etc…
To be able to filter around DateTime you’re going to have to do some manual editing of your application definition file. Also note once you have manually edited the application definition file we cannot guarantee that BDC Meta Man will be able to open it correctly and re-generate. So here’s how to get some DateTime filtering working:
1, Add your entity to the BDC Meta Man Design Surface, in this example I am going to use dbo.SalesOrderHeader from the AdventureWorks2000 sample database provided by Microsoft.
2, Edit your entity by right clicking on it and choosing Edit from the context menu.
3, Go to the Methods tab, ensure the Finder method is selected
4, Click the New Filter Button
5, Setup your Filter as below:
Save the entity and generate your XML file as you normally would.
If we open the XML once it’s been generated we’ll see that the fact that OrderDate is a date time means that a LIKE operand is added to the WHERE clause of our SQL.
when we import this into SharePoint, add and configure a BDC List Web Part we get the following:
Although this is a correct DateTime because the SQL statement is trying to do a LIKE opperation we don’t get anything back. If we were to manually edit our XML file and change the LIKE to =, and import it back in then you’ll be able to get some data back. Note : after importing the app def file again you should delete and add the BDC web part to your page again, this ensures it picks up any new filter information.
This is obviously only doing an equals operation though. A common scenario is to require data being returned between a min and max date. Well to do that you’ll need to create two different filters, and change the operator signs to be < or > as required, here’s a small sample:
Note this XML is displayed using Internet Explorer, if you are editing this XML in notepad you’ll need to encode the < > signs (where([OrderDate] < @MaxOrderDate) and ([OrderDate] > @MinOrderDate) )
Now if we make use of this in SharePoint we can add both our Filters and get:
Remember when creating the filters in BDC Meta Man with the default values for each filter you may want to enter a small date and large date for the MinOrderDate and MaxOrderDate respectively.
