Integration MOSS 2007 – Dynamics AX with BDC through a custom field type (part 1) ~ IT Pro Ramblings

Integration MOSS 2007 – Dynamics AX with BDC through a custom field type (part 1)

by Patrik Luca 0 comments

Introduction

In this series of post, I’ll show how you can integrate Microsoft Office SharePoint Server 2007 with another line of business system, more specifically Dynamics AX.

The purpose is to create a custom field type, which can be added as a column to a SharePoint list. This SharePoint column renders a dropdown box with values from a Microsoft SQL Server table’s column. As such, in stead of duplicating the possible values on your SharePoint box each time new values are added in your line of business system, the available values for this SharePoint column are always in synch with your line of business system.

This first post describes how to create your connection between Microsoft Office SharePoint Server 2007 and Dynamics AX.

In a following post, I’ll describe how to create the custom field type.

In a third post, I'll describe how to fill the dropdown list dynamically for the custom field based on the url.

Create a Business Data Catalog application definition file

To be able to communicate with the Dynamics AX database, you need to create a Business Data Catalog Application Definition File, which defines the entities and associations used by the Business Data Catalog (more information: see Business Data Catalog Application Definition File).

The Microsoft Business Data Catalog Definition Editor helps you to author application definition files for the Business Data Catalog. For more information, see Install the Business Data Catalog Definition Editor Tool and Creating a Database Connection by Using the Business Data Catalog Definition Editor.

I want my dropdown SharePoint custom field type get its values from the PROJACTIVITY table in Microsoft Dynamics AX. The next steps describe how to make the link to this table in BDC.

Add LOB system database connection and enter the following text for Connection String:
```
Server=<DATABASE_SERVER_NAME>\<INSTANCE_NAME>
;Database=<DAXDatabase>;Integrated Security=SSPI;
```
Replace <DATABASE_SERVER_NAME>\<INSTANCE_NAME> with the name of your database server and <DAXDatabase> with the name of the database used by Microsoft Dynamics AX.

Drag and drop the PROJACTIVITY table to the left.

Set the AuthenticationMode to RevertToSelf for your Instance.

Add a new Method: we will use this method to retrieve all Activities of a given Project. I’ll call this method in this example FindByProjId_<>.

Give the Method a Name and a DefaultDisplayName. Enter the RdbCommandText to retrieve all fields from the PROJACTIVITY table for a given Project.

Create a new Parameter for this newly created Method of type Direction In and enter @PROJID as Name and DefaultDisplayName.

Add a second Parameter of type Direction In: @DATAAREAID.

Add a third Parameter of type Direction Return and enter a Name and DefaultDisplayName.

Create Root TypeDescriptor for both added Parameters and Add TypeDescriptors for your Return Parameter (you can copy-paste most of the properties from one of the other Methods).

Add a Method Instance of type Finder. Enter a Name and DefaultDisplayName.

Enter a value for the Title property on the Entity. This column will be used to display the action menu.

Enter a LOB System Name.

Your tree of your application definition file should look like this by now:

Export your application definition file.

Go to your Shared Service Provider in SharePoint Central Administration. Choose Import application definition in the Business Data Catalog section and import your exported file.

Test your Business Data Catalog application definition file

Go to your Shared Service Provider and choose View entities in the Business Data Catalog section.

Choose your created Entity.

In the Actions section, you’ll find a View Profile line with a url next to it: copy this url in your clipboard.

Paste the url from your clipboard in Internet Explorer and replace the parameter values ({0}, {1}, ..) with actual values from your Dynamics AX table and hit the Enter key: you should see the detail of your Dynamics AX record in your browser now if everything works.

Remarks

The system account of your SharePoint application pool user might need read access to your Dynamics AX database.

14 May 2009