Adempiere and iDempiere has not implemented SQL logic in workflow transition.
I proposed following design and discuss with hengsin.
Allowing user to enter SQL which return either Y or N and return value used to check condition.
For security measure, we add method to check that sql entered should not be update, delete or insert.
Patch is attached. Please review.
Patch "Handling Exception case for SQL Operation.patch" need to be applied to fix exception saying "No Column defined".
As suggestion. Needs to mark SQL field as advanced to avoid normal user change it.
Instead of using Value to pass SQL, add additional field SQLLogic, which is visible when operation is SQL. This field is editable for advanced role only. For other Role, it should be read only.
make a readonlylogic to make it readonly when is not an advanced role - sometimes this is better because users can still SEE the configuration but cannot change it
if is possible too - better to make it compatible with the way Heng Sin implemented the @SQL logic for fields