In a fresh installation of SuiteCRM when I select Products I get the error below. I have not done any changes to any fields and I use a Greek Language pack
Mon Jun 26 13:42:36 2017 [3792][1][FATAL] Error running count query for AOS_Products List: Query Failed:(
SELECT count() c FROM (
SELECT
aos_quotes.,
accounts.id AS account_id,
accounts.name AS billing_account,
opportunity_id AS opportunity,
billing_contact_id AS billing_contact,
'' AS created_by_name,
'' AS modified_by_name,
'' AS assigned_user_name
FROM
aos_products
JOIN aos_products_quotes ON aos_products_quotes.product_id = aos_products.id AND aos_products.id = N'449e28ff-1ca1-02dd-4450-5950d040ef24' AND aos_products_quotes.deleted = 0 AND aos_products.deleted = 0
JOIN aos_quotes ON aos_quotes.id = aos_products_quotes.parent_id AND aos_quotes.stage = N'Closed Accepted' AND aos_quotes.deleted = 0
JOIN accounts ON accounts.id = aos_quotes.billing_account_id -- AND accounts.deleted = 0
GROUP BY accounts.id
) AS aos_quotes
)::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Column 'aos_quotes.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
So I have also experienced the very same problem. For some reason this bug https://github.com/salesagility/SuiteCRM/issues/2869 is labeled as low priority but is actually a very nasty one (sales guys are unable to easily identify purchases for some product, eg. last selling price).
Proposed solution for MYSQL installation is to remove ONLY_FULL_GROUP_BY in mysqld by editing my.cnf file but this solution is not very safe considering that you are actually changing grouping mode for the entire db server. Problems with this approach: http://mechanics.flite.com/blog/2013/02/12/why-i-use-only-full-group-by/
On the other side installations on MSSQL (like mine) don’t even have this as an option.
So, we need to edit SQL in the function:getCustomersPurchasedProductsQuery in modules/AOS_Products/AOS_Products.php
I my self have created custom AOS_Products.php and just commented out GROUP BY clause.
SELECT * FROM (
SELECT
aos_quotes.*,
accounts.id AS account_id,
accounts.name AS billing_account,
opportunity_id AS opportunity,
billing_contact_id AS billing_contact,
'' AS created_by_name,
'' AS modified_by_name,
'' AS assigned_user_name
FROM
aos_products
JOIN aos_products_quotes ON aos_products_quotes.product_id = aos_products.id AND aos_products.id = '{$this->id}' AND aos_products_quotes.deleted = 0 AND aos_products.deleted = 0
JOIN aos_quotes ON aos_quotes.id = aos_products_quotes.parent_id AND aos_quotes.stage = 'Closed Accepted' AND aos_quotes.deleted = 0
JOIN accounts ON accounts.id = aos_quotes.billing_account_id -- AND accounts.deleted = 0
--GROUP BY accounts.id
) AS aos_quotes
I am not sure if this is the best fix since there is no grouping at the moment but I needed purchases subpanel populated at least. It would be nice to see this corrected and also with MSSQL installations in mind.
I don’t think the GitHub Issue you link is going anywhere, because issues that start with “I made a custom relationship”, include “I use MSSQL” and end with “I don’t know if this is the right approach” are simply too troublesome and too much work for the stressed-out SuiteCRM team, which isn’t really familiar with MSSQL.
Please post your additional info there, or better, make a new Issue with a simpler statement of what is wrong (for example, if it this is relevant for everyone, even in situations where you don’t have any custom relationships), and a proposed fix, if possible. Thanks!
You are right. It seams too complicated and too custom to reproduce but for what I see this error should be very often since products as line items and quotes in Closed Accepted stage should be very common
Will create new issue and paste link here.
