Tips & Tricks - Year-related 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. You can download the sample application with process here and import it 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 are defined with the date 01.01.<year of operation> and 01.01.<year of operation +1>. The second date value stands for 31.12.; however, the comparison in the SQL statement means that 01.01. must be used. with a time value of 00:00 and the operator "<" to take into account the last minute of the old year. The results of the calculation are written to the processing context as variables. Replace if necessary. the following values if you want to use the Groovy script in other applications:

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

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

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

If the count should always start at a different value, the fallback value can be set to a different value when determining 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.

For this purpose, a user-defined system value with the type "Processing context" is created in the field assignment for the operation number and the operation counter and assigned to the corresponding data fields.