How to best handle mandatory conditions
I doing the ground work for a CustomExpression builder
Forgetting that for the moment using the Criteria and Or columns how would you handle what I would call mandatory conditions
You have table with fields for Total Sales, Area, Category
You want to ask of the database return me all the Total computer and TV sales that are above £100 for the following areas B1 B2 G1 G2
so in SQL
WHERE (TotalSales>100 and (Category='Computers' or Category='TV')) AND (Area='B1' or Area='B2' or Area='G1' or Area='G2')
This is an over simple example and yes I could have used the IN operator but I wanted to emphasize the mix of and's and or's
I assume all of this would have to go in the criteria cell because the Or columns are only ever Or'd with the Criteria and each other ?
If that is the case then perhaps an option to turn off the Or columns when using the custom expression builder as they probably have limited use ?
Please note that implementing a custom expression editor you'll be able to modify a single cell of the Query Columns Grid. And if you'll try to assign such condition string to a single cell of the Query Columns Grid, the component wouldn't be able to act the way you expect. So, I'm afraid that this task can not be solved by means of the Custom Expression builder event.
What's worse is that you can't be sure if a mandatory condition is still present in the query or it's already been modified or removed by the end-user.
What you can do is to use the QueryTransformer API to seamlessly add these conditions after the query has been built by end-user. You can provide your own UI if you want to let end-users to handle these mandatory conditions.