Schweiz Deutschland Österreich
Schweiz Deutschland Österreich
AI Chat  
Schweiz Deutschland Österreich
vertec.com

Vertec XML extension

Vertec is equipped with an XML extension. This is provided by the Vertec XML server. With this extension, requests can be formulated in XML and sent to Vertec via http. The response is also formatted in XML.

Product line

Standard

|

Expert

Operating mode

CLOUD ABO

|

ON-PREMISES

Modules

Services & CRM

Budget & Phases

Purchases

Resource Planning

Business Intelligence

Created: 13.06.2022
Updated: 09.10.2024 | Added example about assigning keyword folder

With the XML extension, you can  query , create , change or delete all data from Vertec.  The extension is therefore suitable for connections to all types of third-party systems.

Communication is based on the request /response schema . A client sends an XML-formulated request via HTTP POST and receives an XML-formulated response as a result.

The format of the XML messages used is based on the SOAP specification. Full SOAP compatibility is not implemented, in particular, the handling of XML namespaces is missing. Namespaces can be used in requests, but are ignored. Response messages do not use namespace information.

The structure of XML messages is always the same. For example, each message, whether request or response, always contains the basic element Envelope and the subelement 'Body’:

<Envelope>
   <Body>
   </Body>
 </Envelope>

XML server

Authentication

XML server authentication takes place via API token , which is passed in the parameter api_token. Example:

import requests
url = 'http://localhost:8081/xml'
api_token = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.MGVhZmUzMzYtNmVhMi00MDdhLTgxNjQtZDYxZmI0NzU2MWZi._r16YlvWmZCMJ3qdDX3bK5_DJHwcczTYaWoKUYUNZuk’
headers = {'Authorization’: 'Bearer %s’ % api_token}
xmlquery = “"”“
<Envelope>
  <Body>
    <Query>
      <Selection>
        <ocl>User</ocl>
      </Selection>
    </Query>
  </Body>
</Envelope>
"”“
response = requests.post(url, headers=headers, data=xmlquery)
print(response.text)

You can access it directly on /xml .

The same session is always used within the timeout interval .

Note: In Vertec versions before 6.6, authentication occurred via a separate endpoint /auth/xml . The corresponding handling can be found in the article at the bottom .

Starting multiple sessions

You can start a separate Vertec.Session process per XML session. This avoids problems with long-running sessions.

If a new (parallel) session is to be used explicitly, an optional HTTP header VertecSessionTag can be provided with any value (e.g. 1). A maximum of 36 characters is allowed. Example:

headers = {'Authorization’: 'Bearer %s’ % api_token, 'VertecSessionTag’: '1'}

A new session is started per VertecSessionTag. Therefore, the session affinity is based on the combination of API token and session ID.

Session handling

  • For an XML request on /xml, first the token is checked for validity.
  • It then checks whether a Vertec session is already running for this token. If yes, the request is passed to this session.
  • If no session exists for the specified XML session ID, a session is created (or taken from the idle pool ).
  • An XML session has a session timeout of five minutes. If no new request is received for the session during this time, the session is ended. For On-Premises, this value can be adapted in the Vertec.ini if required.
  • If the session was ended due to the timeout, a new session is simply started. Thanks to the idle sessions, the delay is minimal even for a new session.

Testing the XML extension

To test the XML request, a simple web interface is available at /xml/query.

The API token is entered in the API token field.

The body element of the XML request is entered in the XML request field.

The XML result is shown below:

Please note that it may happen that you access your real database and any data changes may impact your real data unless you have configured an independent test installation .

Encoding

The XML server uses UTF-8 encoding when sending and receiving data.

XML request

The XML request contains a header in which the authentication information is carried over.

<Envelope>
  <Header>
  </Header>
  <Body>
  </Body>
 </Envelope>

The body then contains the actual query:

Query objects

To query objects using an XML message, the query block is wrapped in a Query element. This element, in turn, contains the elements Selection and Resultdef.

<Query>
  <Selection>
    <objref>676</objref>
    <ocl>offeneleistungen</ocl>
    <sqlwhere>datum between '01.10.2021' and '31.12.2021'</sqlwhere>
    <sqlorder>bold_id</sqlorder>
  </Selection>
  <Resultdef>
    <member>datum</member>
    <member>projekt</member>
    <member>text</member>
    <member>wertext</member>
  </Resultdef>
</Query>

Selection element

The selection element assembles the actual query. The following subelements can be used: <objref>, <ocl>, <sqlwhere> and <sqlorder>.

<objref>

With the <objref> element, you can query Vertec objects by their object ID.

Single object:

<Selection>
  <objref>676</objref>
</Selection>

Or object list:

<Selection>
  <objref>676</objref>
  <objref>2895</objref>
</Selection>
<ocl>

In <ocl> element, you can specify an OCL expression .

If you specify a (single) <objref> element, the OCL is executed on it:

<Selection>
  <objref>676</objref>
  <ocl>eigprojekte</ocl>
</Selection>

If no <objref> element is specified, the OCL is run globally:

<Selection>
  <ocl>projektbearbeiter</ocl>
</Selection>

If the results are to be sorted or filtered, you can do this with the usual OCL list operators ( ->select(), ->orderby() etc.). Also here, pay attention to a high-performance access .

<sqlwhere>

You can specify a SQL filter in a <sqlwhere> element. The <ocl> element specifies the class name (case sensitive).

The executing user must have administrator rights or SQL query rights.

<Selection>
  <ocl>OffeneLeistung</ocl>
  <sqlwhere>datum between '01.01.2023' and '31.12.2023'</sqlwhere>
</Selection>

If you want to query all objects of a class via SQL, you can use the trick: specify a <sqlwhere> which is always true, e.g.

<Selection>
  <ocl>Projektbearbeiter</ocl>
  <sqlwhere>1=1</sqlwhere>
</Selection>
<sqlorder>

In combination with <sqlwhere>, the objects can be sorted with <sqlorder>:

<Selection>
  <ocl>OffeneLeistung</ocl>
  <sqlwhere>datum between '01.01.2023' and '31.12.2023'</sqlwhere>
  <sqlorder>datum</sqlorder>
</Selection>

Resultdef element

The Resultdef element specifies what information is requested. The following subelements can be used:

  • member: Specifies a member of the result object in OCL. If the member is an independent object, the Vertec object ID is returned (in the example, for the member projekt). Any number of members can be listed.
  • alias-expression: If you want to extend a member with if-else conditions or calculations, an alias is used in an expression. OCL expressions can be used in this expression. The structure is as follows: 
    <expression>
 <alias>abgeschrieben</alias>
 <ocl>wertint-wertext</ocl>
</expression>

    All OCL expressions are possible in the <ocl> area.

It is not necessary to specify a Resultdef element. If only a single value is requested (and not, as described above, a list of objects), you can simply delete this element. For example, the selection <ocl>projektbearbeiter->size</ocl> returns the number of users. The response returns a <Value>Anzahl</Value>.

XML response

QueryResponse element

The result is returned in a QueryResponse  element. This always contains a list of objects.

  • objid: Each returned object is given as an independent element. For each of these result objects, an objid is provided that contains the Vertec object ID: 
    <objid>7822</objid>

In addition, all of the members specified under Resultdef are listed. If these are independent objects, the object reference (object ID) is supplied:

<Envelope>
  <Body>
    <QueryResponse>
      <OffeneLeistung>
        <objid>7822</objid>
        <datum>2021-11-14</datum>
        <projekt><objref>4164</objref></projekt>
        <text>Letter to Mr. Meier<text/>
        <wertExt>120.00</wertExt>
      </OffeneLeistung>
      ...

Note: The date format is ISO 8601.

Creating objects

Create element

To create an object, a Create element is used. This contains a list of the objects to be created. The relevant class name in each case is specified as the object to be created. The individual members contain either an object reference (object ID) of an existing object in Vertec or the value to be entered in the member.

In the example below, an open service is created. This is assigned to an already existing user and an already existing project by means of an object ID. The hours are entered in the attribute minutesint as 45 minutes:

<Create>
    <OffeneLeistung>
      <bearbeiter><objref>4036</objref></bearbeiter>
      <projekt><objref>4132</objref></projekt>
      <minutenint>45</minutenint>
    </OffeneLeistung>
</Create>

CreateResponse element

A CreateResponse element is returned. This contains a list of created objects, each specifying the class name of the created object. These, in turn, contain the following subelements:

  • objid: Specifies the object ID of the newly created object.
  • isValid: Specifies whether the object generated in Vertec is valid (1) or not (0).
<Envelope>
  <Body>
    <CreateResponse>
      <OffeneLeistung>
        <objid>12019</objid>
        <isValid>1</isValid>
      </OffeneLeistung>
      ...
    </CreateResponse>
  </Body>
</Envelope>

Updating objects

Update element

Updating objects works in a similar way to creating objects. You can simply use the Update element. This contains a list of elements to be updated. The class name of the object to be updated is specified as a subelement.

  • objref: For each element to be updated, the object reference (object ID) must be specified. It is important that this is specified first, i.e. before the other attributes, otherwise the change will not be carried out.
  • Furthermore, you must specify the attributes to be updated. In the example below, the abbreviation CKE is entered for a project user with the object ID 4019:
<Update>
    <Projektbearbeiter>
      <objref>4019</objref>
      <kuerzel>CKE</kuerzel>
    </Projektbearbeiter>
    ...
</Update>

Changing custom fields

Changing custom fields works the same way as above. Simply use the name of the custom field as the member name. In the example, the text Angefangen is entered in the custom field Project status on a phase with the ID 5491:

<Update>
  <Projektphase>
    <objref>5491</objref>
    <Projektstand>Started</Projektstand>
  </Projektphase>
</Update>

Setting key values and tags

As of Vertec 6.3, key values and tags can be set or removed via the XML server. You can use the following elements for this:

  • <keyvalue> with subelements <key> and <value>
  • <tag> with subelements <add> and <remove>
<Update>
    <Projektbearbeiter>
        <objref>4019</objref>
        <kuerzel>ABC</kuerzel>
        <keyvalue>
            <key>ExternalId</key>
            <value>123465</value>
        </keyvalue>
        <tag>
            <add>selected</add>
        </tag>
    </Projektbearbeiter>
</Update>

The query of existing key values or tags in the query element can be done as usual via OCL. There are no special elements here:

<Query>
    <Selection>
        <ocl>projektbearbeiter->select(hasTag('selected’))</ocl>
    </Selection>
    <ResultDef>
        <member>Kuerzel</member>
    </ResultDef>
</Query>

Setting a value to NULL

If you want to clear a value (set to NULL) that has already been set, you can assign an empty value to the attribute. In the example, the per diem is cleared for the user:

<Update>
    <Projektbearbeiter>
        <objref>300</objref>
        <tagespauschaleext/>
    </Projektbearbeiter>
</Update>

Setting Yes/No values (Boolean)

Yes/No values (Booleans) are set to 0 and 1:

  • 0: No
  • 1: Yes
<Update>
    <projekt>
        <objref>2880</objref>
        <aktiv>1</aktiv>
        <myboolean>0</myboolean>
    </Projekt>
</Update>

When querying Boolean values, it is somewhat confusing that there is a difference between built-in fields and custom fields. Build-in Boolean fields such as the active field for projects return 1 and 0, however, the Boolean custom fields return Y and N.

However, the setting is carried out identically in both cases, namely as described above with 0 and 1.

If you need a uniform XML response, instead of the simple <member> query, you can specify an expression that controls the return value:

<expression><alias>myboolean</alias><ocl>if additional fieldbool('myboolean’) then 1 else 0 endif</ocl></expression>

Set MLString field

For MLString fields, you need a “record separator” (RS) with the code 30 (Hex 1E) after the language text. Use the XML entity &#30; for this:

<Update>
    <zusatzfeldklasse>
        <objref>552293</objref>
        <bezeichnung>DE&#30;Geschäfts-/Projektidee&#30;EN&#30;Business Idea&#30;</bezeichnung>
    </zusatzfeldklasse>
</Update>

UpdateResponse element

Returned is a UpdateResponse element with the following subelement:

  • text: Indicates how many objects have been changed. 
    <Envelope>
  <Body>
    <UpdateResponse>
      <text>Updated 1 Objects</text>
    </UpdateResponse>
  </Body>
</Envelope>

Deleting objects

Delete element

To delete an object, a Delete element used. This contains a list of objects to be deleted:

  • objref: For each element to be deleted, the corresponding object reference (object ID) is specified. 
    <Delete>
    <objref>11764</objref>
    ...
</Delete>

DeleteResponse element

Returned is a DeleteResponse element with the following subelement:

  • text: Specifies how many objects have been deleted. 
    <Envelope>
  <Body>
    <DeleteResponse>
      <text>Deleted 1 Objects</text>
    </DeleteResponse>
  </Body>
</Envelope>

Authentication as of Vertec version 6.7

With version 6.6, API tokens have been introduced to increase the login security of Web API accesses. The API tokens increase the login security of the Vertec XML extension and the BI API extension.

The old system, i.e. authentication via user name and password, is no longer supported as of Vertec version 6.7. The XML server and the BI API now only allow authentication via API tokens. The endpoint /auth/xml has been removed and calls against this endpoint report an error.

Examples of using the extension

The following table describes example XML queries for creating and updating addresses.

Query XML response

Search for company with reference number:

<Query>
  <Selection>
    <ocl>Firma</ocl>
    <sqlwhere>reference like '123456789'</sqlwhere>
  </Selection>
</Query>

The answer is a list of objids that match the search criteria.

Answer with no result/empty list:

<?xml version=”1.0” encoding=”UTF-8” standalone=”yes”?>
<Envelope>
<Body>
<QueryResponse/>
</Body>
</Envelope>

Answer with multiple results:

<?xml version=”1.0” encoding=”UTF-8” standalone=”yes”?>
<Envelope>
<Body>
<QueryResponse>
<Firma>
<objid>8803185</objid>
</Firma> <Firma> <objid>8803206</objid>
</Firma>
</QueryResponse>
</Body>
</Envelope>

Create Firma (company):

<Create>
  <Firma>
    <name>Goldhaus AG</name>
    <zusatz>Standort Entenhausen</zusatz>
    <bemerkung></bemerkung>
    <referenz>123456789</referenz>
    <sprache>1</sprache>
    <betreuer><objref>4721</objref></betreuer>
  </Firma>
</Create>

Language:
0=DE
1=EN
2=FR
3=IT

Betreuer (consultant) is the internal ID of a user in Vertec. This must be determined in advance.

Returns the internal ID of the company generated:

<CreateResponse>
  <Firma>
    <objid>8803185</objid>
    <isValid>1</isValid>
  </Firma>
</CreateResponse>

Ask the newly created company for the Internal ID of the default address:

<Query>
  <Selection>
    <objref>8803185</objref>
    <ocl>defaultadresse</ocl>
  </Selection>
</Query>

The internal ID of the automatically generated address layout:

<QueryResponse>
  <AdressLayout>
    <objid>8803183</objid>
  </AdressLayout>
</QueryResponse>

Create the class Adresse with

<Create>
  <Adresse>
    <layouts><objref>8803183</objref></layouts>
    <owner><objref>8803185</objref></owner>
    <adresse>Talerhügel 1</adresse>
    <plz>9999</plz>
    <ort>Entenhausen</ort>
  </Adresse>
</Create>

Internal ID of the generated address:

<CreateResponse>
  <Adresse>
    <objid>8803189</objid>
  </Adresse>
</CreateResponse>

Creating a communication channel

  • type = internal ID of communication channel in Vertec (query once and save in the code).
  • entry = internal ID of company
<Create>
  <KommMittel>
    <typ><objref>4392</objref></typ>
    <eintrag><objref>8803185</objref></eintrag>
    <zieladresse>077 789 45 61</zieladresse>
    <bezeichnung></bezeichnung>
  </KommMittel>
</Create>

Returns the internal ID of the communication channel created:

<CreateResponse>
  <KommMittel>
    <objid>92503184</objid>
    <isValid>1</isValid>
  </KommMittel>
</CreateResponse>

Update address instead of create

<Update>
  <Firma>
    <objref>8803185</objref>
    <name>Goldhaus AG</name>
    <zusatz>Standort Entenhausen</zusatz>
    <bemerkung>ein Text in Bemerkung</bemerkung>
    <referenz>123456789</referenz>
    <sprache>1</sprache>
    <betreuer><objref>4721</objref></betreuer>
  </Firma>
</Update>
 
<UpdateResponse>
  <text>Updated 1 Objects</text>
</UpdateResponse>

 

Create contact

<Create>
  <Kontakt>
    <firma><objref>8803185</objref></firma>
    <name>Duck</name>
    <vorname>Donald</vorname>
    <gender>male</gender>
  </Kontakt>
</Create>

Returns the internal ID of the contact created:

<CreateResponse>
  <Kontakt>
    <objid>8803216</objid>
    <isValid>1</isValid>
  </Kontakt>
</CreateResponse>

Create a communication channel on the contact:

<Create>
  <KommMittel>
    <typ><objref>4395</objref></typ>
    <eintrag><objref>8803216</objref></eintrag>
    <zieladresse>donald.duck@geldspeicher.ent</zieladresse>
    <bezeichnung></bezeichnung>
  </KommMittel>
</Create>

Returns the internal ID of the communication channel created:

<CreateResponse>
  <KommMittel>
    <objid>8803219</objid>
  </KommMittel>
</CreateResponse>

Assign a project to a keyword folder.

<Update>  
  <Projekt>
    <objref>1170</objref>
    <ordner><objref>31426</objref></ordner>
  </Projekt>
</Update>

Assigns a keyword folder (here with ID 31426) to the project (here with ID 1170). You can find the ID in the Properties dialog  > Internal ID.

 
<UpdateResponse>
  <text>Updated 1 Objects</text>
</UpdateResponse>	 

Switching the XML server on/off (On-Premises)

In order for the XML server to be active, you must make the following setting in Vertec.ini:

[CloudServer]
XML Server = true
Bitte wählen Sie Ihren Standort