Quotes Filter Doesn't Filter For Line Items #4067

New issue

Open

opened 2026-02-20 16:13:15 -05:00 by deekerman · 1 comment

deekerman commented

2026-02-20 16:13:15 -05:00

Owner

Originally created by @jack7anderson7 on GitHub (Oct 21, 2019).

When using the advanced filter in the Quotes Module, it does not allow you to filter by line items as suggested

Issue

Above is the shown that the product is related to the "Test Quote" Record

Above is me searching for the line item via the advanced filter

Expected Behavior

After searching, all quotes with that line item present should be listed.

Actual Behavior

Relevant records are not listed

Steps to Reproduce

Go to Studio -> Quotes -> Layouts -> Filter -> Advanced Filter
Add line items from "Hidden" to "Default"
Return back to Quotes
Use advanced filter to search for Line Items

Context

Your Environment

SuiteCRM Version used: 7.11.8
Operating System and version (e.g Ubuntu 16.04): 18.04

Originally created by @jack7anderson7 on GitHub (Oct 21, 2019). When using the advanced filter in the Quotes Module, it does not allow you to filter by line items as suggested #### Issue ![Screenshot from 2019-10-21 09-20-50](https://user-images.githubusercontent.com/44227185/67188412-2f1c7d00-f3e4-11e9-9a6e-8046efcc90f9.png) Above is the shown that the product is related to the "Test Quote" Record ![Screenshot from 2019-10-21 09-21-03](https://user-images.githubusercontent.com/44227185/67188467-4d827880-f3e4-11e9-94de-5ad95e7ee1ed.png) Above is me searching for the line item via the advanced filter ![Screenshot from 2019-10-21 09-21-09](https://user-images.githubusercontent.com/44227185/67188527-7145be80-f3e4-11e9-8b2e-50db0ea4eccb.png) #### Expected Behavior After searching, all quotes with that line item present should be listed. #### Actual Behavior Relevant records are not listed #### Steps to Reproduce 1. Go to Studio -> Quotes -> Layouts -> Filter -> Advanced Filter 2. Add line items from "Hidden" to "Default" 3. Return back to Quotes 4. Use advanced filter to search for Line Items #### Context   #### Your Environment * SuiteCRM Version used: 7.11.8 * Operating System and version (e.g Ubuntu 16.04): 18.04

deekerman added the

labels

2026-02-20 16:13:15 -05:00

deekerman commented

2026-02-20 16:13:16 -05:00

Author

Owner

@LeoZandvliet commented on GitHub (Oct 25, 2019):

I tested this with the Contracts module, this bug is also present there. And I guess the same would go for invoices?

The field 'line_items' is literally added to the where clause of the query instead of a join with the table aos_products_quotes and a search on it's field name.
From the suitecrm logfile:

Query Failed:  
SELECT
	aos_contracts.id,
	aos_contracts.assigned_user_id,
	aos_contracts.contract_account_id,
	aos_contracts.name,
	aos_contracts.status ,
	jt0.user_name assigned_user_name,
	jt0.created_by assigned_user_name_owner ,
	'Users' assigned_user_name_mod, jt1.name contract_account,
	aos_contracts.total_contract_value,
	aos_contracts.currency_id,
	aos_contracts.start_date,
	aos_contracts.end_date,
	aos_contracts.date_entered,
	aos_contracts.created_by
FROM aos_contracts
	 LEFT JOIN aos_contracts_cstm ON aos_contracts.id = aos_contracts_cstm.id_c 
	 LEFT JOIN users jt0 ON aos_contracts.assigned_user_id=jt0.id AND jt0.deleted=0 AND jt0.deleted=0 
	 LEFT JOIN accounts jt1 ON aos_contracts.contract_account_id = jt1.id AND jt1.deleted=0 
WHERE 
	((aos_contracts.start_date = '2019-10-25' ) 
	AND ( line_items like 'TEST%')) 
	AND aos_contracts.deleted=0 
ORDER BY 
	aos_contracts.name ASC 
LIMIT 
	0,21

MySQL error 1054: Unknown column 'line_items' in 'where clause'

@LeoZandvliet commented on GitHub (Oct 25, 2019): I tested this with the Contracts module, this bug is also present there. And I guess the same would go for invoices? The field 'line_items' is literally added to the where clause of the query instead of a join with the table `aos_products_quotes` and a search on it's field `name`. From the suitecrm logfile: ``` Query Failed: SELECT aos_contracts.id, aos_contracts.assigned_user_id, aos_contracts.contract_account_id, aos_contracts.name, aos_contracts.status , jt0.user_name assigned_user_name, jt0.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, jt1.name contract_account, aos_contracts.total_contract_value, aos_contracts.currency_id, aos_contracts.start_date, aos_contracts.end_date, aos_contracts.date_entered, aos_contracts.created_by FROM aos_contracts LEFT JOIN aos_contracts_cstm ON aos_contracts.id = aos_contracts_cstm.id_c LEFT JOIN users jt0 ON aos_contracts.assigned_user_id=jt0.id AND jt0.deleted=0 AND jt0.deleted=0 LEFT JOIN accounts jt1 ON aos_contracts.contract_account_id = jt1.id AND jt1.deleted=0 WHERE ((aos_contracts.start_date = '2019-10-25' ) AND ( line_items like 'TEST%')) AND aos_contracts.deleted=0 ORDER BY aos_contracts.name ASC LIMIT 0,21 MySQL error 1054: Unknown column 'line_items' in 'where clause' ```