When creating a workflow with forms in SAP Build Process Automation, you may want to assign a unique number to the form. The number is assigned at the time of application, and the form is then processed using that number as a key.
SAP Build Process Automation issues a GUID value called "Instance ID" when a workflow instance is started, and internally manages the information in the workflow using that number, which is "dfa3c3b6-2bd2-8845-7ac 8-84c98e66000b". It is certainly tough to manage an application with this number from a human point of view. It is understandable that you would want to use a number that is easy for humans to understand, such as AAAA00001, for example.
Unfortunately, the functionality to issue this unique format number does not exist in the current SAP Build Process Automation. Therefore, it is necessary to develop a numbering system as an external function and link it to the flow in Action or Automation.
Note: Here is an explanation of a pattern that is just not possible with the current SAP Build Process Automation.
Currently SAP Build Process Automation does not allow the development of workflows that are triggered by a form and that already show a number that has been assigned when the triggering form is displayed. To use a real-world example, you cannot create "a paper form that has a unique number printed on it from the beginning". You can develop a type of flow in which the number portion is blank when the form is filled out, and the number is scored when the form is submitted.
If you want to create a type of form where the number is displayed from the beginning when the trigger form is displayed, you need to create the trigger form using SAP Build Apps, design the workflow as an API trigger, and call the API from the form. |
Numbering format for automatic numbering to be created this time
automatically numbered in the following format
<prefix>-<year number 4 digits>-<zero-filled 8-digit number>
Example: AAA-2023-00000001
The prefix part is specified when performing the numbering. The year is the year at the time of numbering. The zero-filled 8-digit number part starts from 1 and counts up when there is a request to obtain a number with the same prefix & year combination.
Requirements
- Database for managing the numbers.
- This time I will use HANA Cloud , create a table in HDI with CAP.
- REST API that works with the above database to perform numbering and return new numbers
- I will deploy and run an application written in Node.js in the Cloud Foundry environment.
*The BTP account to which these will be run and deployed can be a BTP Free Tier or Trial account for verification and testing purposes. It can also be a different environment from the BTP account where SAP Build Process Automation is subscride.
Now let us begin to explain the development method.
Step1. Create database
In this case, I will create the following table.
// schema.cds
namespace autonum;
using { managed } from '@sap/cds/common';
entity Datatable: managed {
key prefix : String(40);
key year : String(4);
key number : Int64; //countup number
instanceid : String(40); // Instance ID of workflow
workflowinfo : String(255); //Something to store information. Workflow name, etc. for easy extraction later. (Not used this time)
workflowuser : String(255); //ID of the user who obtained the number
generatedate : Timestamp;
}
//number-service.cds
using autonum as autonum from '../db/schema';
service NumberTable {
entity Autonum as projection on autonum.Datatable;
}
//I have made it available as OData, but this is not required this time. (I may use it in a future blog.)
How to create tables in HANA Cloud with CAP ? Please refer at
https://blogs.sap.com/2020/09/08/use-cap-to-expose-hana-cloud-tables-as-odata-services/
this blog etc.
Step2. Create Rest API
Build a REST API that connects to the above HDI environment and tables and automatically assigns numbers.
It will be written in Node.js and run on the Could Foundry Runtime.
var express = require("express");
var router = express();
var vcap_services = JSON.parse(process.env.VCAP_SERVICES);
console.log(vcap_services);
// load HANA Client
var hana = require("@sap/hana-client");
const { response } = require("express");
var conn = hana.createConnection();
// HANA Connection Settings
var conn_params = {
serverNode:
vcap_services.hana[0].credentials.host +
":" +
vcap_services.hana[0].credentials.port,
encrypt: true,
schema: vcap_services.hana[0].credentials.schema,
sslValidateCertificate: false,
uid: vcap_services.hana[0].credentials.user,
pwd: vcap_services.hana[0].credentials.password,
pooling: true,
maxPoolSize: 50,
};
router.use(express.json());
// REST API : /getnextvalue
// Method : POST
// INPUT : prefix / String
// instanceid / String
// workflowuser / String
// RETURN : JSON
// RETURN EXAMPLE:
//{
// "newnumber": "1234556-2023-00000004"
//}
router.post("/getnextvalue", (req, res) => {
var newvalue = 0;
// Connect to HANA
conn.connect(conn_params, function (err) {
if (err) {
if (err.code != -20004) {
console.log("DB Error: DB Connection --- ", err);
var msg = [{ msg: "DB Error: DB Connection" }];
res.json({ searchResult: msg });
return;
}
}
//set schema
var sql0 = "SET SCHEMA " + vcap_services.hana[0].credentials.schema;
var stmt0 = conn.prepare(sql0);
try {
stmt0.exec();
} catch (err) {
console.log("set schema error.");
console.log("SQL=", sql0);
console.log("DB Error: SQL Execution --- ", err);
}
stmt0.drop();
// get current year
var currentDate = new Date();
var currentYear = currentDate.getFullYear();
// prefix and year are exists in the data ?
var sql1 =
"SELECT COUNT(*) AS COUNT FROM AUTONUM_DATATABLE WHERE PREFIX = ? AND YEAR = ?";
var stmt1 = conn.prepare(sql1);
try {
result = stmt1.exec([req.body.prefix, String(currentYear)]);
} catch (err) {
console.log("Get prefix count error.");
console.log("SQL=", sql1);
console.log("DB Error: SQL Execution --- ", err);
}
stmt1.drop();
if (Number(result[0].count) == 0) {
// New prefix and year
// Insert first value for this prefix and year.
var sql2 =
'INSERT INTO AUTONUM_DATATABLE(PREFIX, YEAR, "NUMBER", INSTANCEID ,WORKFLOWUSER ,GENERATEDATE) VALUES(?, ?, 1, ?, ?, CURRENT_TIMESTAMP);';
var stmt2 = conn.prepare(sql2);
try {
stmt2.exec([
req.body.prefix,
String(currentYear),
req.body.instanceid,
req.body.workflowuser,
]);
} catch (err) {
console.log("Insert new prefix error.");
console.log("SQL=", sql2);
console.log("DB Error: SQL Execution --- ", err);
}
stmt2.drop();
newvalue = 1;
} else {
// Exists
// get current max value
var sql3 =
'SELECT MAX("NUMBER") AS MAXNUM FROM AUTONUM_DATATABLE WHERE PREFIX = ? AND YEAR = ?';
var stmt3 = conn.prepare(sql3);
try {
result3 = stmt3.exec([req.body.prefix, String(currentYear)]);
} catch (err) {
console.log("Get prefix max error.");
console.log("SQL=", sql3);
console.log("DB Error: SQL Execution --- ", err);
}
stmt3.drop();
// MAXNUM+1
newvalue = Number(result3[0].MAXNUM) + 1;
// insert new value
var sql4 =
'INSERT INTO AUTONUM_DATATABLE(PREFIX, YEAR, "NUMBER", INSTANCEID ,WORKFLOWUSER ,GENERATEDATE) VALUES(?, ?, ?, ?, ?, CURRENT_TIMESTAMP);';
var stmt4 = conn.prepare(sql4);
try {
stmt4.exec([
req.body.prefix,
String(currentYear),
newvalue,
req.body.instanceid,
req.body.workflowuser,
]);
} catch (err) {
console.log("Insert new number error.");
console.log("SQL=", sql4);
console.log("DB Error: SQL Execution --- ", err);
}
stmt4.drop();
}
//Return new number as json
var newnumber = {};
newnumber.newnumber =
req.body.prefix +
"-" +
String(currentYear) +
"-" +
(Array(8).join("0") + newvalue).slice(-8);
res.json(newnumber);
console.log("Return: " + String(newnumber.newnumber));
});
});
router.listen(process.env.PORT || 4000);
As described in the source code, this API is called with /getnextvalue and requires three arguments in POST format: prefix, instanceid, and workflowuser.
If you have instanceid and workflowuser, you can link them to the actual workflow later.
Deploy to the Cloud Foundry environment and bind the HDI instance created in step 1. Now your Node.js app will connect to the database and work.
Step3. Create OpenAPI specification document(json)
This RestAPI is called as an Action on SAP Build Process Automation, and an Open API specification is required to call it as an Action.
An OpenAPI specification was created from the Rest API specification. It looks like the following.
{
"openapi": "3.0.3",
"info": {
"title": "Get Number API Sample",
"version": "1.0.0",
"description": "Sample API for POST method to get the next value"
},
"paths": {
"/getnextvalue": {
"post": {
"summary": "Get the next value",
"operationId": "getNextValue",
"requestBody": {
"description": "Input",
"required": true,
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/InputSample"
}
}
}
},
"responses": {
"200": {
"description": "Successful response",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/ReturnSample"
}
}
}
},
"400": {
"description": "Bad Request",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/ErrorResponse"
}
}
}
},
"500": {
"description": "Internal Server Error",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/ErrorResponse"
}
}
}
}
}
}
}
},
"components": {
"schemas": {
"InputSample": {
"type": "object",
"properties": {
"prefix": {
"type": "string"
},
"instanceid": {
"type": "string"
},
"workflowuser": {
"type": "string"
}
},
"required": ["prefix", "instanceid", "workflowuser"],
"example": {
"prefix": "ABC",
"instanceid": "dedewd-dede-dede-dede",
"workflowuser": "test@test.com"
}
},
"ReturnSample": {
"type": "object",
"properties": {
"newnumber": {
"type": "string"
}
},
"required": ["newnumber"],
"example": {
"newnumber": "ABC-2023-00000001"
}
},
"ErrorResponse": {
"type": "object",
"properties": {
"error": {
"type": "string"
}
},
"required": ["error"]
}
}
}
}
*As a side note, you can also call this API from Automation. In that case, you do not need to write an OpenAPI specification. ( However, you will need a Desktop Agent.)
In the next , I will explain how to create an Action using Step.3’s openapi json and incorporate it into the workflow process.
