Hi, all.
I am developing a form for SharePoint 365 that will utilize three picklists, each of whose source/values should be "cascaded," i.e. filtered by using previous choices. The best analogy I can use is trying to select a City by drilling down from a State picklist to a County picklist to a City picklist: there are way too many cities in the U.S. to put in a picklist (you would certainly incur the dreaded "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator" error); but a picklist of 50 States will be able to provide a filtered list of Counties, which in turn will result in a reasonable list of Cities.
However, I find that I am unable to apply any filter values to this large list (78,000+ rows) even though I know the number of records returned would be well under the limit. (The list itself was created by importing a large spreadsheet.) When I try to query the list for Cities that match the County that was selected, I am told the list view threshold has been exceeded.
In attempting to fix the problem, I thought I would start with a nice, filtered default view of less than 100 rows, to avoid SharePoint's wrath. But I can't even apply a filter to the view; doing so still throws the "query results too large" error, which seems bizarre since I know the resulting view would return well under the maximum number of records. (I figured I'd use this filtered view and re-filter the list in InfoPath using the selected criteria, which would definitely return an acceptable number of records, as tested in Excel.)
Has anyone hit this impasse before? It's quite the conundrum: I can't use the list because there are too many records, but I can't filter it in SharePoint nor InfoPath, apparently for the same reason???
I know it seems like a lot of effort for picklists, but this involves hundreds of purchase orders and vendors and account numbers, and this would be the most reliable way to validate the user's input.
Please help!