Custom Record Number generation with sequence counter reset option

 

Problem Statement:

1. System shall generate a unique record number in a Submittal Business Process with Concatenation of these data element <Project Number> <Company Code> <Submittal Package> <Submittal Type> <Sequence Counter>

2. based on the <Submittal Package> and <Submittal Type> combination, the <Sequence Counter > should increment from 1, most importantly the Sequence Counter should start from 1 if the combination does not exists in the record log else it should start increment and show next number.

For Example:

In the above example, Item #1, #2, and #3 are unique and first transactions w.r.t Submittal Package and Submittal Type, so the sequence counter starts with 0001. 

Whereas Item #4 and #5, the combinations are already exists in the log, so the sequence counter start increments from the previous number.

The solution in Unifier:

This requirement can be achieved by Sys Numeric Query Based DE and Advanced formula options.

Step1: Created the below Data Elements (DE) in the form and kept in a hidden block

a)     Submittal Number (SNQ)” Also enabled Trigger option

b)    SNQ +1 (Counter

c)     Constant 1 – Assigned Constant Value as 0001 in Advance Formula

Step 2: Applied Advanced Formula for this field as below

SNQ +1 (Counter) = Submittal Number (SNQ) +1

 

 

 

 

Step 3: Define Query condition for Submittal Number (SNQ) DE in the Business Configuration Setup window as below