View All Blog Posts

Dynamic Column Example Joining Related SharePoint Lists

27 April 2011

This is an example how you can use Dynamic Columns to pull in additional data to decorate your resultset with related Data. You could easily use this same process to decorate your data with additional data from other data sources. If you think about this makes Data Sync a very powerful Data Migration/Transformation tool. In this example I created a SharePoint List with Product data from the Northwind Database I then created a Lookup List with Category names and linked the 2 lists together via a SharePoint Lookup Column. The Goal is to pull out the "Description" column from the Category List for each Product in the Product List.

Here's the Data in SharePoint

SharePoint

Here is the code for the Dynamic Columns in here we load the "Categories" list into a Data Table from SharePoint and create an index to lookup the Description value for each row.

#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using Simego.DataSync.Providers.MSSharePoint;
#endregion

class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{
    //Category Data
    DataTable categories;
    //Category Index
    Dictionary<int, DataRow> lookup = new Dictionary<int, DataRow>();
    
    /// <summary>
    /// Get the Category ID from the Category Lookup Column
    /// </summary>
    public int O_CategoryID
    {
        get { 
            int ix = this.Category.IndexOf(";#");
            if ( ix > 0 )
                return Convert.ToInt32(this.Category.Substring(0, ix));
            
            return 0; 
        }
    }

    /// <summary>
    /// Description
    /// </summary>
    public string O_CategoryDescription {         
        get 
        { 
            object val = lookup[O_CategoryID]["Description"];
            if ( val == DBNull.Value ) return null;
            return (string)val;
        } 
    }
    
    public override void Setup()
    {
        //Get a DataTable object of the related List via a new SharePoint Reader
        SharePointDataSourceReader reader = new SharePointDataSourceReader();
        reader.ListUrl = ((SharePointDataSourceReader)this.DataSourceA.Reader).ListUrl;
        reader.ListName = "Categories";
        //Get the Default Schema for this List
        reader.SchemaMap = reader.GetDefaultDataSchema();
        //Load the Data Table
        categories = reader.GetDataTable();        
        //Create a Lookup Index to help speed-up lookups
        foreach(DataRow r in categories.Rows) 
            lookup.Add((int)r["ID"], r);
    }
}

This is the result, here you can see we have pulled in the Description value from the related Categories List.

Preview

Posted at 9:49 AM | By Sean Cleaver |

Would you like to try Data Sync and Ouvvi for your Data Integration projects?

ACTIVATE YOUR 30 DAY FREE TRIAL

Recent Blogs