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: 

// Import required classes
import java.util.Calendar
import java.text.SimpleDateFormat

// Initialize data connection to data base
def conn = g_dbConnections.systemConnection

// Get date of process
// Enter the GUID of the data field "Operationdate" here
def dtOperationdate = g_record["0158FC7B4355CA2D10A1621A9A7CAA8F53ECB2AC"].value

// Determine time zone of the logged in user
def tz = g_session.user.timeZone

// Determine year numbers from operation date
// Four digit year
def strYear = String.format('%tY', dtOperationdate)
// Two digit year
def strYearShort = String.format('%ty', dtOperationdate)

// Create start date for meter reading determination
// (01.01. start of operation year)
def calStart = Calendar.getInstance(tz)
calStart.setTime(dtOperationdate)
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 meter reading determination
//(31.12.end of operation year)
def calEnd = Calendar.getInstance(tz)
calEnd.setTime(dtOperationdate)
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 the last counter reading in the transaction year and
// create the new transaction number with +1
// Enter the GUID of the data group "transaction" here
def intCounter = g_dbQuery.executeAndGetScalarIntValue(conn, "SELECT MAX(L_OPERATIONCOUNTER)+1 FROM DATAGROUP('27B3340528694E79C45E3A7F693F287DE0ABC758') WHERE DT_OPERATIONDATE >= ? AND DT_OPERATIONDATE < ?", 1){
	setTimestamp(1, calStart.time)
	setTimestamp(2, calEnd.time)
}

// Formatting the operation number
def strVorgangsnummer = strYearShort + "-" + intCounter.toString().padLeft(4, "0")

// Put results into processing context
g_sharedState.put("Operationnumber", strOperationnumber)
g_sharedState.put("Operationcounter", 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. 

// Enter the GUID of the "Process" data group here.
def intCounter = g_dbQuery.executeAndGetScalarIntValue(conn, "SELECT MAX(L_OPERATIONCOUNTER)+1 FROM DATAGROUP('27B3340528694E79C45E3A7F693F287DE0ABC758') WHERE DT_OPERATIONDATE>= ? AND DT_OPERATIONDATE < ?", 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.