This project is read-only.

SLAM, relations and the BBC Catalog Editor

Sep 3, 2009 at 8:00 AM

I have been using SLAM for quite some time mainly to maintain an SQL version of the library for use with forms (i prefer to do SQL queries as opposed to XML lookups when working with Infopath for example so that I'm not having to work with sharepoint views for the purpose of sorting, filtering etc..)

 

However recently it has become important for me to create some tables with relationships so that I can later use the Business Data Catalog to show relational views.

The basic issue I have however is that I'm not able to create the relationships (note that I'm not an expert with SQL).

Here is the basic layout and what I'm trying to acomplish (not this is just a working example, not the actual database I'm working with but if I can solve it in this simple example I will be able to solve it in the more complex database that I'm actually working with).

I have two sharepoint lists, a product list with 2 fields (title and customer).  The Customer field is a lookup field to the Customers List which itself only has one field Customers.  Effectivly the relationship between the product and customer list is the customer field in both.

Here is the excerpt form the SLAM.Config

<List Site="Operations/opsmanagement" Name="Product" ActivationOrder="1">
    <Fields>
     <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
     <Field Name="Customer" SPType="lookup" AssociatedList="Customer" AssociationName="Product To Customer" Required="False"></Field>
    </Fields>
   </List>

   <List Site="Operations/opsmanagement" Name="Customer" ActivationOrder="1">
    <Fields>
     <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
    </Fields>
   </List>

SLAM has no problems with the configuration file and it creates the tables.  However their is no field called Customer in the product list showing up, but it does create the association table (Product To Customer).

My question is how does the relationship work here.  I mean when I query the Product Table the Customers aren't showing up (because the field is not their).  Do I need to manualy setup a relationship in SQL Manager?  I was under the impression SLAM created the relationships itself.

Now looking at the Business Catalog Editor, of course the relationship is not their so I need to make sure the relationship is their before I generate the Definition File.

Hope that makes sense, any help would be greatly appriciated

Developer
Sep 3, 2009 at 12:29 PM

Rather than creating a foreign key relationship (e.g. adding a customer id to the products table), SLAM uses association tables.  The reason for this is that SLAM allows for not only one to one relationships but also one to many.  Take a look at our illustrated guide to SLAM which shows a relationship between three lists and the SQL query used to create the join.

Allan

Sep 4, 2009 at 12:50 PM

Thanks for the reply.  I think I understand how it works within SQL now (Association Tables). I guess the question is wether or not you can use association tables with business data catalog definition files.  Im working with the BDC Definition Editor released with the SDK pack and I think it assumes that foreign keys are used, but I have to do a bit more research on the topic.  I would be interested to know if anyone has ever used SLAM to create SQL tables with relations and then in turn used the BDC to view the SQL data in sharepoint (and of course the how of it).

Developer
Sep 4, 2009 at 1:02 PM

The association tables exist only in the context of SQL - so the BDC would know nothing about them.  Remember, though, that you use SharePoint lookup fields to describe the relationships.  Although the lookup doesn't become a column in the SLAM tables (it becomes an association table instead), the field is still a valid lookup column in SharePoint which the BDC has access to.

Hope that helps.

Yours,

Allan