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:
- 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
// 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> und 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 "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.