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.
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
Solved! Go to Solution.
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 😊👍
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
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 😀👍
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
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)
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😀👍
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
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
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