My 2p about ERP Solutions, Information Worker Solutions and other software products (mainly Microsoft Dynamics AX and Microsoft SharePoint).

20 July 2009

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

by Patrik Luca 14 comments

Tag



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 this post, I’ll describe how to create the custom field type.

In a following post, I’ll describe how to make this custom field type more generic by using a dynamic where clause in the query.

Preparation

  1. In Visual Studio, select External Tools from the Tools menu.
  2. In the External Tools dialog, click Add and enter Get Assembly Public Key for the Title.
  3. Fill the Command textbox by browsing to C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\sn.exe.
  4. In the Arguments textbox, type the following (case sensitive) –Tp $(TargetPath).
  5. Enable the Use Output window checkbox.
  6. Click OK. The new command is added to the Tools menu.

See also: Prepare for Development of Custom Field Rendering Controls.

Create a Visual Studio project

  1. Create a Class Library project with Microsoft Visual Studio 2005.

  2. Create a folder structure in your project.

  3. Add a reference to the System.Web dll (add it by selecting it in the .NET tab page) and the dlls: Microsoft.Office.Server, Microsoft.SharePoint and microsoft.sharepoint.portal (add them by browsing to C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI to find it).

  4. Right-click the project name in Solution Explorer and select Properties.
  5. On the Application tab of the Properties dialog, enter MyCompany.SharePoint.DAX.CustomFields as the Default namespace and MyCompany.SharePoint.DAX.CustomFields.DAXProjActivityTxt as the Assembly name.
  6. Open the Signing tab and the select Sign the assembly.
  7. Choose <New…> from the Choose a strong name key file drop-down list box.
  8. In the Create Strong Name Key dialog, type MyCompany.SharePoint.DAX.CustomFields.DAXProjActivityTxt in the Key file name box, and then be sure that the Protect … check box is not checked. Click OK.
  9. Open the Build Events tab and type the following in the Post-build event command line box.
    cd "$(ProjectDir)"
    "%programfiles%\microsoft visual studio 8\sdk\
    v2.0\bin\gacutil" /i "$(TargetPath)" /nologo /f
    %systemroot%\system32\iisapp.vbs /a
    "SharePoint_App_Pool" /r
    xcopy *.ascx
    "C:\Program Files\Common Files\Microsoft Shared\
    web server extensions\12\TEMPLATE\CONTROLTEMPLATES\"
    /y
    xcopy 12\Template\xml\fldtypes*.xml
    "C:\Program Files\Common Files\Microsoft Shared\
    web server extensions\12\TEMPLATE\XML\" /y

  10. SharePoint_App_Pool with the actual name of the Internet Information Server (IIS) Application Pool that is assigned to your Windows SharePoint Services Web Application.

See also: To set up the custom field project.


Create the Field Rendering Control


  1. Create the Field Rendering Control, to render your custom field in New mode or Edit mode (see Field Rendering Controls). Rename the Class1.cs file to DAXProjActivityTxtFieldControl.cs.

  2. Edit the DAXProjActivityTxtFieldControl.cs and change the namespace to MyCompany.SharePoint.DAX.CustomFields.

  3. Add the necessary using statements:

    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.HtmlControls;
    using Microsoft.SharePoint.WebControls;
    using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;
    using Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db;
  4. Replace the Class declaration with the following code:

    public class DAXProjActivityTxtFieldControl : TextField
    {
    }
  5. Add following code lines to define the controls:
    private DropDownList DAXProjActivityTxtDropDownList;
    private HtmlTable table;
  6. Add the following override of the CreateChildControls method.

    protected override void CreateChildControls()
    {
    base.CreateChildControls();
    this.table = new HtmlTable();
    HtmlTableRow row = new HtmlTableRow();
    table.Rows.Add(row);
    HtmlTableCell cell = null;
    DAXProjActivityTxtDropDownList
    = TemplateContainer.FindControl(
    "DAXProjActivityTxtDropDownList")
    as DropDownList;
    if (DAXProjActivityTxtDropDownList == null)
    throw new ArgumentException("Activity
    dropdown list not found.
    Please check if your control
    template is valid.");

    if (this.ControlMode == SPControlMode.Edit ||
    this.ControlMode == SPControlMode.New)
    {
    row = new HtmlTableRow();
    cell = new HtmlTableCell();
    this.DAXProjActivityTxtDropDownList =
    new DropDownList();
    DAXProjActivityTxtDropDownList.ToolTip =
    "Enter Activity";
    DAXProjActivityTxtDropDownList.Width = 255;
    PopulateDAXProjActivityTxts(Guid.Empty.ToString());
    string currentValue = (string)this.ItemFieldValue;

    if (currentValue != null &
    currentValue != string.Empty)
    {
    this.DAXProjActivityTxtDropDownList.SelectedValue
    = currentValue;
    }
    else if
    (this.DAXProjActivityTxtDropDownList.Items.Count
    > 0)
    {
    this.DAXProjActivityTxtDropDownList.SelectedValue
    = "";
    }

    cell.Controls.Add(this.DAXProjActivityTxtDropDownList);
    row.Cells.Add(cell);
    table.Rows.Add(row);

    }

    LiteralControl literalControl = new LiteralControl();
    cell = new HtmlTableCell();
    cell.ColSpan = 4;

    if (this.ControlMode == SPControlMode.Display)
    {
    literalControl.Text
    = "<p id='SPFieldChoice'
    style='visibility:visible;
    font-size:11; margin-bottom:-3;
    margin-top:-3'>"
    + this.ItemFieldValue + "</p>";
    }

    cell.Controls.Add(literalControl);
    row.Cells.Add(cell);
    table.Rows.Add(row);
    base.Controls.Add(table);
    }
  7. Add the following code to update the underlying value of the field to the latest user-set value:

    public override void UpdateFieldValueInItem()
    {
    this.EnsureChildControls();

    try
    {
    this.Value =
    this.DAXProjActivityTxtDropDownList.SelectedValue;
    this.ItemFieldValue = this.Value;
    }
    catch
    {
    ;
    }
    }
  8. Add a method which uses the Business Data Catalog object model to populate the dropdown list with activities:

    private void PopulateDAXProjActivityTxts(string partName)
    {
    //connect to the LobSystemInstance that represents
    //the DAX Activities business application in the BDC
    NamedLobSystemInstanceDictionary instances
    = ApplicationRegistry.GetLobSystemInstances();
    LobSystemInstance instance = instances["Your_Instance"];

    // Connect to the DAX Activity entity
    Entity entity
    = instance.GetEntities()
    ["[Your_DB].[dbo].[PROJACTIVITY]"];

    Method method
    = entity.GetMethods()
    ["FindByProjId_[Your_DB].[dbo].[PROJACTIVITY]"];

    MethodInstance methodinstance
    = method.GetMethodInstances()
    ["FindByProjId_[Your_DB].[dbo].
    [PROJACTIVITY]_Instance"];
    Object[] args
    = methodinstance.GetMethod().
    CreateDefaultParameterInstances(methodinstance);
    args[0] = “your projid”;
    args[1] = “your dataareaid”;
    DbEntityInstanceEnumerator daxprojactivitytxts
    = (DbEntityInstanceEnumerator)entity.Execute(
    methodinstance, instance, ref args);

    // loop through the activities
    // and populate the activities dropdown
    while (daxprojactivitytxts.MoveNext())
    {
    DbEntityInstance daxprojactivitytxt
    = (DbEntityInstance)daxprojactivitytxts.Current;
    DAXProjActivityTxtDropDownList.Items.Add(
    new ListItem(
    daxprojactivitytxt.GetFormatted("TXT").ToString()));
    }
    daxprojactivitytxts.Dispose();
    }
  9. Add the following override of the Render method.
    protected override void Render(HtmlTextWriter output)
    {
    this.table.RenderControl(output);
    }

Create the Custom Field Class


  1. Add a new class to the root of the project called DAXProjActivityTxtField.cs.


  2. Edit the DAXProjActivityTxtField.cs and change the namespace to MyCompany.SharePoint.DAX.CustomFields.
  3. Add the necessary using statements:

    using Microsoft.SharePoint;
    using Microsoft.SharePoint.WebControls;
  4. Let the class derive from SPFieldText.

    public class DAXProjActivityTxtField : 
    Microsoft.SharePoint.SPFieldText
  5. Implement two required constructors:

    public DAXProjActivityTxtField(
    SPFieldCollection fields, string fieldName)
    : base(fields, fieldName)
    {
    }

    public DAXProjActivityTxtField(
    SPFieldCollection fields, string typeName,
    string displayName)
    : base(fields, typeName, displayName)
    {
    }
  6. Override the FieldRenderingControl as follows:

    public override BaseFieldControl FieldRenderingControl
    {
    get
    {
    BaseFieldControl control =
    new DAXProjActivityTxtFieldControl();
    control.FieldName = this.InternalName;
    return control;
    }
    }

Create the Field Type definition


  1. Add a new XML File in the folder XML of your project folder structure, called fldtypes_daxprojactivitytxt.xml.


  2. Edit the file by entering following lines:

    <?xml version="1.0" encoding="utf-8" ?>
    <fieldtypes>
    <fieldtype>
    <field name="TypeName">DAXProjActivityTxt</field>
    <field name="ParentType">Text</field>
    <field name="TypeDisplayName">DAX ProjActivityTxt</field>
    <field name="TypeShortDescription">DAX project activity cust fld.</field>
    <field name="UserCreatable">TRUE</field>
    <field name="FieldTypeClass">
    MyCompany.SharePoint.DAX.CustomFields.DAXProjActivityTxtField,
    MyCompany.SharePoint.DAX.CustomFields.DAXProjActivityTxt,
    Version=1.0.0.0, Culture=neutral, PublicKeyToken=cf2f0bd24cc4b888</field>
    </fieldtype>
    </fieldtypes>
  3. Replace MyCompany with the same value as done in the replacement of the namespaces. Replace the value for the PublicKeyToken with the value you receive by executing the previously created External Tool in Visual Studio (Tools > Get Assembly Public Key)

Build the Visual Studio project


Build your Visual Studio project: the post-build events added previously will install everything needed to your SharePoint environment.


Add column to list


Add a SharePoint column to one of your SharePoint lists or create a site column: you’ll have a new type of information option. Choose it for the column you are creating.



Set Permission


You need to give the appropriate permissions to the SharePoint users who need to be able to edit the values in this column. Open SharePoint Central Administration, go to your Shared Service Provider and choose View applications in the  Business Data Catalog section. Select your previously created Application and choose Manage Permissions. Give the SharePoint users Execute Right on the Application.


Result


When you create or edit an item in the list, your drop down column will be loaded with values from your Microsoft Dynamics AX source and should look like below:



Interesting resources


Comments 14 comments
BWL said...

Hello Patrik,

I used your code and I did everything like you did, but in my sharepoint website it wont show the type of column. As I build my project it wasnt any errors and it was successfully builed. Can you please help me!!!

Patrik Luca said...

To be sure everything got installed correctly: can you check the following:

* Go to the 12 hive on your SharePoint server and go to the directory \TEMPLATE\XML: you should find a fldtypes_daxprojactivitytxt.xml file there.

* Go to your global assembly cache folder (normally it should be c:\Windows\assembly: you should find two assemblies there named <yourcompany>.SharePoint.DAX.CustomFields.DAXProjActivityTxt and <yourcompany>.SharePoint.DAX.DAXProjActivityTxt.CustomFields

Anonymous said...

Great solution, but when editing the meta data in Word (from the Document Information Panel), the field is greyed out (and it says it must be edited from server). Any idea how to make it work in Word?

BR
Jens

Patrik Luca said...

Hi Jens,

I don't have immediately an idea, and haven't run (yet) into the problem. Does your lookup to the database has over 255 entries? Else I would suggest to let t point to a column with less values, just to see if the problem is related to it or not.

Carl said...

Great post Patrik thanks for the hard work. I've been looking for this for a long time, I'll check it out.

Jens,

Office doc panels won't show custom field types by default other than as a greyed out text box. Simply rebuild the panel using MS InfoPath and create a secondary data source to access the BDC data using a drop down.

CW

Patrik Luca said...

Thanks Carl: glad to hear this post is valuable info for you!

Carl said...

Hi Patrik.

I've tried this with what you have provided on the page but am receiving quite a bit of errors.

Do you by chance hapen to have the code available for download SVP?

Thanks

CW

Patrik Luca said...

I am sorry Carl: I don't have this code anymore: I started with it as described in these 3 posts, but I elaborated it further a lot, so my project contains lots of other stuff which makes it probably difficult to understand. Moreover, it is not in a stable state for the moment: I am in the middle of further development.

You receive quite a bit of errors: is it still upon compilation that you receive some stuff? Or after deployment (so during the tests)? Maybe you can clarify more about those errors you're receiving?

Anonymous said...

Hi Patrick,

Thanks for the great post.
I followed the steps mentioned in the post but i seem to get the following error when i select the option in the Document Library add columns page.

Any idea where im going wrong,

The file '/_layouts/CRMAccountControl' does not exist. at System.Web.UI.Util.CheckVirtualFileExists(VirtualPath virtualPath)
at System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile)
at System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile)
at System.Web.Compilation.BuildManager.GetVPathBuildResult(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile)
at System.Web.UI.TemplateControl.LoadControl(VirtualPath virtualPath)
at System.Web.UI.TemplateControl.LoadControl(String virtualPath)
at Microsoft.SharePoint.ApplicationPages.FieldCustomizationPage.CreateChildControls()
at Microsoft.SharePoint.ApplicationPages.FieldNewPage.CreateChildControls()
at System.Web.UI.Control.EnsureChildControls()
at Microsoft.SharePoint.ApplicationPages.FieldCustomizationPage.OnLoad(EventArgs e)
at Microsoft.SharePoint.ApplicationPages.FieldNewPage.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Patrik Luca said...

CRMAccountControl: is this the name of your Field Rendering Control?
If so, it seems as it is not correctly installed on your SharePoint server, as SharePoint seems not be able to find it.

Check the following:

* Go to the 12 hive on your SharePoint server and go to the directory \TEMPLATE\XML: you should find a fldtypes_daxprojactivitytxt.xml file there.

* Go to your global assembly cache folder (normally it should be c:\Windows\assembly: you should find two assemblies there named <yourcompany>.SharePoint.DAX.CustomFields.DAXProjActivityTxt and <yourcompany>.SharePoint.DAX.DAXProjActivityTxt.CustomFields

Anonymous said...

Thanks Patrik,

Now i get the error Unable to cast object of type 'ASP._controltemplates_crmaccountcontrol_ascx' to type 'Microsoft.SharePoint.WebControls.IFieldEditor'

Appreciate your help

Patrik Luca said...

Hi,

it seems as you've created your own Field Rendering Template which is typically a .ascx file: in my example, I didn't. So I guess there is something wrong with your template...

You should have installed it in C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\CONTROLTEMPLATES\ on your SharePoint server.

Syed said...

Really Very nice post Patrik. I was basically looking for a similar scenario and this post has shown me a direction.I want to discuss with you my problem.
I need to add a field in EPM which is also integrated with Dynamics Ax. Just like we have Project Id, Project name, DAX_Task_Description etc. The field will be of a lookup of Project categories.
Please Patrik reply on this comment. Its really very urgent and important for me.Any type of help ans suggestion will be highly appreciated. I will try the code of this code and then will try to map it for my case. Just posting this comment so that you can guide me the best way.
looking forward for your response.

Patrik Luca said...

Hi Syed,

just following the guidelines in my 3 posts and you should get the result you want. Making a lookup on a project activity or on a project category is the same: it is only another table and SQL mapping you should apply: but that's the easiest part in the development of such functionality :-)

Patrik Luca, Ieper, BELGIUM
Feel free to use or spread all of the content on my blog. In return, linking back to my blog would be greatly appreciated. All my posts and articles are provided "AS IS" with no warranties.

Subscribe feeds via e-mail
Subscribe in your preferred RSS reader

Subscribe feeds rss Most Read Entries

Subscribe feeds rss Recent Entries

Categories

Recommended Books


Subscribe feeds rss Recent Comments

This Blog is part of the U Comment I Follow movement in blogosphere. Means the comment field of this blog is made DOFOLLOW. Spam wont be tolerated.

Live Traffic Feed

Recent Visitors

Blog Archive

My Blog List

Followers

Guest Links