SLAM! SharePoint List Association Manager

New Post: Activation Error

jackxiao — Mon, 15 Mar 2010 16:01:14 GMT

Nope. I've given up using the SLAM tool for now. Let me know if you've found the workaround.

New Post: Activation Error

allday — Mon, 15 Mar 2010 14:01:32 GMT

Hi Jackxiao,

Just wondering if you found a solution to this problem. I am currently experiencing exactly the same issue...

New Post: Table Lookups

brjenkins — Sat, 13 Mar 2010 00:23:37 GMT

This seems to be an issue with SLAM, I don't think it handles internal names on association columns. Validation probably doesn't find it as it checks internal and display names, but the process of creating associations itself seems to just use item.Fields["something"] notation which, as you can see from the stack trace, only checks against the display name. So my guess is it cannot find Bill_x0020_of_x0020_Lading. Try using the Display Name instead of the Internal Name for the Name attribute value of the Bill_x0020_of_x0020_Lading. You should do that as well for the Order_x0020__x0023_ column in the other list.

-Brian

New Post: Table Lookups

parkerjon — Sat, 13 Mar 2010 00:14:59 GMT

With the following config file:

<?xml version="1.0" encoding="utf-8" ?>
<Configuration>
<ConnectionStrings Default="SLAM">
<add Name="SLAM" ConnectionString="Database=SLAM;Data Source=cpc-db1;User Id=SLAMConnect;Password=SLAMDB" />
</ConnectionStrings>

All is good except for the join table which is blank and exceptions in the log:

3/12/2010 7:04:36 PM Exception System.ArgumentException: Value does not fall within the expected range. at Microsoft.SharePoint.SPFieldCollection.GetFieldByDisplayName(String strDisplayName, Boolean bThrowException) at Microsoft.SharePoint.SPFieldCollection.get_Item(String strDisplayName) at AWS.SLAM.Events.ListItemUpdater.Update(SPListItem listItem)
3/12/2010 7:04:36 PM Information Update LI Associations - Parts Orders List: Parts Orders ListItem: 324880, aa5ab0e0-4834-4e6e-9de7-40104b685381
3/12/2010 7:04:36 PM Information Update LI Complete - Parts Orders List: Parts Orders ListItem: 324880, aa5ab0e0-4834-4e6e-9de7-40104b685381
3/12/2010 7:04:36 PM Information Update Item: update [SLAM].[PartsOrders] set [ListItemID] = 'aa5ab0e0-4834-4e6e-9de7-40104b685381',[ListID] = 'f02aa8a2-1f52-4b45-820c-488b6349cd27',[ListRelativeID] = 249,[Order Number] = '324880',[Entry Date] = '11/03/2010 12:00:00 AM',[Expected Delivery] = '11/03/2010 12:00:00 AM',[Customer] = '538;#NEXEN INC.',[Job] = '',[PO Number] = '4500127170',[Description] = '<div>SHAFT/IMPELLER/IMPELLER;WEAR RINGS/GASKET</div> <div>(2220A)</div>',[City] = 'ANZAC',[State] = '2;#AB - ALBERTA',[Country] = '1;#Canada',[Order Type] = 'PARTS',[Sales Office 1] = '8;#WPA',[Sales Office 1 %] = '1',[Sales Office 2] = '',[Sales Office 2 %] = 'float;#0',[FSI] = '3534',[PTO] = 'float;#14136.0000000000',[PTC] = '17670',[Currency] = 'CAD' where ListItemID = 'aa5ab0e0-4834-4e6e-9de7-40104b685381'
3/12/2010 7:04:36 PM Information Update LI Start - Parts Orders List: Parts Orders ListItem: 324880, aa5ab0e0-4834-4e6e-9de7-40104b685381

Thanks,

Jonathan

New Post: Table Lookups

brjenkins — Fri, 12 Mar 2010 21:16:55 GMT

I was not thinking about how SLAM generates the column names for a connector table and how these will need to be consistent from either direction. So if a connector table is created while processing Parts Orders, say, then the resulting table will contain PartsOrdersListItemID and, following my example, PartsShippingLogListItemID. From the other direction it will be PartsShippingLogListItemID and PartsOrdersListItemID, which is what you want. "AssociatedList" then needs to have the same value as the Name of the List it is pointing to. Based on the error you received, my assumption is the AssociatedList value in Parts Orders has "Parts Shipping", with the resulting table containing PartsOrdersListItemID and PartsShippingListItemID. This will be a problem is the other list is "Parts Shipping Log" and not "Parts Shipping" because from the perspective of Parts Shipping Log the table should contain a PartsShippingLogListItemID column. I suspect that is the issue, so you should make sure the attribute values reciprocal, delete the connector table and let SLAM generate it again using Activate. You can try to synch again or just edit a single item in one of your lists to see if it is slamming one direction, then try the other.

As for the question about whether it is possible to slam only a part of the lookup value as text, you could create a custom Type Profile to do so, but it seems like your situation is resolvable without customization.

-Brian

New Post: Table Lookups

parkerjon — Fri, 12 Mar 2010 15:17:50 GMT

Brian,

Thanks for the ideas.

I gave the above a try but it yields an empty 'ShippingLogToOrders' table.

When it goes to add items to the join table 'Prep association table for synchronization: ShippingLogToOrders' it yields the following exception. I tried creating the column manually: though the table didn't populate after a resynch.

System.Data.SqlClient.SqlException: Invalid column name 'PartsShippingListItemID'. Invalid column name 'PartsShippingListItemID'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters) at System.Data.Linq.DataContext.ExecuteCommand(String command, Object[] parameters) at AWS.SLAM.Data.SqlDataRepository.AddAssociationTableSynchronizationObjects(String tableSchema, String tableName, String firstTypeName, String secondTypeName) at AWS.SLAM.Activation.SqlDataSynchronizeTypeProcessor.Process(String sharepointSiteLocation, IDictionary`2 parameters) at AWS.SLAM.Administration.Synchronization.DoSynchronization(SPSite site, String typeName, String typeId, String typeSiteCollection, String typeSite)

Other recurring error message:
System.ArgumentException: Value does not fall within the expected range. at Microsoft.SharePoint.SPFieldCollection.GetFieldByDisplayName(String strDisplayName, Boolean bThrowException) at Microsoft.SharePoint.SPFieldCollection.get_Item(String strDisplayName) at AWS.SLAM.Events.ListItemUpdater.Update(SPListItem listItem)

It would seem that the issue may be KWIZCOM's re-implementation of the Lookup feature. Is there a way, using SLAM, to modify the data prior to appending it to the table? In the sample data above I'd like to remove the RelativeID info (93;#) from the front of the Order information in the Parts Shipping Log table to simplify the join when working with the data (and no third table required).

Regards,
Jonathan

New Post: Table Lookups

brjenkins — Thu, 11 Mar 2010 18:28:50 GMT

Try changing the Bill of Lading field like:

to (setting the AssocationName to whatever the connector table should be called):

Then change:

to:

So the intent here is to manifest the lookup fields as associations in the SLAM database. If you set the AssociationName as the same in both cases both associations, since it seems you are managing these connections bidirectionally, will appear in the same table. While you can do this, it may become problematic when doing a full synch. Doing a synch through the SLAM Control Panel actually performs synch one type at a time, so once it processes all items in a list, whatever is left in the table that is not found in the list will be deleted. Now if the Kwizcom custom field type makes sure the data is in both list items, regardless of where the lookup was specified, then this will not a problem. Otherwise it would synch, say, the Parts Orders first then do the Shipping Log list after which it would remove all of the associations created by the Parts Orders synch. If all are present in both cases then obviously nothing will be extraneous from the point of view of either list.

In any case if you want the lookup values to manifest in a connector table you want to use the AssocationName and AssociatedList attributes in the lookup fields.

-Brian

New Post: Creating new list item doesn't automatically insert the item in the DB

brjenkins — Thu, 11 Mar 2010 18:00:33 GMT

When you add or delete do you see events logged in the SLAMEventLog table? If the event isn't logged then it didn't occur, meaning SharePoint likely did not execute the event receiver.

-Brian

New Post: Table Lookups

parkerjon — Thu, 11 Mar 2010 15:30:32 GMT

I have a question about implementing a lookup between the following two lists. The following config file works great: it creates two tables with the necessary data. The relationship between the two tables is either via Order Number or Bill of Lading. The KWIZCOM Cascading Lookup tool allows for reverse lookups to be created automatically. What I don't understand is how in the Config file to explicitly indicate which field links the lists to create the third table. I have tried a couple of lookup tests but the third table is empty.

Here are excerpts from the records in the two created tables that show the possible many to one relationship (related to one order):

Parts Orders:
ListItemID: 046cdffe-dc34-42b6-8a3b-ce458a8d83e4
ListID: f02aa8a2-1f52-4b45-820c-488b6349cd27
ListRelativeID: 93
Order: 324675
Bill of Lading: 177;#1ZR70W721492902934;#178;#1ZR70W721492963146;#179;#1ZR70W721494698957

Parts Shipping Log:

ListItemID: 4fb2b905-22d9-4b03-9135-6f5a7621df82
ListID: 1a57ffbb-3eca-4bb9-a2d4-a2bbf9ff86b0
ListRelativeID: 178
Bill of Lading: 1ZR70W721492963146
Order: 93;#324675

ListItemID: 3ed43b03-df12-431d-938b-6abf5c810008
ListID: 1a57ffbb-3eca-4bb9-a2d4-a2bbf9ff86b0
ListRelativeID: 179
Bill of Lading: 1ZR70W721494698957
Order: 93;#324675

ListItemID: ded185f3-6782-4b13-bbc2-f43522e07aa1
ListID: 1a57ffbb-3eca-4bb9-a2d4-a2bbf9ff86b0
ListRelativeID: 177
Bill of Lading: 1ZR70W721492902934
Order: 93;#324675

Current config file:

<?xml version="1.0" encoding="utf-8" ?>
<Configuration>
<ConnectionStrings Default="SLAM">
<add Name="SLAM" ConnectionString="Database=SLAM;Data Source=cpc-db1;User Id=SLAMConnect;Password=SLAMDB" />
</ConnectionStrings>

What I need to accomplish is a report that shows the activity for a specific order: most importantly adding the ship date to the report (and later a $ value of what was on the bill of lading). I could accomplish this within Excel by exporting the data from the two tables to two sheets: then using a third sheet to glue the data together but doing it in SQL would be preferable as I could present the data via Sharepoint.

Thanks in advance for any suggestions.

Jonathan

New Post: Creating new list item doesn't automatically insert the item in the DB

konvert — Wed, 10 Mar 2010 09:36:45 GMT

Hi Brian,

When I add or delete an item in my sharepoint list, the new item isn't inserted and the deleted item isn't removed in/from the appropiate table. None the less, when I update an item, the record is updated in the db.

When I synchronize the slam content in the configuration panel the item has been uploaded/ correctly.

In this list I have a workflow sending a mail and creating a task everytime a new item is inserted.

I already adjusted my web.config but no extra messages are shown in the event and messagelog tables.

<system.diagnostics>
    <switches>
      <add name="SLAMLogger" value="4" />
    </switches>
</system.diagnostics>

Some help understanding this problem would be great.

With kind regards,

Koen

Updated Wiki: Home

AWSystems — Tue, 09 Mar 2010 19:40:21 GMT

SLAM! SharePoint List Association Manager

Most developers who have used SharePoint as an application development framework have run into the realization that SharePoint is NOT a relational database. In fact, the accepted wisdom is if you need relational tables, use ASP.NET/SQL straight-up, not SharePoint.

Enter SharePoint List Association Manager (SLAM). In short it allows you to define relationships (one to one, one to many, many to many) between SharePoint lists (or Content Types) and then leverage those relationships in webparts or custom field types using familiar and straight forward SQL queries.

Read our Illustrated Guide to SLAM for more information on what SLAM is and how to start using it.

View our Intro to SLAM movie for a brief introduction to what SLAM is, how to use SLAM and a look at the first SLAM add-on, hierarchy.

Version 1.3 is now the latest version of SLAM. Major updates include the ability to set up Data Mappings to use different connection strings to allow different site collections to slam to different databases. You can specify which site collection will SLAM to which database within the SLAM Config, see the SLAM Config page for more details. In addition, SLAM will now automatically create columns to existing tables when SLAM is reactivated.

To Upgrade:
If upgrading from 1.2, just run the SLAM 1.3 Setup. If upgrading from a previous version, run the 1.3 setup and then Reactive SLAM by navigating to the SLAM Control Panel > Activation > Activate.

Previous major features (as of 1.2): multiple site collection support, inheritance and the hierarchy add-on. Read our Hierarchy page for more info on what hierarchy does and how to start using it. For the latest SLAM news follow us on twitter.

Documentation

For more information on the general architecture of SLAM, click here

We have a set of API Documentation available here. There's a lot of good stuff there and more will be added as we find time. In the mean time, contact us if you have questions.

Note that you can set up SLAM and use it without ever looking at the API documentation. We provide it for those that are interested in extending the platform, or are just curious as to how it works.

Need help? Have questions?

You can either use the discussion tab, or feel free to contact us either using our website form or email us directly at slam@awsystems.com. We actively support SLAM and will continue to be posting more information here in the coming days.

Not a Developer? Still interested in using SLAM?

SLAM, in its current form, is aimed as a tool for developers. However, if you're interested in implementing SLAM and you aren't a developer please let us know!

Follow us!

For the latest news and updates to SLAM, follow us on twitter.

Updated Wiki: config file

brjenkins — Tue, 09 Mar 2010 19:33:33 GMT

SLAM XML Configuration

By default SLAM uses an XML Configuration file. SLAM looks in the root of the AppDomain, such as a SharePoint virtual directory or an executing location for a console/windows application, in which it is running for a slam.config file. This can be changed by setting the appSetting "AWS.SLAM.ConfigurationManager.Config" to contain a physical path to a specific, valid, SLAM configuration XML file, for example "C:\myslamconfig.xml". A SLAM configuration looks something like this:

<?xml version="1.0" encoding="utf-8" ?>
<Configuration>
  <ConnectionStrings Default="SLAM">
    <add Name="SLAM" ConnectionString="Database=db;Data Source=server;User Id=user;Password=password" />
    <add Name="SLAM_Alternate" ConnectionString="Database=db_alt;Data Source=server;User Id=user;Password=password" />
  </ConnectionStrings>

  <DataMapping DataSchema="SLAM">
    <List Site="subsite" Name="List" ActivationOrder="1">
      <Fields>
        <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="OtherListLookup" SPType="Lookup" AssociatedList="OtherList" AssociationName="ListToOtherList"></Field>
      </Fields>
    </List>
    <ContentType Name="ContentType" ActivationOrder="2">
      <Fields>
        <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="ListLookup" SPType="Lookup" AssociatedList="List" AssociationName="ContentTypeToList"></Field>
        <Field Name="Description" Column="Notes" SqlType="varchar(max)" SPType="Note" Required="false"></Field>
      </Fields>
    </ContentType>
  </DataMapping>

  <DataMapping DataSchema="SLAM" SiteCollection="sites/mysecondcollection" ConnectionString="SLAM_Alternate">
    <List Site="subsite" Name="List" ActivationOrder="2">
      <Fields>
        <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="OtherListLookup" SPType="Lookup" AssociatedList="OtherList" AssociationName="ListToOtherList"></Field>
      </Fields>
    </List>
  </DataMapping>
</Configuration>

The SLAM configuration contains two main sections, ConnectionStrings and DataMapping.
ConnectionStrings defines those SQL Server connection strings SLAM will use by default. The Default attribute defines precisely that by Name as seen here with "SLAM" being the default connection string.
The DataMappings are the focus of the SLAM configuration however. The Lists and Content Types that SLAM should manage are defined in a DataMapping. For each of these a set of Fields is defined, then each Field that should be mapped from SharePoint to the SLAM database. The example describes the nodes used in the default DataMapping.

With the release of version 1.3 different site collections can synchronize with different databases. Each DataMapping section can specify a ConnectionString to use by name as defined in the ConnectionStrings section. If no ConnectionString attribute is present in the DataMapping node the default connection string is used.

With the release of version 1.2 SLAM works across multiple site collections. The SLAM configuration can have many DataMapping sections which configure Content Types and Lists to be managed by SLAM. Each DataMapping corresponds to a different site collection. If the SiteCollection attribute of a DataMapping element is missing or blank then that DataMapping is used to configure types and fields in the root site collection.

Each node has the following attributes:

List and Content Type
- Name - This is the name of the List or Content Type in SharePoint.
- ID - For Lists, ID is the GUID ID of the list. It is irrelevant for Content Types.
- Site - The Name of the subsite (web), if applicable, in which the List or Content Type exists in SharePoint. This name takes the form like "MySubsite/MysubsubSite". If left out or blank SLAM looks in the Root site (web).
- TableName - This is an optional attribute for giving SLAM an alternative name to give the table it creates for a type. For example, a configuration may define two lists with the same name in different Sites and different IDs to be managed by SLAM. These two lists might need "slammed" to separate tables.
- ActivationOrder - When SLAM is activated, it loads each type according to the ActivationOrder. If order does not matter, this can be the same value for each type.

Field
- Name - This is the display name or inner name of the Field in SharePoint.
- SqlType - This is the SQL datatype which the field should be stored as. This is used when SLAM is creating database columns.
- SPType - The equivalent of the SPFieldType of the Field in SharePoint.
- Required - Indicates whether SLAM should create the Field's column as allowing nulls or not. This value should correspond with the Field's Required flag in SharePoint.
- AssociationName - The presence of this attribute defines a Field as one that establishes an association. This is the name given to that association.
- AssociatedList - This is the name of List, treated as a "type name", to which the containing type is being associated by this association field.

New Post: Synchronize SLAM

konvert — Mon, 08 Mar 2010 13:46:46 GMT

Brian,

Thx for your reply, I've figured it out. I made a typo, the name of the list "VerlofAanvraag" should have been "Verlofaanvraag".

I didn't get any errors when I validated the slam.config. The error occured when I synchronized for the first time. My version = 1.3.

With kind regards,

Koen

New Post: Activating SLAM

brjenkins — Fri, 05 Mar 2010 23:05:31 GMT

Right, so you need to change the dummy config. It only contains examples, so it won't be at all useful unless you created lists to match it. And of course in any case you need to change the connection string.

New Post: Synchronize SLAM

brjenkins — Fri, 05 Mar 2010 23:03:18 GMT

Koen,

Are you using v1.3? I'm assuming if you activated it successfully/it created tables that your config validates without any issues and you have event handlers attached to your list. This is happening when you just edit an item in your list?

-Brian

New Post: Error in Diagnostic log but new line correctly inserted?

mygalio — Fri, 05 Mar 2010 09:29:48 GMT

Hi ;

I have only 'Sharepoint Designer' worflow associated with my 'projet' list and in this case it's not possible to disable events ?

My sharepoint designer Workflow make only 'Update' for particulars fields.

It's not to serious for the moment I'll leave things like that..

A big 'Thank' for yous help . This tools is very useful

Lionel

New Post: Synchronize SLAM

konvert — Wed, 03 Mar 2010 16:10:45 GMT

Hi,

First, great work guys, this is something we have been looking out for some time.

After installing SLAM and creating a simple relation to a sharepoint list, I got the following error:

System.NullReferenceException: Object reference not set to an instance of an object. at AWS.SLAM.Configuration.XmlSqlDataMapping.GetTypeFields(String id, String typeName) at AWS.SLAM.Events.ListItemUpdater.Update(SPListItem listItem)

Here's how my slam.config file looks like:

In my database I have a table called SLAM.VerlofAanvraag containing the following fields: ListItemId varchar(40) not null, ListID varchar(40) not null, ListRelativeId int not null, Gebruiker varchar(255) null.

I added a few links to some screenshots.

http://www.4shared.com/file/233567501/76e23b3f/slamconfig.html
http://www.4shared.com/file/233567529/4a0fd18f/column_definition.html
http://www.4shared.com/file/233567557/e2f66a4f/database.html

http://www.4shared.com/file/233568807/cf3a1d0f/diagnostic.html

Any help would be nice.

With kind regards,

Koen

New Post: Activating SLAM

Renator — Wed, 03 Mar 2010 07:11:44 GMT

Hi Brian,

well i've used the slam.dummy.config, renamed it to slam.config and put it to the root of the site's virtual directory. Is there no chance to test it with the dummy or is it a must to config it immediatly the right way?

I'm doing hard to config...

New Post: Activating SLAM

brjenkins — Tue, 02 Mar 2010 19:55:15 GMT

Renator,

SLAM is a site collection feature, not a site feature. Once you activate the site collection feature you will see the SLAM Control Panel link appear under Site Collection Administration. Even if you get to the SLAM Control Panel via the link the result will be the same as navigating directly. As the error suggests, you need to create a slam.config in the root of your site's virtual directory. Right now it cannot find a slam config.

-Brian

New Post: Sync: manual works, auto doesn't

jegan — Tue, 02 Mar 2010 13:45:49 GMT

I modified the slam.config file to include the SiteCollection="sites/MTDT" parameter in the DataMapping section, which fixed the broken sync issue:

<?xml version="1.0" encoding="utf-8" ?>

</ConnectionStrings>

</Fields>

</List>

</Fields>

</List>

</Fields>

</List>

</DataMapping>

</Configuration>

**********************************************************************
The New York City Department of Health & Mental Hygiene is now offering information important for the health of all New Yorkers.  To sign up for these new and valuable updates, log-on to our website at http://www.nyc.gov/health/email and select the NYC DOHMH updates you'd like to receive.
IMPORTANT NOTICE:  This email is meant only for the use of the intended recipient.  It may contain confidential information that is legally privileged or otherwise protected by law.  If you have received this communication in error, please notify me immediately by replying to this message and please delete it from your computer.  Thank you for your cooperation.