Si tu manum coding ADF lima facio multus of code / necessarium / test circuitus, use the version number to make your life easier.

Odi illud admittere,, sed usque hoc septimana, I was always deleting the ADF and re-importing it. This would break my business data columns and make me re-wire them. All unnecessary.

File this under "it’s obvious once you see it".

Verbigratia:

<LobSystem
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd"
  Typus="WebService"
  Versionem="1.2.0.0" Nomen="xyzzyDocumentReview"
  xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

Vestibulum et Re-version important et existentem negotium notitia agmen utitur updated version automatically cum nulls configuration requisita.

</finem>

Scribet ad mea blog.

In my on-going attempts at providing a more useful lookup column using BDC, I hit a wall with the BDC picker. If you haven’t see it, the BDC picker is similar to a people picker except that it works with columns of type "business data".

You access the picker by clicking on the open book icon of a business data column as shown:

The above image shows a business data column called "Master Document Id". That column is connected, via BDC, to a web service. The web service returns two columns of information: Document ID and Title. The business purpose here is to provide a "this document is based on" munus. Users select a "master" document and when they save, an event receiver copies meta data fields from the referenced master.

By default, the BDC picker looks like this when I search for a document whose ID = "38":

That’s helpful, but not good enough. People don’t think in terms of IDs, they think in terms of titles and/or other meta data. The picker allows you to search on other columns (e.g. Title) but won’t show the actual list of titles it found, just their DocId’s as shown here:

(The screen shot isn’t so great because I didn’t pick a search that returns any valid results, but you can see that if it had found some results, it would only have shown DocId’s, not titles).

I searched high and low for the answer to this and failed. Collega, the venerable Jonathan Bradshaw, had faced and solved this issue. When I reached out to him for help, he pointed me in the right direction.

Configure the picker to show multiple columns via the "ShowInPicker" property in the ADF:

<Proprietas Nomen="ShowInPicker" Typus="System.Boolean">verum</Proprietas>

In more detail:

  <!-- Title -->
  <TypeDescriptor TypeName="System.String" Nomen="Title" >
    <LocalizedDisplayNames>
      <LocalizedDisplayName LCID="1033">Title</LocalizedDisplayName>
    </LocalizedDisplayNames>
    <Proprietates>
      <Proprietas Nomen="DisplayByDefault" Typus="System.Boolean">verum</Proprietas>
      <Proprietas Nomen="ShowInPicker" Typus="System.Boolean">verum</Proprietas>
    </Proprietates>
  </TypeDescriptor>

Setting this property does introduce a minor problem. As soon as you set it once, you need to set it for every column you want to show. In meam, BDC picker showed DocId by default. Autem, once I added "ShowInPicker" to Title, DocId no longer displayed. I solved that by explicitly setting the ShowInPicker property for Doc ID.

Here is the result:

(I’ll explain the odd-looking "168 – CamlSchema.xsd" construction in a future blog post. In short, it’s a concatenated string that allows for a slightly better user experience).

Utique, having written this blog entry, I just did a search for "ShowInPicker" and found numerous hits, including this one: http://msdn2.microsoft.com/en-us/library/ms583986.aspx. It explains the meaning of that property along with some other good BDC stuff.

</finem>

Scribet ad mea blog!

Quondam etiam innectis BDC per manum ADF files (iniquitatem meam, ut "salvum me PRATUM!" cred) et ledo hoc laetificum errore:

"Application definition import failed. Sequens erroris inciderat:"

Ut potest, illic 'an erroris, sed … quid dicturus est turpis.

In meam, the issue turned out that I had started off with a functional ADF for a different project that connected to a database and executed a SQL query against a view. In this new project, I am calling a method on a web service. I had stripped out the DB specific stuff and added my web service stuff, sed defecit ad update <LobSystem>’s Type attribute. I switched it to "WebService" et ad beate moveri newer et magis excitando momento errores, quae tractanda sunt in ordine suo.

Hic est iniuriam LobSystem:

<LobSystem
xmlns:xsi =http://www.w3.org/2001/XMLSchema-instance
xsi:schemaLocation =http://schemas.microsoft.com/office/2006/03/BusinessDataCatalogBDCMetadata.xsd
Type="Database"
Version="1.0.0.0" Name="xyzzy"
xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

Hoc est rectam:

<LobSystem
xmlns:xsi =http://www.w3.org/2001/XMLSchema-instance
xsi:schemaLocation =http://schemas.microsoft.com/office/2006/03/BusinessDataCatalogBDCMetadata.xsd
Type="WebService"
Version="1.0.0.0" Name="xyzzy"
xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

</finem>

UPDATE: Hoc MSDN dispositis habet aliquid interesting animadversiones ex JXJ fundatur eius, maxime negative, Hanc viam experientiae descendentis: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2623565&SiteID=1

We have a business scenario where we need to link two documents libraries via a common "document ID" agrum.

We often use a lookup column to implements links like this. There are several drawbacks to lookup columns, quorum tres sunt,:

1. Only one column from the lookup lookup library can be linked.
2. Perficientur: The source library could contain hundreds of entries. That’s too many entries in the lookup.
3. Quaerere: There is no integrated search. I don’t mean in the MOSS sense of search, Sed nihil est quaerere / spurcamen multipliciter columnas principium tabellae bibliothecam pagina vis collocare.

Subnixum BDC, we can use a "business data" column type and it provides a superior search and even allows multiple columns of data to appear in list views.

Proin praeparant aliqua de re plura scribam cum aditus consilium.

Si youve 'operatus est, et ante hoc angulo quidquam comments, Placere participes!

</finem>

Ive 'been opus in BDC hodie, coding ADF files by hand and generating myself some errors. One such error:

Applicatio definitio important defecit. Sequens erroris inciderat: Non oneratis Type describitur per TypeDescriptor TypeName scriptor. Nomen modularis: typeName error invenitur vel paulo ante Line: '35’ et Position: ’20’.

MUSCUS praetulit hunc errorem Ego conatus ad important lima ADF pron.

Ego investigentur quam Internets et invenerunt me indiciunt lob, sicut in nómine (ex <LobSystemInstance>) in mea <TypeDescriptor> node cum essem referenced in nomine ipsius lob (ex <LobSystem>).

Iniuriam:

<TypeDescriptor TypeName="Conchango.KeyValue, Instantia nomen lob" Name="KeyValue">

Corripe:

<TypeDescriptor TypeName="Conchango.KeyValue, Lob Name" Name="KeyValue">

Spero hanc unam alteramve horam aliquem salvat tempore.

</finem>

Scribet ad mea blog!

Ego causatur a BDC error hoc septimana quod manifestavit se in user interface et in in 12 alveare stipes at runtime.

Primum, hoc apparuit in in user interface:

Posset non agros inserere omnes Identifier valores recte exequi a SpecificFinder MethodInstance cum nomen … Accumsan porttitor input habent TypeDescriptors coniungitur cum omni Identifier definitur hoc Ente.

Hic 'a screen iecit:

Uideri possem facere verbum hoc 12 alveare stipes nutum (using my patented high-tech-don’t-try-this-at-home "mysterious errors" methodo):

11/14/2007 09:24:41.27 w3wp.exe (0x080C) 0x0B8C SharePoint Portal Server Business Data 6q4x High Exception in BusinessDataWebPart.OnPreRender: System.InvalidOperationException: Identifier ad valentiam ”, type ”, invalida. Expected Identifier value of Type ‘System.String’. at Microsoft.Office.Server.ApplicationRegistry.MetadataModel.Entity.FindSpecific(Obiectum[] subIdentifierValues, LobSystemInstance lobSystemInstance) at Microsoft.SharePoint.Portal.WebControls.BdcClientUtil.FindEntity(Entitate Entitas, Obiectum[] userValues, LobSystemInstance lobSystemInstance) at Microsoft.SharePoint.Portal.WebControls.BusinessDataItemBuilder.GetEntityInstance(View desiredView) at Microsoft.SharePoint.Portal.WebControls.BusinessDataDetailsWebPart.GetEntityInstance() at Microsoft.SharePoint.Portal.WebControls.BusinessDataDetailsWebPart.SetDataSourceProperties()

Quæsivi circa et invenit in aliquo vestrum MSDN forum, but they weren’t enough for me to understand what I was doing wrong. I watched a webcast by Ted Pattison quod meae cuneum squirreled ablata fuerit cultor est, et venit ad animadverto forsit meo.

In meam ADF, Ego coniuncta ad database ut ostensum SQL:

            <Proprietas Nomen="RdbCommandText" Typus="System.String">
              <![CDATA[
                OPTO
                      , CARRIER_ID, EFFDT, DESCR, EFF_STATUS, TAXPAYER_ID, NETWORK_ID, FRT_FORWARD_FLG, ALT_NAME1, ALT_NAME2, LANGUAGE_CD,
                      RUS, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CIVITAS, NUM1, NUM2, HOUSE_TYPE, ADDR_FIELD1, ADDR_FIELD2, ADDR_FIELD3,
                      COMIVA, LOQUOR, Praesent, GEO_CODE, IN_CITY_LIMIT, COUNTRY_CODE, Phone, PROPAGATIO, Fax, LAST_EXP_CHK_DTTM, FREIGHT_VENDOR,
                      INTERLINK_DLL, TMS_EXCLUDE_FLG
                 (nolock)
                UBI
                  (Leas <> 'Vomere) et
                  (demitto(CARRIER_ID) >Inferior-(@ MinID)) et
                  (demitto(CARRIER_ID) <Inferior-(@ MaxId)) et
                  (demitto(DESCR) Sicut inferiora(@ InputDescr))
                ]]>
            </Proprietas>

SQL DBA, si quis a me, et ego suus datur intelligi quod specialis view they created just for me. The unique key there is CARRIER_ID.

Ego introducta est hic cimex:

      <Identifiers>
        <Identify Nomen="CARRIER_ID" TypeName="System.String" />
        <Identify Nomen="DESCR" TypeName="System.String" /> 
      </Identifiers>

Alicubi in linea, Ego gessisset confundas me super significationem <Identifiers> and added DESCR even though it’s not actually an identifier. I took DESCR out of the identifiers set and presto! Omne fermentatum.

I hope this saves someone some grief 🙂

I’ve noticed some awkward and unnecessary hand-encoding of RdbCommandText in some examples (including MSDN documentation).

I wanted to point out to newcomers to BDC that commands can be wrapped inside a CDATA tag in their "natural" form. Ita, this awkward construction:

<Proprietas Nomen="RdbCommandText" Typus="System.String">
  SELECT dbo.MCRS_SETTLEMENT.id, dbo.MCRS_SETTLEMENT.settlement from dbo.MCRS_SETTLEMENT 
    UBI (id &gt;= @MinId) ATQUE (id &LT;= @ MaxId)
</Proprietas>

can be better represented this way:

<Proprietas Nomen="RdbCommandText" Typus="System.String">
<![CDATA[
SELECT dbo.MCRS_SETTLEMENT.id, dbo.MCRS_SETTLEMENT.settlement from dbo.MCRS_SETTLEMENT
UBI (id >= @MinId) ATQUE (id <= @MaxId)
]]>
</Proprietas>

</finem>

BDC Primer

Intro to BDC

I needed to wire up MOSS to a SQL database via BDC. For testing/POC purposes, I wanted to embed the SQL account user id and password in the ADF. Starting with this template (http://msdn2.microsoft.com/en-us/library/ms564221.aspx), I created an ADF that connects to a particular SQL server instance and logs in with a specific user id and password and shown in this snippet:

  <LobSystemInstances>
    <LobSystemInstance Nomen="ClaimsInstance">
      <Proprietates>
        <Proprietas Nomen="AuthenticationMode" Typus="System.String">PassThrough</Proprietas>
        <Proprietas Nomen="DatabaseAccessProvider" Typus="System.String">SqlServer</Proprietas>
        <Proprietas Nomen="RdbConnection Data Source" Typus="System.String">actual server\actual instance</Proprietas>
        <Proprietas Nomen="RdbConnection Initial Catalog" Typus="System.String">actual initial catalog</Proprietas>
        <Proprietas Nomen="RdbConnection Integrated Security" Typus="System.String">SSPI</Proprietas>
        <Proprietas Nomen="RdbConnection Pooling" Typus="System.String">falsum</Proprietas>

        <!-- These are the key values: -->
        <Proprietas Nomen="RdbConnection User ID" Typus="System.String">actual User ID</Proprietas>
        <Proprietas Nomen="RdbConnection Password" Typus="System.String">actual Password</Proprietas>
        <Proprietas Nomen="RdbConnection Trusted_Connection" Typus="System.String">falsum</Proprietas>

      </Proprietates>
    </LobSystemInstance>
  </LobSystemInstances>

It is not a best practice, but it’s useful for a quick and simple configuration for testing. This was surprisingly difficult to figure out. I never found a functional example with search keywords:

• adf embedded userid and password
• embed user id and password in adf
• embed user id and password in adf bdc
• sharepoint bdc primer
• sharepoint embed user id and password in adf

</finem>

Scribet ad mea blog.