After trying a few suggested changes, we found a reply to a very old post, which appeared to address the same type of issue for the list view problem.
We made the suggested changes to the SugarBean.php code - the second change on line 930 was already done but the first line was also commented out in our code.
This fixed the Products Purchases list view issue in the Purchases panel at the bottom of the Products screen issue with immediate effect, after a browser refresh. We have tried it with users with full privileges and those who are restricted through security groups and roles within them and it works for all.
So always worth a deep dive into the Forum, as this was on a 2019 post which was not specifically related to Products but was related to ‘Database Failure refer to SuiteCRM logs’ but the answer was posted over 3 years after the initial post. It is rightly highlighted by @pgr that you need to be careful with such old responses and that they are not a ‘one size fits all’ solution, but in our case, it has worked.
We still have the SQL issue in reports when trying to create a report but have a workaround for this so will open as a separate discussion if we can’t resolve that ourselves.
With these, we always implement on the test system localhost, and even though we have version control on the code, copy the original source file renaming it xxxxxxxx.old before we make any changes, so that we can simply back out if it causes more problems than it solves.