Table Lookups

Mar 11, 2010 at 2:30 PM

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>

  <DataMapping DataSchema="SLAM">
    <List Name="Parts Orders" ActivationOrder="1" TableName="PartsOrders">
      <Fields>
        <Field Name="Title" Column="Order Number" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Bill_x0020_of_x0020_Lading" Column="Bill Of Lading" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Entry_x0020_Date" Column="Entry Date" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Expected_x0020_Delivery_x0020_Da" Column="Expected Delivery" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Customer" Column="Customer" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Job" Column="Job" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="PO_x0023_" Column="PO Number" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Description" Column="Description" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="City" Column="City" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="State2" Column="State" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Ship_x002d_To_x0020_Canada" Column="Country" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Order_x0020_Type" Column="Order Type" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Sales_x0020_Office" Column="Sales Office 1" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Sales_x0020_Office_x0020_1_x0020" Column="Sales Office 1 %" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Sales_x0020_Office_x0020_2" Column="Sales Office 2" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Sales_x0020_Office_x0020_2_x0020" Column="Sales Office 2 %" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="FSI" Column="FSI" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="PTO" Column="PTO" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="PTC" Column="PTC" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Currency" Column="Currency" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
      </Fields>
    </List>
    <List Name="Parts Shipping Log" ActivationOrder="2" TableName="PartsShipping">
      <Fields>
        <Field Name="Title" Column="Bill of Lading" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Order_x0020__x0023_" Column="Order Number" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Carrier" Column="Carrier" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Forwarder" Column="Forwarder" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Ship_x0020_Date" Column="Ship Date" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
      </Fields>
    </List>
  </DataMapping>
</Configuration>

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

 

Developer
Mar 11, 2010 at 5:28 PM

Try changing the Bill of Lading field like:

<Field Name="Bill_x0020_of_x0020_Lading" Column="Bill Of Lading" SqlType="varchar(255)" SPType="Text" Required="true"></Field>

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

<Field Name="Bill_x0020_of_x0020_Lading" SPType="Lookup" AssociatedList="Parts Shipping Log" AssociationName="ShippingLogToOrders"></Field>

 

Then change:

<Field Name="Order_x0020__x0023_" Column="Order Number" SqlType="varchar(255)" SPType="Text" Required="true"></Field>

to:

<Field Name="Order_x0020__x0023_" SPType="Lookup" AssociatedList="Parts Orders" AssociationName="ShippingLogToOrders"></Field>

 

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

Mar 12, 2010 at 2:17 PM

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

 

 

Developer
Mar 12, 2010 at 8:16 PM

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

Mar 12, 2010 at 11:14 PM

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>

  <DataMapping DataSchema="SLAM">
    <List Name="Parts Shipping Log" ActivationOrder="1" TableName="PartsShippingLog">
        <Fields>
        <Field Name="Title" Column="Bill of Lading" SqlType="varchar(255)" SPType="Text" Required="true"></Field>       
        <Field Name="Order_x0020__x0023_" SPType="Lookup" AssociatedList="Parts Orders" AssociationName="PartsShippingLogToPartsOrders"></Field>
   <Field Name="Carrier" Column="Carrier" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Forwarder" Column="Forwarder" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Ship_x0020_Date" Column="Ship Date" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Order_x0020_Complete" Column="Order Complete" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        </Fields>
    </List>
    <List Name="Parts Orders" ActivationOrder="2" TableName="PartsOrders">
      <Fields>
        <Field Name="Title" Column="Order Number" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Bill_x0020_of_x0020_Lading" SPType="Lookup" AssociatedList="Parts Shipping Log" AssociationName="PartsShippingLogToPartsOrders"></Field>
        <Field Name="Entry_x0020_Date" Column="Entry Date" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Expected_x0020_Delivery_x0020_Da" Column="Expected Delivery" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Customer" Column="Customer" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Job" Column="Job" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="PO_x0023_" Column="PO Number" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Description" Column="Description" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="City" Column="City" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="State2" Column="State" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Ship_x002d_To_x0020_Canada" Column="Country" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Order_x0020_Type" Column="Order Type" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Sales_x0020_Office" Column="Sales Office 1" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Sales_x0020_Office_x0020_1_x0020" Column="Sales Office 1 %" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Sales_x0020_Office_x0020_2" Column="Sales Office 2" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Sales_x0020_Office_x0020_2_x0020" Column="Sales Office 2 %" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="FSI" Column="FSI" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="PTO" Column="PTO" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="PTC" Column="PTC" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Currency" Column="Currency" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
      </Fields>
    </List>
   
  </DataMapping>
</Configuration>

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

 

 

 

 

Developer
Mar 12, 2010 at 11:23 PM

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

 

Mar 18, 2010 at 4:27 PM

Brian,

(I started this last Monday - but forgot to click on the 'Post your reply' - probably due to someone not knowing how to print to legal size paper)

All is good.

I changed the two lookup lines per your suggestion:

<Field Name="Order #" SPType="Lookup" AssociatedList="Parts Orders" AssociationName="PartsShippingLogToPartsOrders"></Field>

<Field Name="Bill of Lading" SPType="Lookup" AssociatedList="Parts Shipping Log" AssociationName="PartsShippingLogToPartsOrders"></Field>

And it created the third table successfully.

Many thanks for you patience.

One possible added functionality bit for SLAM would be the ability to purge the portion of a lookup field before and including the ;# so that the exported data is cleaner.

I will contact you personally regarding other work: as you/your organization definitely seem to know your way around Sharepoint.

Regards,
Jonathan