Help with Complex Hierarchy Functionality

Josh Reed

Sorry the title of this question is not great.. Here's the abstract:

I'm attempting a build for a large R&D department... My company built a similar system for internal use albeit in a slightly different way.

We have two "Trackers" which are just sheets that are repositories for tons of information. One is an "event" tracker and the other is a tracker that houses all of the "expert contracts" pertaining to that event. So, when a new event is submitted it is given an unique identifier. Then, when individual contracts are added to the sister tracker the submitter is prompted to enter the corresponding "event" ID. This helps with reporting and metrics later on down the road. It's much more complex than that but that is the gist.

For this new project I've essentially rolled the two sheets into one to simplify their processes... Per the request of the manager of that dept. So far my idea has been to utilize hierarchy to distinguish between "events" and their associated individual contracts. "Requesters" will fill out a form that populates the first part of the sheet(tracker), this is high level event based information. I have frozen the pane at the end of the "event" section so it is easy to navigate to the beginning of the "expert contract" section. Event level submissions are the ancestor rows. Under each ancestor row we will add rows for each "expert" contracted for the parent row event. I've utilized a basic formula to conditionally format ancestor rows to indicate which are events and which are contracts.

Sounds fine right? Tons of issues. For one, when items are moved to an "archive sheet" as it inevitably fills up, hierarchy is not maintained. One solution... (forgive my Smenglish [smartsheet mixed with plain english logic as I am new]) introduce a new column that is populated by a formula which says if a parent exists then insert text from "Event ID" column. Then an additional checkbox column or some way to indicate that this row was a contract and not an event. That way it is still tied to the event even when hierarchy is removed and it is easy to see quickly whether it was a contract or an event. Hope that makes sense, I'm very open to suggestions on how to refine that.

Second issue that comes to mind is that the user entering all of the individual contracts will have to remember to indent them once there is data in the row. They will also have to remember to check the checkbox column. There will not be many users that are involved in the actual tracker as the customer only has access to the intake form, but I am still not confident they will follow these special and not so intuitive instructions. Once this build goes live I will no longer have access to it as it naturally will house incredibly sensitive information. -- I'm thinking maybe I make the checkbox also a formula that will check the box IF there is data in columns that for sure are only used at the contract level and will be blank for the event rows.

I'm sure there's more but I've rambled on, thank you to whomever takes the time to read this. Please ask clarifying questions and provide any suggestions if anyone has attempted to build something like this already.

Much appreciated,

Josh

Andrée Starå

@Josh Reed

Happy to help!

Yes, the Children will follow the Parent when it's moved, and yes, it works the same when it's automated.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Josh Reed

I hope you're well and safe!

I'd recommend focusing on the parent/child and hierarchy. Then if they want to archive something, they would check a box or similar on the parent level, and then the children will be included, and they'll easily see different groupings.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Josh Reed

I think I understand. Checked box will indicate parent and they will know the children belong to that parent based on a shared "event name" or ID number of some kind. . . I was going to ask if children rows will follow a parent row if it is moved to another sheet but based on how it highlights i believe it would. I can easily test this of course.

Another question would be if this same effect is achieved via automation... so for instance I could say on a weekly basis move all rows with "status" marked "closed out" to archive sheet.. now if that status is only populated on the parent row, would it move the children as well?

Thank you @Andrée Starå !

Andrée Starå

@Josh Reed

Happy to help!

Yes, the Children will follow the Parent when it's moved, and yes, it works the same when it's automated.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!