Common Elements

List of common elements XML syntax reference.

<table>

The <table> element is used to reference a single table. If a table is read multiple times in a query aliases have to be used.

<table/>

Appearance

<table> </table>

Attributes

alias allfields flags flags2 index keys keysend linkId maxrecords readtype relindex setlen

Contents

<table>

May occur in

<request>

Remarks

none

 
      <request>
        <query>
          <tables>
            <table tablename="Company">
              <table tablename="Person">
                <table tablename="Contact"/>
              </table>
              <table tablename="Contact" alias="Contact2"/>
            </table>
          </tables>
          <condition>
            <cond tablename="Contact" alias="Contact2" fieldname="PeGrp" op="=" value="0"/>
            <cond tablename="Contact" alias="Contact2" fieldname="PeNo" op="=" value="0"/>
          </condition>
          <fields tablename="Company" fields="Company"/>
          <fields tablename="Person" fields="LastName"/>
          <fields tablename="Contact" fields="Contact,Date,Time"/>
          <fields tablename="Contact" alias="Contact2" fields="Contact,Text,Subject"/>
        </query>
      </request>

In this example the contact table is read twice, first person-related and second company-related.

In the second case, without the condition, all contacts related to the company would be returned (also contacts related to any person from that company). The condition serves to return only the contacts directly related to the company.

Note: In the second case the alias name is used as tablename.
      <Contact2 tableshort="MA" id=" 4294967394">
        <Contact>Letter</Contact>
        <Text>any text</Text>
        <Subject>any text</Subject>
      </Contact2>

Because the combination of tablename and alias has to be unique, one additional reference to the same table can also use the tablename as alias in order to have the same "tablename" in the response.

<field>

The <field> element is used to reference a single field. This can be used if only a single field is needed or for fields that need to have differing flags set (like read as external key etc.).

<field/>

Appearance

<field> </field>

Attributes

table tablename fid fieldname extkey (bool)

Contents

(no contents)

May occur in

< fields >

Remarks

None

In this case different attributes (e.g. extkey) can be set separately for each field.

      <request>
      <query>
        <tables>
          <table tablename="Company"/>
          <table tablename="Person"/>
        </tables>
        <fields>
          <field tablename="Company" fieldname="Company"/>
          <field tablename="Company" fieldname="Synonym"/>
          <field tablename="Company" fieldname="Country" extkey="true"/>
          <field tablename="Company" fieldname="LeadStatus" extkey=" false" />”
          <field tablename="Person" fieldname="LastName"/>
          <field tablename="Person" fieldname="FirstName"/>
        </fields>
      </query>
      </request>

<link>

The <link> element is used to reference a linked record (or "record link”) by record id.

<link/>

Appearance

<link> </link>

Attributes

table tablename recId linkId optional id

Contents

(no contents)

May occur in

<links>

Remarks

none

In this example the linked company record is referenced by its record id (attribute recId). The imported person record is linked to this company.

      <request>
        <import>
          <fields>
            <Person>
              <links>
                <link tablename="Company" recId="4294967297"/>
              </links>
              <FirstName>John</FirstName>
              <LastName>Doe</LastName>
            </Person>
          </fields>
        </import>
      </request>
      <request>
        <import>
          <fields>
            <Contact>
              <links>
                <link tablename="Company" recId="4294967297"/>
                <link tablename="Activity" recId="4294967565" optional="true"/>
              </links>
              <Contact>Brief</Contact>
              <Subject>Imported via CRM.interface</Subject>
            </Contact>
          </fields>
        </import>
      </request>
      <request>
        <import>
          <fields>
            <ProblemResolution>
              <links>
                <link table="FI" tablename="Company" recId="4294980423"/>
                <link table="FI" tablename="Company" recId="4294981501" linkId="1"/>
                <link table="FI" tablename="Company" recId="4294981502" linkId="2"/>
                <link table="FI" tablename="Company" recId="4294981503" linkId="3"/>
              </links>
              <No>4032010</No>
              <ProblemGroup>Software</ProblemGroup>
              <Problem>Bug</Problem>
            </ProblemResolution>
          </fields>
        </import>
      </request>

<links>

The <links> element is used to reference linked records (or "record links”).

<links/>

Appearance

<links> </links>

Attributes

(no attributes)

Contents

<link>

(any record)

May occur in

<import>

<query>

<putdoc> <doclinks>

(any record)

<match>

Remarks

The record links can be specified via the <link> element (it by record id), or using by specifying a record.

 
      <request>
        <import>
          <fields>
            <Person>
              <links>
                <Company>
                  <Company>update software AG</Company>
                  <Synonym>update</Synonym>
                  <Country>Österreich</Country>
                </Company>
              </links>
              <FirstName>John</FirstName>
              <LastName>Doe</LastName>
            </Person>
          </fields>
        </import>
      </request>

In the example above the linked record (company) is specified by contents of certain fields. The person record is imported and linked to this company, if exactly one unique matching company record exists. Otherwise an error is returned.

Note: The link record has to be just one unique record.

In the next example more than one link record for the contact record is specified (company and activity). Usually the contact is only imported if unique company and activity record exist. In this case the activity link record is not mandatory (due to optional=”true”).

      <request>
        <import>
          <fields>
            <Contact>
              <links>
                <Company>
                  <Company>update software AG</Company>
                  <Synonym>update</Synonym>
                  <Country>Österreich</Country>
                </Company>
                <Activity optional="true">
                  <Activity>PR Event</Activity>
                  <Level>1</Level>
                </Activity>
              </links>
              <Contact>Brief</Contact>
              <Subject>Imported via interface</Subject>
            </Contact>
          </fields>
        </import>
      </request>

In the default data model a ticket record has more than one link to the company table (default link, end customer company, contact company and company billing address).

In the next example the ticket record is linked to 4 different companies, each specified by the relevant link id.

Note: The link ids can be checked up in the data model in the service module of CRMwin or with a suitable <metainfo> request.
      <request>
        <import>
          <fields>
            <ProblemResolution>
              <links>
                <Company>
                  <Company>update software AG</Company>
                  <Synonym>default link</Synonym>
                </Company>
                <Company linkId="1">
                  <Company>company for ticket end customer</Company>
                  <Synonym>link no 1</Synonym>
                </Company>
                <Company linkId="2">
                  <Company>company for ticket contact</Company>
                  <Synonym>link no 2</Synonym>
                </Company>
                <Company linkId="3">
                  <Company>company for billing address</Company>
                  <Synonym>link no 3</Synonym>
                </Company>
              </links>
              <No>4032010</No>
              <ProblemGroup>Software</ProblemGroup>
              <Problem>Bug</Problem>
            </ProblemResolution>
          </fields>
        </import>
      </request>
      <response>
        <import>
          <return table="KM" tablename="ProblemResolution" id="4294967501" type="insert">
            <links>
              <link table="FI" tablename="Company" id="4294980423" linkId="-1"/>
              <link table="FI" tablename="Company" id="4294981501" linkId="1"/>
              <link table="FI" tablename="Company" id="4294981502" linkId="2"/>
              <link table="FI" tablename="Company" id="4294981503" linkId="3"/>
            </links>
          </return>
        </import>
      </response>

<condition>

The <condition> element is used to describe a condition for one table.

<condition/>

Appearance

<condition> </condition>

Attributes

(no attributes)

Contents

<cond> <lop>

May occur in

<query>

Remarks

none

<cond>

The <cond> element is used to describe a condition on a single field.

<cond/>

Appearance

<cond> </cond>

Attributes

table tablename fid fieldname fieldname2 mnr name value extkey (bool) extkey2 mnr2 value2 op

Contents

(no contents)

May occur in

<condition> <lop>

Remarks

none

The example request below returns all companies located in Austria.

      <request>
        <query>
          <tables>
            <table tablename="Company"/>
          </tables>
          <fields tablename="Company" fields="Company, Synonym,Country,Street,Tel"/>
          <condition>
            <cond tablename="Company" fieldname="Country" op="=" value="Austria"/>
          </condition>
        </query>
      </request>

The request below returns all companies located in Austria and within these companies all person and contact records matching the condition (Person: LastName starts with "B” and Contact: contact type=Letter).

Note: A separate <condition> section has to be declared for each table.
      <request>
        <query>
          <tables>
            <table tablename="Company">
              <table tablename="Person"/>
              <table tablename="Contact"/>
            </table>
          </tables>
          <fields tablename="Company" fields="Company,S ynonym,Country,Street,Tel"/>
          <fields tablename="Person" fields="LastName, FirstName"/>
          <fields tablename="Contact" fields="Contact, Subject "/>
          <condition>
            <cond tablename="Company" fieldname="Country" op="=" value="Austria"/>
          </condition>
          <condition>
            <cond tablename="Person" fieldname="LastName" op="=" value="B*"/>
          </condition>
          <condition>
            <cond tablename="Contact" fieldname="Contact" op="=" value="Letter"/>
          </condition>
        </query>
      </request>

In the next example the condition is interpreted as follows: companies where LeadStatus=”Customer” AND Revenue>1000000 and Employees>250 AND Rep=current Rep AND (Country=Austria OR Germany).

Note: In order to combine conditions with the logical operator OR you have to include them into a <lop value=”or”/> element. By default <cond> elements on the top level are combined with the logical operator AND. 
      <request>
        <query>
          <tables>
            <table tablename="Company"/>
          </tables>
          <fields tablename="Company" fields="Company,Synonym,Country,Street, el"/>
          <condition>
            <cond tablename="Company" fieldname="LeadStatus" op="=" value="Customer"/>
            <lop value="and">
              <cond tablename="Company" fieldname="Revenue" op=">" value="1000000"/>
              <cond tablename="Company" fieldname="Employees" op="=" value="250"/>
            </lop>
            <cond tablename="Company" fieldname="Rep" op="=" value="$curRep"/>
            <lop value="or">
              <cond tablename="Company" fieldname="Country" op="=" value="Austria"/>
              <cond tablename="Company" fieldname="Country" op="=" value="Germany"/>
            </lop>
          </condition>
        </query>
      </request>

In the example below all interest records are returned where InterestGroup="sports" AND Interest="soccer". The child catalog value is specified by the attribute value, the parent catalog value by the attribute value2.

Note: For backwards compatibility, the catalog values can be combined into the value attribute by using ~ as separator. (e.g. value="soccer~sports"). But this is deprecated and should not be used because it might not be supported in upcoming versions of interface any more.
      <request>
        <query>
          <tables>
            <table tablename="Interests"/>
          </tables>
          <fields tablename="Interests" fields="InterestGrp,Interest"/>
          <condition>
            <cond tablename="Interests" fieldname="Interest" op="=" value="soccer" value2="sports"/>
          </condition>
        </query>
      </request>

In the example below all companies are returned where the contents of the field FreeC1 and FreeC2 are equal (and not empty).

Note: In order to compare the contents of two fields in a condition, the first field has to be specified by the fieldname attribute, the second field by the fieldname2 attribute.
Note: It is not possible to compare fields with different field types.
      <request>
        <query>
          <tables>
            <table tablename="Company"/>
          </tables>
          <fields tablename="Company" fields="Company,FreeC1,FreeC2"/>
          <condition>
            <cond tablename="Company" fieldname="FreeC1" op="=" fieldname2="FreeC2" />
            <cond tablename="Company" fieldname="FreeC1" op="!=" value=""/>
            <cond tablename="Company" fieldname="FreeC2" op="!=" value=""/>
          </condition>
        </query>
      </request>

In example below all companies are returned where the contents of the field Rep equals the rep of the logged-in user.

      <request>
        <query>
          <tables>
            <table tablename="Company"/>
          </tables>
          <fields tablename="Company" fields="Company,Rep"/>
          <condition>
            <cond tablename="Company" fieldname="Rep" op="=" value="$curRep" />
          </condition>
        </query>
      </request>

In this example all companies are returned where the contents of the field FreeD1 equals the current date plus 5 days.

Note: Further variables that can be used are described in the trigger section of the CRM.core Administrator Guide.
      <request>
        <query>
          <tables>
            <table tablename="Company"/>
          </tables>
          <fields tablename="Company" fields="Company,FreeD1"/>
          <condition>
            <cond tablename="Company" fieldname="FreeD1" op="=" value="$curDay+5d" />
          </condition>
        </query>
      </request>

CRM.interface supports conditions on the timestamps of field values. The example below illustrates how to find out when the value of a certain field was last modified (using the attribute lupd).

      <request>
        <query>
          <tables>
            <table tablename="Company"/>
          </tables>
          <fields tablename="Company" fields="Company,FreeD1"/>
          <condition>
            <cond tablename="Company" fieldname="Synonym" op=">=" lupd="$curDay-2" />
          </condition>
        </query>
      </request>

In this example all companies are returned where the content of the field Synonym was last modified within the last 2 days.

Note: In order to use time conditions as well, you have to add the time to the value of lupd separated by a comma using the time format hhmmssttt. e.g. lupd="$curDay-2,144500000" … last modified since 2:45 p.m. 2 days ago.
Note: If no time is specified the condition is evaluated for the date only.

<sortlist>

The <sortlist> element is used to specify the sort order for one table.

<sortlist/>

Appearance

<sortlist> </sortlist>

Attributes

(no attributes)

Contents

<sort>

May occur in

<query>

Remarks

none

<custom_sortlist>

The <custom_sortlist> element is used to describe post-processing sort criteria.

<custom_sortlist/>

Appearance

<custom_sortlist> </custom_sortlist>

Attributes

(no attributes)

Contents

<sort>

May occur in

<query>

Remarks

none