Question on creating template tools that automatically feed and update rows to a central repository

J M · 09/22/21

Our department was recently running into the issue of housing data and tools in consistent manner that allows for accurate department wide reporting without the need for additional administrative activities and duties to keep it up to date

As part of this I was trying to figure out a way to accomplish the two items below through Smartsheet.

Problem 1

Build template tools that would be copied and used for one specific project. These would have automation prebuilt in that can automatically feeds any new entries made to the copy of this tool into a collective repository sheet. This would also need to allow for the updating of these repository rows if changes are made in the project specific copy (does not create an entirely new entry in the repository each time a change is made to a template row as this will lead to duplicate entries).

Current Solution to Problem 1

My current attempt to solve problem one was creating template with an automation workflow that says

If row or column changes----->Copy Row to Master Repository

The problem with this per the below image is that this creates duplicate entries in the repository for the same entry. The goal would be to have these upload the row to the repository as soon as information is entered to the template, and then update that single row in the repository as updates are made to the single row on the template.

Problem 2

Set a template up so that copies made of the template can only be saved/copied to a specific folder/workspace location. The hope for this would be to avoid having the different projects copies for these tools spread all over Smartsheet and instead they would always be collected in the same folder/workspace.

Solution to Problem 2

At this time I have not found any solutions that address the issue for problem 2

Thanks for any assistance that can be provided!

Genevieve P. · 09/24/21

Hi @J M

Problem 1

The Copy Row automation copies rows statically and one-time, as you've found. You would potentially want to change the trigger to only be when rows are created, not when they're updated, to avoid duplicate entries. However as the information is changed in the source sheet it won't update the secondary sheet. You would need to manually create cell-links or cross-sheet formulas looking into that first sheet to have it auto-update.

I would actually recommend using a Report instead of a second sheet. Reports gather rows from multiple sheets and will display data in real-time, meaning if you update the source sheet row then it will automatically update the row in the Report as well. Additionally, if you make an update in the Report, this will update the underlying sheet. See: SmartStart: Reporting

If your concern is having rows/sheets automatically added to a Report, then you can have the Report looking at an entire Workspace instead of selecting individual sheets. Then as new sheets are created and put in this workspace they will be automatically brought into your Report (based on your Report filter criteria). See the "Tip" in this Help Article: Select Source Sheets for Report Builder

Problem 2

In regards to Problem 2, there currently isn't a way to force-save a Smartsheet item in one location. If you set up a Template type of Smartsheet item, then the default location for created sheets will be in the same folder/workspace as the Template, but users could choose to save this in a different location if they wanted to.

I would recommend ensuring that your Template item is stored in the same workspace that you want the sheets to be stored in. Then add clear instructions, perhaps even in the template name, to make sure your users know where to keep this sheet (especially if your Report is looking at this location).

Cheers,

Genevieve

Question on creating template tools that automatically feed and update rows to a central repository

Answers

Categories