Tips & Tricks - Year-based numbering

This workshop demonstrates how a transaction number can be generated from a transaction date and transaction counter. The transaction number starts each year at 1, is increased automatically and also uses the year in the number. Background knowledge of Groovy and good Intrexx skills are an advantage here. The example application for this workshop can be downloaded here and imported into your portal as usual. Activate the included process afterwards so that you can test the example.

The edit page contains three edit fields with the corresponding data fields:

  • Transaction date (control type: date)

  • Transaction counter (control type: integer)

  • Transaction number (control type: string)

To generate the transaction number, a process needs to respond when a record is added to the data group.

The following script is performed in the subsequent Groovy action:

// Importieren notwendiger Klassen
import java.util.Calendar
import java.text.SimpleDateFormat

// Datenverbindung zur Datenbank initialisieren
def conn = g_dbConnections.systemConnection

// Datum des Vorgangs auslesen
// Hier GUID des Datenfelds "Vorgangsdatum" eintragen
def dtVorgangsdatum = g_record["0158FC7B4355CA2D10A1621A9A7CAA8F53ECB2AC"].value

// Zeitzone des angemeldeten Benutzers bestimmen
def tz = g_session.user.timeZone

// Jahreszahlen aus Vorgangsdatum ermitteln
// Vierstellige Jahreszahl
def strYear = String.format('%tY', dtVorgangsdatum)
// Zweistellige Jahreszahl
def strYearShort = String.format('%ty', dtVorgangsdatum)

// Startdatum für Zählerstandsermittlung erstellen
// (01.01.Vorgangsjahr)
def calStart = Calendar.getInstance(tz)
calStart.setTime(dtVorgangsdatum)
calStart.set(Calendar.MONTH, Calendar.JANUARY)
calStart.set(Calendar.DATE, 1)
calStart.set(Calendar.HOUR, 0)
calStart.set(Calendar.MINUTE, 0)
calStart.set(Calendar.SECOND, 0)
calStart.set(Calendar.MILLISECOND, 0)

// Enddatum für Zählerstandsermittlung erstellen
//(31.12.Vorgangsjahr)
def calEnd = Calendar.getInstance(tz)
calEnd.setTime(dtVorgangsdatum)
calEnd.set(Calendar.MONTH, Calendar.JANUARY)
calEnd.set(Calendar.YEAR, strYear.toInteger() + 1)
calEnd.set(Calendar.DATE, 1)
calEnd.set(Calendar.HOUR, 0)
calEnd.set(Calendar.MINUTE, 0)
calEnd.set(Calendar.SECOND, 0)
calEnd.set(Calendar.MILLISECOND, 0)

// Letzten Zählerstand im Vorgangsjahr ermitteln und
// mit +1 die neue Vorgangsnummer erzeugen
// Hier GUID der Datengruppe "Vorgang" eintragen
def intCounter = g_dbQuery.executeAndGetScalarIntValue(conn, "SELECT MAX(L_VORGANGSZAEHLER)+1 FROM DATAGROUP('27B3340528694E79C45E3A7F693F287DE0ABC758') WHERE DT_VORGANGSDATUM >= ? AND DT_VORGANGSDATUM < ?", 1){
	setTimestamp(1, calStart.time)
	setTimestamp(2, calEnd.time)
}

// Formatierung der Vorgangsnummer
def strVorgangsnummer = strYearShort + "-" + intCounter.toString().padLeft(4, "0")

// Ergebnisse in Verarbeitungskontext legen
g_sharedState.put("vorgangsnummer", strVorgangsnummer)
g_sharedState.put("vorgangszaehler", intCounter)

The script identifies the highest counter number of the transaction year and uses this to determine the number for the new record. To do this, the year from the date entered is read and two comparison date values are generated for the year. Two calendar objects with the date 01.01.<transaction year> and 01.01.<transaction year +1>are defined here. The second date value corresponds to 31.12.; due to the comparison in the SQL statement, 01.01 with a time value of 00:00 and the operator "<" needs to be entered to take the last minute of the last year into account. The results of the calculation are written to the processing context as variables. If you are using the script in other applications, the following values may need to be modified:

  • GUID of the date field "Transaction date" (0158FC7B4355CA2D10A1621A9A7CAA8F53ECB2AC in the example)

  • GUID of the data group "Transaction" (27B3340528694E79C45E3A7F693F287DE0ABC758 in the example)

Make sure to use the correct data field names in the database query.

If the counter should start at a different value, the fallback value can be set to a different value during the identification of the new counter number. Here is an example of a counter that starts at 1000.

// Hier GUID der Datengruppe "Vorgang" eintragen
def intCounter = g_dbQuery.executeAndGetScalarIntValue(conn, "SELECT MAX(L_VORGANGSZAEHLER)+1 FROM DATAGROUP('27B3340528694E79C45E3A7F693F287DE0ABC758') WHERE DT_VORGANGSDATUM >= ? AND DT_VORGANGSDATUM < ?", 1000){

The data record is modified in the subsequent data group action.

On the Field assignment tab, a user-defined system value with the type "Processing context" is created for each of the transaction number and transaction counter and assigned to the corresponding data fields.