strange behaviour with filters in list view when using custom field

[slechaix]

Hi,

When I want to use advanced filter in a view, if I use a negative condition like "not equal to" or "does not contain" on a custom field, then I have no result.

My filter is really simple with only one condition:
Model field (cf_801) => does not containt "ABCDE" give me an empty list

Wheareas if I use a positive condition, I have no issue.
Same if I use filter on any non custom field, I have no issue at all.

Do you think it's a bad configuration, a bug, or maybe I'm not using filters correctly ?

Thanks for your help
Steve

[slechaix]

I enabled Debug_ListView_Query and I got this SQL request:

SELECT vtiger_products.productname, vtiger_crmentity.smownerid, vtiger_productcf.cf_706, vtiger_productcf.cf_801, vtiger_crmentity.modifiedtime, vtiger_products.productid
FROM vtiger_products
INNER JOIN vtiger_crmentity ON vtiger_products.productid = vtiger_crmentity.crmid
LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id
LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid
INNER JOIN vtiger_productcf ON vtiger_products.productid = vtiger_productcf.productid
WHERE vtiger_crmentity.deleted=0 AND ( ( (( vtiger_productcf.cf_801 IN ( select translation_key from vtiger_cbtranslation where locale="en_us" and forpicklist="Products::cf_801" and i18n <> 'WIXB-175_pret')
AND vtiger_productcf.cf_801 <> 'WIXB-175_pret') ))) AND vtiger_products.productid > 0;

I'm not sure if it's related but I noticed my vtiger_cbtranslation table is empty.
Is it an issue?

---

so if I changed my request to

SELECT vtiger_products.productname, vtiger_crmentity.smownerid, vtiger_productcf.cf_706, vtiger_productcf.cf_801, vtiger_crmentity.modifiedtime, vtiger_products.productid
FROM vtiger_products
INNER JOIN vtiger_crmentity ON vtiger_products.productid = vtiger_crmentity.crmid
LEFT JOIN vtiger_users ON vtiger_crmentity.smownerid = vtiger_users.id
LEFT JOIN vtiger_groups ON vtiger_crmentity.smownerid = vtiger_groups.groupid
INNER JOIN vtiger_productcf ON vtiger_products.productid = vtiger_productcf.productid
WHERE vtiger_crmentity.deleted=0 AND vtiger_productcf.cf_801 <> 'WIXB-175_pret' AND vtiger_products.productid > 0;

then I've got some result.

Is there a way to not use vtiger_cbtranslation or disable it in filter ?

[omarllorens]

Hi.

You have to go to coreBOS Updater and unblock the picklist_translations changeset. After you have to access to your database and find this changeset on vtiger_cbupdater table to change the Executed status to Pending status. After that you can go again to coreBOS Updater to select this changeset and apply again.

This changeset will update the vtiger_cbtranslation table with all the values that you need.

[joebordes]

Just a note: you don't have to go to the database to change anything. As Omar indicates, find the picklist_translations changeset in coreBOS updater, unblock it, then undo it (link in the same right panel widget) and then apply it. That should fix the issue

[slechaix]

Thanks a lot, that fixed the issue.