First I'll share how we do this manually inside smartsheet in case others are trying to do the same thing but then I'll make a feature request for this to be done automatically.
Many times we want to make a sheet available to users with parent/child data. SmartSheet's hierarchy is great for this, but getting the data from database to smartsheet can be a little tricky. Here's what I currently do. I'll use the concept of Transaction Header and Item detail.
1. First we query the data so that the result set contains both header and detail rows with the transaction # on all rows. The Item # column contains '0' in the header row. The result set is dumped to an excel sheet.
2. We import this Excel file into Smartsheet choosing the transaction # as the Primary
3. Then we filter out all Item# = 0 rows. This hides the headers and shows only detail lines
4. We select all rows and indent
5. Remove filter and voila, if all the rows were in the correct order the details are now children of the header rows.
Some Additional Cleanup
1. Sometimes we'll collapse all and change the ITEM# column of all the parent rows to a formula like =COUNT(CHILDREN()) which changes the value in ITEM# column of the header row from '0' to the count of items.
Feature Request:
What would save us an enormous amount of time is if we had an option on import to have rows with duplicate primary column values automatically indented as a child under the first row of the set of duplicates.
Or even how about the ability to indicate secondary and tertiary columns which would be used to keep indenting to allow for children of children, etc.
This would allow us to instantly create a hierarchical sheet from flat file. Awesome right?
