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
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>

• Forum thread