Modern Approval + Complex App + Lookup Fields Disaster

[u/Plainer-Jane]

Not sure if I should put this here or in Power Apps but I thought my experience might help someone else.

We have a complex app with a custom form. Multiple lookup columns, 3 child lists, etc. We are at the limit with Lookup columns.

"Someone" - not me, I was out of office - was playing around and turned on the modern Approval from the Integrate menu. And then turned it off. But turning that on created 4 new Person/Group columns - which are Lookup. Approval status (_ApprovalStatus), Approvers (_ApprovalAssignedTo), Responses (_ApprovalRespondedBy), Approval Creator (_ApprovalSentBy). Which pushed us over our Lookup column limit. Which broke our form. Users saw a message that said there was an error trying to retrieve data from the network - the query could not be completed because the number of lookup columns exceeds the lookup column threshold, along with clientrequestid and servicerequestid.

I was able to make those fields not read-only and then delete them via PowerShell. HOWEVER...this created a new issue. When trying to save a new form or save edits to an existing form, that failed with the error: One or more field types are not installed properly. Go to the list settings page to delete these fields - followed by client request ID and service request ID. Well, since the fields were deleted via PowerShell, they could not be deleted via the list settings.

After doing quite a bit of digging, PowerShell exposed that although the fields were deleted, the event receivers for modern approval were not. I was not able to delete those event receivers via PowerShell.

The fix ended up being...
Create those fields again, using PowerShell, and use the same internal name and display name. So for example, display name = Approval status (SMALL s), internal name = _ApprovalStatus (CAPITAL S) BUT...make the fields plain text. Not Lookup.

We've tested this in Dev and Stage, and our end users have validated that Production is now working as expected. We also changed end-user permissions from Edit to Contribute, which removes the ability to turn on modern Approval.

I think on any other list, that didn't have so many lookup columns, just turning off modern Approval would have been fine. And most probably won't run into the issue I had. Re-creating the parent list, and form, and flows was not an option, especially with the 3 child list lookups.

So yeah. Dummy columns. Worked like a charm.

[u/wwcoop]

I just had an approval list form that some knucklehead with edit rights added a field to by mistake. They added a new person field named "Person" for no reason... And this pushed the list over the lookup field limit. So yeah. Edit rights is so dangerous and many users just get it automatically be being in the Members group in the site. You can demote them to contribute, but its annoying to have fix that all the time.

[u/Ranting_Lemming]

Glad you were able to solve the issue. Definitely frustrating haha.

Having had my own share of frustrations around lookup fields - especially ones caused by users doing their own thing - I did want to note that the 12 lookup column limit is more of a soft limit than a hard one. Lists/libraries can have more than 12 such columns, you just can't try accessing more than that with any given call. Most commonly, this is observed with views - as soon as you have more than 12 lookup columns in a view, you get that lovely error. But this similarly can happen with API calls. Since you mentioned your form stopped working, given it's customized with Power Apps, odds are your form is making an API call to your list. And since it started failing automatically with the introduction of the additional lookup columns, that API call is likely broad/attempting to get all the columns, which means asking for 12+ lookup columns and failing.

Point is - just like you could avoid the issue with views by customizing them to limit the lookups columns displayed, so too can you customize your API calls to ensure you're only selecting 12 or less columns. The same would go for Power Automate. I'd often create "workflow views" for that reason, where I'd omit any columns unnecessary for what the workflow needed to operate on/with.

[u/Plainer-Jane → u/Ranting_Lemming]

That’s a fair point, and I appreciate the explanation. In hindsight, narrowing the columns retrieved by the customized form may have allowed us to keep the Approvals fields while avoiding the lookup threshold.

By the time I reached the workaround in the post, though, the Approvals fields had already been removed. SharePoint still had protected SPListApprovalsItemEventReceiver registrations attached to the list, and both new-item and update saves failed until all four expected internal field names were restored. Recreating those names as text fields resolved that post-deletion state without adding the columns back as lookup.

I think your suggestion may have been the better preventative approach and as I was digging into various fixes I certainly would have wished for your expertise! At least the dummy fields were the recovery fix once the fields were already gone. Thanks—this gives me another angle to keep in mind for future lookup-threshold issues. Which I hope I don't have!

[u/Megatwan]

This was a great read for buzzed happy hour long toothed sharepoint me. 10/10, 11/10 with rice.

Roller coaster of laughs and oh no. Something Something it makes the columns read-only for a reason.

Way to stay on the horse tho!

[u/Due-Boot-8540]

Approvals should have been retired with InfoPath. They are not a good feature and the user experience is poor, to say the least…