Best Of
Auto-Create Child Rows WITHOUT Bridge or API
This initial solution is based on form submissions being made and each form submission triggering the creation of a set of "template" child rows per submission WITHOUT the need for a 3rd party app, the premium add-on Bridge, or the API.
So if your form submissions look like this:
Your working sheet could look like this:
There are a number of modifications that can be made such as the number of children, the number of levels, column data in the child rows, etc.. The below is just the basics to help everyone get started.
So here we go...
On the sheet where your form submissions will be made, you will need two sets of columns. One set that has the data to be pushed over, and another set that collects the data from the form submissions. You can leverage custom field titles in the form so that the form looks decent but the sheet stays easier to organize/manage. You are also going to need an auto-number column.
At the top of the form sheet and utilizing the primary column, you will put in your parent/child "template" set.
There are a number of formulas that can pull in the most recent form entry into that parent row, but for the sake of this example I have used
=INDEX([Form Field A]:[Form Field A], MATCH(MAX([Auto-Number]:[Auto-Number]), [Auto-Number]:[Auto-Number], 0))
Of course that same formula is tweaked and used in the B and C columns pulling from the corresponding Form Field columns.
The final step is creating a Copy Row automation. Set it to trigger whenever the A column changes (or a different column or combination of columns used in the top row of the template to accommodate your needs). The action would be to Copy Row to your Working Sheet.
Additional screenshots are below. This is just the base idea and can be expanded on in a number of ways, but accommodating every single possibility just isn't feasible in a single post.
Paul Newcome
Re: Can't a System Admin recover a deleted sheet for someone?
@Matt Rasmussen if the user is active and their sheets/resources have not been deleted, a Smartsheet Sys Admin CAN transfer their owned sheets to someone else. The kicker is that they ALL have to transfer to the same person. Once that one person owns the other user's sheets, they can individually reassign ownership to other licensed users.
This is done via User Management in the Admin Center. Go to Manage Licensed Users, and Transfer Owned Items is a menu option for a user.
Jeff Reisman
Re: Automation Workflow Not Running
Hey @gjohnson.pcpro
Using a cell looking at a cross-sheet reference will have the same issue, as source of that cell's change is the same thing.
What I would likely do here is set up a Time Based workflow instead, running once a day at the end of the day (e.g. 9pm) where it adds the date if the Current Stage is your criteria and the Date column is blank. Will that work for you?
Cheers,
Genevieve
Genevieve P.
Re: Automation Workflow Not Running
How are the cells changing in your Current Stage column?
Actions that modify a sheet (including Record a Date) cannot be triggered by inbound cell-links or cross-sheet references. The Note on this Help Article has more information: Action blocks: Specify what automation is triggered
Genevieve P.
Re: Multiple Approvals Based on Selection in Multi-Select Field
Hey @UrbanLawrence
Are you using the same Approval column for all your different requests?
You'll want to set up individual columns to record the different types of approval. That way it won't be registered as "Approved" for that row, since the associated column is set to "Submitted". Otherwise, the "Approve" in a single column will assume it's an Approve for all workflows - does that make sense?
Cheers,
Genevieve
Genevieve P.
Re: NEW! Dashboard center alignment and mailto links!
The mailto is pretty nice, any chance we can get this same functionality on forms too?
Samuel Mueller
Re: Folder-level permissions
This is a greatly needed enhancement. We manage very large Programs through Control Center with multiple workstreams and projects under that Program Workspace. The folder tree and artifacts are extensive with many being confidential. Like other commenters, because of the time-consuming process of giving access at each artifact level, we end up giving most people access to the entire Program workspace and confidential data they really should not have access to.
Brenda Merrill
Re: Sorting
Hi @Liz_Snavely I believe this is because it is treating those beginning with 0 as text rather than numbers.
If you add a leading 0 to them all they do sort correctly.
Hope this helps!
John
John_Foster
Re: Divide By Zero Error with Average
@Kariv You need to get the error removed from the cells you are referencing.
Paul Newcome
Re: Control Center - Cannot provision some projects using our blueprint
Hi @summetg
The first thing I would do is verify that parent project value in the intake sheet matches the project in Manage Program > All Projects.
Then the second thing to check is that the Parent Project Selection column in the blueprint is pointing to the correct column on the intake sheet.
If both of these things are correct, I would recommend reaching out to Smartsheet Support with additional screen captures of your blueprint configuration and intake sheet so they can troubleshoot with you in a private channel.
Cheers,
Genevieve
Genevieve P.