cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
ArohShukla
Nintex Newbie

Split huge list with 255 columns into 4 or 5 lists

Jump to solution

Hi All, 

Wishing everyone a very HAPPY and prosperous new year 2017!

Background: 
Company hired a partner to automate their paper based system into electronic forms. 

Partner used SharePoint Online and used Nintex forms and workflows. However partner are using a single list with 255 columns and at times forms and workflow shows following error screenshot:

Current: 

Now, company approached us and we are in process of fixing the issue.

We found that it that list has some workflow is too slow due to number of columns and workflow at times shows this error.

We raised a  support ticket with Nintex and they asked to export form and import the form to a new list.  While doing so we got the following error:


 
Nintex also concluded that because of large columns company should reduce no of columns. However, the company cant reduce the no of columns as they are important columns.      

We are in process to re-develop the solution. 

We are thinking to split the that lists and planning to Nintex form to query other lists within the form


Q: Is it possible to have  a Nintex form to query different lists and display those columns within the form

Vadim TabakmanFernando HunthFranck Cornu

Labels: (1)
Reply
29 Replies
Nintex Employee
Nintex Employee

Re: Split huge list with 255 columns into 4 or 5 lists

Jump to solution

Hi Sandy,

Happy New Year Smiley Happy

You could use the List Lookup control on the form to do a query on another list and display the information.

Do you know why they have so many fields on that list?  Is it absolutely necessary?

You can have a bunch of controls on the form that aren't linked to fields and where needed, a workflow can query the data inside the form, rather than having so many fields on the list itself.

Vadim

View solution in original post

Reply
ArohShukla
Nintex Newbie

Re: Split huge list with 255 columns into 4 or 5 lists

Jump to solution

Thanks ‌ for your quick reply. I am also thinking on the same lines. However, company mentioned that they have different sections which are required.

We are in very initial stage and will make a final decision in 2-3 days. 
But I am very happy that you replied to my query !!  

Reply
Automation Master
Automation Master

Re: Split huge list with 255 columns into 4 or 5 lists

Jump to solution

What Vadim Tabakman‌ wrote is a really good solution. You have to review the columns list and decide if some of them can be considered as dictionary values for example. For such columns you should then create separate lists and use the lookup. This will also makes your workflow/ process data easier to maintain.

Maybe you can also find separable sub-processes in your workflow? Then you could create a separate list for each sub-process and trigger them one after another, using the "Start workflow" action.

Regardm,

Tomasz

Reply
Automation Master
Automation Master

Re: Split huge list with 255 columns into 4 or 5 lists

Jump to solution

Depending on how much the data within the list is going to grow, I would question whether SharePoint is the correct solution for this.

255 columns in a normal database table would be one very big, flat and unwieldy table, put that in SharePoint and it becomes more so.

Sounds to me like what you are now doing by breaking up the list into smaller lists is normalizing it and as we know SharePoint is not a relational database tool.

Have a good think about where this might head in the future. Whilst you might be able to manage the relationships between the tables now, will this be possible if the solution develops further. It might be worth looking at this as a database solution rather than a SharePoint solution.

Also, check out the column type limits here. This is for SharePoint 2013, but I believe similar limitations exist in O365

Reply
Automation Master
Automation Master

Re: Split huge list with 255 columns into 4 or 5 lists

Jump to solution

This is an interesting problem to say the least, because no matter what you do you will tend to run into one size limitation or another - even if you do resort to pulling in data from other places into a single form, your form could crash due to the amount of data.

You might want to consider building a series of forms, connected by a workflow, and then you could use task forms along the way to collect data and stash that data along the way (to either different lists, or to a data base).   A data base table might be ideal, because you could then use a reporting tool that would be better suited to produce output that could be visually reviewed.

SP is great at a lot of things, but there are a few cases that make fitting it to a solution challenging.  

Here is an article that describes the size limitations (pertaining to items) and some other interesting stats:

SharePoint Server 2010 capacity management: Software boundaries and limits 

Reply
ArohShukla
Nintex Newbie

Re: Split huge list with 255 columns into 4 or 5 lists

Jump to solution

Hi Mike M‌  Ryan GreenawayTomasz PoszytekVadim Tabakman

Thanks a ton for your valuable inputs.

I had to study this complicated form due to no of columns. 

I found that 67 columns were the primary columns. 
When a drop down item is selected (such as specific Database), other selections within a form with more fields such as SQL Server, Oracle, MySql etc as options  is shown.  

I need to make some more lists with lookup fields for this primary list with 67 columns and make solution more easier to maintain and scalable.

Any suggestion from experts?

Thanks.  
 

Reply
burkslm
Nintex Newbie

Re: Split huge list with 255 columns into 4 or 5 lists

Jump to solution

I would break down the form into sections/tabs. Each section would be a different list. The first section that is created would have the automated ID that would be used in the other lists to pull the information together. I definitely would still try to reduce any fields that aren't necessary because that is still a lot of fields.

Reply
ArohShukla
Nintex Newbie

Re: Split huge list with 255 columns into 4 or 5 lists

Jump to solution

burkslm‌ 

Thanks for your wonderful idea. That's my initial design to split the lists.  I came up with following list design

List Names

Columns

Main List (General Information, Request Number is main column, Requested Date, Requested by (Current user), Current Date etc. )

8 Columns

Project Information and lookup to main list

16 Columns

Commercial Information and lookup to main list

19 Columns

Supplier Chain Information

5 Columns

Options and lookup to main list

      • 4 Columns  (Basic Option, Option A, Option B, Option C).
      • Using repeated sections.
      • Each Repeating section will have  25 controls
      • Not all the Repeated sections will be used.

Database and lookup to main list

30 Columns

Other’s Information and lookup to main list

6 columns

 

In their paper form, it’s a 6 page and when a user checks the specific options such as Option A (each one has exactly same configuration ), Option B and Option C. Past partner created 25 columns each for options that amounts 75 columns.

That’s why I am planning to use repeating section and get just 4 columns instead of 75 columns.

Can you please suggest, how could I use 
automated ID which you suggested for all the lists? In my design each list will have its own ID.    

Thanks!!

Reply
ArohShukla
Nintex Newbie

Re: Split huge list with 255 columns into 4 or 5 lists

Jump to solution

Thanks  burkslm‌ for inputs, 

I found that there are redundant data in one of major section in that huge 255 columns list.

List stores a specific configuration for product.

For e.g. Product A has specific configuration and each configuration has exact same 25 columns (column Color of Prod A: “Green”, Material used: “Laminate Flooring” etc.) Product B has specific configuration and each configuration has 25 columns (column Color of Product B: “Blue”, Material used: “Hardwood Flooring” etc.)

I am making 2 lists to separate.

a) One list for Products with one column. For e.g. Product list with 4 products. Product A, B, C and D.

b) Second list with all Products Configuration and Product lookup.


However, in the Main form (Project Name, Customer Name, Product Category etc.)  I need to present all the Products and their related configuration to end users.

For e.g. Two entries in current form  looks this in Main form:  
1) Project Name: Riverside View Condo , Customer Name: John Smith , Product Category (Drop Down): Duplex house, Product: A,  Color: Green, Material: Laminate Flooring etc.

2) Project Name: Skyline Condo , Customer Name: Alex Yew , Product Category (Drop Down): Penthouse , Product: D,  Color: Blue, Material: Hardwood Flooring etc.  

I am thinking to use concatenate “Products” and “Products Configuration in the main  form.  For e.g.  Material_Hardwood_Flooring_ProductA in the main form.

Using this technique I will be significantly reduce the number of columns.

Any suggestions or recommendations would be greatly appreciated. 
Reply