7

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 ?

1
  • Will all these fields show on one form? With that many fields, you may want to re-think your data architecture. I've never seen a case where a single record had to have that many fields. You probably need a parent/child setup.
    – teylyn
    Aug 18, 2014 at 21:26

3 Answers 3

11

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

Choice 276

Number 72

Currency 72

Date and Time 48

Lookup 96

Yes / No 96

Person or group 96

Hyperlink or picture 138

Calculated 48

GUID 6

Int 96 Managed

metadata 94

SP2010 Linkk https://technet.microsoft.com/en-us/library/cc262787(v=office.14).aspx#Column SP2013 Link http://technet.microsoft.com/en-us/library/cc262787(v=office.15).aspx#Column

1
5

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

|locationNumber|locationName|locationCity|managerFirstName|managerLastName|managerContact|cashierFirstName|cashierLastName|cashierContact|cleanerFirstName|cleanerLastName|cleanerContact|

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

|locationNumber|locationName|locationCity|

And an employee's list that looks like this:

|firstName|lastName|contactInfo|location|position|

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.

1
  • I agree. People looking at 260 pieces of information on a single item won't be able to take in all the details, get a quick overview of an item, or be able to easily find the most important information.
    – Erin L
    Jul 13, 2016 at 21:02
0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.