InfoPath Dev

I have a SP2016 list that has now reached 5000 items or its search limit.  My form can longer query the data connection to get the highest ID value I use to increment my records.  I have been told I need to create a subset data connection that doesn't query the entire list but rather records created more recently like yesterday, a subset. I am familiar with filtering on a list but not familiar with the process to make a data connection subset.  Any help is greatly appreciated.

 Thanks, 

So - the issue is with our query fields in InfoPath data connections, we can only use equivalence. You won't be able to check if something is less than today minus 7 for example. If you are using on-prem you might be able to use a REST query for this, using the listdata.svc. I'm trying to think of an approach that can use a regular list query and not need to use anything but equals - I was thinking about a calculated column with just the year and month from the created date, then you could use that for equivalence... like your calc'd column had 201907 in it... but then you need to be careful at month or year change. I suppose if nothing is returned, you could try the prior month (or year) in your query.

 I have created a calculated column with the formula =IF([Created]>=(TODAY()-7),"Yes","No") and this sets the field to Yes on all records created in the last 7 days.  I've added the new column in my data connection but it is not showing up in the query fields section of the data connection.  Any thoughts?

I'm sorry, I'm an idiot - I forgot that we can't query on calculated fields. Yuck. Any chance you can use REST here? Is this SharePoint Online?

 I am not using SharePoint Online, using SP2016.  I'm not sure how to use REST.

You'll probably need to use the older version - you can see if the ListData.svc is available with this URL (put in your server name, including site collection if needed in place of "myserver"): http://myserver/_vti_bin/ListData.svc

I'll go ahead and add a syntax sample for gt (greater than) - ListName replaced with your list name, and this is comparing the Modified date: /_vti_bin/listdata.svc/ListName?$filter=Modified gt datetime'YYYY-MM-DDT12:00:00Z'

 Hi Hilary,

For now I got it working using the xml of a library view sorted by ID for the last 7 days.  I am able to pull in the highest ID and increment my new records.  I'll need to lock down the view so it doesn't get tampered with.

We'll see how it goes.  I still have to do subsets on other data connections so I'm sure I'll be looking for your help again. 

Thanks for the help. 

 

Perfect, I'm happy you found a work around!