Lookup field in SharePoint List data source occasionally shows random blank values on report refresh

[rcascarano]

I have a report built from a SharePoint list which contains just over 10,000 records. There is one field that is a lookup column to another list that I have a card built off and also display in a table.

Occasionally, this field will not have data contained for a few records (always random records, generally no more than 1-10). Typically another refresh in the service fixes this, but occasionally it will just switch around which records are missing data. No data is being changed in the data source for these records. There is no other identifiable issue with other data other than some of these records having a blank value in the field that comes from this lookup column. 

I can't find any instance of this occurring for others online - but it seems like it must be related to the lookup field. Has anyone experienced this or is there something can be configured to prevent it? 

[collinq]

Hi @rcascarano ,

I have experienced this!  SharePoint lists are finicky and when you start with lookups, references to other lookups and value lists it gets tougher.  There is a 5000 row limit on views within SharePont itself.  And then, if you get to the point of having a number of lookups/selections (I think 10 or 12 or 15 - so not very many) you run into more problems. 

The only way to solve this problem is to break up and fix your SharePoint lists.  Which, is not a very good answer nor is it overly helpful, but, it is the answer.  

That is why the refresh sometimes gets rows and sometimes it doesn't - SharePoint is not feeding the data consistently or effeciently and records or parts of records get lost.  

SharePoint is great for easy "data" but reporting from it once we get into the 1,000's (not even 10,000's) gets a bit dicey.

Oddly, I have had a few lists that work just fine in the 20,000 range but have no idea why those work and everything else does not (maybe they are a different type of "site")?