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:

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

Example of a result:

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

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

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.

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:

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

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

<field sortorder="asc" sortindex="1">name</field>

<field sortorder="desc" sortindex="1">name</field>

<field sortorder="desc" sortindex="2">name</field>
<field sortorder="desc" sortindex="1">city</field>

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.

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.

Note that when “LIKE” and “NOT LIKE” are valid, all other variants are also valid.

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.

<condition operator="=">
  <exp type="field">responsible.name</exp>
  <exp type="string">Charles Bullit</exp>
</condition>

<condition operator="=">
  <exp type="field">numberofemployees</exp>
  <exp type="string">100 - 200</exp>
</condition>

<condition operator="=">
  <exp type="field">turnover</exp>
  <exp type="numeric">500000</exp>
</condition>

<condition operator="=">
  <exp type="field">orderdate</exp>
  <exp type="date">2008-10-10</exp>
</condition>

<condition operator="IN">
  <exp type="field">responsible</exp>
  <exp type="query">{urval i xml-format}</exp>
</condition>

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:

<condition operator="=">
  <exp type="field">person.idperson</>
  <exp type="numeric">10001</exp>
</condition>

When the situation is reversed and the company is a field, you do not need to specify the ID field:

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

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.

<condition operator="=">
  <exp type="field">name</exp>
  <exp type="string">Lundalogik</exp>
</condition>

<condition operator=">">
  <exp type="field">turnover</exp>
  <exp type="numeric">500000</exp>
</condition>

<condition operator="<">
  <exp type="field">turnover</exp>
  <exp type="numeric">100000</exp>
</condition>

<condition operator=">=">
  <exp type="field">orderdate</exp>
  <exp type="date">2008-10-10</exp>
</condition>

<condition operator="<=">
  <exp type="field">activeuntil</exp>
  <exp type="date">2010-12-31</exp>
</condition>

<condition operator="!=">
  <exp type="">active</exp>
  <exp type="numeric">0</exp>
</condition>

<condition operator="LIKE">
  <exp type="field">companyname</exp>
  <exp type="string"> AB</exp>
</condition>

<condition operator="IN">
  <exp type="field">category</>
  <exp type="string">VD;Vice VD;Säljchef</exp>
</condition>

<condition operator="ANY">
  <exp type="products"></>
  <exp type="string">Tallrikar;Glas;Bestick</exp>
</condition>

<condition operator="ALL">
  <exp type="field">products</exp>
  <exp type="string">Tallrikar;Glas;Bestick</exp>
</condition>

<condition operator="LIKE%">
  <exp type="field">name</exp>
  <exp type="string">Rudolf</exp>
</condition>

<condition operator="%LIKE">
  <exp type="field">name</exp>
  <exp type="string">Mulen</exp>
</condition>

<condition operator="NOT LIKE">
  <exp type="field">name</exp>
  <exp type="string">Lundalogik</exp>
</condition>

<condition operator="NOT LIKE%">
  <exp type="field">name</exp>
  <exp type="string">Lunda</exp>
</condition>

<condition operator="NOT %LIKE">
  <exp type="field">name</exp>
  <exp type="string">logik</exp>
</condition>

<condition operator="NOT IN">
  <exp type="category"></exp>
  <exp type="string">Kund;Prospekt</exp>
</condition>

<condition operator="IS">
  <exp type="field">turnover</exp>
  <exp type="numeric"></exp>
</condition>

<condition operator="IS NOT">
  <exp type="field">orderdate</exp>
  <exp type="date"></exp>
</condition>

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.

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:

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

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.

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.

<condition>
  <exp type="parenthesis">(</exp>
</condition>

<condition>
  <exp type="("/>
</condition>

<condition>
  <exp type=")"/>
</condition>

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:

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

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

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.

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.

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

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.

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.

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.

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

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.

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