Nintex lookup SQL Server on prem 5000 record limitation bypass?

Hi ​@HeidiV 

The 5000 Item limit is a method to protect the access to the resource.  At queries that involve more than 5000 items SQL places a lock on the table which prevents other queries from accessing the table.

One method to work around this is to apply a condition to the Data Lookup control that allows it to be filterable using a text field.  To do this you’d use the begins with condition on the data lookup control, then use the value as a text short control.

Heidi, keep in mind the 5000 limit is only for the query results. We typically use queries with various parameters to ensure significantly fewer results and we never get close to the 5000 limit. That is our way of getting around it and we have 10s of thousands of items to query.

Hope this helps.

Heidi, the other alternative is to increase the List View Threshold on the SharePoint list/lib. We’ve done this on two libraries which serve as active record repositories and noticed no significant performance issues. Results may vary based on your server configuration.

​@gman we are not looking up to SharePoint but to Sql view.

Heidi, my apologies for misinterpreting your request. I get so focused on SharePoint’s limitations sometimes I forget that Nintex has few of their own. And because I like to get around these, I was giving your situation some more thought. If I were faced with the same challenge, I would look for ways to get unfiltered (ie; SP or Nintex) access to my SQL data. 

One method I would start with is PowerShell. We employ a PowerShell 4 SharePoint add-on for Nintex Workflow (by DataOne) which allows us to extent the capability of workflows. There are connectors in PowerShell that allow one to access SQL Server. From there I would find a way to store and access the data temporarily to get the needed results.

Another option that comes to mind is by using another connector Nintex workflow provides - Web Calls to REST API. I’m certain there’s a way to use REST to connect to SQL data as well.

Just some thoughts. Hope this helps.

Hi Gman, the issue is that I want the full record set already on the start form. I don’t think that you can use powershell / Rest API from a start form. Only after the start event you can get creative with Nintex. Don’t stress about it - it is a start form limitation.