fetchData criteria when field has customSQL="true"

[Alexf97]

When applying criteria on fetchData, if the field name of the criteria has customSQL="true" in the datasource file, the criteria does not work. For example, in my datasource file I have a field:

<field name="department_id" type="integer" customSQL="true" canSave="false"></field>

and when I run: fetchData({department_id: 5}), the returned data is not as expected, it just returns all data without taking into consideration the criteria I passed in the fetchData method.

Is there a way to fix that, maybe add an attribute in the data source field, or in the operation binding.

Using SmartClient Version: v11.1p_2021-05-26/Enterprise Deployment (built 2021-05-26)

Thank you

[Isomorphic]

Please see the docs for customSQL - what you are describing is exactly what it is documented to do, and is the desired effect. If you don’t want this to happen, just remove the attribute.

[Alexf97]

Hi Isomorphic,

I can't remove the attribute customSQL from the data source field because in my operation binding I am using an alias on that specific field. I use it in the following way:

select ifnull(department_id, 'Undefined') as department_id
from my_table

Basically, I am using an alias for the field department_id, with the alias being department_id, as seen in the code above. I hope it makes sense what I am doing there. Anyway, is there a way to keep the customSQL attribute in the data source field and still be able to use that field as criteria in the fetchData() method?

Thank you

[Isomorphic]

See docs - customSQL turns off all SQL generation for a field. You have other options like customSelectExpression, customCriteriaExpression, etc, or operationBinding.customSelectExpression et al if you want to change things on a per-operation rather than per-field basis.

It looks like, at most, you probably just want customSelectExpression here. Although it also seems as if you are possibly doing things at the wrong layer; if you just want null displayed visually to the user as "Undefined", DataSourceField.emptyDisplayValue accomplishes that, and avoids several possible problems with the client system treating the string "Undefined" as a real value for the field, instead of knowing that the field is in fact null and just displaying "Undefined" for end users.