Solution for Table Join-Like Behavior

999

I'm looking for some ideas... Being that Smartsheet is a project management software, I imagine there is at least one person doing something like this!

Some Background

For apartment complex renovation projects, we have a list of tasks that are repeated across many apartment units. Inside of that task list, there are also higher-level task categories that we need to have grouped. For example:

• Apartment 101
  • Demo
    • Demo Kitchen
    • Demo Bathroom
  • Mechanical
    • Replace all switches and receptacles
    • Install new furnace/AC
    • Air seal ducts
  • Drywall
    • Hang new drywall
    • Tape and mud new drywall
    • Patch/repair existing drywall
  • Etc...
• Etc...
• Apartment n
  • Repeat tasks
• Apartment 102
  • Repeat tasks

The Issue

If we realize we need to add/remove tasks or add/remove units, someone will have to go in and adjust the nested tasks within each unit row or adjust the rows of units. At a project with 30 units, this isn't a big deal. But with hundreds, it becomes a bit unrealistic and increases room for error.

Sometimes, certain apartment types need different tasks done--ADA units need grab bars in bathrooms, 3-bed 2-baths need something else done to the second bathroom, etc--so the tasks should dynamically reflect that. Like here, the Applies to column would dictate units of that type would get those tasks underneath it:

My Initial Solution

My plan was to have 3 sheets:

• Unit List
  • Columns: Unit Name, Unit Type (single-select dropdown)
  • Manually populated rows
• Task List
  • Columns: Task, Unit Type (multiple-select dropdown), required Gantt Columns
  • Manually populated rows
• Tracker
  • Basically a combination of columns from the other two sheets
  • Automatically populated rows, ability to track progress of tasks (rows) by manually editing some columns

Then use the API to combine the data (kind of like a SQL Inner Join for those familiar) from the Unit and Task List sheets and then populate the Tracker sheet with that combined data. Pulling out the data and stucturing it properly through code is no problem at all. The issue with the API approach is how the API limits you when pushing nested data back in.

To make it even more complicated... the Tracker sheet will have additional columns and we'll actually use this sheet to track progress of the project. That means the task rows here underneath each unit can't just be repeated references. For example, changing a % Complete column value of a drywall repair row underneath Apartment 101 shouldn't also change % Complete of drywall repair of Apartment 102.

Sorry for such a long explanation... Hopefully my explanation makes a little sense! Wondering if anyone knows any native solutions with cross-sheet references and formulas.

markkrebs

for removing rows, one idea could be to have a checkbox column "remove?" with a column formula that looks at a sheet summary field with the attribute(s) that determine removal. This coupled with a move row automation could then move them off into another dumping ground sheet.