Cannot filter on specific BDC field in the list toolbar
A list is displaying secondary BDC field of both string and decimal types. For some reason the string types do not allow filtering on the field from the list toolbar dropdown, the decimal types do allow filtering. Strangely enough, another library in the same site collection (so with the same BDC definition) does allow filtering on some string type BDC fields, but not on all fields.
I also noticed that the fields / columns that do not allow in place filtering are also not available in a calculated column.
Any suggestions to resolve this would be appreciated.
Jeroen
Answers
- Microsoft has now confirmed that this change was by design. They changed the secondary BDC text columns from a single line to multiple lines, which cannot be sorted or filtered. The fix was put in to address complaints from some users that data was being truncated.
They also told us that there is no known workaround, including trying to use SharePoint Designer to change the settings. However, I think it would be worth investigating if this can be changed back using the CAML definition files (not sure where these BDC settings are defined though).
They have also warned us that changes to BDC field settings in existing lists (where the filtering/sorting still works) may actually break the functionality as well (!).
It seems this change wasn't really thought through very well; hopefully more people will report this as an issue so they can provide a workaround/solution.- Marked As Answer byjeroen_sf Tuesday, November 17, 2009 9:55 PM
All Replies
- Just curious... Do you have SharePoint SP 2 installed?
- Yes, SP2 + Infrastructure update.
We just opened a case with Microsoft and luckily the support person could reproduce the exact issue and agreed that it must be a bug. Awaiting more information from their end.
I saw your post, I assume you have the same problem? - Yes... Let me know what happens on your case.
I will. I am posting some of my internal notes:
- Filtering inside a list/library on secondary BDC fields of type System.Decimal seems to work fine; filtering on fields of type System.String doesn’t work in all situations (this only applies to filtering using the dropdown option in the list itself; filtering through views does work)
- Existing lists or libraries don’t seem to have the issue but a newly created library does not allow filtering on BDC fields of type String
- Considering the above, the cause of the issue is most likley not in the BDC definition since this would apply to both existing as well as new lists
Microsoft support reported back:
"First I tried on: MOSS 2007 + SP2 -: Issue is reproducible.
Secondly tried on: MOSS 2007 + SP1 + Infrastructure. Able to do sorting for string column.
Forwarded the same information to seniors. I will contact you once I get any updates from them."
I asked them to test with the June 2009 cumulative update because we are running version 12.0.0.6510; awaiting their feedback.- I've got the same problems. In addition:
Unable to "Group By" a BDC string field in a View.
Unable to use a Calculated Column on a BDC field of string type. (Numerics work). - Any update on this?
Andy - not really, other than that MS can also reproduce it with the June 2009 update in place. they have escalated it but so far they don't seem to have a solution. their last update (from today actually) mentioned that they are still testing.
- Can you move to SP2010? I'm pretty sure your problem will be eliminated with the external list feature.
Another option and I hate to even consider it, but you can convert the list view web part to data view web part and then "massage" the XSLT to sort the way you want. XSLT can be messy but it will work. Microsoft confirmed that SP2 broke this and that they are working on a hotfix for this issue with high priority.
- I had also opened a ticket on this incident on Oct 14. Microsoft could reproduce the problem and confirmed that the problem did not occur until SP2 was installed. I was not given a time or date for a hotfix. My support technician did not give me any indication of when it may be completed. In fact he mentioned that they have not determined whether or not they will build a hotfix for the problem.
Andy - I received a call from Microsoft tech support today. It is a problem, but there is no eta on a hot fix, service pack, or work around. They are closing the ticket.
Microsoft support has now also closed our case. Strangely enough they say it may be a bug but it could also be for performance reasons; you would think they would know??
Our technical account manager to whom we escalated the case responded: "Even if it is a bug, [Microsoft] indicated it was not put on a high priority case, so it would be a while before it gets addressed."
We escalated the case further to find out if it will be treated as a bug because that may be the only way to get it resolved. It seems very unlikely to me that this was changed for performance reasons because it only affects BDC string values which are stored in the SharePoint database in exactly the same way as other string values that still can be filtered on. Also, since the filtering/sorting only happens on client request, I don't see how this could be considered a performance issue. And finally, this 'change' only affects newly created lists with BDC string fields, not existing lists!- Microsoft has now confirmed that this change was by design. They changed the secondary BDC text columns from a single line to multiple lines, which cannot be sorted or filtered. The fix was put in to address complaints from some users that data was being truncated.
They also told us that there is no known workaround, including trying to use SharePoint Designer to change the settings. However, I think it would be worth investigating if this can be changed back using the CAML definition files (not sure where these BDC settings are defined though).
They have also warned us that changes to BDC field settings in existing lists (where the filtering/sorting still works) may actually break the functionality as well (!).
It seems this change wasn't really thought through very well; hopefully more people will report this as an issue so they can provide a workaround/solution.- Marked As Answer byjeroen_sf Tuesday, November 17, 2009 9:55 PM
- someone else has reported the same issue: http://suguk.org/forums/21630/ShowThread.aspx#21630. I have added a link to this post.
- I as well had this issue but there is some free web parts out there that worked for me
