Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SharePoint Products and Technologies >> SharePoint - Business Data Catalog

BDC lookup in office

Hi,

I am creating a document library in SharePoint. In the document library I have a BDC attached that gets the account data from my MS CRM database. By picking browse in the properties of a document I’m able to find accounts.However when I open a word template I don’t get the opportunity to pick browse. So the only option there is to enter a PK. Is there any way to sheach the accounts in word.

My BDC for conectiong to the crm DB:

<?xmlversion="1.0"standalone="yes"?>

<LobSystemxmlns: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.6.0.0"Name=" MSCRMLOBSystem">

<Properties>

<PropertyName="WildcardCharacter"Type="System.String">%</Property>

</Properties>

<LobSystemInstances>

<LobSystemInstanceName=" MSCRMInstance">

<Properties>

<PropertyName="DatabaseAccessProvider"Type="System.String">SqlServer</Property>

<PropertyName="AuthenticationMode"Type="System.String">RevertToSelf</Property>

<PropertyName="RdbConnection Data Source"Type="System.String">server</Property>

<PropertyName="RdbConnection Initial Catalog"Type="System.String">catalog</Property>

<PropertyName="RdbConnection Integrated Security"Type="System.String">SSPI</Property>

<PropertyName="RdbConnection Pooling"Type="System.String">false</Property>

</Properties>

</LobSystemInstance>

</LobSystemInstances>

<Entities>

<EntityEstimatedInstanceCount="0"Name="dbo.AccountBase">

<Properties>

<PropertyName="Title"Type="System.String">Name</Property>

</Properties>

<Identifiers>

<IdentifierName="AccountId"TypeName="System.String" />

</Identifiers>

<Methods>

<MethodName="GetAccounts">

<Properties>

<PropertyName="RdbCommandText"Type="System.String">Select AccountId,AccountCategoryCode,TerritoryId,DefaultPriceLevelId,CustomerSizeCode,PreferredContactMethodCode,CustomerTypeCode,AccountRatingCode,IndustryCode,TerritoryCode,AccountClassificationCode,DeletionStateCode,BusinessTypeCode,OwningBusinessUnit,OwningTeam,OwningUser,OriginatingLeadId,PaymentTermsCode,ShippingMethodCode,PrimaryContactId,ParticipatesInWorkflow,Name,AccountNumber,Revenue,NumberOfEmployees,Description,SIC,OwnershipCode,MarketCap,SharesOutstanding,TickerSymbol,StockExchange,WebSiteURL,FtpSiteURL,EMailAddress1,EMailAddress2,EMailAddress3,DoNotPhone,DoNotFax,Telephone1,DoNotEMail,Telephone2,Fax,Telephone3,DoNotPostalMail,DoNotBulkEMail,DoNotBulkPostalMail,CreditLimit,CreditOnHold,IsPrivate,CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,VersionNumber,ParentAccountId,Aging30,StateCode,Aging60,StatusCode,Aging90,PreferredAppointmentTimeCode,Merged,MasterId,PreferredSystemUserId,LastUsedInCampaign,PreferredServiceId,DoNotSendMM,PreferredAppointmentDayCode,PreferredEquipmentId From dbo.AccountBase where Name like '%'+ @Name +'%' OR AccountNumber like '%'+@Name +'%'</Property>

<PropertyName="RdbCommandType"Type="System.Data.CommandType">Text</Property>

</Properties>

<FilterDescriptors>

<FilterDescriptorType="Wildcard"Name="Name" />

</FilterDescriptors>

<Parameters>

<ParameterDirection="In"Name="@Name">

<TypeDescriptorTypeName="System.String"AssociatedFilter="Name"Name="Name" />

</Parameter>

<ParameterDirection="Return"Name="dbo.AccountBase">

<TypeDescriptorTypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"Name="dbo.AccountBaseDataReader"IsCollection="true">

<TypeDescriptors>

<TypeDescriptorTypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"Name="dbo.AccountBaseDataRecord">

<TypeDescriptors>

<TypeDescriptorTypeName="System.String"IdentifierName="AccountId"Name="AccountId" />

<TypeDescriptorTypeName="System.Int32"Name="AccountCategoryCode" />

<TypeDescriptorTypeName="System.String"Name="TerritoryId" />

<TypeDescriptorTypeName="System.String"Name="DefaultPriceLevelId" />

<TypeDescriptorTypeName="System.Int32"Name="CustomerSizeCode" />

<TypeDescriptorTypeName="System.Int32"Name="PreferredContactMethodCode" />

<TypeDescriptorTypeName="System.Int32"Name="CustomerTypeCode" />

<TypeDescriptorTypeName="System.Int32"Name="AccountRatingCode" />

<TypeDescriptorTypeName="System.Int32"Name="IndustryCode" />

<TypeDescriptorTypeName="System.Int32"Name="TerritoryCode" />

<TypeDescriptorTypeName="System.Int32"Name="AccountClassificationCode" />

<TypeDescriptorTypeName="System.Int32"Name="DeletionStateCode" />

<TypeDescriptorTypeName="System.Int32"Name="BusinessTypeCode" />

<TypeDescriptorTypeName="System.String"Name="OwningBusinessUnit" />

<TypeDescriptorTypeName="System.String"Name="OwningTeam" />

<TypeDescriptorTypeName="System.String"Name="OwningUser" />

<TypeDescriptorTypeName="System.String"Name="OriginatingLeadId" />

<TypeDescriptorTypeName="System.Int32"Name="PaymentTermsCode" />

<TypeDescriptorTypeName="System.Int32"Name="ShippingMethodCode" />

<TypeDescriptorTypeName="System.String"Name="PrimaryContactId" />

<TypeDescriptorTypeName="System.Boolean"Name="ParticipatesInWorkflow" />

<TypeDescriptorTypeName="System.String"Name="Name" />

<TypeDescriptorTypeName="System.String"Name="AccountNumber" />

<TypeDescriptorTypeName="System.Decimal"Name="Revenue" />

<TypeDescriptorTypeName="System.Int32"Name="NumberOfEmployees" />

<TypeDescriptorTypeName="System.String"Name="Description" />

<TypeDescriptorTypeName="System.String"Name="SIC" />

<TypeDescriptorTypeName="System.Int32"Name="OwnershipCode" />

<TypeDescriptorTypeName="System.Decimal"Name="MarketCap" />

<TypeDescriptorTypeName="System.Int32"Name="SharesOutstanding" />

<TypeDescriptorTypeName="System.String"Name="TickerSymbol" />

<TypeDescriptorTypeName="System.String"Name="StockExchange" />

<TypeDescriptorTypeName="System.String"Name="WebSiteURL" />

<TypeDescriptorTypeName="System.String"Name="FtpSiteURL" />

<TypeDescriptorTypeName="System.String"Name="EMailAddress1" />

<TypeDescriptorTypeName="System.String"Name="EMailAddress2" />

<TypeDescriptorTypeName="System.String"Name="EMailAddress3" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotPhone" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotFax" />

<TypeDescriptorTypeName="System.String"Name="Telephone1" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotEMail" />

<TypeDescriptorTypeName="System.String"Name="Telephone2" />

<TypeDescriptorTypeName="System.String"Name="Fax" />

<TypeDescriptorTypeName="System.String"Name="Telephone3" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotPostalMail" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotBulkEMail" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotBulkPostalMail" />

<TypeDescriptorTypeName="System.Decimal"Name="CreditLimit" />

<TypeDescriptorTypeName="System.Boolean"Name="CreditOnHold" />

<TypeDescriptorTypeName="System.Boolean"Name="IsPrivate" />

<TypeDescriptorTypeName="System.DateTime"Name="CreatedOn" />

<TypeDescriptorTypeName="System.String"Name="CreatedBy" />

<TypeDescriptorTypeName="System.DateTime"Name="ModifiedOn" />

<TypeDescriptorTypeName="System.String"Name="ModifiedBy" />

<TypeDescriptorTypeName="System.Byte[]"Name="VersionNumber" />

<TypeDescriptorTypeName="System.String"Name="ParentAccountId" />

<TypeDescriptorTypeName="System.Decimal"Name="Aging30" />

<TypeDescriptorTypeName="System.Int32"Name="StateCode" />

<TypeDescriptorTypeName="System.Decimal"Name="Aging60" />

<TypeDescriptorTypeName="System.Int32"Name="StatusCode" />

<TypeDescriptorTypeName="System.Decimal"Name="Aging90" />

<TypeDescriptorTypeName="System.Int32"Name="PreferredAppointmentTimeCode" />

<TypeDescriptorTypeName="System.Boolean"Name="Merged" />

<TypeDescriptorTypeName="System.String"Name="MasterId" />

<TypeDescriptorTypeName="System.String"Name="PreferredSystemUserId" />

<TypeDescriptorTypeName="System.DateTime"Name="LastUsedInCampaign" />

<TypeDescriptorTypeName="System.String"Name="PreferredServiceId" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotSendMM" />

<TypeDescriptorTypeName="System.Int32"Name="PreferredAppointmentDayCode" />

<TypeDescriptorTypeName="System.String"Name="PreferredEquipmentId" />

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstanceName="GetAccounts"Type="Finder"ReturnParameterName="dbo.AccountBase"ReturnTypeDescriptorName="dbo.AccountBaseDataReader"ReturnTypeDescriptorLevel="0" />

</MethodInstances>

</Method>

<MethodName="AccountsSpecificFinder">

<Properties>

<PropertyName="RdbCommandText"Type="System.String">Select AccountId,AccountCategoryCode,TerritoryId,DefaultPriceLevelId,CustomerSizeCode,PreferredContactMethodCode,CustomerTypeCode,AccountRatingCode,IndustryCode,TerritoryCode,AccountClassificationCode,DeletionStateCode,BusinessTypeCode,OwningBusinessUnit,OwningTeam,OwningUser,OriginatingLeadId,PaymentTermsCode,ShippingMethodCode,PrimaryContactId,ParticipatesInWorkflow,Name,AccountNumber,Revenue,NumberOfEmployees,Description,SIC,OwnershipCode,MarketCap,SharesOutstanding,TickerSymbol,StockExchange,WebSiteURL,FtpSiteURL,EMailAddress1,EMailAddress2,EMailAddress3,DoNotPhone,DoNotFax,Telephone1,DoNotEMail,Telephone2,Fax,Telephone3,DoNotPostalMail,DoNotBulkEMail,DoNotBulkPostalMail,CreditLimit,CreditOnHold,IsPrivate,CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,VersionNumber,ParentAccountId,Aging30,StateCode,Aging60,StatusCode,Aging90,PreferredAppointmentTimeCode,Merged,MasterId,PreferredSystemUserId,LastUsedInCampaign,PreferredServiceId,DoNotSendMM,PreferredAppointmentDayCode,PreferredEquipmentId From dbo.AccountBase where AccountId=@AccountId</Property>

<PropertyName="RdbCommandType"Type="System.Data.CommandType">Text</Property>

</Properties>

<FilterDescriptors>

<FilterDescriptorType="Comparison"Name="AccountId" />

</FilterDescriptors>

<Parameters>

<ParameterDirection="In"Name="@AccountId">

<TypeDescriptorTypeName="System.String"IdentifierName="AccountId"AssociatedFilter="AccountId"Name="AccountId" />

</Parameter>

<ParameterDirection="Return"Name="Accounts">

<TypeDescriptorTypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"Name="dbo.AccountBaseDataReader"IsCollection="true">

<TypeDescriptors>

<TypeDescriptorTypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"Name="dbo.AccountBaseDataRecord">

<TypeDescriptors>

<TypeDescriptorTypeName="System.String"IdentifierName="AccountId"Name="AccountId" />

<TypeDescriptorTypeName="System.Int32"Name="AccountCategoryCode" />

<TypeDescriptorTypeName="System.String"Name="TerritoryId" />

<TypeDescriptorTypeName="System.String"Name="DefaultPriceLevelId" />

<TypeDescriptorTypeName="System.Int32"Name="CustomerSizeCode" />

<TypeDescriptorTypeName="System.Int32"Name="PreferredContactMethodCode" />

<TypeDescriptorTypeName="System.Int32"Name="CustomerTypeCode" />

<TypeDescriptorTypeName="System.Int32"Name="AccountRatingCode" />

<TypeDescriptorTypeName="System.Int32"Name="IndustryCode" />

<TypeDescriptorTypeName="System.Int32"Name="TerritoryCode" />

<TypeDescriptorTypeName="System.Int32"Name="AccountClassificationCode" />

<TypeDescriptorTypeName="System.Int32"Name="DeletionStateCode" />

<TypeDescriptorTypeName="System.Int32"Name="BusinessTypeCode" />

<TypeDescriptorTypeName="System.String"Name="OwningBusinessUnit" />

<TypeDescriptorTypeName="System.String"Name="OwningTeam" />

<TypeDescriptorTypeName="System.String"Name="OwningUser" />

<TypeDescriptorTypeName="System.String"Name="OriginatingLeadId" />

<TypeDescriptorTypeName="System.Int32"Name="PaymentTermsCode" />

<TypeDescriptorTypeName="System.Int32"Name="ShippingMethodCode" />

<TypeDescriptorTypeName="System.String"Name="PrimaryContactId" />

<TypeDescriptorTypeName="System.Boolean"Name="ParticipatesInWorkflow" />

<TypeDescriptorTypeName="System.String"Name="Name" />

<TypeDescriptorTypeName="System.String"Name="AccountNumber" />

<TypeDescriptorTypeName="System.Decimal"Name="Revenue" />

<TypeDescriptorTypeName="System.Int32"Name="NumberOfEmployees" />

<TypeDescriptorTypeName="System.String"Name="Description" />

<TypeDescriptorTypeName="System.String"Name="SIC" />

<TypeDescriptorTypeName="System.Int32"Name="OwnershipCode" />

<TypeDescriptorTypeName="System.Decimal"Name="MarketCap" />

<TypeDescriptorTypeName="System.Int32"Name="SharesOutstanding" />

<TypeDescriptorTypeName="System.String"Name="TickerSymbol" />

<TypeDescriptorTypeName="System.String"Name="StockExchange" />

<TypeDescriptorTypeName="System.String"Name="WebSiteURL" />

<TypeDescriptorTypeName="System.String"Name="FtpSiteURL" />

<TypeDescriptorTypeName="System.String"Name="EMailAddress1" />

<TypeDescriptorTypeName="System.String"Name="EMailAddress2" />

<TypeDescriptorTypeName="System.String"Name="EMailAddress3" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotPhone" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotFax" />

<TypeDescriptorTypeName="System.String"Name="Telephone1" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotEMail" />

<TypeDescriptorTypeName="System.String"Name="Telephone2" />

<TypeDescriptorTypeName="System.String"Name="Fax" />

<TypeDescriptorTypeName="System.String"Name="Telephone3" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotPostalMail" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotBulkEMail" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotBulkPostalMail" />

<TypeDescriptorTypeName="System.Decimal"Name="CreditLimit" />

<TypeDescriptorTypeName="System.Boolean"Name="CreditOnHold" />

<TypeDescriptorTypeName="System.Boolean"Name="IsPrivate" />

<TypeDescriptorTypeName="System.DateTime"Name="CreatedOn" />

<TypeDescriptorTypeName="System.String"Name="CreatedBy" />

<TypeDescriptorTypeName="System.DateTime"Name="ModifiedOn" />

<TypeDescriptorTypeName="System.String"Name="ModifiedBy" />

<TypeDescriptorTypeName="System.Byte[]"Name="VersionNumber" />

<TypeDescriptorTypeName="System.String"Name="ParentAccountId" />

<TypeDescriptorTypeName="System.Decimal"Name="Aging30" />

<TypeDescriptorTypeName="System.Int32"Name="StateCode" />

<TypeDescriptorTypeName="System.Decimal"Name="Aging60" />

<TypeDescriptorTypeName="System.Int32"Name="StatusCode" />

<TypeDescriptorTypeName="System.Decimal"Name="Aging90" />

<TypeDescriptorTypeName="System.Int32"Name="PreferredAppointmentTimeCode" />

<TypeDescriptorTypeName="System.Boolean"Name="Merged" />

<TypeDescriptorTypeName="System.String"Name="MasterId" />

<TypeDescriptorTypeName="System.String"Name="PreferredSystemUserId" />

<TypeDescriptorTypeName="System.DateTime"Name="LastUsedInCampaign" />

<TypeDescriptorTypeName="System.String"Name="PreferredServiceId" />

<TypeDescriptorTypeName="System.Boolean"Name="DoNotSendMM" />

<TypeDescriptorTypeName="System.Int32"Name="PreferredAppointmentDayCode" />

<TypeDescriptorTypeName="System.String"Name="PreferredEquipmentId" />

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstanceName="AccountsSpecificFinder"Type="SpecificFinder"ReturnParameterName="Accounts"ReturnTypeDescriptorName="dbo.AccountBaseDataReader"ReturnTypeDescriptorLevel="0" />

</MethodInstances>

</Method>

</Methods>

<Actions>

<ActionName="CRM Search"Position="1"IsOpenedInNewWindow="true"Url="http://servername/sfa/accts/edit.aspx?id={0}"ImageUrl="">

<ActionParameters>

<ActionParameterName="AccountId"Index="0"/>

</ActionParameters>

</Action>

</Actions>

</Entity>

</Entities>

</LobSystem>

[194506 byte] By [WardNoppe] at [2008-2-4]
«« Uninstalling from winsxs
»» Increasing Form Transparency from 0 to 100
# 1
If we can get an answer to this question from Microsoft that would be awesome. It appears the boat was missed on the BDC integration with Office 2007. Although Office 2007 shows the metadata information from the BDC directly, it makes it virtually impossible to update the metadata from Office 2007. This especially becomes a problem when the BDC column is marked as a required field.
PLiebrand at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 2

You will have to customize the Document Information Panel using Infopath 2007. You can use dropdown list boxes and populate them with data of your choice so that it is selectable in Office applications such as Word.

If you are not familiar with Infopath 2007, you will have to learn it first before attempting something like this.

FrankChiang at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 3
I know you can customize the Document Information panel. But the minute you do that then anytime you add additional meta data you have to manually go back and modify your document information panel. This does not seem to be the most efficient use of someone's time. The problem is not with the customize of the information panel, it is the problem that Office will recognize the BDC columns and automatically add them to the information panel, however, when you attempt to interact with them it fails.
PLiebrand at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 4

Let me just say that the lack of a BDC Metadata Configuration Manager from Microsoft is the biggest abuse of our time, so reconfiguring the Document Information Panel when metadata is changed seems to be not too bad.

The reason why pick list works in Sharepoint is that you have configured the BDC. But when you open a Word document, only the required data is sent over to Word, not the configuration (Word doesn't know BDC) and not the pick list. From this point on, it is the Document Information Panel (along with Infopath) that has to interface with the backend database to populate the pick list.

I guess if the Microsoft developers really push it, they can get the automation done, but they probably have this and/or other reasons.

FrankChiang at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 5

I agree, in my assessment the BDC integration with Office just doesn't cut it in the real world, and custom Document Information Panels for an Enterprise Portal of any size will quickly become a nightmare. Office 2007 clients must mave SOME BDC interaction, since it can validate valid values for keys. The lack of Finder support makes no sense to me, even for the simple example. If a finder is needed to locate a relevant item (e.g. a customer ID from a name) in a SharePoint list, it would make sense that if I am working in Word and saving a document to my document library, that I would need the same finder.

IMHO, this lack of finder support makes BDC columns in document libraries a feature that I would stay away from unless absolutely necessary (I've been down this road on a real world MOSS implementation)..

DC

collom at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 6

Hi Frank,

I was looking into writing a blog post to demonstrate your solution, but I'm not even sure it's possible.

If you want to edit the Document Information Panel, you need to create your own content type. With a content type, you can only add (or create new) site columns. Unfortunately with MOSS 2007 RTM you can't create a site column of Business Data type.

I guess you could create standard text columns for the content type and fill it with BDC data collected from the InfoPath Form but it kind of wouldn't be the same.

Nick

NickSwan at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 7

Nick,

My apology if I wasn't clear about what I said. Since Document Information Panel can be edited in IP, then one can replace a text box with a dropdown list, and one can retrieve a list from the backend database (by creating a secondary data source) to populate the dropdown list, just like BDC does for SP list. When a user selects a choice from the dropdown, it is equivalent to putting a value in a textbox, so the whole save process is the same but the user has a much better user experience with the dropdown listbox selection.

FrankChiang at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 8
right, so you don't use the business data type column in your content type, just a normal column of type text
NickSwan at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 9


I created a little App that synchronizes BDC with a normal SP Liste at Site Collection level. Then, I can create a Site Column with this data and use that column as meta data in documents.

Problem here:

The document information panel in Office 2007 clients (Word 2007) only displays the first 100 entries in the dropdown. I have not found a way to show more than 100 entries.

Magganpice at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
# 10
As awesome as the BDC is with SharePoint - Microsoft definitely missed the boat with the Office 2007 integration. My guess is you will have to wait for MOSS 2012 and Office 2012 to use that feature =)
PLiebrand at 2007-9-5 > top of Msdn Tech,SharePoint Products and Technologies,SharePoint - Business Data Catalog...
SharePoint Products and Technologies
SharePoint - Business Data Catalog

SharePoint Products and Technologies Hot Topic

SharePoint Products and Technologies New Topic

SharePoint Products and Technologies

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified