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
