We are designing a system where data from forms will be stored in sharepoint lists. Problem is there are many fields around (260).
What is the better way to handle this ?
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It only takes a minute to sign up.Sign up to join this community
260 is still under limit. But its depend which type of column you are using in it. Inserting the items will not be an issue, but real issue will be how you retrive it in a view. There is list view threshold limit, which cause performance issue.
Below the list column limit per type:
Column Name Limit
Single line of text 276
Multiple Lines of Text 192
Date and Time 48
Yes / No 96
Person or group 96
Hyperlink or picture 138
Int 96 Managed
Even if it is (slightly!) under the limit -- there are almost no practical circumstances where you should be using 260 fields (or columns) on a single item.
Without any more details on exactly what you're trying to accomplish, the way that I generally handle lists where I want to put a lot of columns in, is to break them into groups of related items (typically in two or more separate lists).
I'll give an example of what I'm talking about below, but tell us the kind of data you're trying to collect and I can provide a more accurate example for your situation.
Typically when people want to have lists or tables with massive amounts of columns like this, it's because they're tracking many sub-items that tend to repeat themselves. Imagine if you will, a store with 3 employees. Maybe you call them manager, cashier, and cleaner -- but fundamentally they're all the same sort of thing, an employee.
One way I could track this store's data is to make it a single list item and have a firstname, lastname, contact number, etc. column for each employee -- for this store (I'll use location) with 3 employee's my list items would look like this
That's 12 columns already for some pretty simple data.
What I'm proposing is breaking these apart and providing a lookup between which employees belong to which location. Then you would have a location list, that looks like this
And an employee's list that looks like this:
Where location field in the employee's list is a lookup to the locations that exist in the 'locations' list. You would (so far) have 1 entry in the location list, and 3 entries in the employee list.
This does involve a fairly major architectural shift from single list items, but I think you'll find that your SharePoint application will perform better for your users and be much more straightforward to understand and maintain for you if you put some more work in on architecting the structure of your data up front.
You will get major performance problems with the first row wrapping - this occurs ie when you declare more than 8 datetime columns on one list - have a look at this discussion. I would strongly recommend you to NOT save your data in sharepoint at all, when talking about this amount of data. Save your Data on a SQL-Server (this could be the same sql server as your content-databases). But save it in a structure, that you designed. You can than access the data through a webpart or external lists. SharePoint internally saves all ListItems in one table - this is an advantage for the search engine, but as that table has a limited amount of columns it also has its drawbacks.
Believe me - don't try this! - I worked on a project once, that tried to save similar structures in sharepoint and it ended very abruptly.