Parent Child Departments - Member of

[eddy42]

Our organisation has quite a complex structure (as I imagine many do) in that there are a lot of department levels.

e.g.

Directors

-- Business Services

---- ICT

------ Operations

-------- Service Desk

-------- Field Engineering

-------- Systems

------ Development

-------- Systems

Some areas of the business go to only 2 levels and some go up to 9 levels.

I have mocked up using the parent field on cmn_department in our dev environment and my integration from the external HR system (which feeds in flat file) carefully builds the structure so that you can open a department and see a parent field and a related list of child departments - great.

That is until I need to know who (for example) is a member of Business Services (e.g. for a report or for user critera on the portal) - it would need to include everyone who is in the child departments of Business Services.   I see this as being a bit like roles in ServiceNow but they seem to use a complex inheriting method and this surely is overkill for this.

I thought it may be doable via a dynamic filter (using a script include to add all child group members to an array and returning that) but I can't see any way to get a dynamic filter to use a sepcified field (i.e. pick User isAMemberOfDepartment Business Services) where isAMemberOfDepartment is my script include and business services is picked by the user.

I really don't want to have to add 9 fields to the user form in case they are needed as this will require reporting based on fields like DeptLevel1, DeptLevel2, DeptLevel3 etc.

Has anyone had to model a hierarchical structure like this and if so how did you get around these issues?

[LaurentChicoine]

Just to make sure you understood my proposition, what I propose is a List field, to allow tracking these relations by storing them in the DB (this field would not need to be shown on the form but just to make sure you understand which type of field I'm talking about here is a screenshot:

In your example, let's say you wanted to filter all user in Operations, your Operations department would have a field Child departments containing :

-------- Service Desk

-------- Field Engineering

-------- Systems

And these childs departments would have a Parent departments field containing:

Directors

-- Business Services

---- ICT

------ Operations

These fields would be populated automatically by a script.

So when you want to filter for users in the Operations department you would filter with User.Department.Parent departments contains Operations

[eddy42]

Thanks again,

An interesting and novel approach - and you would keep this updated using a business rule?   So on change of department run through the parents and add them to this list...

I will certainly bear it in mind - it still feels more of a workaround than the solution but if it works...

[LaurentChicoine]

Yes, doing it with a business rule would require a quite complex business rule that would take into account insert, update and delete and cascade the required changes to the fields. A calculated value would be simpler as you could have a script that only deal with the current record (and check their childs and parent) and does not need to care about cascading to other records.

However, from a discussion I had with HI on a previous incident, it seems like calculated values get stored in the DB and you can query them with the value of the calculated field on their last update, but not necessarily the current value. From my understanding, lets say you create a new department, it would query fine, but when you would let say add a new parent in the tree, the new parent would be ignored from queries until the child department record gets updated.

So I would go with business rules.

Being able to create custom filters (other than dynamic filters which takes no parameter) would certainly be better, but I'm not aware of anything like that that is supported.

[eddy42]

It looks like this may be the only option as you say - and I would definitely go with the business rule option if I do it.   As you say I have found calculated values are not reportable anyway (you can't pick them as a field on a report) so this is no good.

I am going to leave this as not yet solved because I want to see if anyone has a solution to this - but as you say I don't think there is any support for this type of custom filtering (amazing really).

If it's not solved I will give you the credit - thanks.

[Bryan Morland]

I know I am responding to an old thread but I was having this same issue, odd, 4 years later it is not resolved.  Here is where I landed, I ended up creating 9 levels called Department Level 1, 2, etc  and then I placed these fields as a Reference to the Department table within the Cost Center table.  I basically made it a Flat File Hierarchy, which you can never go wrong with that.  I am looking forward to an Out of the Box elegant solution in the future.