This mapping permits us to evaluate an SQL command in the context of the application and get the result to decide subsequent actions. It accepts two formats, one is a direct SQL command with interrogation as place holder for parametres and the other is 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>

Additional Information

• Forum thread

Next | Chapter 2: Condition Expression.