Unique Fields combination Trigger with one empty field

Hi,

Null value validation depends on "Ignore combinations where at least one field has no value" option in trigger definition. If you check this option the fields having null will be ignored from unique field combination check.

Please see if you can use this option to resolve this problem

Thanks and Regards

Pradeep

What does it mean then, ignored?

The check for uniqueness will not be done at all?

I want the check to operate all the time, I am not ticking this option right now. And I want to know what is the trigger supposed to do when I don't tick this option and one field is null or empty.

Thank you

Mery

Hi Pradeep,

Ok I am not ticking the option "Ignore combinations where at least one field has no value” , as I want the check to be performed in the case of one empty field as well, I want it to perform all the time basically.

It is doing what you explained properly. I just wanted to check what behavior should be produced because I did some testing at the beginning and was not doing that check.

Now it looks fine. Maybe it was something else.

Thank you :)

Mery

Hi Pradeep,

I found the case where unique fields combination trigger was not working properly.

So here is the situation again :

I am running the Unique field combination Trigger on two fields : A (lookup field to parent object/hierarchy relationship) and field B (Text Area). The field A can be empty as a record can be top level one, i.e no parent. I am not ticking the option is the trigger, as I want it to run all the time, even with empty fields.

The unique field combination is working fine, until I delete one record.

Let's say I have myRecord with TextArea='1', I delete myRecord. And after that I try creating another record with TextArea='1'. Normally there is no more record where TextArea is equal to 1. So the trigger shouldn't say anything.

Well it is not allowing me to create that record, returning the error of that trigger !

Another annoying thing, if I try to create mySecondRecord with TextArea='2', knowing the value '2' already exists, it will let me do so.

So the Unique Field Combination trigger is simply not working after I delete one record.

Can you give some help with this please? Is that a possible bug or am I doing something wrong?

Thank you,

Mery

Hi Mery,

I am a developer and I am very interested in the "Unique Fields Combination" trigger bug you found.

I'd like to reproduce it but Rollbase should not (and did not) allow using TextArea filed in this trigger.

What Rollbase version and what database do you use? Is it an external or legacy object?

Thank you,

Yuriy

Hi Yuriy,

Sorry for the confusion, it is not a textArea field, but a picklist field. So the unique field combination is between a picklist field and a lookup (to parent/hierarchy relationship) field.

But still, I checked and we can have textArea fields in the 'Unique fields combination'.

We are using version 3.2 of Rollbase. And database is RB.

We are not using any external objects. I am not sure what legacy objects means. But we are using standard objects.

Thank you

Mery

Hi again,

I can confirm the unique field Combination is not working when one of the fields is empty.

It is working at times but not always. And you can actually see it in the trigger debugger that the trigger hasn't run.

Waiting for your help guys.

Thanks

Mery

Hi Mery,

Thank you for the update. I was able to reproduce the problem. Unfortunately as you said sometimes the trigger works and sometimes it doesn't. However now it is my problem to find out when and why it happens.

Thank you again.

Hi Mery,

I have found the reason.

When Rollbase deletes an object it saves it in the Recycle Bin but keeps its relations in case a user would like to restore the deleted object.

This relation doesn't display in the view because the parent doesn't exist

When a new object created the trigger ignores the existing record because it still has a parent id in the relation field and a new object has this field empty.

This is a bug. The trigger should be smart enough to analyze not just the relation field but also whether the parent object really exists.

We will fix this bug in the new release.

Meanwhile I would recommend to create additional trigger "Update Field Value" to clean the parent relation field in the child objects when an object deleted.

The trigger script may look like this:

{!#LOOP_BEGIN.R924998}

rbv_api.setFieldValue("ancestor", {!R924998#C.id}, "Pancestor", null);

{!#LOOP_END.R924998}

"ancestor" -  the name of the object I used

"Pancestor" - the parent relation field name

R924998 - parent-child relation

Thank you,

Yuriy

Hi Yuriy,

Thanks for this clarification!

Just one thing : The record I am deleting is one with no parent, so is Rollbase keeping its relationships even if this particular record does not have any?

What I am saying, is that this record has already the field parent, as empty, if you see what I mean.

I understand your point, it absolutely makes sens when deleting a record that has a parent, so when deleted, the relationship to parent is not, and this is what is messing up things when creating a new record.

Thank you for this !

Mery

Hi again Yuriy,

Just checked something and this might be interesting for you to consider :

For a Top Level Record, i.e one with no parent, in the Database, the field parent is 'NULL'. And sometimes, after updating the record, it goes to 0, but not always.

We had this issue sometime back, and worked around it. But it seems that when a record is created with an "empty" field in Rollbase, it is first created with a value "NULL" in the database, then only after updating it, it goes to "empty".

We tried to reproduce this behavior for our Hierarchy relationship, but as I said, it is 'NULL' first, then goes to '0' or stays 'NULL'.

Is that something you were aware of ? We think it has something to do with the issue we are facing now with the unique field combination trigger.

Thank you

Mery

Hi Mery.

I probably was not clear. In the case of Parent - Child relation the child record has a reference to Parent because a child has only one parent and a parent may have many children. So when we deleted a parent object it's id is still in the child record.  That explains the case when you can create a new record (A2 in your example) even if such record already exists.

I am sorry, I missed the A1 case when you deleted a record but could not create another one (A1 case). So far I can't see how it happens but I will do more experiments.

I will investigate the parent NULL - 0 case as well. It should be always NULL if parent doesn't exist. Nevertheless 0 value usually does not cause a problem since no object could have 0 id.

Thanks Yuriy for the reply.

Ok please let us know.

Tahnk you :)

Mery

Hi Yuriy,

Thank you for the reply.

1- Ok for the first case, but in my case, I was getting that bug without even restoring the deleted object. Let me try to reproduce it again and will give you more details hopefully.

2- Ok for this case too. I understand. Fortunately we do not have to deal with this in our case, because we are not allowing deleting an Item that has children.

For the 0 value, we could not really reproduce the scenario of obtaining 0 in the parent field.

But what we did, is attaching this item (with 0 value in the parent field) to a parent, then detaching it, and then this value went back to NULL. So this is how we fixed it. But sorry we could not go back to how we get that 0 from the beginning..

Tahnk you

Mery

Hi Yuriy,

I just have another scenario to suggest, maybe this is another case of the bug:

Let's say we have two records 1 and 2. Record 1 has field combination (x,y) and Record 2 has field combination (x,z).

If I go to record 2 and update the second field from value 'z' to value 'y' , it is allowing me to do so, which shouldn't be allowed again. Now I have both  Record 1 and Record 2 with the same combination (x,y).

Can you please try this case as well? And confirm what behavior it is having in this specific case please?

Only thing I can see from the trigger debug, is that the unique field combination is just not running, but it should as the timing is Before Create and Before Update.

Thank you

Mery