Reduction of Column Count from dynamic form
L_123
✭✭✭✭✭
Has anyone found a way to reduce the column count from a long dynamic form? I was thinking of using conditional logic on a separate form with a copy workflow to move data, but I'm not confident it would be reliable. I'm running some tests now, but if anyone already has a solution I would be very happy to hear about it.
0
Answers
I would think that even the copy row won't work because it automatically inserts the missing columns into the destination sheet.
thinkspi.com
I've found a partial solution, still testing.
Start with 10 columns
Reduce to 5 column using conditional index formula
Archive to remove formulas
I am still testing though, I'm worried the program won't catch up if the form is submitted in succession, so i'm thinking of adding a unique index to the equation, checking the archive for matching ones, then pulling potentially multiple results from the 10 columns sheet into the reduce to 5 sheet that can then be copied over to be archived
It looks like your setup is under the assumption that columns 1 and 2 are grouped together, 3 and 4, 5 and 6, 7 and 8, and 9 and 10 in that if column 1 is blank then column 2 will be filled and if column 1 is filled then column 2 is blank kind of thing. What if columns 1 and 2 are both filled? Then you'd only be pulling column 1 and column 2's data would be lost.
thinkspi.com
Yeah, I have conditional logic in a form that forces them to only fill out one or the other for the final sheet, and it is about 65 columns I am trying to reduce to about 10. Depending on the options they choose they will follow a path down the form, and I will set up the formulas to pick the chain they went down. I'm less worried about the formulas, and more worried about loss of data from the workflow kicking in at the wrong time when the formulas haven't updated yet.
Ah. Ok. As long as that logic is built in, then it looks like you have yourself a solution.
Those INDEX formulas are some pretty slick thinking by the way.
I'm not sure I follow this though:
"I'm worried the program won't catch up if the form is submitted in succession"
Do you mean if for example I submit the form twice in a row, or...?
thinkspi.com
Yeah that, and smartsheet tends to save resources by not updating sheets that aren't opened. The conversion sheet is a middleman that shouldn't ever be opened, so if the send catches when it hasn't updated, or to many submissions are sent in at once, then i'm worried it won't keep up. It's taking ~20 minutes for the workflow to catch up to a single submission right now.
and thanks, right now they are just a placeholder. I'll probably do a more standard horizontal collect for the final solution to simplify it since there will be a lot more columns and complexity I will have to deal with
~20 minutes? Ugh.
As for the automation triggering before all data is updated, I have found that using a checkbox column with a basic nested IF to only check the box once all columns are not blank and then triggering the automation off of that seems to be pretty reliable.
=IF(AND([email protected] <> "", [email protected] <> "", [email protected] <> ""), 1)
thinkspi.com
That's an interesting solution, thanks. I'll definitely include that check in my final draft if I go this route.
I'm not sure if i'm going to pursue this or not, it's just to slow. this is to track maintenance items, and I don't want there to be the potential for long wait periods prior to alerts being sent out.I may have to separate the 1 large form into several other ones, or simply use a different software to get what I want.
Yeah. I can imagine that many columns can bog things down with this type of setup.
And I tend to use the above trigger for automation as much as I can. In my mind it is more reliable to have a formula run the check and then create a very basic automation based on that than it is to build out more complex automations that may or may not trigger before the rest of the data is updated.
That and formulas are MUCH easier to test than automations and having to wait on emails to come through or rows to move or constantly hitting the refresh button on your browser. Hahaha.
thinkspi.com
I 100% agree. I wish there was more visibility on where the workflows are at in their process, and if they have updated. Personally I feel like workflows sending update requests/notifications/alerts should be included inside of the activity log. I can troubleshoot formulas all day and usually the wait is on me. When you troubleshoot workflows you have a delay for every test where you are just there sitting praying it works.
+1 for all of that. Better visibility, activity log, troubleshooting...
thinkspi.com