
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. |
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.
*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.
// 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.
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.
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.)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
27 | |
23 | |
17 | |
12 | |
8 | |
8 | |
7 | |
7 | |
6 | |
6 |