Multi-level relationships

Will Mercer · June 2021

We're looking at possible ways of tracking internal stakeholder interactions in a way that allows us to identify what are of the University the stakeholder falls under. Our structure means we have the University > Schools > Departments > Faculty's. It's possible for someone to be employed at every level, but historically we've only added employment history for the University.

It's still early so I'm not sure exactly where this will go, but I believe the intention will be able to produce data on how many interactions / a list of interactions for specific individuals (which we can already do), as well as for faculties, departments and schools depending on the requirements. I've setup some test structures using relationships, but the issue I'm having is that an employee of a Department has no direct relationship with the School etc.

I am assuming I am not the first person to sit down and think about this, so I'm hoping someone can share some insight on where I'm going wrong, or what I'm not considering.

Stacey Segal · July 2021

Hi Will,

Have you thought about trying the Organizational Hierarchy if that's not already in use?

That might help accomplish what you're looking to do.

Also, just brainstorming here, if you're not using Sites perhaps that could help. It's a hierarchical setup and interactions can have more than one site attached.

Stacey

Steven Finnell · July 2021

Hello Will,

I second Stacey's suggestions. The Organizational Hierarchy seems like it would fit what you're looking for and Sites could as well.

If you're already using both of those for other areas you can always use the structure of the Organizational Hierarchy page to where you have a table with the given level and the id of the level that it would fall under (if it's the top most level then the parent level would be null). By using this structure you can easily navigate through the tree by setting up a recursive CTE.

Here's a link that covers some other possible approaches that you could use to set up and navigate a hierarchical structure:

Will Mercer · August 2021

I contacted BB support about this around the same time and they suggested the following:

Adding relationships as participants in interactions
Configuring a solution in the corporate relationship settings
Use the Interaction categories and sub-categories to identify levels of UCL employment.
Extending the interaction add form etc to include some newly created interaction categories

We had also separately come up with a few ideas some of which are more simple than others, but all of which will require upkeep

Each organisation constituent has an attribute to indicate upwards relationship and then a traditional relationship to each individual
An SDK customisation which essentially replicates the existing relationship structure but to meet our requirements
An extension to the existing relationships query view or a brand new one to allow us to return the required data

I'm not familiar with the the organisational hierarchy, is there any documentation you can point me to? At the moment we have decided to put this work on hold as regardless of the method of implementation it will be nearly a full time job to keep the structure up to date and therefore useful. We felt that none of the BB solutions felt like they met requirements, although it was useful to see the suggestions and that the solutions we came up with could work, but were often fairly complex.

Steven Finnell · August 2021

Hey Will,

Organizational Hierarchy is found under the administration tab in the Data section. When you click on it you may or may not see a tree already set up showing how Fundraisers are assigned in a hierarchical format. You'll want to select the Edit action to begin working with it. In the tree view that pops up you'll be able to add positions under your organization as well as under other positions. You can also add fundraisers under positions.

Now the only down side to this approach is that you'd need to set everyone up as a Fundraiser which in I don't think is what you'll want.

Have you thought of setting up Organization records for the different levels (i.e. Department, School, University) so that when you look at the Corporate Structure that it makes logical sense from your perspective.

For Example:

Organization
- School A
  - Department 1
  - Department 2
- School B
  - Department 3
  - Department 4

At that point just make it best practice to maintain the relationships at the lowest feasible levels possible (i.e. you would want faculty members to have a direct relationship to the correct corresponding Department and not the Organization unless that's what makes the most sense). Then I would swap out the dataform with a custom version to allow Organizations (as well as Individuals and Groups) to be the owner of an interaction. Then say you wanted to pull all interactions that happened at different levels throughout your Organization you could use something like a recursive CTE to pull that by navigating up or down the tree of relationships.

Will Mercer · August 2021

Hi Steve,

Thanks for pointing me in the right direction, but you are correct in assuming that we would want to avoid setting up all employees as fundraisers to avoid the additional complexity this would cause.

We were definitely intending on setting up each level as it's own constituent record in order to be able to accurately have the relationship, but I had not thought about the potential of using a recursive CTE to expose the data. In fact I've never used a recursive CTE. Quick google and it looks like it could be a good way to tackle this almost regardless of how we actually set this up.

As I mentioned this is on the backburner at the moment, in part because I explained to the team requesting it how much admin would be involved. But I'll bookmark this so I can come back to it when I start working on it again.

Thanks for your help!

Multi-level relationships

Comments

Categories