Can't Filter, sort or group by a BDC field

Question

I created a BDC App to a view in SQL.  I used the BDC App as a "Business Data" column in a custom list.  I created a "Calculated Column" to extract information from a field in the BDC data.  Created a View on the custom list that "Grouped by" a BDC column (not the lookup column) of type string. 
Everything was working fine. 

Went back in and made some changes to the XML a number of times.  Went back to test the whole app, and the calculated column name references changed from mid([SqlTbl: extract],1,15) to mid(#NAME?,1,5).  Furthermore only int32 columns are showing up in the "Insert Column" pick list, I can no longer use any of the BDC fields that are "string" in a calculated column, and, when creating a View, I can no longer use a string BDC column in a "Group by" clause (it does not appear in the pick list).   

It seems like the data type changed... 

I've gone back a number of times and tried to reconstruct what I originally had, but I keep getting the same result.

Ideas?  Thanks,
Andy

Answer 1

Hi,

I experienced the same problem. My bdc definition is a join over multiple columns in a sql database. I have to pick the id as return type and additional information checked to show up as columns.

I had no difficulties to group by that columns in our customers MOSS 2007. Now I started to set up the same website at our presentation system MOSS 2007 and the additional Information doesn't appear in the pick list.

Additional I experienced that if I picked the correct ID as return type it also appeared in the drop down, but not if I selected a different column of my sql query as return type (on the create-new-bdc-column site in MOSS).

I'm now checking if the service pack 2 helps me out.

And another curiosity: in the case it worked maybe I forgot to select "this column must contain data" or I didn't have the chance to select it, because now the field is no longer displayed on the edit-column site.

Well, I have one working system, maybe I get it working, maybe we could have a look together on it via SharedView - if that's fine for you and no one else has any suggestions.

Thanks,
Michael

Answer 2

It doesn't appear to be related to a join or view...  trying to narrow in on the problem, I created a new table:

CREATE TABLE [dbo].[AndyCoyle.TestTbl](
	[KeyField] [int] IDENTITY(1,1) NOT NULL,
	[decimal] [decimal](18, 0) NOT NULL,
	[TextFieldnchar] [nchar](10) NULL,
	[Numeric] [numeric](18, 0) NULL,
	[datetimefield] [datetime] NULL,
	[charTen] [char](10) NULL,
	[integerfld] [int] NULL,
 CONSTRAINT [PK_AndyCoyle.TestTbl] PRIMARY KEY CLUSTERED 
(
	[KeyField] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 

·          I use BDC MetaMan to generate an XML file of the application definition using the table above.

·          I import the application definition file.

·          I add the business data column to a list.
I create a calculated column and try to insert the "abctest: charTen" field but  only numeric fields are available.  The insert columns pick list only shows "abctest: decimal", "abctest: integerfld", "abctest: keyfield", "abctest: Numeric" and "abctest".   "abctest: charTen" and "abctest:TextFieldnchar" do not appear in the pick list.

Since it is happening site wide, on every table in the DB that I test on, including the new one, it seems like it might be a SharePoint setting.

Ideas?
Thanks,
Andy

Answer 3

I  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

Answer 4

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.