Hello to all,
is possible to use a specific field in sqs where clause?
My Test:
sqs_objects["product_name[" + prodln + "]"] = {
"form": "EditView",
"method": "query",
"modules": ["AOS_Products"],
"group": "and",
"field_list": ["name", "id", "part_number", "cost", "price", "description", "currency_id","cdz_providers_aos_productscdz_providers_ida"],
"populate_list": ["product_name[" + prodln + "]", "product_product_id[" + prodln + "]", "product_part_number[" + prodln + "]", "product_product_cost_price[" + prodln + "]", "product_product_list_price[" + prodln + "]", "product_item_description[" + prodln + "]", "product_currency[" + prodln + "]"],
"required_list": ["product_id[" + prodln + "]"],
"conditions": [{
"name": "name",
"op": "like_custom",
"end": "%",
"value": ""
},
{
"name": "cdz_providers_aos_products_c.cdz_providers_aos_productscdz_providers_ida",
"op": "equal",
"value": provider_id
}],
"order": "name",
"limit": "30",
"post_onblur_function": "formatListPrice(" + prodln + ");",
"no_match_text": "No Match"
};
This will produce an error like below:
Error retrieving AOS_Products list: Query Failed: SELECT aos_products.* ,aos_products_cstm.r1_calc_end_unit_c,aos_products_cstm.r2_calc_end_unit_c,aos_products_cstm.r3_calc_end_unit_c,aos_products_cstm.r4_calc_end_unit_c,aos_products_cstm.r5_calc_end_unit_c,aos_products_cstm.min_contract_unit_c,aos_products_cstm.r1_calc_source_c,aos_products_cstm.r2_calc_source_c,aos_products_cstm.r3_calc_source_c,aos_products_cstm.r4_calc_source_c,aos_products_cstm.r5_calc_source_c,aos_products_cstm.product_url_c,aos_products_cstm.r1_calc_end_amt_c,aos_products_cstm.r2_calc_end_amt_c,aos_products_cstm.r3_calc_end_amt_c,aos_products_cstm.r4_calc_end_amt_c,aos_products_cstm.r5_calc_end_amt_c,aos_products_cstm.r1_refund_c,aos_products_cstm.r2_refund_c,aos_products_cstm.r3_refund_c,aos_products_cstm.r4_refund_c,aos_products_cstm.r5_refund_c,aos_products_cstm.refund_percentage_c,aos_products_cstm.r1_refund_percentage_c,aos_products_cstm.r2_refund_percentage_c,aos_products_cstm.r3_refund_percentage_c,aos_products_cstm.r4_refund_percentage_c,aos_products_cstm.r5_refund_percentage_c,aos_products_cstm.promo_disc_unit_c,aos_products_cstm.promo_c,aos_products_cstm.status_tag_c,aos_products_cstm.max_disc_unit_c,aos_products_cstm.r1_calc_unit_c,aos_products_cstm.r2_calc_unit_c,aos_products_cstm.r3_calc_unit_c,aos_products_cstm.r4_calc_unit_c,aos_products_cstm.r5_calc_unit_c,aos_products_cstm.min_contract_amt_c,aos_products_cstm.valid_from_c,aos_products_cstm.r1_calc_start_amt_c,aos_products_cstm.r2_calc_start_amt_c,aos_products_cstm.r3_calc_start_amt_c,aos_products_cstm.r4_calc_start_amt_c,aos_products_cstm.r5_calc_start_amt_c,aos_products_cstm.r1_calc_amt_c,aos_products_cstm.r2_calc_amt_c,aos_products_cstm.r3_calc_amt_c,aos_products_cstm.r4_calc_amt_c,aos_products_cstm.r5_calc_amt_c,aos_products_cstm.r1_payment_window_c,aos_products_cstm.r2_payment_window_c,aos_products_cstm.r3_payment_window_c,aos_products_cstm.r4_payment_window_c,aos_products_cstm.r5_payment_window_c,aos_products_cstm.promo_valid_from_c,aos_products_cstm.max_disc_amt_c,aos_products_cstm.r1_calc_start_unit_c,aos_products_cstm.r2_calc_start_unit_c,aos_products_cstm.r3_calc_start_unit_c,aos_products_cstm.r4_calc_start_unit_c,aos_products_cstm.r5_calc_start_unit_c,aos_products_cstm.r1_enabled_c,aos_products_cstm.r2_enabled_c,aos_products_cstm.r3_enabled_c,aos_products_cstm.r4_enabled_c,aos_products_cstm.r5_enabled_c,aos_products_cstm.status_c,aos_products_cstm.valid_to_c,aos_products_cstm.r1_recurrence_c,aos_products_cstm.r2_recurrence_c,aos_products_cstm.r3_recurrence_c,aos_products_cstm.r4_recurrence_c,aos_products_cstm.r5_recurrence_c,aos_products_cstm.promo_disc_amt_c,aos_products_cstm.payment_window_c,aos_products_cstm.promo_valid_to_c , jt0.user_name modified_by_name , jt0.created_by modified_by_name_owner , 'Users' modified_by_name_mod , jt1.user_name created_by_name , jt1.created_by created_by_name_owner , 'Users' created_by_name_mod , jt2.user_name assigned_user_name , jt2.created_by assigned_user_name_owner , 'Users' assigned_user_name_mod, LTRIM(RTRIM(CONCAT(IFNULL(jt3.first_name,''),' ',IFNULL(jt3.last_name,'')))) contact , jt4.name aos_product_category_name , ' ' cdz_providers_aos_products_name , ' ' cdz_providers_aos_productscdz_providers_ida FROM aos_products LEFT JOIN aos_products_cstm ON aos_products.id = aos_products_cstm.id_c LEFT JOIN users jt0 ON aos_products.modified_user_id=jt0.id AND jt0.deleted=0
AND jt0.deleted=0 LEFT JOIN users jt1 ON aos_products.created_by=jt1.id AND jt1.deleted=0
AND jt1.deleted=0 LEFT JOIN users jt2 ON aos_products.assigned_user_id=jt2.id AND jt2.deleted=0
AND jt2.deleted=0 LEFT JOIN contacts jt3 ON aos_products.contact_id = jt3.id AND jt3.deleted=0 LEFT JOIN aos_product_categories jt4 ON aos_products.aos_product_category_id = jt4.id AND jt4.deleted=0 where ((aos_products.name like '%%' AND (aos_product_category_id = '4365f18b-cba1-6c01-89f2-60a36fdd5194') AND (cdz_providers_aos_products_c.cdz_providers_aos_productscdz_providers_ida = 'a16f0535-7ae3-3011-5691-60a3c6f083f5'))) AND aos_products.deleted=0 ORDER BY aos_products.name LIMIT 0,30: MySQL error 1054: Unknown column 'cdz_providers_aos_products_c.cdz_providers_aos_productscdz_providers_ida' in 'where clause'
Fri May 21 11:22:42 2021 [123879][1][FATAL] Exception handling in include/MVC/Controller/SugarController.php:409
Fri May 21 11:22:42 2021 [123879][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.
Seems that the query does not make JOIN with relation table so the fields in there cannot be used in where clause.
Any help would be really appreciate!
Many Thanks