Erulu

I'm working with the BDC for about 2-3 weeks and there are some things that are quite confusing. Although I've read and searched a lot in the BDC's literature I couldn't answer my questions:

Business Data List web part: Search field

# I remember that The BDC MetaMan created at first a Business Data List where a search field (not the MOSS search it was something like <primary key field> {is equal to, contains} <my search critera> "Retrieve data" button) was present. In the recent version there's no such field that restricts the number of displayed items, is it How can I create this selection field Is there a way to add more search fields instead of just the primary key field Or to add more filter critera (e.g. "begins with", "ends with",...) As far as I remember there was just an "And" conjunction of the filter criterias - is "(X)or" also possible

Actions

There are two places where "Actions" can take place:

1. As a dropdown menu in the upper left corner of the Business Data List.

2. As a dropdown menu in a hover link at one item.

# Where is the column, in which the items's hover link is displayed, defined I guess it's somewhere in the XSL definition!

# How can I add simultaneously an Action to (1) and (2)
# How can I add an Action just to (1) xor (2)

# Is there a way to group items by a specific field (This is easily configured in a form library list)

# Although I've created some Actions with the BDC MetaMan and all Actions are visible in the SSP admin page (http://<myserver>:<myport>/ssp/admin/_layouts/BDCAdminUI/ViewBDCEntity.aspx Id=123) no Action appears in the BDC List (neither at position (1) nor at positioin (2)!). Even if I try to add an action in SSP administration: There appears no Action in the hover link or in the upper left corner of the BDC list web part. The complete xml definition file is attachted at the bottom of this post [1].


Some other minor issues/possible bugs ( ):

- The attribute "Position" is wrongly created by MetaMan (Position="0" is not included).
- If the SQL table contains field labels that are reserved names in SQL the query fails. It could be safer to enclose all fields in the SELECT-statement with brackets.


References:

[1] The complete application defintion file (some labels are modified or deleted):

< xml version="1.0" standalone="yes" >
<LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.XSD" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog" Type="Database" Version="1.0.0.0" Name="IM_xxx_DataLOBSystem_2">
<Properties>
<Property Name="WildcardCharacter" Type="System.String">%</Property>
</Properties>
<LobSystemInstances>
<LobSystemInstance Name="IM_xxx_DataInstance_2">
<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">xxxDEV</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">IM_xxx_Data</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
<Property Name="RdbConnection Pooling" Type="System.String">false</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<Entity EstimatedInstanceCount="1000" Name="dbo.man">
<Properties>
<Property Name="Title" Type="System.String">lastname</Property>
</Properties>
<Identifiers>
<Identifier Name="Uid" TypeName="System.String" />
</Identifiers>
<Methods>
<Method Name="Getdbo.man">
<Properties>
<Property Name="RdbCommandText" Type="System.String">Select Uid, [Group], (...) From dbo.man</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
</Properties>
<Parameters>
<Parameter Direction="Return" Name="dbo.man">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.manDataReader" IsCollection="true">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.manDataRecord">
<TypeDescriptors>
<TypeDescriptor TypeName="System.String" IdentifierName="Uid" Name="Uid" />
(...)
<TypeDescriptor TypeName="System.String" Name="Language" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="dbo.manFinder" Type="Finder" ReturnParameterName="dbo.man" ReturnTypeDescriptorName="dbo.manDataReader" ReturnTypeDescriptorLevel="0" />
</MethodInstances>
</Method>
<Method Name="dbo.manSpecificFinder">
<Properties>
<Property Name="RdbCommandText" Type="System.String">Select Uid, Language From dbo.man Where (Uid=@Uid)</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptor Type="Comparison" Name="Uid" />
</FilterDescriptors>
<Parameters>
<Parameter Direction="In" Name="@Uid">
<TypeDescriptor TypeName="System.String" IdentifierName="Uid" AssociatedFilter="Uid" Name="Uid" />
</Parameter>
<Parameter Direction="Return" Name="dbo.man">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.manDataReader" IsCollection="true">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.manDataRecord">
<TypeDescriptors>
<TypeDescriptor TypeName="System.String" IdentifierName="Uid" Name="Uid" />
(...)
<TypeDescriptor TypeName="System.String" Name="Language" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="dbo.manSpecificFinder" Type="SpecificFinder" ReturnParameterName="dbo.man" ReturnTypeDescriptorName="dbo.manDataReader" ReturnTypeDescriptorLevel="0" />
</MethodInstances>
</Method>
<Method Name="dbo.manIDEnumerator">
<Properties>
<Property Name="RdbCommandText" Type="System.String">Select Uid,firstname,lastname,middlename,title From dbo.man</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
</Properties>
<Parameters>
<Parameter Direction="Return" Name="Uids">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.mans" IsCollection="true">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.man">
<TypeDescriptors>
<TypeDescriptor TypeName="System.String" IdentifierName="Uid" Name="Uid" />
<TypeDescriptor TypeName="System.String" Name="firstname" />
<TypeDescriptor TypeName="System.String" Name="lastname" />
<TypeDescriptor TypeName="System.String" Name="middlename" />
<TypeDescriptor TypeName="System.String" Name="title" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="UidEnumeratorInstance" Type="IdEnumerator" ReturnParameterName="Uids" />
</MethodInstances>
</Method>
</Methods>
<Actions>
<Action Name="Email Action" Position="1" IsOpenedInNewWindow="true" Url="Mailto:{0}" ImageUrl="">
<ActionParameters>
<ActionParameter Name="Uid" Index="0" />
</ActionParameters>
</Action>
<Action Name="Google lastname" Position="2" IsOpenedInNewWindow="true" Url="http://www.google.com/search q={0}" ImageUrl="">
<ActionParameters>
<ActionParameter Name="lastname" Index="0" />
</ActionParameters>
</Action>
<Action Name="Delete man" Position="3" IsOpenedInNewWindow="true" Url="http://xxxdev:9099/DeleteEntry.asmx uid={0}" ImageUrl="http://xxx.mydomain.de/sites/EveryManagement/man/_layouts/delete_image.gif">
<ActionParameters>
<ActionParameter Name="Uid" Index="0" />
</ActionParameters>
</Action>
<Action Name="Edit man" Position="4" IsOpenedInNewWindow="true" Url="http://xxx.mydomain.de/sites/EveryManagement/_layouts/FormServer.aspx XsnLocation=http://xxx.mydomain.de/sites/EveryManagement/FormServerTemplates/mydomain_Master_man.xsn&amp;DefaultItemOpen=1&amp;uid={0}" ImageUrl="http://xxx.mydomain.de/sites/EveryManagement/_layouts/edit_man_image.gif">
<ActionParameters>
<ActionParameter Name="Uid" Index="0" />
</ActionParameters>
</Action>
<Action Name="Invite man...." Position="5" IsOpenedInNewWindow="true" Url="http://xxxdev:9099/Invite.asmx uid={0}" ImageUrl="http://xxx.mydomain.de/sites/EveryManagement/_layouts/invite_man_image.gif">
<ActionParameters>
<ActionParameter Name="Uid" Index="0" />
</ActionParameters>
</Action>
</Actions>
</Entity>
</Entities>
</LobSystem>



Re: SharePoint - Business Data Catalog Confusing Actions, missing search field/"Retrieve Data" link

Frank Chiang

Here are some answers:

# I remember that The BDC MetaMan created at first a Business Data List where a search field (not the MOSS search it was something like <primary key field> {is equal to, contains} <my search critera> "Retrieve data" button) was present. In the recent version there's no such field that restricts the number of displayed items, is it How can I create this selection field Is there a way to add more search fields instead of just the primary key field Or to add more filter critera (e.g. "begins with", "ends with",...) As far as I remember there was just an "And" conjunction of the filter criterias - is "(X)or" also possible

--> To add search criteria, you have to first add 'FilteredDescriptors', then you have to include them in your SQL statement, then you have to add in 'Edit View - Items to retrieve' when you add the webpart in your web page. After you have done all these, the search fields will appear along the business data list. You use SQL statement conditions (AND, OR, etc.) to define your search criteria, not when you search. This is not nice, but I think it is how it can work.

# Where is the column, in which the items's hover link is displayed, defined I guess it's somewhere in the XSL definition!

--> It is what you define in 'Title' property or you can change it in 'Edit View'.

# How can I add simultaneously an Action to (1) and (2)

# How can I add an Action just to (1) xor (2)

--> Actions are added in your definition file and will display in (1) if there is no parameter and display in both (1) and (2) if there is parameter.

# Is there a way to group items by a specific field (This is easily configured in a form library list)

You do this in SQL statement or in sort in 'Edit View' or sort on the page.

# Although I've created some Actions with the BDC MetaMan and all Actions are visible in the SSP admin page (http://<myserver>:<myport>/ssp/admin/_layouts/BDCAdminUI/ViewBDCEntity.aspx Id=123) no Action appears in the BDC List (neither at position (1) nor at positioin (2)!). Even if I try to add an action in SSP administration: There appears no Action in the hover link or in the upper left corner of the BDC list web part. The complete xml definition file is attachted at the bottom of this post [1].

-->When actions don't show up, it generally means your action(s) is not defined correctly. Try to just put 'www.msn.com' to see if it shows up.

Some other minor issues/possible bugs ( ):

- The attribute "Position" is wrongly created by MetaMan (Position="0" is not included).

--> Position "0" is for defult action, which is the 'Profile Page'

- If the SQL table contains field labels that are reserved names in SQL the query fails. It could be safer to enclose all fields in the SELECT-statement with brackets.

--> Sure.





Re: SharePoint - Business Data Catalog Confusing Actions, missing search field/"Retrieve Data" link

Erulu

Thanks a lot! Your answer helped me a lot. Some things are theoretically clearer, I'll try them practically now...



Re: SharePoint - Business Data Catalog Confusing Actions, missing search field/"Retrieve Data" link

Erulu

Frank Chiang wrote:
To add search criteria, you have to first add 'FilteredDescriptors', then you have to include them in your SQL statement, then you have to add in 'Edit View - Items to retrieve' when you add the webpart in your web page. After you have done all these, the search fields will appear along the business data list. You use SQL statement conditions (AND, OR, etc.) to define your search criteria, not when you search. This is not nice, but I think it is how it can work.

To add a search label ("a SQL table's column to search") to the "search field" in the Business Data List web part I've included this lines in my ADF:


<Identifier Name="lastname" TypeName="System.String" />
...
<FilterDescriptor Type="Comparison" Name="lastname" />
...
<Parameter Direction="In" Name="@aLastname">
<TypeDescriptor TypeName="System.String" IdentifierName="lastname" AssociatedFilter="lastname" Name="lastname" />
</Parameter>

The SQL statement (and the xml around it) looks like:

Select Uid,lastname From dbo.People Where (Uid&gt;=@GeneratedMinUid) and (Uid&lt;=@GeneratedMaxUid) and (lastname = @aLastname)


Uid is the primary key in the database's table.


Then I get the error message:

"The Business Data Catalog is configured incorrectly. Administrators, see the server log for more information."


The server logs contains:

A Metadata Exception was constructed in App Domain '/LM/W3SVC/2050891852/Root-1-128189382366966640'. The full exception text is: The parameter that has ID 2927 and is mapped to a SQL Server query parameter named @GeneratedMinUid has a null input value. Specify a Filter or a DefaultValue for this parameter if necessary.

I've tried to add a default value:

<Parameter Direction="In" Name="@GeneratedMinUid">
<TypeDescriptor TypeName="System.String" IdentifierName="Uid" AssociatedFilter="Uid" Name="Uid">
<DefaultValues>
<DefaultValue MethodInstanceName="dbo.ParticipantSpecificFinder" Type="System.String">%</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>

But this doesn't help!

# How can I specify a filter (as suggested in the server log)


# Is it necessary that every "Identifier" is a primary key in the database, is it


# Why creates the BDC Meta Man this statement:

Where (<myField> >=@GeneratedMin<myField>) and (<myField> <=@GeneratedMax<myField>)

This looks like a range-limited search but the search criteria is "is equal to". Wouldn't it be sufficient to just write


Where <myField> = @GeneratedMin<myField>


# Where are the the mins and max' are "created" I see that just one specific value is entered in the search text box that is compared with the <myField>s.

# Is there anywhere an example of how to search a non primary key field

Frank Chiang wrote:
You do this in SQL statement or in sort in 'Edit View' or sort on the page.

But this is not the same as the nice view in form libraries where you can collapse/expand grouped items with a [+] button, is it





Re: SharePoint - Business Data Catalog Confusing Actions, missing search field/"Retrieve Data" link

Frank Chiang

# How can I specify a filter (as suggested in the server log)

Creation of Application Definition File is one of those mysteries that is not really explained well in the documentation. What we have is a few sample files to help you through. The key to this problem is that the SQL statement has to work for both the FinderInstance and SpecificFinderInstance and therefore it has to be constructed with seemingly redundant parameters. Luckily there is a good example in AdventureWorks2000 or 2005. What I suggest you to do as a first step is to examine 'GetProducts' method and replicate what it is doing (it is exactly what you want to do), make sure that it works in your situation. BDCMetaMan is doing exactly like what the example does but I think you need to do &gt;= rather than >=.

# Where are the the mins and max' are "created" I see that just one specific value is entered in the search text box that is compared with the <myField>

Min and Max values are specified in the AppDefFile. They are never altered. Check out the AdventureWorks example. The 'GetProducts' method specifies them as 0 and 99999999 respectively. In a way it means you have to be sure that ProductID falls into this range.

# Is there anywhere an example of how to search a non primary key field

Searching non primary key field is no differnet than searching primary key field. Again check out the 'GetProducts' method. It searches on EnglishProductName and not the ProductKey to fill the list.

#But this is not the same as the nice view in form libraries where you can collapse/expand grouped items with a [+] button, is it

What you are thinking of is some kind of hierarchical representation of data that I think BDC doesn't do (as I said in my last post, it can do sorting). The best is to do master-detail representation using Business Data List and Business Data Related List.