OCL Expressions

OCL expression for all entries
OCL expressions for addresses
OCL expressions for users

OCL expressions for projects / phases

• Budget values on projects
• Budget values on project phases
• Values on phase allocations

OCL Expressions for Invoice Reports

• Access to posting line of the invoice and evaluate postings
• Downpayments on invoices

OCL Expressions for Invoice Reports

• Conditional expression
• Groupings

OCL expressions for downpayments

OCL expressions with date operators

• Calendar week of a date
• Examples: calculating a start date
• Formatting date and time values

• Up-to-date logged-in user: Timsession.allInstances->first.login. Returns the up-to-date logged-in user.
• Access unique object ID: objid (until Vertec 6.2.0.6 boldid). Returns the unique object ID number. This number is assigned consecutively for newly created objects and can therefore also be useful for sorting by creation order.
• Creator of an entry: creator
• Modifier of an entry: modifier
• Record creation date: creationDateTime
• Modified Date: modifiedDateTime

• All addresses: adresseintrag.
• All persons: person.
• All companies: firma.
• All contacts: kontakt.
• All couples: paar.
• All simple addresses: einfacheAdresse.
• Customers (addresses that have one or more projects): adresseintrag->select(projekte->size>0)
• Addresses that have the entry CEO in the “position” field: adresseintrag->select(stellung.asstring='CEO')
• Addresses that do not have an entry in the “position” field: adresseintrag->select(stellung.asstring='')
• Addresses that have an entry in the “position” field: adresseintrag->select(stellung.asstring<>'')
• Addresses that have the entry ZH in the “Canton/State” field: adresseintrag->select(standardkanton='ZH')
• People who do not have a contact link to a company: person->select(kontakte->size=0)
• Addresses that contain “wald” in the name or whose names begin with “Treu”:
  adresseintrag->select(name.sqlLike('%wald%') or name.sqlLike('Treu%'))

  The variant sqlLikeCaseInsensitive does the same comparison, but is not case sensitive; “and,” “or” and “not” are allowed (note brackets, always case sensitive).

• First names of persons: In lists different types of addresses can be shown at the same time (persons, companies, contacts, couples). If you want to show the first name of the person in such a list, you have to make a distinction:

  if self.oclistypeof(Person) then self.oclastype(Person).vorname else if self.oclistypeof(Kontakt) then 
  if self.oclastype(Kontakt).person->notempty then self.oclastype(Kontakt).person.vorname 
  else self.oclastype(Kontakt).vorname endif else '' endif endif

• Names of persons: In the list different types of addresses can be shown at the same time (persons, companies, contacts). If you want to show the name of the person in such a list, you have to make a different case:

  if self.oclistypeof(Person) then self.oclastype(Person).name else if self.oclistypeof(Kontakt) then 
  if self.oclastype(Kontakt).person->notempty then self.oclastype(Kontakt).person.name 
  else self.oclastype(Kontakt).name endif else '' endif endif

• Names of all address entries (companies, contacts, persons, couples):

  if self.oclistypeof(Paar) then if self.oclastype(Paar).personA->notempty and self.oclastype(Paar).personB->notempty then if self.oclastype(Paar).personA.name.asstring = self.oclastype(Paar).personB.name.asstring then self.oclastype(Paar).personA.name else self.oclastype(Paar).personA.name + ' & ' + self.oclastype(Paar).personB.name endif else if self.oclastype(Paar).personA->notempty then if self.oclastype(Paar).personA.name.asstring = self.oclastype(Paar).nameb.asstring then self.oclastype(Paar).personA.name else self.oclastype(Paar).personA.name + ' & ' + self.oclastype(Paar).nameb endif else if self.oclastype(Paar).personB->notempty then if self.oclastype(Paar).namea.asstring = self.oclastype(Paar).personB.name.asstring then self.oclastype(Paar).namea else self.oclastype(Paar).namea + ' & ' + self.oclastype(Paar).personB.name endif else if self.oclastype(Paar).namea.asstring = self.oclastype(Paar).nameb.asstring then self.oclastype(Paar).namea else self.oclastype(Paar).namea + ' & ' + self.oclastype(Paar).nameb endif endif endif endif else if self.oclistypeof(Kontakt) then if self.oclastype(Kontakt).person->notempty then self.oclastype(Kontakt).person.name else self.oclastype(Kontakt).name endif else name endif endif

• First names of couples: Both first names are shown, separated by '&':

  if self.oclastype(Paar).personA->notempty and self.oclastype(Paar).personB->notempty then self.oclastype(Paar).personA.vorname + ' & ' + self.oclastype(Paar).personB.vorname else if self.oclastype(Paar).personA->notempty then self.oclastype(Paar).personA.vorname + ' & ' + self.oclastype(Paar).vornameb else if self.oclastype(Paar).personB->notempty then self.oclastype(Paar).vornameA + ' & ' + self.oclastype(Paar).personB.vorname else self.oclastype(Paar).vornameA + ' & ' + self.oclastype(Paar).vornameb endif endif endif

• Last names of couples: Both names are shown, separated by '&':

• if self.oclastype(Paar).personA->notempty and self.oclastype(Paar).personB->notempty then self.oclastype(Paar).personA.name + ' & ' + self.oclastype(Paar).personB.name else if self.oclastype(Paar).personA->notempty then self.oclastype(Paar).personA.name + ' & ' + self.oclastype(Paar).nameb else if self.oclastype(Paar).personB->notempty then self.oclastype(Paar).nameA + ' & ' + self.oclastype(Paar).personB.name else self.oclastype(Paar).nameA + ' & ' + self.oclastype(Paar).nameb endif endif endif

• Birthdays of (active) people in the next 30 days:

  person->select(aktiv)->select(geburtsdatum.asstring<>'')->select(((date<=encodedate(date.year,geburtsdatum.month,geburtsdatum.day))
  and(date>=encodedate(date.year,geburtsdatum.month,geburtsdatum.day).incday(-30)))or((date<=encodedate(date.year+1,geburtsdatum.month,geburtsdatum.day))
  and (date>=encodedate(date.year+1, geburtsdatum.month,geburtsdatum.day).incday(-30))))

• Person’s gender: The expression works on any address list and returns an empty string in the case of a company or couple.

  if ocliskindof(Kontakt) then self->oclastype(Kontakt)->asset->collect(x| if x.person->size>0 then
  if x.person.ismale then 'Male' else 'Female' endif else if x.ismale then 'Male' else 'Female' endif  endif)->first
  else if ocliskindof(Person) then if oclastype(Person).ismale then 'Male' else 'Female' endif else '' endif endif

• All users: projektbearbeiter
• All active users: projektbearbeiter->select(aktiv)
• Registered (currently logged in) user: Timsession.allInstances->first.login

• Project leaders of one or more projects are: projektbearbeiter->select(eigprojekte->size>0)
• the user level “Secretary” are assigned: projektbearbeiter->select(stufe.asstring='Secretary')
• have no entry in the field “Shortcuts”: projektbearbeiter->select(kuerzel.asstring='')
• have an entry in the “Abbreviations” field: projektbearbeiter->select(kuerzel.asstring<>'')
• All services of a user during a year:

  projektbearbeiter->groupleistungenb('01.01.2011', '31.12.2011','')
  ->collect(minutenintoffen + minutenintverrechnet)->sum

• Percentage of user’s chargeable services during a year:

  self->groupleistungenB('1.1.2011', '31.12.2011', '')
  ->collect(s | (s.minutenextOffen-s.minutenextOffenUnprod+s.minutenextVerrechnet-s.minutenextVerrechnetUnprod)
  / (s.minutenIntVerrechnet+s.minutenIntOffen) *100)->sum

• All projects: projekt or Projekt.allInstances (case sensitive)
• All project phases: projektphase or Projektphase.allInstances (case sensitive)

• All phases and sub-phases of a project: From Vertec 6.3. <projekt>.allePhasen
• Project of a phase or sub-phase: From Vertec 6.3. <projektphase>.owningProjekt

• have no project phases: projekt->select(phasen->size=0)
• have at least one project phase: projekt->select(phasen->size>0)
• are assigned to a productive project type: projekt->select(typ.produktiv)
• in French: projekt->select(sprache.asstring='FR')
• are active: projekt->select(aktiv)
• are inactive: projekt->select(not aktiv)
• List projects with duplicate codes: projekt->select(code->asset->collect(x|Projekt.allinstances->select(code=x)->size)->sum>1)
  As a result, all projects with duplicate codes are listed.

• Flat rates are: projektphase->select(pauschal)
• not chargeable: projektphase->select(not verrechenbar)
• The parent phase of a project phase: parentphase
• The first level of the sub-phases of a project phase: subphasen
• All parent phases of a project phase: parentlist
  Order is first the top parent phase, last the immediate parent phase. With parentlist->first you can access the top level in the tree.
• All subphases of a project phase: sublist
  Goes through the entire tree of subphases and includes the subphases of the subphases.

• Date offered: offertDatum
• Date accepted: erteiltDatum
• Date completed: abschlussDatum
• Date rejected: abgelehntDatum

The individual status can be queried by a number (e.g. phasen->select(status=1)). The numbers of the individual status are as follows:

0: On offer

1: Accepted

2: Completed

3: Rejected

• Planned hours: planMinutenInt
• Hours effective: sumMinutenInt
• Fee planned: planWertExt
• Fee effective: sumWertExt
• Internally planned fee: planWertInt
• Internal fee effective: sumLeistungWertInt
• Cost planned: planKostenLeistung
• Cost effective: sumLeistungWertKosten

• Amount planned: planSpesenWert
• Effective amount: sumSpesenWert
• Cost planned: planKostenSpesen
• Cost effective: sumKostenSpesen

• Amount planned: planAuslagenWert
• Effective amount: sumAuslagenWert
• Cost planned: planKostenAuslagen
• Cost effective: sumKostenAuslagen

• Planned hours: planMinutenInt
• Hours effective: sumMinutenInt
• Fee planned: planWertExt
• Fee effective: sumWertExt
• Internally planned fee: planWertInt
• Internal fee effective: sumWertInt
• Cost planned: planKostenLeistung
• Cost effective: sumKostenLeistung

• Amount planned: planSpesenWert
• Effective amount: sumSpesenWert
• Cost planned: planKostenSpesen
• Cost effective: sumKostenSpesen

• Amount planned: planAuslagenWert
• Effective amount: sumAuslagenWert
• Cost planned: planKostenAuslagen
• Cost effective: sumKostenAuslagen

In addition to the budget values listed above, phase assignments have the following additional values:

• Call transaction line: buchungsbeleg->oclAsType(Beleg).buchungen

From here you can now access the different values such as the

• Net amounts posted on an invoice: buchungsbeleg->oclastype(Beleg).buchungen->select(isthaben).betrag->sum

• Which downpayments are deducted with which amounts on the invoice:

  rechnungvorschusslink->select(betragnetto<>0)->collect
  (vorschuesse.bezeichnung + ': ' + betragnetto.asstring)->listtostring(' / ')

  Displays the designation of the downpayments and the respective amount, e.g. in a list column.

• Downpayments charged to the invoice: vorschuesseAufRechnung
• Downpayments deducted from the invoice: vorschuesse

To learn how to build Word reports, see Word reports. To learn what each field on the invoice means, see The invoice in OCL.

• Invoice address: rechnungsadresstext
• Invoice Date: datum
• Invoice number: nummer
• Billing period from: von.asstring
• Billing period until: bis.asstring
• Salutation: rechnungsbriefanrede
• Complimentary formula: rechnungsgrussformel
• Project: projekt
• Project subject: projekt.betreffend
• Is lump sum invoice: pauschal
• Fixed amount: pauschalbetrag
• Total services: leistwertext
• Discount Amount: rabattBetrag
• DiscountPercent: rabattProzent
• Total discount (whether in % or fixed): totalRabattBetrag
• VATPercentServices: leistmwstsatz
• VATServices: leistmwst
• Total services incl. VAT: leistwertextmitmwst
• Total expenses fixed-price: pauschalspesenbetrageff
• Fee Amount: pauschalspesenbetrag
• Percentage fee: pauschalspesenprozent
• Total expenses (lump sum + entries): spesenext
• Total expenses (excluding lump sum): spesenext - pauschalspesenbetrageff
• VAT expenses: spesenmwst
• VATPercent expenses: spesenmwstsatz
• Total expenses incl. VAT: spesenextmitmwst
• Outlays: auslagenext
• VAT outlays: auslagenmwst
• VATPercentage outlays: spesenmwstsatz
• Total outlays including VAT: auslagenextmitmwst
• Downpayments charged: vorschussbetrag
• VAT downpayments: vorschussmwst
• Downpayments with VAT: vorschussbetrag + vorschussmwst
• VAT percentage on advances: leistmwstsatz
• Total services, expenses, outlays and downpayments, including VAT: leistspesenextmitmwst + vorschussbetrag + vorschussmwst
• Invoice Total: total
• Invoice currency: waehrung
• Project leader: projekt.projektleiter
• Total amount without penny: total.floor.asstring
• Sorting by ZIP code of an invoice report running on a list of invoices (expression of the master tape):

  if oclisKindOf(Container) then oclAsType(Container).eintraege
  ->list->oclAsType(Rechnung)->ordermulti('if xRechnungsadresse
  ->isEmpty then projekt.kunde.oclastype(Adresseintrag) else 
  xRechnungsadresse.oclastype(Adresseintrag) endif.standardplz') 
  else self->asset endif

Expressions that query a condition, i.e. return YES or NO. Usually band expressions for 'cond’ bands (see Word reports).

• Has billing period: von.asstring <> '' or bis.asstring <> ''
• Has services (and is not a lump sum): (not pauschal) and (leistwertext <> 0)
• Has discount: rabatt
• Discount is as amount: rabattBetrag > 0
• Discount is in percent: rabattProzent > 0
• Has VAT on services: leistmwst > 0
• Has fixed-price expenses: pauschalspesen
• Expenses fixed-price as amount: pauschalspesenbetrag > 0
• Expenses fixed-price in percent: pauschalspesenprozent > 0
• Expense entries?: usespesen
• Has expenses: spesenext > 0
• It has expenses and they should be used: (spesenext > 0) and usespesen
• Has VAT onexpenses: spesenmwst > 0
• Has outlays: auslagenext > 0
• Has VAT on outlays: auslagenmwst > 0
• Has expenses or outlays: ((spesenext > 0) and usespesen) or (auslagenext > 0)
• Has downpayments to be charged: vorschussbetrag > 0
• Has made downpayments: vorschusseffektiv > 0
• Downpayments are incl. VAT: vorschusseffektivmwst > 0
• VAT Amount of downpayments: vorschusseffektivmwst

• Services by rate:

  leistungen->orderby(boldid)->select(x|x.boldid=self.leistungen
  ->orderby(boldid)->select(ansatzext=x.ansatzext)->first.boldid)

  Technically speaking, the expression makes a list of services, where each one has a different rate.

  Within such a band you can then filter the services, e.g. with:

  leistungen->select(bndAnsatz.ansatz=ansatz).wertext->sum

• Services by service type AND rate:

  leistungen->orderby(boldid)->select(x|x.boldid=self.leistungen
  ->orderby(boldid)->select((ansatzext=x.ansatzext) and (typ=x.typ))
  ->first.boldid)

  Filter with:

  leistungen->select(bndTaetigkeit.typ=typ)
  ->select(bndTaetigkeit.ansatz=ansatz).wertext->sum

• On which invoices how much of the downpayment is deducted:

  rechnungvorschusslink->select(betragnetto<>0)->collect
  (rechnungen.nummer.asstring + ': ' + betrag.asstring)->listtostring(' / ')

  Displays the invoice number and the respective amount, for example in a list column.

For information on the OCL date operators, click here.

• Today’s date (incl. time): now
• Today’s date (without time part): date

date->asset->collect(x|(x.mondayofweek.incday(3).dateToFloat - encodedate(x.mondayofweek.incday(3).year,1,1).dateToFloat + 7).floor div 7)->first

Examples: Calculating a start date

OCL expressions that calculate the start date (date of Monday of this week) for any calendar week (of any year):

• For the up-to-date year:

  date.firstOfYear.incDay(3).mondayOfWeek.incDay(7*(kw-1))

• For the 12th calendar week:

  date.firstOfYear.incDay(3).mondayOfWeek.incDay(7*(12-1))

• For any year:

  encodeDate(jahr, 1, 4).mondayOfWeek.incDay(7*(kw-1))

• For the 12th calendar week of the year 2026:

  encodeDate(2026, 1, 4).mondayOfWeek.incDay(7*(12-1))

formatdatetime: To format date and time values, the operator formatdatetime can be used. For example, this query represents the current time, in the format hours.minutes: now.formatdatetime('hh.mm'). For more information about formatdatetime, see OCL.

encodedate: For date queries, the operator encodeDate is used. Example: folder containing all invoices created in 2018: rechnung->select(datum >= encodeDate(2018,01,01)). For more information about encodeDate, see OCL.

• Unpaid invoices older than 30 days:

    rechnung->select(not bezahlt)->select(faelligdatum<=date.incday(-30))

• Who (personal addresses) has a birthday in the next thirty days:

  person->select(aktiv)->select(geburtsdatum.asstring<>'')->select( ((date<=encodedate(date.year,geburtsdatum.month,geburtsdatum.day)) 
  and(date>=encodedate(date.year,geburtsdatum.month,geburtsdatum.day) .incday(-30)))or((date<=encodedate(date.year+1,geburtsdatum.month, geburtsdatum.day))
  and (date>=encodedate(date.year+1, geburtsdatum.month,geburtsdatum.day).incday(-30))))

• Addresses entered in the last 360 days (e.g. for Christmas card check):

  adresseintrag->select(creationDateTime >= date.incDay(-360))