SQS where clause taken from Relation field

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

The query will work with fields on the same table oly. It wont take into consideration the _cstm table. One option is to manually create the fields in the main table.

Agree with you, this is currently the only option I’ve found.

Thanks