Sequence number for a field

[hbock]

Hello Community,

for a apecial "storage" item, there is the requirement, that a unique sequence number shall be generated. The number shall start on the 1st of January with 1 and then shall be incremented by 1 for every item created during the year.

Examples: 16-0246, 17-0001, 17-3564 (formatting is not the problem)

What would be the preferred method to implement this sequence number (for native databases there is the feature "autoincrement" but this is not available for Integrity fields).

My ideas so far:

• count over existing items (of the current year, but cannot imagine a query rule other than setting date interval manually every year) by query/aggregation, incement result by 1 and store in global field.
• Fire a trigger on Jan 1st and reset sequence in  global field

Glad to hear about your contributions
Harald

[VolkerEckardt · Accepted solution]

Hello Harald,

I would also suggest a trigger to get this implemented. The trigger can also provide the format you want like "nn-mmmm".

Even though we could put the current (last) value into an item, I would probably go with a propery file on server.

I would read the value, increment, use it. And save the latest back into the property.

Additionally, the property file would hold the year, and would compare it with the current, to identify when to reset the counter to 1.

Why a property file? This would not cause any item history entry, and should be also quite fast.

Just be careful with creating 100 of these items in parallel. Then the property file might cause issues.

To guaratie uniqueness we also use a "uniqueness" trigger. This additional trigger will not guaratie gaplessness, but uniqueness.

You may consider this as an alternative implementation option.

Volker

[hbock]

OK, the story about the property file sounds reasonable! I know the properties from Java kind of

int myvalue = System.getProperty("myclass.myproperty");

myvalue++;

System.setProperty("myclass.myproperty",myvalue);

My problem is now:

Where can I find the property file interface for the trigger? There is no method "set(Int)Property()" or similar...

Best Regards

Harald

[VolkerEckardt]

Hello Harald,

you can, in any trigger, call standard Java classes like this:

EXAMPLE:

function writeFile(path, fileName, content, lines)

{

    // create directory if not exists yet

    var basePath = "..\\data\\gateway";

    var directory = new java.io.File(basePath + path);

    if (!directory.exists()) {

        directory.mkdirs();

    }

    // write the file

    var filePath = basePath + path + fileName;

    debug("Writing " + filePath + " ...");

    var writer = new java.io.PrintWriter(filePath, "UTF-8");

    writer.println(content);

    writer.close();

    general("INFO: File " + filePath + " written with " + lines + " lines.");

}

At least, this is the way how I would program this ...

For sure, you can also use any standard Java property file routines - which would handle this task even better.

Volker

[hbock]

Hello Community,

with the help of Volker and some digging in the web I found my solution using Java:

// property object

var props = new java.util.Properties();

function getPropFilename() {

    var fs = java.lang.System.getProperty("file.separator");

    var fn = new java.io.File(eb.getInstallDirectory() + fs + "config" + fs + "properties" + fs + "sequence");

    return fn;

}

function readProps() {

    try {

        // prepare file stream and property object

        var is = new java.io.BufferedInputStream(new java.io.FileInputStream(getPropFilename()));

        // Read the properties file

        props.load(is);

        is.close();

    }

    catch (e) { print(e); }       

}

function writeProps() {

    // prepare file stream and property object

    var os = new java.io.BufferedOutputStream(new java.io.FileOutputStream(getPropFilename()));

    // write back the properties

    props.store(os,"Sequence Number");

    os.close();

}

//////////////////////////////////////////////////////////////////////////////

// Usage

var eb = bsf.lookupBean("siEnvironmentBean");

// ...

// read all specific properties

readProps();

// Fetch the current count and increment it

var counter = props.getProperty("counter","0");

counter++;

props.setProperty("counter",counter);

// write properties back

writeProps();