Sharepoint Field Types Question

Apr 3, 2009 at 6:03 AM
Hi,

first I want to thank the SLAM team for this tool. I was asked to provide a reporting solution on a sharepoint list and SLAM + SQL accomplished that in a snap. :)

As I am no developer (yet - working on my skills) I had trouble identifying the correct "name" of the Sharepoint field type in the slam.config. As Sharepoint only has a limited number of field types I wanted to ask if there is a list that matches Sharepoint field types to SQL field types? Especially the people picker field in Sharepoint caused some trouble.

Thanks a lot

Best regards

Silmarin
Developer
Apr 3, 2009 at 3:26 PM
Silmarin,
In one sense the SQL type is up to you, in that you could map everything to a nvarchar if you wanted, for example.  But that isn't generally useful.  For fields that should probably not be stored as strings:
Number -> decimal (with or without precision such as decimal(18,2) etc)
Currency -> money or decimal (usually with precision like decimal(18, 2)
Date and Time -> datetime
Yes/No -> bit
Calculated may be any one of the above.

Special cases of course are Lookup and Person or Group.  The Lookup and the people picker can create an association or can be saved as string, in which case the value stored is the delimited string of IDs and the field values for the lookup.  So to your specific question about the people picker you can create an association in the following way:

<List Name="MyList" ActivationOrder="1">
    <Fields>
        <Field Name="Title"  SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Person" AssociatedList="User Information List" AssociationName="MyListToUsers" SPType="Person" Required="false"></Field>
    </Fields>
</List>
<List Name="User Information List" TableName="Users" ActivationOrder="2">
    <Fields>
        <Field Name="Account" Column="UserName" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Title" Column="Name" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
        <Field Name="E-Mail" Column="E-Mail" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
    </Fields>
</List>

You can alternatively slam people by content type, leaving the MyList config the same:
<ContentType Name="Person" TableName="Users" ActivationOrder="2">
    <Fields>
        <Field Name="Account" Column="UserName" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Title" Column="Name" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
        <Field Name="E-Mail" Column="E-Mail" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
    </Fields>
</ContentType>

Using the ContentType is cleaner for slamming users in that it will not include Groups unlike slamming the User Information List.  One more note if you go the route of treating a Person field as an association and not a value stored in a varchar, the User Information List presents a significant problem in that Deleting events do NOT fire.  SharePoint documentation claims no events fire on the user list but that is not strictly the case.  However, deleting a user will not delete it in the SLAM database for this reason.  This may or may not create a problem for you, but it is something important to note (and something we cannot do a lot about in terms of changing SharePoint's behavior).

Hope this is helpful,
-Brian