Some fields never synchronise

Aug 3, 2009 at 11:04 PM
Edited Aug 3, 2009 at 11:08 PM

Hello

I've a list that I'm trying to sync into SQL.  After much trial and error (mainly until I realised I needed to use the "internal" field names) I've got the following in the slam.config which validates correctly.

<?xml version="1.0" encoding="utf-8" ?>
<Configuration>
  <ConnectionStrings Default="SLAM">
    <add Name="SLAM" ConnectionString="Database=SLAMData;Data Source=xxxxx\live;User Id=slam_app;Password=xxxxxxxxxxxxxxxx" />
  </ConnectionStrings>

  <DataMapping DataSchema="SLAM">
    <List Name="Legacy Projects" ActivationOrder="1">
      <Fields>
        <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
        <Field Name="Stage" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
        <Field Name="Description" SqlType="varchar(6000)" SPType="Note" Required="false"></Field>
        <Field Name="Account_x0020_Name" SqlType="varchar(255)" SPType="Lookup" Required="false"></Field>
        <Field Name="Product_x0020_Type" SqlType="varchar(255)" SPType="Lookup" Required="false"></Field>
        <Field Name="Business_x0020_Unit" SqlType="varchar(255)" SPType="Lookup" Required="false"></Field>
        <Field Name="Estimated_x0020_Revenue" SqlType="decimal" SPType="Number" Required="false"></Field>
        <Field Name="Estimated_x0020_Revenue_x0020_Cu" SqlType="varchar(5)" SPType="Text" Required="false"></Field>
        <Field Name="Estimated_x0020_Revenue_x0020__x" SqlType="varchar(20)" SPType="Number" Required="false"></Field>
        <Field Name="Gross_x0020_Margin" SqlType="varchar(20)" SPType="number" Required="false"></Field>
        <Field Name="Proposal_x0020_Due_x0020_Date" SqlType="DateTime" SPType="DateTime" Required="false"></Field>
        <Field Name="Est_x002e__x0020_Project_x0020_S" SqlType="datetime" SPType="DateTime" Required="false"></Field>
        <Field Name="Est_x002e__x0020_Project_x0020_E" SqlType="datetime" SPType="DateTime" Required="false"></Field>
        <Field Name="Work_x0020_Region" SqlType="varchar(255)" SPType="Text" Required="Lookup"></Field>
        <Field Name="Win_x0020_Probability" SqlType="int" SPType="Number" Required="false"></Field>
        <Field Name="Opportunity_x0020_Owner" SqlType="varchar(40)" SPType="User" Required="false"></Field>
        <Field Name="Latest_x0020_News" SqlType="varchar(6000)" SPType="Note" Required="false"></Field>
        <Field Name="Project_x0020_Files" SqlType="varchar(255)" SPType="URL" Required="false"></Field>
        <Field Name="ProjectID" SqlType="int" SPType="Number" Required="false"></Field>
      </Fields>
    </List>
  </DataMapping>
</Configuration>

Annoyingly, the only fields it synchronises to the SQL table are Title, Stage, Description and ProjectID.  It doesn't even seem to be trying to do the others.  In diagnostics it shows that the following SQL is being used:

Insert Item: insert into SLAM.LegacyProjects ([ListItemID],[ListID],[ListRelativeID],[Title],[Stage],[Description],[ProjectID]) values ('8de179c9-0474-460f-8e19-d33ed3be6ffb','116386b7-8e8d-4459-84cb-b17ddbb9cbd5',6,'Project Name Removed','2','<div>Project Description was removed to save confidentiality and all that...</div>',694)

There are still a whole bunch of fields that it seems to ignore.  I tried setting a few to Required="True" to see what would happen.  SLAM used the same SQL insert statement but SQL threw it out as the columns in the table were set to NOT NULL when it was created.  One odd thing, seems far to coincidental to ignore, is that the fields that it is syncing are those that are on the default view of the Sharepoint list.  I have tried making a different view, one that includes all fields, the default view but this hasn't made any difference.  I even deactivated, deleted the SQL table and re-activated.  So, it seems that the fields on the default view doesn't really have any bearing on what it "SLAM"s.

So, what could I have done wrong here!  I can't figure it out!

thanks in advance for any help!!

Dave

Developer
Aug 4, 2009 at 9:48 PM

Dave,

Not sure whether you had tried using Display Names first or not, but when activating had you already had this configuration in place?  What does the LegacyProjects table look like?  Do its column names look like "Account_x0020_Name" for example?  Or AccountName?  SLAM currently does not consider Account_x0020_Name to be equivalent to "Account Name".  That means it is looking for a column called "Account_x0020_Name" which may not actually be the column in the table.  What you should try then is setting Column values, like:

<Field Name="Account_x0020_Name" Column="AccountName" SqlType="varchar(255)" SPType="Lookup" Required="false"></Field>

That way you can keep the internal name as the field Name value but keep your table as having an AccountName column.

 

-Brian

Aug 4, 2009 at 10:49 PM
Hi Brian

I've not used the Column name attribute from the start, so my column name in SQL is Account_x0020_Name and has been from the beginning. I don't think that is what's causing the problem but it's certainly worth me knowing as it would simplify the column names in the SQL table!

Dave


________________________________

From: brjenkins [mailto:[email removed]
Sent: Tue 04/08/2009 22:49
To: Lee, Dave (GMSL)
Subject: Re: Some fields never synchronise [SLAM:64357]



From: brjenkins

Dave,

Not sure whether you had tried using Display Names first or not, but when activating had you already had this configuration in place? What does the LegacyProjects table look like? Do its column names look like "Account_x0020_Name" for example? Or AccountName? SLAM currently does not consider Account_x0020_Name to be equivalent to "Account Name". That means it is looking for a column called "Account_x0020_Name" which may not actually be the column in the table. What you should try then is setting Column values, like:

<Field Name="Account_x0020_Name" Column="AccountName" SqlType="varchar(255)" SPType="Lookup" Required="false"></Field>

That way you can keep the internal name as the field Name value but keep your table as having an AccountName column.



-Brian

Read the full discussion online <http://slam.codeplex.com/Thread/View.aspx?ThreadId=64357&ANCHOR#Post219470> .

To add a post to this discussion, reply to this email ([email removed] <mailto:[email removed]?subject=[SLAM:64357]> )

To start a new discussion for this project, email [email removed]

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe <http://www.codeplex.com/site/discussions/thread/unsubscribe/64357> on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


*******************************************************************************************************************
Please Think. Do you really need to print this e-mail?
Global Marine is an ISO certified company with a firm commitment to environmental issues. The information transmitted in this email (and contained in any attachments) is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you receive this email in error, please contact the sender and delete the material from any computer

Registered Number: 1708481 England
Registered Office: New Saxon House, 1 Winsford Way, Boreham Interchange, Chelmsford, Essex CM2 5PD
*******************************************************************************************************************