Advanced Techniques - 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:


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:
// Import required classes
import java.util.Calendar
import java.text.SimpleDateFormat

// Initiliaze connection to database
def conn = g_dbConnections.systemConnection

// Read transaction date
// Enter the GUID of the "Transaction date" data field
def dtTransactionDate = g_record["0158FC7B4355CA2D10A1621A9A7CAA8F53ECB2AC"].value

// Identify user's timezone
def tz = g_session.user.timeZone

// Determine year from transaction date
// Four-digit year
def strYear = String.format('%tY', dtTransactionDate)
// Two-digit year
def strYearShort = String.format('%ty', dtTransactionDate)

// Create start date for counter
// (01.01.Transaction year)
def calStart = Calendar.getInstance(tz)
calStart.setTime(dtTransactionDate)
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)

// Create end date for counter
//(31.12.Transaction year)
def calEnd = Calendar.getInstance(tz)
calEnd.setTime(dtTransactionDate)
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)

// Determine latest counter in event year and 
// add +1 to create the new transaction number
// Enter the GUID of the "Transaction" data group here
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)
}

// Formatting for transaction number
def strTransactionNumber = strYearShort + "-" + intCounter.toString().padLeft(4, "0")

// Put results into processing context
g_sharedState.put("transactionnumber", strTransactionNumber)
g_sharedState.put("transactioncounter", 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 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: 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.
// Enter the GUID of the "Transaction" data group here
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.