Hi there “Process Automation” fans,

Welcome to a new installment of “Process Automation” tips.

A fascinating ‘Rule’ building block implementation passed my journey last week. It’s a ‘Rule’ BB of type ‘Uniqueness’ and when you tell me that it is the easiest BB to implement…I agree but continue your read and reconsider your thoughts on this type of building block when you have a heavy loaded production environment to deliver your CAP-package to.

Let get right into it…

Yes, it’s time to hit the wall…Well, we already hit that wall on an interesting implementation on a requested ‘Rule’. Why/How? Well, in our project we have an external client calling an exposed ‘Create’ operation for an entity over the ‘Web Service’ building block. All great and wise, but the external client has an open entrance to endless create new entity instances…Is it then still that wise? Well, that’s a hard conclusion made by our administration team cleaning duplicate instances from production. It’s time for a change…

Let’s start with a simple and basic ‘Case’ entity implementation like this (nothing fancy; You’ll manage it!):

That screenshot is of the new DEV-eXperience (available since 24.2)

After publication, create some entity instances in the “Application Client” (the good old runtime) and make sure to create some duplicates like this:

Our next move is to add a new ‘Rule’ of type ‘Uniqueness’ with the fancy name u_case:

Select both the properties of the case and send out a friendly message:

Be aware that the uniqueness rule can only select from current entity properties! So, parent entity properties are not available; Also related properties, or internal techie properties are not on the list! #FEATURE_REQUEST

Now do a publication and watch this:

Well, hit it and check the logging:

By the way, that’s an enjoyable way of showing the error! 😁

Interesting as the ‘Rule’ BB tries to create a database uniqueness index on our entity table! If you don’t have a clue? Read this.

…

We now have some samples on DEV upfront, but let’s assume that you normally don’t create your own mess in Dev like this, also your testers in TST don’t make a mess like this, and even your key-users (or knowledge workers) don’t make a mess in ACP. In production, we have a special “species” called “the End User”…And guess what!? Yes, my friend…They make the impossible possible and at that moment you will hit the wall hard as your package will not deploy exactly with this constraint error! So, be aware!

…

Ok, let’s clean the one duplicate entry:

Do your publication and you’ll be fine! Creating a similar entity instance will show you the message:

…

BUT…Do I sense a “smell” here? Why are those last four entries not detected as duplicate (see 2 screenshots back)? Well, those are null values in the database and (by default) they are not considered equal! AHA… 🤔

This is a look in the database with HeidiSQL on the entries clearly showing the null values:

Have a look…the table itself shows me indeed a new uniqueness index:

Interesting, but why does the uniqueness rule not have an option to flag the uniqueness index with a NULLS NOT DISTINCT option!? #SUPPORT.

Well, can we update the index ourselves? Interesting question! 🤠

…

Don’t try this at home as I’m on a sandbox VM with snapshots!

How about these SQL statements:

1
2
3
4
5
6
7
8
9

DROP INDEX ei_r_2080027607520a1efaf5613074d33a491;

CREATE UNIQUE INDEX ei_r_2080027607520a1efaf5613074d33a491
ON o2opa_tipsprj_generalcase (case_name, case_subject)
NULLS NOT DISTINCT;

ALTER TABLE o2opa_tipsprj_generalcase
ADD CONSTRAINT ei_r_2080027607520a1efaf5613074d33a491
UNIQUE (case_name, case_subject) NULLS NOT DISTINCT;

Well, the conclusion; It keeps complaining about a syntax error at “NULLS”…weird!? Well, that’s where my database knowledge ends…Also for this post, but we learned again about “a special force” called indexing!

That’s a unique “DONE” where a ‘Rule’ building block of type ‘Uniqueness’ exposes its secrets. The great lesson learned here is to implement this functionality as quick as possible as changing or implementing it afterward can have a direct impact on your production data with a data manipulation action as a result. Prepare yourself for winter as it always happens when the odds are against you. Have a great weekend and I see you next week…Cheers!

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 Process Automation guy”?