How to set value in one field when insert/update item in custom (same) table

[Manikandan J R]

I have a custom table called Department. with fields like ID, Name, Code, and Parent.

When I insert/update any item I should query the same Department table and update Parent field if the Code  starts with any existing item

Ex:

Existing item

    ID: 101

    Name: Dept 1

    Code: 401-101

    Parent: Empty

Now insert new value

    ID: 102

    Name: Dept 1 - Child

    Code: 401-101-102

    Parent: Empty

Now I should updated Parent in new item as "Dept 1" since this is child for existing item

Prefer to do this via BR, since we have api updates from background

[simonezini]

Hi @Manikandan J R ,

you need to create a before Business Rule while you insert the record on Department table.

The condition should be for example that "Code" field should not be empty, and the code will look like this:

//Rebuilt first two number of Code Field
var arr = current.code.split('-');
var parentCode = arr[0] + '-' + arr[1];

var grDep = new GlideRecord('your_department_table');
grDep.addEncodedQuery('codeSTARTSWITH' + parentCode);
grDep.query();
if(grDep.next()){
    current.parent = grDep.sys_id;
}

 Remember to not use "current.update()" while using a before Business Rule (otherwise it will trigger again creating a loop).

Obviously, this won't work if the format of the Code field or the logic behind it will change...

Hope this helps

Regards

Simone