Hi there AppWorks fans,

Welcome to a new installment of AppWorks tips.

My friend “the analyst” passed my desk again…and guess what!? He has again a “mission impossible” adventure task to do for us as low-code developers. Let me explain the use-case we had a discussion on:

1
2
3

As an administrator, 
I want to have a unique numbering system for my 'Project' entity instance, 
So that I can identify each project on its own.

My first thought: enable the Business ID on the entity and off you go, but WAIT…😫

This time we also get to see the acceptance criteria (which always has a catch!):

• The ID must start with the current year
  • Given: Is the variable: {current_year}_{6 leading zeros}{number}
  • When: The year is 2023 starting with the first entity
  • Then: This will be the ID: 2023_0000001
• The ID must get a reset to 1 each year
  • Given: Is the variable: {current_year}_{6 leading zeros}{number}
  • When: The year is now 2024 where we create the first entity
  • Then: This will be the ID: 2024_0000001

Yes, how is that for a use-case!? Already getting the itch, already getting sleepless nights, sweating, or what else? Lucky for you to have a subscription on this blogpost where you found the answer to another helpless situation! 🤗

Let get right into it…

So, what are we facing here!? Well, it’s a business ID with logic; The first acceptance criteria is possible! Just a separate prj_number property which we fill with a ‘Rule’ BB getting the current year and the Business ID value (I’ll show you!). The second acceptance criteria is a lot harder; Resetting the Business ID is challenging (not impossible!) thing to do. The best chance to make it till the end of this post is to first dive into the logic of the Business ID. It’s an automagically numbering system and by experience I know this has to do with database sequences. So, let’s start…

Ohw yeah…Before I forget, don’t make the mistake of building a numbering system via entity ‘Rule’ logic or BPMs. It’s possible, till you start to create the same entities at the same time with separate users! This is a 100% failure scenario…eventually! Trust me; I saw it already in 2 projects. The only solid way for unique sequential numbering in a database is via sequences!

…

Create a simple entity ‘Project’ with a property prj_name and a property prj_number; Both of type ‘Text’ and default length 64. On the ‘Identity’ BB, we make sure to enable the Business ID with six leading zeros. Simple like this:

I also add the Identity.BusinessId and Identity.Id as column to the lst_all_projects!

Publish the entity and give it a first instance in runtime where you’ll experience a business ID of 0000001. Where is this Business ID retrieved from? Well, it’s just a database sequence which we can find with a query like this (for PostgreSQL!): SELECT * FROM information_schema.sequences;

I use HeidiSQL for my database view!

It will give an extensive list of sequences, but we are only interested in the sequence behind our Business ID; To get a closer look, we first need to grab the entity type ID! How? Well, open the just created project entity and have a view at the URL

1

http://192.168.56.107:8080/home/appworks_tips/app/start/web/item/080027008c65a1edaa184dd3bca38d8b.30/080027008c65a1edaa18513f404a191e/6f08c0bbdc5f3d11b1ef0296caa12a7c

The URL-parts explained:

• This is your runtime host URL pointing to a specific item: http://192.168.56.107:8080/home/appworks_tips/app/start/web/item
• This is the full entity ItemID: 080027008c65a1edaa184dd3bca38d8b.30
  • This is the entity TypeID: 080027008c65a1edaa184dd3bca38d8b
  • This is the entity InstanceID: 30
• This is the LayoutID: 080027008c65a1edaa18513f404a191e
• This is the FormID: 6f08c0bbdc5f3d11b1ef0296caa12a7c

With this knowledge we can extend our query like this (watch the extra q2!): SELECT * FROM information_schema.sequences WHERE sequence_name = 'q2080027008c65a1edaa184dd3bca38d8b';

This last query will give an interesting insight on what is happening here:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

{
  "table": "information_schema.sequences",
  "rows":
  [
    {
      "sequence_catalog": "appworks_db",
      "sequence_schema": "public",
      "sequence_name": "q2080027008c65a1edaa184dd3bca38d8b",
      "data_type": "bigint",
      "numeric_precision": 64,
      "numeric_precision_radix": 2,
      "numeric_scale": 0,
      "start_value": "1",
      "minimum_value": "1",
      "maximum_value": "9223372036854775807",
      "increment": "1",
      "cycle_option": "NO"
    }
  ]
}

NOTE: Be aware what you’re doing in your database and always make sure to have a backup available!

So, we see this sequence starts at 1 and also increments with 1 when the next call is received. Can we also see the current value for this sequence? For sure; Give it a call on SELECT * FROM public.q2080027008c65a1edaa184dd3bca38d8b; to retrieve the last value! How does it get a next value? Well, simple: SELECT NEXTVAL('public.q2080027008c65a1edaa184dd3bca38d8b');

You can call this nextval() function for this sequence a couple of times and check what the new entity instance business ID when you (after the calls), create an instance from the runtime perspective! So, the sequence is fully separated from the entity creation path. The platform just gets the ‘nextval’ at that moment of creation (and skips the rest!)

Can we now also reset this specific sequence? Yes, we can! Have a call like this: ALTER SEQUENCE public.q2080027008c65a1edaa184dd3bca38d8b RESTART WITH 1;; Now for the great question: What will happen in runtime for the entities that already have this Business ID 0000001 applied?. Well, it fails! It fails till a new Business ID is generated which is not yet created. Only, if you have 100.000 items in your database, I wish you good luck! 🤣
We can of course call the ‘nextval()’ so many times; we can even set it to a direct value with an update, but this does fulfill our acceptance criteria for resetting back to 1 after a year! How to solve this? See the next section!

Building a solution for our problem

Where did we leave? Ohw yeah! Our ‘Project’ entity with Business ID enabled…Let’s get back into it! The first thing we do is implementing that ‘Rule’ BB where we apply a value to the prj_number property. Simple and easy like this where we directly save the Business ID with the variable year:

For you to copy from now.getYear() + '_' + item.Identity.BusinessId

Works as expected in runtime after a publication of the entity:

Next acceptance criteria…”Resetting the Business ID value after a year”!

…

My last entity (currently) has now number 2023_0000004; Let’s “fake” our business rule skipping one year with an expression like this: now.getYear()+1 + '_' + item.Identity.BusinessId. After publication and creating a new instance, my next project number will be: 2024_0000005, but this is incorrect according to the specs!

How to start with 2024_0000001? Well, that’s a little database manipulation! First we reset the sequence: ALTER SEQUENCE public.q2080027008c65a1edaa184dd3bca38d8b RESTART WITH 2;. From the previous section, we experienced getting errors in runtime because the Business ID already exists!

Note that we start with 2 for a reason! Number 5 is already created in 2024!

So, let’s update the Business IDs for the old instances!

1
2
3

UPDATE o2aw_tipsaw_genericproject
SET    s_item_business_id = '2023_' || s_item_business_id
WHERE  s_item_business_id NOT IN ( '0000005' );

Number 5 get a special treatment:

1
2
3
4

UPDATE o2aw_tipsaw_genericproject
SET    s_item_business_id = '0000001',
       prj_number = '2024_0000001'
WHERE  s_item_business_id IN ( '0000005' );

A refresh in runtime will show you this result:

Now create that new project entity instance in runtime and guess what will happen!?

NICEEEEEE!! That’s an exact match on our second acceptance criteria. 💪

Try to create new entities with multiple browser tabs at the same time (with several users)! I will say that a duplicate project number is now indeed a “mission impossible”…

That’s it, a sequential “DONE” where we learned all the knowledge about manipulating the Business ID enablement for the ‘Identity’ building block on an entity. I thought it was a challenging task to cover both acceptance criteria, but you see things are manageable once you just take that one step further. Have a great weekend, and see you in the next post, next week.

Don’t forget to subscribe to get updates on the activities happening on this site. Have you noticed the quiz where you find out if you are also “The AppWorks guy”?