Solved

Forms field lookup limitation

  • 28 May 2015
  • 7 replies
  • 239 views

Badge +4

We have a O365 Form solution in that has a lookup to another list.

The content in the other list has grown and now we found out no more that 2000 items can be returned.

Even when using a javascript with 'auto complete' this issue cannot be resolved.

 

Does anybody has an idea to overcome this hard limitation? (which makes our customer solution useless).
The only option I see is moving the lookup data to SQL Azure, but here it cannot be easily managed by Power users.

icon

Best answer by ms 2 June 2015, 10:29

View original

7 replies

Badge +5
EDIT: The latest versions of Nintex Forms 2010 (1.11.0.0) and 2013 (2.9.0.0) have now addressed this. As per the release notes: "Changed List Lookup limit if view not specified. Previously was limited to 2000 items but will now show all items.". Please see the Product Downloads page for more information.

It's not possible to adjust this limit of 2000 items for a Nintex Forms Lookup control. However we can workaround this limit by creating filtered views. I have done some testing with this and attached some screenshots which I hope you will find useful.

  • Create additional views on the source list for the lookup. I've created View1 and View2.

1.png

  • Each view will filter the items. In my example View1 shows ID's less than 1000.

2.png

  • In Nintex Forms Designer add the Lookup control to the form twice. I've created Lookup1 and Lookup2.

3.png

  • In the Settings for each control add the view name to the "Source view" field.

4.png

  • Now add a rule to disable the Lookup control if the other Lookup control contains a value. For example I've highlighted Lookup1 and added a "Formatting" rule to disable Lookup1 if Lookup2 has a value. The condition is: not(isNullOrEmpty(Lookup2))

5.png

I hope this helps you.

Badge +6

Hi Maarten,

I built a solution for a Building Surveying company not long ago and we too came up against the threshold limit issue. O365 solution with Nintex forms. They had a list for their "Site" Information and a list for their "Jobs". The form was linked to the "Jobs" list as this was where they'd log requests. As part of that they needed to 'lookup' the site to obtain post code, grid ref etc in the form and save it to the jobs record. They had 8,785 sites in total by the end, but only 1,670 at the very beginning.

As such I had to make some changes. The steps I took were to:

1. Create multiple views dividing the Sites list by Area (Leicestershire, Derbyshire, Nottinghamshire, etc)

2. Nintex lookup allows you to lookup a 'view' rather than the whole list so I created 9 different lookups (in the form with Nintex lookup, not a connected field to the Jobs list itself)

3. I created a drop down menu with these 9 different areas and assigned "Hide" rules to the lookups. The lookup field that appeared depended on which area you had selected from the drop down.

The user only ever saw one lookup field in the form as a lookup would only appear if its corresponding area had been selected. The form did end up with 10 extra fields but the user did not notice this, only noticing two. I then used JavaScript to run when the lookup fields changed and populate other fields on the form such as "SiteManager" and "PostCode" etc. Not only could this be done for SiteNames, but peoples/contacts list where the views are by Surname: A-I / J-P / Q-Z.

Hopefully Nintex lookup capabilities can help you too, once I got my head around how I was going to break up my data into views Nintex allowed me to do the rest.

Andrew

Badge +4

Thanks. It's not the perfect solution but it works.


We made it a littlebit more pretty by hiding both Lookup fields and adding a javascript 'autocomplete' field in the form. The javascript does the autocomplete on the values on both Lookup fields.
Sure, It takes a 2-3 seconds to load all 2800 items in both lookups, but the customer was happy with this solution.

We used this example to build the autocompete solution (until it will be added to the product).

Badge +4

Thanks for sharing your Experience. Useful.

Badge +5

Thanks Maarten. The link you provided is useful.

Userlevel 6
Badge +13

Maarten Visser​ Out of interest, I've just implemented the same autocomplete function on a list with some 700+ items (with an old version of Nintex) However, because the SPServices operation is separate from Nintex, there is no need to worry about the item limit imposed by Nintex. It just returns results to our text box control without worrying about any of the limits from Nintex so you shouldn't need to have your script run against the two different views that you seem to have. We do have a CAML query that filters out some unnecessary results for us.

I wonder though, does anyone know how the autocomplete would behave against a list that exceeds the List View Threshold?

Badge +4

Yes, I know what will happen.

It doesn't break, but all results above 5000 won't be shown.

Reply