Child / Parent Relationship Count and Hierarchy Build

[Masond3]

Good Morning Community, 

I am hoping you can help me.

i am not sure how to describe my problem statement but hopefully with the attached and examples it will make sense 

In my input file i have 4 columns (See below), i would like to produce 2 different analysis based on those 4 Columns.

Tab : Data 

Company ID (Long) (Column A)

Parent Company ID (Long) (Column B)

Company Name (Column C)

Legal (Column D)

Outcome 1 : 

For a given Hierarchy determined by "Column A & "Column B count how many children are associated to the parent, and how many times is the Legal column field not null for that given hierarchy

NB : Please note some Company ID (Long) will not have parent and therefore is the parent in its own right

Outcome  :

Parent Company ID (Long)	Count of Children	Legal Count
001j000000hdRDpAAM	30	2

Outcome 2 :

Using the data from the Data tab, i have documented the current hierarchy (Column A - C). As you can see from this hierarchy it’s a very flat Structure. However within this Hierarchy two record  have been identified as  Legal.

Rules of the Road. 

• For a given relationship hierarchy if there is more than 1 in the legal count, a new record needs to be created at the top of the tree.
• For any record in that relationship hierarchy where the legal column is populated.
• I would like to create a new Parent record ( in my example the naming convention was "New Record -" + Company Name
• I would then like to link the legal Record (child) to the new legal parent Record.
• Once the Legal child / parent relationship is built, the Parent of the legal record would need to be linked to the top most parent.

 Example 

 "Mason Management Limited" is identified legal

Company ID (Long)	Parent Company ID (Long)	Company Name	Legal
001j000000hdPYZAA2	001j000000hdRDpAAM	Mason Management Limited	Y

Creating the Legal child parent relationship 

Name	Company ID (Long)	Legal
New Record - Mason Management Limited
Mason Management Limited	001j000000hdPYZAA2	Y

The final Hierarchy output is in column M-O

Looking forward to your help and response 

[atcodedog05]

Hi @Masond3 ,

I was able to understand the first outcome.

Here is a workflow for the task.

Output:

 Second one isnt clear. Can give more clarity.

Hope this helps : )

If this post helps you please mark it as solution. And give a like if you dont mind 😊👍

[Masond3]

Thank you for your Quick response. I am struggling to articulate point 2. But basically i am trying to re build a hierarchy based on some rules / definitions.

So if break it down in the simplest form ; 
Any time a record has the legal field populated, i  would like to create a new record with is the parent of the legal record 

[atcodedog05]

Hi @Masond3 ,

Here is what i was able to come up with.

Output:

Hope this helps : ) Check and let me know. I have added Null() row just as padding filter it of using Filter tool if you dont need it.

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[Masond3]

Hey, 

This is fantastic You have already solved 3 out of the 4 asks in matter of seconds. 

So in the flow that you have kindly provided  (if you look at the hierarchy tab in the excel (column G- I) you have replicated this 🙂

The next step is, because there is 2 legal companies in the overall Hierarchy , i would like to create a new company (which will become the new Ultimate parent) .(this only applies if there is more than 2 legal companies in a hierarchy ) 

Then with the two new parent records you have created , Link these records to the New Ultimate Parent 

[atcodedog05]

Hi @Masond3 ,

Confused can you show with a example.

[Masond3]

Of Course. 

So Your flow has currently produced the following 

What i would like to do now is to create another Parent (which enables me to group these new newly created records together) (however it should only group the new records together which were in that existing relationship hierarchy)  

My final outcome i would like to achieve the following .

I know this is visual but if you compare the expected hierarchy above to the one below, (basically we are saying for that relationship we are creating 3 new records, the 2 parents for the companies where legal = Y, and a new parent to group them together)

[atcodedog05]

Hi @Masond3 ,

I am able to get the desired outputs.

Workflow

Outcome 2

Outcome 3

------------------------------------------------------------------------------------

Please check and let me know.

Hope this helps : )

If this post helps you please mark it as solution. And give a like if you dont mind😀👍

[Masond3]

Hey 

This looks good . 

I decided to add an additional row to the data set to see what would happen.

Company ID (Long)	Parent Company ID (Long)	Company Name	Legal
0013a00001klsYNAAY	001j000000hdNg4AAE	ABC Ltd	Y

Whilst checking the final outcome i can see that  "ABC Ltd" got grouped to the "Global" record. 

This isn't correct as "ABC LTD" is a new relationship and there is only 1 legal company in that relationship 

in the output , can you add the following 

• Parent Company ID (Long)
• And i think we need to create a column called "External id".

The Concept so far is any time a record has the legal field populated, we are creating  a new record which is is the parent of the legal record 

In the External ID column for the new parents of legals records, can we populate an auto number + the Child company id 

Example 

Name	Company ID (Long)	Legal	External ID
New Record - Mason Management Limited			001+001j000000hdPYZAA2
Mason Management Limited	001j000000hdPYZAA2	Y

Thank you for your time and assistance 

[atcodedog05]

Hi @Masond3 

So where should ABC LTD row come ?

And when you say different you mean Parent ID is different right?

I will look into the external ID