The goal of the Condition Query and Condition Expression(which we will see next) maps are to give the user control over conditions in your code. In other words, they are the developer's maps.
Imagine that you need to get a discount for an account. Instead of hard coding that discount/logic you can use one of these maps to get the value. That way, when you need to change the logic in obtaining the value you can simply change the mapping functionality. Your code will stay the same as it will simply call the map and get the value, how it is actually calculated stays in the map and can change when needed.
Another example, suppose that you can set an invoice due date to 30 days for all accounts that have more than 10 paid invoices. Instead of hard coding that "10" in your custom validations you launch the query in a map, so when the direction comes and changes the condition to 5 or "10 in the last two months" you simply change the map, not your code
The accepted format is a direct SQL OR a QueryGenerator specification.
If it contains both, the SQL part will be used and the other ignored
You MUST specify the return value which can be:
-
a field from the query, the value of the first row found will be returned
-
the string COUNT which will return the number of rows of the query
-
the string RECORDSET which will return all the results. Be careful!!
In QueryGenerator:
-
you can specify the fields to be returned in the SQL by a comma-separated string of field names
-
if no fields are specified the ID of the records will be used
-
the conditions are a JSON encoded string as generated by the conditions editor of the application
Examples:
<map>
<sql>
SELECT accountid,accountname<br>FROM vtiger_account
INNER JOIN vtiger_crmentity ce ON ce.crmid=vtiger_account.accountid
WHERE ce.deleted=0 AND vtiger_account.accountid =?
</sql>
<return>accountname</return> {count|recordset}
</map>
<map>
<module>Assets</module>
<fields>assetname,asset_no,productname</fields>
<conditions>[{"fieldname":"assetname","operation":"contains","value":"j","valuetype":"rawtext","joincondition":"and","groupid":"0"},{"fieldname":"product : (Products) unit_price","operation":"greater than","value":"30","valuetype":"rawtext","joincondition":"and","groupid":"0"}]</conditions>
<return>recordset</return>
</map>