====== Queries ======
When retrieving data from Lime CRM Web Service, you must specify a query that describes the data to be returned. A query is given in XML format and specifies the table on which the query is based, the fields from which data should be retrieved, the conditions that the data must meet and how it should be sorted. The result is also returned as an XML document.

Example of a query:

<code xml>
<query distinct="0">
  <tables>
    <table>company</table>
  </tables>
  <fields>
    <field>idcompany</field>
    <field sortorder="asc" sortindex="1">name</field>
    <field>responsible.name</field>
    <field>city</field>
    <field>category</field>
  </fields>
  <conditions>
    <condition operator="=">
      <exp type="field">city</exp>
      <exp type="string">Lund</exp>
    </condition>
    <condition operator="in" or="0">
      <exp type="field">category</exp>
      <exp type="string">Customer;Prospect</exp>
    </condition>
  </conditions>
</query>
</code>

Example of a result:

<code xml>
<data>
  <company idcompany="2001" name="Alfa Laval" responsible.name="Curt Cobain" city="Lund" category="221001" category.text="Prospect"/>
  <company idcompany="1001" name="Lundalogik AB" responsible.name="Charles Bullit" city="Lund" category="21001" category.text="Customer"/>
</data>
</code>

If the query is not correct, Lime CRM Web Service throws out an exception.

===== Tables =====
A query looks as if it will allow more than one table specification, but actually it does not. A query must contain one and only one table. However, it is possible to retrieve data from more than one table - but they must be related and derive from the table specified in the query.

Lime CRM Web Service validates the table specification. In order for a table to be used in a query, the following conditions must be met:

  * The table must exist.
  * The account used by Lime CRM Web Service must have read rights to the table.
  * The table must be published before being accessed via the web.

===== Fields =====
A query must contain at least one field specification that describes the information that is to be returned. The specifying fields do not need to be found in the specified table, but may exist in related tables. At the present time, related tables must be of the "field" type, i.e. from person you can retrieve information from company, but not the reverse. This is on condition that the "person" table can only have one "company" field while the "company" table may have many "person" fields. Related fields are denoted by a full stop.

This example shows how to retrieve the company's address from a person:

<code xml>
<tables>
  <table>person</table>
</tables>
<fields>
  <field>company.address</field>
</fields>
</code>

The way the results are sorted is specified via attributes for the fields that should be sorted.

^ Attribute ^ Value ^ Description ^ Example ^
| sortorder | 1 | Sorts in ascending order. | <code xml><field sortorder="asc" sortindex="1">name</field></code> |
| ::: | asc | ::: | ::: |
| ::: | 2 | Sorts in descending order. | <code xml><field sortorder="desc" sortindex="1">name</field></code> |
| ::: | desc | ::: | ::: |
| sortindex | 1-N | Specifies the order in which fields should be sorted. | <code xml><field sortorder="desc" sortindex="2">name</field>
<field sortorder="desc" sortindex="1">city</field></code> |

==== Additional fields ====
Certain fields specified in a query automatically generate additional fields for text representation in the result file. These fields are named ''{fieldname}{suffix}'', where "suffix" can differentiate between the various field types. The following table lists the fields that generate additional fields.

^ Field type ^ Suffix ^ Comments ^
| Relation | .descriptive | Entry description for the related entry. |
| Alternative | .text | Text of the alternative. |
| Quantity | .text | The alternative text representations in the format [Option 1];[Option 2];[n...] |
| File | <nowiki>__</nowiki>fileextension | Read more about additional fields for file and document fields here. TODO TODO TODO |
| ::: | <nowiki>__</nowiki>size | ::: |
| ::: | <nowiki>__</nowiki>lockedby | ::: |
| ::: | <nowiki>__</nowiki>timestamp | ::: |
|User | .text | The user's username. |

===== Conditions =====
Conditions are read and added in the same order as they are found in the XML document. The relationship between one condition and preceding ones is specified via the or attribute for the condition element. Valid values for the "or" attribute are "1" and "0", where "1" indicates that the condition's relation to preceding conditions is OR and "0" indicates the "AND" relation, according to reverse Polish notation. This means that the attribute does not fulfil any function for the first condition, and if it is included it is ignored. An example of how the "or" attribute is used can be seen below.

All conditions are validated and Lime CRM Web Service returns errors for invalid conditions along with information about the part that is invalid.

The table below lists how fields, operators and data types that can be combined in a condition.

  ; Data type : Indicates that the code applies to fields with the specified data type.
  ; Field type : Means that the field type has special rules. An asterisk (*) means that the rules apply to all remaining fields of the specified data type.
  ; RHS : Indicates whether the field type can also be used on the right-hand side of the operator.
  ; F2 : Indicates whether the field type can be used in conditions containing fields on both sides of the operator.
  ; Numerical : Lists valid operators when the value or field on the right-hand side is numerical.
  ; String : Lists valid operators when the value or field on the right-hand side is text.
  ; Date : Lists valid operators when the value or field on the right-hand side is a date.


^ Left ^ Right ^ ::: ^ ::: ^ ::: ^ ::: ^ ::: ^ ::: ^
^ Data type ^ Field type ^ RHS ^ F2 ^ Numerical ^ String ^ Date ^ Query ^
| Text | Set | No | No | | =, !=, IN, NOT IN, ANY, ALL | | |
| ::: | * | Yes | Yes | | =, !=, <, <=, >, >=, LIKE | | |
| Numerical | Option | Yes | Yes | =, !=, IN, NOT IN | =, !=, LIKE, NOT LIKE, IN, NOT IN | | |
| ::: | File | Yes | No | =, !=, IS, IS NOT | | | |
| ::: | Relation ((Query can only be specified on the right-hand side, which in this case means that the relation field must be specified on the left-hand side.)) | Yes | Yes | =, !=, IS, IS NOT | =, !=, LIKE, NOT LIKE | | IN, NOT IN |
| ::: | * | Yes | Yes | =, !=, <, <=, >, >=, IS, IS NOT | | | |
| Date | * | Yes | Yes | | =, !=, <, <=, >, >=, LIKE, NOT LIKE | =, !=, <, <=, >, >=, IS, IS NOT | |

Note that when "LIKE" and "NOT LIKE" are valid, all other variants are also valid.
==== Types ====
With the help of types, we can specify what exists on the relevant side of the operator. If one side consists of a constant value, the type indicates the data type that the value should be interpreted as. When a field is specified, the value is initially interpreted according to the data type for the field. However, certain fields can be represented as several data types; alternative fields, for example, are stored as whole integers but are also represented by text. In this situation, the type is used on the other side of the operator to indicate the data type that applies.

^ Type ^ Alternate syntax ^ Description ^ Example ^
| field | 1 | Indicates that the value for "exp" should be interpreted as a field. If the field is found in a different table, the full file path should be specified, with each step separated by a full stop. | <code xml>
<condition operator="=">
  <exp type="field">responsible.name</exp>
  <exp type="string">Charles Bullit</exp>
</condition></code> |
| string | 2 | Indicates that the value for "exp" should be interpreted as text. | <code xml>
<condition operator="=">
  <exp type="field">numberofemployees</exp>
  <exp type="string">100 - 200</exp>
</condition>
</code> |
| numeric | 3 | Indicates that the value for "exp" should be interpreted as a number. | <code xml>
<condition operator="=">
  <exp type="field">turnover</exp>
  <exp type="numeric">500000</exp>
</condition>
</code> |
| date | 4 | Indicates that the value for "exp" should be interpreted as a date. | <code xml>
<condition operator="=">
  <exp type="field">orderdate</exp>
  <exp type="date">2008-10-10</exp>
</condition></code> |
| query | 10 | Indicates that the value for "exp" should be interpreted as a query. | <code xml>
<condition operator="IN">
  <exp type="field">responsible</exp>
  <exp type="query">{urval i xml-format}</exp>
</condition></code> |

Unlike fields from which data is retrieved, relation fields that are used in conditions must be of the type "tab". For example, it is possible to specify conditions for persons related to the companies that are being queried, although a company may have many persons. However, there is one important difference, because you must specify a field from the related table, even if it is the field entry ID that you want to compare.

The following example involves a company that must be related to a specific person:

<code xml>
<condition operator="=">
  <exp type="field">person.idperson</>
  <exp type="numeric">10001</exp>
</condition>
</code>
 
When the situation is reversed and the company is a field, you do not need to specify the ID field:

<code xml>
<condition operator="=">
  <exp type="field">company</>
  <exp type="numeric">1001</exp>
</condition>
</code>

==== Operators ====
An operator specifies how the left side should be compared with the right. The table table lists the operators currently supported by Lime CRM Web Service. To improve legibility, all operators have at least one text representation. An operator can be described in several different ways so as to maintain compatibility with existing software. Note that the left-hand side constitutes the first "exp" element, while the right-hand side constitutes the other one.

^ Operator ^ Alternative syntax ^ Description ^ Example ^
| = |  1 | The left-hand side must be equal to the right-hand side. | <code xml>
<condition operator="=">
  <exp type="field">name</exp>
  <exp type="string">Lundalogik</exp>
</condition>
</code> |
| ::: | Equal | ::: | ::: |
| ::: | EqualTo | ::: | ::: |
| > | 2 | The left-hand side must be greater than the right-hand side. | <code xml>
<condition operator=">">
  <exp type="field">turnover</exp>
  <exp type="numeric">500000</exp>
</condition>
</code> |
| ::: | Greater | ::: | ::: |
| ::: | GreaterThan | ::: | ::: |
| < | 3 | The left-hand side must be less than the right-hand side. | <code xml>
<condition operator="<">
  <exp type="field">turnover</exp>
  <exp type="numeric">100000</exp>
</condition>
</code> |
| ::: | Less | ::: | ::: |
| ::: | LessThan | ::: | ::: |
| >= | 4 | The left-hand side must be greater than or equal to the right-hand side. | <code xml>
<condition operator=">=">
  <exp type="field">orderdate</exp>
  <exp type="date">2008-10-10</exp>
</condition>
</code> |
| ::: | GreaterOrEqual | ::: | ::: |
| ::: | GreaterThanOrEqualTo | ::: | ::: |
| <= | 5 | The left-hand side must be less than or equal to the right-hand side. | <code xml>
<condition operator="<=">
  <exp type="field">activeuntil</exp>
  <exp type="date">2010-12-31</exp>
</condition>
</code> |
| ::: | LessOrEqual | ::: | ::: |
| ::: | LessThanOrEqualTo | ::: | ::: |
| != | 6 | The left-hand side must not be equal to the right-hand side. | <code xml>
<condition operator="!=">
  <exp type="">active</exp>
  <exp type="numeric">0</exp>
</condition>
</code> |
| ::: | NotEqual | ::: | ::: |
| ::: | NotEqualTo | ::: | ::: |
| LIKE | 13 | The right-hand side must appear on the left. | <code xml>
<condition operator="LIKE">
  <exp type="field">companyname</exp>
  <exp type="string"> AB</exp>
</condition>
</code> |
| ::: | %LIKE% | ::: | ::: |
| IN | 14 | The left-hand side must contain at least one of the alternatives on the right. | <code xml>
<condition operator="IN">
  <exp type="field">category</>
  <exp type="string">VD;Vice VD;Säljchef</exp>
</condition>
</code> |
| ANY | 16 | The left-hand side must be included in at least one of the alternatives on the right. | <code xml>
<condition operator="ANY">
  <exp type="products"></>
  <exp type="string">Tallrikar;Glas;Bestick</exp>
</condition>
</code> |
| ALL | 17 | The left-hand side must contain all alternatives on the right. | <code xml>
<condition operator="ALL">
  <exp type="field">products</exp>
  <exp type="string">Tallrikar;Glas;Bestick</exp>
</condition>
</code> |
| LIKE% | 18 | The left-hand side must begin with the right-hand side. | <code xml>
<condition operator="LIKE%">
  <exp type="field">name</exp>
  <exp type="string">Rudolf</exp>
</condition>
</code> |
| ::: | StartsWith | ::: | ::: |
| %LIKE | 19 | The left-hand side must end with the right-hand side. | <code xml>
<condition operator="%LIKE">
  <exp type="field">name</exp>
  <exp type="string">Mulen</exp>
</condition>
</code> |
| ::: | EndsWith | ::: | ::: |
| NOT LIKE | 20 | The right-hand side must not appear on the left. | <code xml>
<condition operator="NOT LIKE">
  <exp type="field">name</exp>
  <exp type="string">Lundalogik</exp>
</condition>
</code> |
| ::: | NotLike | ::: | ::: |
| ::: | NOT %LIKE% | ::: | ::: |
| NOT LIKE% | 21 | The left-hand side must not begin with the right-hand side. | <code xml>
<condition operator="NOT LIKE%">
  <exp type="field">name</exp>
  <exp type="string">Lunda</exp>
</condition>
</code> |
| ::: | NotStartsWith | ::: | ::: |
| NOT %LIKE | 22 | The left-hand side must not end with the right-hand side. | <code xml>
<condition operator="NOT %LIKE">
  <exp type="field">name</exp>
  <exp type="string">logik</exp>
</condition>
</code> |
| ::: | NotEndsWith | ::: | ::: |
| NOT IN | 23 | The left-hand side must not be included in any alternatives on the right. | <code xml>
<condition operator="NOT IN">
  <exp type="category"></exp>
  <exp type="string">Kund;Prospekt</exp>
</condition>
</code> |
| ::: | NotIn | ::: | ::: |
| IS | 25 | The left-hand side must be ZERO. The "Exp" value for the right-hand side is ignored. However, the type for the right-hand side must be valid. | <code xml>
<condition operator="IS">
  <exp type="field">turnover</exp>
  <exp type="numeric"></exp>
</condition>
</code> |
| ::: | IsNull | ::: | ::: |
| ::: | IS NULL | ::: | ::: |
| IS NOT | 26 | The left-hand side must not be ZERO. The "Exp" value for the right-hand side is ignored. However, the type for the right-hand side must be valid. | <code xml>
<condition operator="IS NOT">
  <exp type="field">orderdate</exp>
  <exp type="date"></exp>
</condition>
</code> |
| ::: | IsNot | ::: | ::: |
| ::: | IsNotNull | ::: | ::: |
| ::: | IS NOT | ::: | ::: |
Most operators are simple to use and understand, but there are a couple that are not particularly obvious, and which require certain formatting in regard to the value to which they are applied.

=== IN and NOT IN ===
May be used for alternative fields and quantity fields, and indicate that the field should be compared with a number of alternatives separated by semi-colons. The number of alternatives may consist of either the ID or text, but not a mixture. You specify what the quantity contains via the "type" attribute, where "numeric" means the ID and "string" means text.

When the operator is used for an alternative field, this means that the value of the field must or cannot be found in the specified alternatives. When it is used for a quantity field, this means that the field must contain at least one or none of the specified alternatives.

The following example indicates that the alternative field "personcategory" must have a value within a specific quantity:

<code xml>
<condition operator="IN">
  <exp type="field">personcategory</exp>
  <exp type="string">Sales manager;Consultancy manager;Manager</exp>
</condition>
</code>

=== ANY and ALL ===
These operators may only be used for quantity fields, and indicate that the field should be compared with a quantity of alternatives. The "ANY" operator works in the same way as the "IN" operator, while "ALL" indicates that the field must contain all alternatives in the specified quantity. However, the field may contain additional alternatives. For exact matching, use the "=" and "!=" operators.

==== Grouping ====
Conditions can be grouped by inserting brackets around the conditions that are to be grouped. The relationship of the grouping to preceding conditions is indicated via the "or" attribute or in the condition element. The table below shows how brackets are inserted into the query.

^ Type ^ Alternate syntax ^ Description ^ Example ^
| parenthesis | | Indicates that a parenthesis should be inserted. The type of bracket is indicated via the exp value. Possible values: 8, 9, (, ), left, right, leftparenthesis, rightparenthesis | <code xml>
<condition>
  <exp type="parenthesis">(</exp>
</condition></code> | 
| ( | 8 | Indicates that a left parenthesis should be inserted. | <code xml>
<condition>
  <exp type="("/>
</condition>
</code> |
| ::: | left | ::: | ::: |
| ::: | leftparenthesis | ::: | ::: |
| ) | 9 | Indicates that a right parenthesis should be inserted. | <code xml><condition>
  <exp type=")"/>
</condition>
</code> |
| ::: | right | ::: | ::: |
| ::: | rightparenthesis | ::: | ::: |

==== Example ====
The following example shows a query that returns companies with a customer status of "Not contacted" when the person responsible is Charles Bullit or nobody at all:

<code xml>
<conditions>
  <condition operator="=">
    <exp type="field">customerstatusr</exp>
    <exp type="string">Ej kontaktad</exp>
  </condition>
  <condition or="0">
    <exp type="("/>
  </condition>
  <condition operator="=">
    <exp type="field">responsible.name</exp>
    <exp type="string">Charles Bullit</exp>
  </condition>
  <condition operator="IS" or="1">
    <exp type="field">responsible</exp>
    <exp type="numeric"/>
  </condition>
  <condition>
    <exp type=")"/>
  </condition>
</conditions>
</code>

===== Attributes =====
The query element here supports a number of attributes that indicate general properties for the query.

==== Output format ====
The ''outputformat'' attribute accepts "auto" or "raw", where "auto" is the default value and means that the result is formatted, involving extra fields with suffixes such as {.descriptive}, {.text}. Numerical values are also formatted. The value "raw" means that all data will be returned as it is stored in the database.

==== Distinct ====
The ''distinct'' attribute accepts "1" or "0", where "1" is the default value and means that all entries in the results must be unique. Imagine that the "entry ID" field is very important to the results of a search, with "distinct" set to "1" because all entries in a table have a unique entry ID. We would normally recommend that you always include the entry ID in a search, but there are situations when you do not wish to include the field and want to do a distinct search.

The following example allows you to retrieve all districts where there are customers. Each district will appear only once in the results. If the entry ID was included for this query, all companies with "customerstatus = Customer" would be included in the results, which would presumably return many more results.

<code xml>
<query distinct="1">
 <tables>
     <table>company</table>
 </tables>
 <fields or="0">
     <field sortorder="asc" sortindex="1">city</field>
 </fields>
 <conditions>
     <condition operator="=">
         <exp type="field">customerstatus</exp>
          <exp type="string">Customer</exp>
     </condition>
 <conditions>
</query>
</code>

==== Top ====
The ''top'' attribute indicates the maximum number of entries that are returned. The default value is "0", which means that all entries are returned; the attribute accepts all values greater than 0. Remember that the sorting can affect the entries that are returned.

==== First ====
You can use the first attribute to offset the starting point from which entries are retrieved. The default value is "1", which means that retrieval begins with the first entry. All values greater than 0 are accepted. If no sorting has been specified, the results will be sorted on the first specified field. You can retrieve, say, 20 entries at a time in intervals by combining this attribute with top; this can be useful when viewing search results.

==== Count ====
The ''count'' attribute indicates whether the number of entries that match the query should be returned. This attribute accepts "0" and "1", where "0" is the default value, meaning that the entries rather than the number of entries are returned. When you retrieve a number of entries, no field can be specified in the query, since Lime CRM Web Service automatically generates the field that is required.

The following example returns the number of customers.

<code xml>
<query count="1">
  <tables>
    <table>company</table>
 </tables>
 <conditions>
   <condition operator="=">
     <exp type="field">customerstatus</exp>
     <exp type="string">Customer</exp>
   </condition>
  <conditions>
</query>
</code>

The result you obtain contains only one data field, which contains the number of entries that match the query. The name of this data field is always count.

<code xml>
<?xml version="1.0" encoding="utf-16"?>
<data>
  <company count="321"/>
</data>
</code>