AssociationName Issue

Jan 14, 2011 at 7:07 PM

I am having a problem getting the ListItemID values in the AssociationName Tables. Through SQL Profiler, I can see the Triggers being created which inserts the ListItemIDs for the list association, but then it explicitly calls a DELETE from the association table. Any idea why this happens? Seems like that is how it creates the relationship between tables/lists.

Here's my setup:

Table5 relates to Table1

Table4 relates to Table1

Table3 relates to Table1

Table2 relates to Table1

Thanks!

Developer
Jan 14, 2011 at 7:31 PM

I'm not sure I quite understand the question.  The only triggers SLAM uses are for synchronization, which is generally just used for setting it up against existing lists with data.  For synch, SLAM adds a column for flagging rows that correspond to items in SharePoint and the trigger updates that flag when a row is updated.  Whatever is not flagging afterward is deleted along with the trigger needed for synch and the flag column.

Jan 14, 2011 at 8:08 PM
Edited Jan 19, 2011 at 12:36 PM

When the syncrhonization completes, the association tables are empty, but it looks like the rest of the tables are populated with the correct data. We created 5 lists with a custom tabbed format instead of having 1 huge list and we join them based on the ID of the main table. Here is my config file.. does anything look out of place?

<?xml version="1.0" encoding="utf-8" ?>
<Configuration>
  <ConnectionStrings Default="SLAM">
    <add Name="SLAM" ConnectionString="************" />
  </ConnectionStrings>

  <DataMapping DataSchema="SLAM">
    <List Site="businessrequests" Name="Business Requests" ActivationOrder="1" TableName="BusinessRequests">
      <Fields>
        <Field Name="ID" SqlType="varchar(36)" SPType="Counter" Required="true"></Field>
        <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="BR_BusinessRequestStatus" SqlType="varchar(255)" SPType="Choice" Required="false"></Field>
        <Field Name="BR_BusinessPriority" SqlType="varchar(255)" SPType="Choice" Required="false"></Field>
        <Field Name="PM_ProcessCluster" SqlType="varchar(255)" SPType="Choice" Required="false"></Field>
        <Field Name="Local Business Priority" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
      </Fields>
    </List>
    <List Site="businessrequests" Name="Business Requests Approval Plan" ActivationOrder="2" TableName="ApprovalPlan">
      <Fields>
        <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="ID" SqlType="varchar(255)" AssociatedList="Business Requests" AssociationName="ApprovalPlanToBusinessRequests" SPType="Lookup" Required="true"></Field>
        <Field Name="Approving_x0020_Committees" SqlType="varchar(255)" SPType="MultiChoice"></Field>
        <Field Name="GRIPPS_x0020_Ticket_x0020_Name" SqlType="varchar(255)" SPType="Text"></Field>
        <Field Name="GRIPPS_x0020_Ticket_x0020_Number" SqlType="varchar(255)" SPType="Text"></Field>
        <Field Name="ITSC_x0020_Approval_x0020_Date" SqlType="varchar(255)" SPType="DateTime"></Field>
        <Field Name="ITSC_x0020_Submission_x0020_Date" SqlType="varchar(255)" SPType="DateTime"></Field>
        <Field Name="TPA_x0020_Ticket_x0020_Number" SqlType="varchar(255)" SPType="Text"></Field>
      </Fields>
    </List>
    <List Site="businessrequests" Name="Business Requests Business Columns" ActivationOrder="3" TableName="BusinessColumns">
      <Field Name="Title" SPType="Text" SqlType="varchar(255)"></Field>
      <Field Name="BR_Description" SPType="Note" SqlType="varchar(max)"></Field>
      <Field Name="BR_BusinessCategorization" SPType="Choice" SqlType="varchar(255)"></Field>
      <Field Name="BR_BusinessRequestOwner" SPType="User" SqlType="varchar(255)"></Field>
      <Field Name="Business_x0020_Lead" SPType="UserMulti" SqlType="varchar(255)"></Field>
      <Field Name="Cost_x0020_of_x0020_Sales_x0020_" SPType="Currency" SqlType="decimal(18,2)"></Field>
      <Field Name="Future_x0020__x002f__x0020_Readi" SPType="Note" SqlType="varchar(max)"></Field>
      <Field Name="Headcount_x0020_Impact_x0020_Cos" SPType="Currency" SqlType="decimal(18,2)"></Field>
      <Field Name="Headcount_x0020_Impact_x0020_Typ" SPType="Choice" SqlType="varchar(255)"></Field>
      <Field Name="Legal_x0020_Impact_x0020_Explana" SPType="Note" SqlType="varchar(max)"></Field>
      <Field Name="Operating_x0020_Budget_x0020_Imp" SPType="Currency" SqlType="decimal(18,2)"></Field>
      <Field Name="Quality_x0020_Impact_x0020_Expla" SPType="Note" SqlType="varchar(max)"></Field>
      <Field Name="Requested_x0020_Go_x002d_Live_x0" SPType="DateTime" SqlType="datetime"></Field>
      <Field Name="Target_x0020_Relevance" SPType="MultiChoice" SqlType="varchar(max)"></Field>
      <Field Name="PM_Impact" SPType="MultiChoice" SqlType="varchar(max)"></Field>
      <Field Name="ID" SqlType="varchar(255)" AssociatedList="Business Requests" AssociationName="BusinessColumnsToBusinessRequests" SPType="Lookup" Required="true"></Field>
    </List>
    <List Site="businessrequests" Name="Business Requests Financial Columns" ActivationOrder="4" TableName="FinancialColumns">
      <Field Name="ID" SqlType="varchar(255)" AssociatedList="Business Requests" AssociationName="FinancialColumnsToBusinessRequests" SPType="Lookup" Required="true"></Field>
      <Field Name="Discount_x0020_Rate" SPType="Number" SqlType="numeric(18,2)"></Field>
      <Field Name="Internal_x0020_Rate_x0020_of_x00" SPType="Number" SqlType="numeric(18,2)"></Field>
      <Field Name="Monetary_x0020_Benefit_x0020_wit" SPType="Currency" SqlType="decimal(18,2)"></Field>
      <Field Name="Net_x0020_Present_x0020_Value" SPType="Currency" SqlType="decimal(18,2)"></Field>
      <Field Name="ROI_x0020_Approval_x0020_Date" SPType="DateTime" SqlType="datetime"></Field>
      <Field Name="ROI_x0020_Exists" SPType="Boolean" SqlType="bit"></Field>
      <Field Name="Year_x0020_of_x0020_Amortization" SPType="Text" SqlType="varchar(255)"></Field>
      <Field Name="ROI_x0020_Amount_x0020_USD" SPType="Currency" SqlType="decimal(18,2)"></Field>
      <Field Name="ROI_x0020_Amount_x0020_Euro" SPType="Currency" SqlType="decimal(18,2)"></Field>
    </List>
    <List Site="businessrequests" Name="Business Requests IT Columns" ActivationOrder="5" TableName="ITColumns">
      <Field Name="BR_ITResponsible" SPType="User" SqlType="nvarchar(255)"></Field>
      <Field Name="BR_ITChangeDesc" SPType="Note" SqlType="nvarchar(255)"></Field>
      <Field Name="BR_ITRelease" SPType="Number" SqlType="numeric(18,2)"></Field>
      <Field Name="BR_EffortEstimate" SPType="Number" SqlType="numeric(18,2)"></Field>
      <Field Name="x0020_Priority" SPType="Choice" SqlType="nvarchar(255)"></Field>
      <Field Name="Criticality" SPType="Choice" SqlType="nvarchar(max)"></Field>
      <Field Name="Project_x0020_Quality_x0020_Lead" SPType="User" SqlType="nvarchar(255)"></Field>
      <Field Name="Project_x0020_Type" SPType="Choice" SqlType="nvarchar(255)"></Field>
      <Field Name="Project_x0020_Source" SPType="Choice" SqlType="nvarchar(255)"></Field>
      <Field Name="Request_x0020_Status" SPType="Choice" SqlType="nvarchar(255)"></Field>
      <Field Name="Global_x0020_Status" SPType="Choice" SqlType="nvarchar(255)"></Field>
      <Field Name="Project_x0020_Support_x0020_risk" SPType="Choice" SqlType="nvarchar(255)"></Field>
      <Field Name="Non_x002d_Local_x0020_Sourcing" SPType="Boolean" SqlType="bit"></Field>
      <Field Name="Work_x0020_Package_x0020_Created" SPType="DateTime" SqlType="datetime"></Field>
      <Field Name="Estimated_x0020_Start_x0020_Date" SPType="DateTime" SqlType="datetime"></Field>
      <Field Name="Estimated_x0020_Go_x0020_Live_x0" SPType="DateTime" SqlType="datetime"></Field>
      <Field Name="Estimated_x0020_Closedown_x0020_" SPType="DateTime" SqlType="datetime"></Field>
      <Field Name="Effort_x0020_Estimate_x0020__x00" SPType="Number" SqlType="numeric(18,2)"></Field>
      <Field Name="Application_x0020_Operations_x00" SPType="User" SqlType="nvarchar(255)"></Field>
      <Field Name="Application_x0020_Operations_x000" SPType="Choice" SqlType="nvarchar(255)"></Field>
      <Field Name="Startup_x0020_Manager" SPType="User" SqlType="nvarchar(255)"></Field>
      <Field Name="FZ_x0020_Applications_x0020_Oper" SPType="Boolean" SqlType="bit"></Field>
      <Field Name="ID" SqlType="varchar(255)" AssociatedList="Business Requests" AssociationName="ITColumnsToBusinessRequests" SPType="Lookup" Required="true"></Field>
    </List>
  </DataMapping>
</Configuration>

 

Thanks

Developer
Jan 14, 2011 at 9:40 PM

If you just update an item in one of the 4 lists that have a lookup to Business Requests does anything show up?

Jan 18, 2011 at 2:58 PM

The DB schema is being updated with changes to the config file, but using SQL Profiler, it doesn't appear to updating all columns based on the config file. It seems like it has cached an older version of the config file and won't let go. I've tried deactivating and reactivating, but no luck.

Developer
Jan 18, 2011 at 3:34 PM

In the diagnostics, which is just coming from the SLAMMessageLog table, after activate check the SQL being used to update the tables.  There should be an Information line on activate for each table with SQL used to add columns.  If columns are missing that are specified in the slam.config then maybe you can try recycling the app pool.  Going to the SLAM Control Panel should reload the slam.config (and any of the other SLAM pages) but if the file really isn't reloading then the app pool recycle will force it to happen.

Jan 18, 2011 at 4:59 PM

That's the weird part.. Activating is making the necessary schema changes based on the config file, but when adding/editing list items, it is ignoring most of the columns outlined in the config file. For example, the last list in the config file is ITColumns, it doesn't go any farther than 'Criticality' when updating the SQL data.

Developer
Jan 18, 2011 at 5:11 PM

If you add this to your web.config:

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

 

Do you see the insert/update just contain those first few values?  Or are there any errors?  Does the configuration validation show all columns as matched/validated?

Jan 18, 2011 at 5:25 PM

Only seeing it inserting/updating those first few values. Configuration shows matched/validated. There is an error in the application log: This is followed by two more errors from our existing custom event receivers. I'm thinking there is some kind of conflict maybe. That error is related to a 'Value cannot be null. Parameter name: item'. I'll need to research that part a little more.

System.NullReferenceException: Object reference not set to an instance of an object.

at AWS.SLAM.ListItemEventManager.<>c__DisplayClass4.<ItemAdded>b__0()

at Microsoft.SharePoint.SPSecurity.CodeToRunElevatedWrapper(Object state)

at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass4.<RunWithElevatedPrivileges>b__2()

at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode)

at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param)

at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode)

at AWS.SLAM.ListItemEventManager.ItemAdded(SPItemEventProperties properties)

Jan 18, 2011 at 6:26 PM

Errors have been resolved (removed our custom event receivers), but still has the same behavior.